PHP and MYSQL Database

Compiled By:
Hayelom M. Gebrye
Contents
 MYSQL Functions
 User Defined Function
 PHP MySQL Connection
 Integrating Web Forms And Databases
 Displaying, inserting and deleting Queries
In Tables
MYSQL Functions
 A function is a block of statements that can be
used repeatedly in a program.
 A function will not execute immediately when
a page loads. A function will be executed by a
call to the function.
 Inbuilt Function
 User Defined Function
 Inbuilt Function:
 There are several Inbuilt functions are available in
php.
 Generally all its are single line functions.
User Defined Function
 A user defined function declaration starts with the word "function":
 Syntax

function function_Name()
{
to be executed;
}
 Function names are NOT case-sensitive.

<?php
function fun()
{
echo "Hello world!";
}
fun(); // call the function
?>
PHP MySQL Connection
 PHP provides mysql_connect() function to
open a database connection.
 This function takes three parameters and
returns a MySQL link identifier on success or
FALSE on failure.
 There are some MySQL functions for database
connectivity such as
 Mysql_connection()
 mysql_select_db()
 mysql_query()
 mysql_result()
 mysql_close()
PHP mysql_connection() Function
 PHP mysql_connect() function is used to connect with
MySQL database.
 It returns resource if connection is established or null.
 Syntax of mysql_connection

connection mysql_connect(server, user, password);

server:
 Optional - The host name running database server.
 If not specified then default value is localhost:3306.

User:
 Optional - The username accessing the database.
 If not specified then default is the name of the user that
owns the server process.
password :
 Optional - The password of the user accessing the
database.
 If not specified then default is an empty password.
PHP mysql_select_db() Function
 The mysql_select_db() function sets the active
MySQL database.
 This function returns TRUE on success, or FALSE on
failure.
 mysql_create_db() function attempts to create a
new database on the server associated with the
specified link identifier.
 The mysql_select_db() function is used to change
the default database for the connection.
 Syntax of mysql_select_db() function
mysql_select_db(connection, name)
connection:
 Required. Specifies the MySQL connection to use

name:
 Required. Specifies the database name
PHP mysql_query() Function
 mysql_query() Function performs a query
against a database.
 This function selects a database and executes
a query on it.
 If the optional link identifier isn't specified, the
function will try to find an open link to the
MySQL server and
 if no such link is found it'll try to create one as
if mysql_connect() was called with no
arguments.
Cont..
 The mysql_query() sends a query to the
currently active database on the server that's
associated with the specified link identifier.
 This function returns a resource identifier or
FALSE if the query was not executed correctly.
 Syntax of mysql_query() function

mysql_query(database connection, Sql

query);
Example:
$conn = mysqli_connect($host, $user,
$pass,$dbname);
$sql = "CREATE DATABASE myDb";
mysql_query($conn, $sql);
PHP mysql_result() Function
 The mysql_result() returns the contents of one cell from a
MySQL result set.
 When working on large result sets, the mysql_result() should not
be mixed with calls to other functions that deal with the result
set.
 Syntax of mysql_result() function

mysql_result ($result , $row , $field);

Parameters of mysql_result
result:
 The result resource that is being evaluated. This result comes
from a call to mysql_query().
 row:
 The row number from the result that's being retrieved. Row
numbers start at 0.
 field:
 The name or offset of the field being retrieved.
 Example:
 mysql_close()
 This function used to disconnect Mysql database
Integrating Web Forms And Databases

 PHP has a pretty straight forward method to

working with MySQL databases.
 To establish a connection to the MySQL
database, but SQL which is the language used
to put data in and get data out of a database.
 There are five steps to make PHP database
interaction
 Create a connection
 Select database
 Perform database query
 Use return data
 Close connection
Cont..
Create a connection
 It is very important when you want to start a
dynamic website to create a connection with your
SQL (we are talking about MySQL) by using
mysql_connect() function.
 In mysql_connect() arguments should be a string
and it’s important to use die() function with
mysql_error() function to check if there is a
problem with the connection or not.
Select database
 Now we have to select the database which we are
creating and saving our data by using
mysql_select_db() function which the arguments
are the name of the database and connection we
made earlier.
Cont..
Perform database query
 In this step, we have to select out data from
the database and bring it into our web page.
 The best decision to use mysql_query()
function which it will Send a MySQL query with
2 arguments as displayed in the above code.
Use return data
 To display the result on your web page, you
have to use while() loop function in addition to
mysql_fetch_array() function which fetches a
result row as an associative array, a numeric
array, or both.
Cont..
Close connection
 To close connection, it is better to use
mysql_close() function to close MySQL
connection.
 This is a very important function as it closes
the connection to the database server.
 Your script will still run if you do not include
this function.
 And too many open MySQL connections can
cause problems for your account.
 This is a good practice to close the MySQL
connection once all the queries are executed.
Example
<?php
// Five steps to PHP database connections:
// Create a database connection
$connection =
mysql_connect("localhost","root","myPassword");
if (!$connection) {
die("Database connection failed: " .
mysql_error()); }
// Select a database to use
$db_select =
mysql_select_db("widget_corp",$connection);
if (!$db_select) {
die("Database selection failed: " .
mysql_error());
}
Cont..
// Perform database query
$result = mysql_query("SELECT * FROM
subjects", $connection);
if (!$result) {
die("Database query failed: " .
mysql_error()); }
// Use returned data
while ($row = mysql_fetch_array($result)) {
echo $row["menu_name"]."
".$row["position"]."<br />"; }
// Close connection
mysql_close($connection);
Displaying, inserting and deleting Queries In Tables
 Select Data From a MySQL Database
 The SELECT statement is used to select data from one or more
tables. SELECT column_name(s) FROM table_name
<?php
$servername = "localhost"; $username = "username";
$password = "password"; $dbname = "myDB"; // Create
connection
$conn = new mysqli($servername, $username, $password,
$dbname);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error); }
$sql = "SELECT id, firstname, lastname,Address FROM
student_info"; $result = $conn->query($sql);
if ($result->num_rows> 0) {
while($row = $result->fetch_assoc()) {
echo "<br> id: ". $row["id"]. " - Name: ". $row["firstname"]. "
" . $row["lastname"] . " ” . $row["Address"] . "<br>"; } }
else {
echo "0 results";}
$conn->close(); ?>
Cont..
 Insert Data into MySQL Database:
<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'mydb');
define('DB_USER','root');
define('DB_PASSWORD','');
$con=mysql_connect(DB_HOST,DB_USER,DB_PASSWORD)or
die("Failed to connect to MySQL: " . mysql_error());
$db=mysql_select_db(DB_NAME,$con) or die("Failed to connect to
MySQL: " . mysql_error());
$name = $_POST['name'];
$address = $_POST['address'];
$gender = $_POST['gender'];
$sql="INSERT INTO student_info(Name,Address,Gender)
VALUES('$name','$address','$gender')";
mysql_query($sql)or die(mysql_error());
echo "Successfully updated database";
mysql_close($con);
} ?>
Cont..
 Delete Data from MySQL Database:
 PHP mysql_query() function is used to delete record in a table.

<?php
define('DB_HOST', 'localhost');
define('DB_NAME', 'mydb');
define('DB_USER','root');
define('DB_PASSWORD','');
$test = $_POST['id'];
$con=mysql_connect(DB_HOST,DB_USER,DB_PASSWORD)
or die("Failed to connect to MySQL: " . mysql_error());
$db=mysql_select_db(DB_NAME,$con) or die("Failed to
connect to MySQL: " . mysql_error());
$sql = "DELETE from student_info WHERE id=$test";
if (mysql_query($sql)) {
echo "Record Deleted successfully"; }
else {
echo "Error deleting record: " .mysql_error($con); }
?>
 End

Questions?