MySQL in PHP

MySQL is a freely available open source reational dabase system (RDBMS) based on structured query lanaguage (SQL).
MySQL is the reational dabase management system and PHP as the object-oriented scripting language (light-weight).

SQL

SQL is a special perpose programming language designed for meaning data folding a reational database manage system (RDBMS).
Originally based upon reational elzebra and tupple relational calculate. SQL consist of a data difinition language and a data manipulation language.

PHP and Relational Database Management System

PHP Connect to MySQL

mysql

  • MySQLi extension (the "i" stands for improved)
  • PDO (PHP Data Objects)

Both MySQLi and PDO have their advantages:
PDO will work on 12 different database systems, where as MySQLi will only work with MySQL databases.

Three ways of working with PHP and MySQL:

  • MySQLi (object-oriented)
  • MySQLi (procedural)
  • PDO (object-oriented)

Connect to Database

To connect to a MySQL server, the following syntax is to be used:

<?php 

$host = 'localhost';
$user = 'yourusername';
$pass = 'yourpassword';
$dbname = 'databasename';

// OOP way
$db = new mysqli($host, $user, $pass, $dbname);
// Procedural way would be:
$db = mysqli_connect($host, $user, $pass, $dbname);

?>

Displaying an error

we have a function that tells us if the connection was successful or not. This function can be invoked the same way for both procedural and object oriented way.

<?php 
if(mysqli_connect_errno())
{
    die('The connection to the database could not be established.');
}
?>

Changing databses

If at a certain point, you need to change the databse, you can do so with the following:

<?php 
// OOP way
$db->select_db($new_dbname);
// Or the procedural way
// We send the paramater $db as the resourse
mysqli_select_db($db, $new_dbname);
?>

Running a query

Let’s create a query to retrieve all the users and their information to display it on the screen.

<?php
// Set up query
$query = 'SELECT * FROM users';
// OOP way
$result = $db->query($query);
// Procedural way
$result = mysqli_query($db, $query);
?>

Get the results

If we have just queried a database, it means that we need this data. If you would like to know how many records this query has returned, then we can go ahead and use the attribute num_rows.

<?php 
// OOP way
$total_results = $result->num_rows;
// Procedural way
$total_results = mysqli_num_rows($result);
?>

Let’s go ahead and use a while loop. The fetch_assoc function will return an array for each record that was found, and each will have each key as their attributes, and each value in the array.

<?php 

echo 'There are $total_results record(s) found';
// OOP way
while ($row = $result->fetch_assoc()) 
{
    echo '<p>';
    echo $row['username'].' ';
    echo $row['firstname'].' ';
    echo $row['lastname'].' ';
    echo $row['city'].' ';
    echo $row['state'].'</p>';
}

// Procedural way 
while ($row = mysqli_fetch_assoc($result))
{
    echo '<p>';
    echo $row['username'].' ';
    echo $row['firstname'].' ';
    echo $row['lastname'].' ';
    echo $row['city'].' ';
    echo $row['state'].'</p>';
}
?>

If you wanted to grab the results as an object, you could just use the fetch_object function.

<?php 
// OOP way
$row = $result->fecth_object();
// Procedural way
$row = mysqli_fetch_object($result);
// Attributes are accessed in the following:
$row->username;
$row->firstname; // Etc...
?>

Close connection

Although PHP automatically closes your connection upon script termination, if you want to close the connection before your script is done, you can do so by just invoking the close function.

<?php 
// OOP way
$result->free();
$db->close();
// Procedural way
mysqli_free_result($result);
mysqli_close($db);
?>

Simple PHP Programs

Example of MySQLi Object-oriented connection

<?php
$server='localhost';
$user='root';//default user
$password='';//default password is blank
$database='school'; //database name
$db=new mysqli($server, $user, $password, $database) or die("unable to connect");
if(!$db->connect_error)
{
    echo "Connected successfully";
}
?>

Output is:
Connection successfully
Example of MySQLi procedural connection

<?php
$host='127.0.0.1';
$use='root';
$pass='';
$con=mysqli_connect($host, $use, $pass);
if(!$con)
{
    die("Connection failed:". mysql_connect_error());
	}
	echo "Connection successfully";
?>

Output is:
Connection successfully
Example Access Database through MySQLi Procedural

<?php
$host='127.0.0.1';
$use='root';
$pass='';
$database='school';
$con=mysqli_connect($host, $use, $pass, $database);
if(!$con)
{
    die("Connection failed:". mysql_connect_error());
	}
	else
	{
	echo "Connection successfully";
	$sql="SELECT sroll, sname, scourse from student";
	$result=mysqli_query($con,$sql);
$row=mysqli_fetch_assoc($result);
if(mysqli_num_rows($result)>0)
{
echo "<br/>rows found";
echo "<br/>".$row['sroll']."|".$row['sname']."|".$row['scourse'];
}
	}

?>

Output is:

Connection successfully
rows found
101|Ram|BCA

Use a while loop to fetch whole row in a table MySQLi through object-oriented

<?php
$server='localhost';
$user='root';
$password='';
$database='school';
$db= new mysqli($server,$user,$password,$database) or
 die('Unable to connect');
if ( ! $db->connect_error) 
{
echo 'Connected Sucessfully...';
$result = $db->query('select * from student');
echo "<table  border='1' align='center' width='400'
 cellspacing='10' cellpadding='5' style=' text-align:center'>";
echo "<tr style='background-color:gray  ;color:white'>
<td colspan=3 style='text-align:center'> <h2>Student List</h1> </td>" ;    
while($row =  $result->fetch_assoc())   
{
echo "<tr style='background-color:maroon;color:white'>";
echo "<td>".$row['sroll']."</td> <td>".$row['sname']."</td>
<td>".$row['scourse']."</td>";
echo "</tr>";	
}
echo "</table>";
$db->close();
}
?>

Output is:

Student List

101 Ram BCA
102 Mohan BScIT
103 Ravi MCA

Use a while loop to fetch whole row in a table MySQLi through Procedural

<?php
$host='127.0.0.1';
$user='root';
$password='';
$database='school';
$conn = mysqli_connect($host, $user, $password,$database);
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
    
}
else
{
echo "Connected successfully";
$sql="SELECT sroll,sname,scourse from student";  
$result=mysqli_query($conn,$sql);		
echo "<table  border='1' align='center' width='400'
cellspacing='10' cellpadding='5'>";
while ($row = mysqli_fetch_assoc($result))
{
			 
echo "<tr style='background-color:maroon;color:yellow'>";
echo "<td>".$row['sroll']."</td> <td>".$row['sname']."</td>
<td>".$row['scourse']."</td>";
echo "</tr>";	
		 
}
		echo "</table>";		 
		 mysqli_close($conn);
}
?>

Output is:

101 Ram BCA
102 Mohan BScIT
103 Ravi MCA

Program with the help of PDO:

PDO

The PHP Data Objects (PDO) extension defines a lightweight, consistent interface for accessing databases in PHP. Each database driver that implements the PDO interface can expose database-specific features as regular extension functions. Note that you cannot perform any database functions using the PDO extension by itself; you must use a database-specific PDO driver to access a database server.

Example

<?php
$server='localhost';
$username='root';
$password='';
try
{
$db= new PDO("mysql::host=$server;dbname=school",
$username,$password);    

$result=$db->query('select * from student');

$result->setFetchMode(PDO::FETCH_ASSOC);

echo "<table border='1' align='center'
 width='400' cellspacing='10' cellpadding='5'>";
echo "<tr style='background-color:yellow; color:red'>
<td colspan='4' style='text-align: center'>'
<h2>Student List</h2></td>";
	 while($rows = $result->fetch())
	 {
		echo "<tr style='background-color: blue;
		 color:white'>";
		echo "<td>".$rows['sroll']."</td>
		     <td>".$rows['sname']."</td>
		     <td>".$rows['scourse']."</td>";
		echo "</tr>"; 
	 }
	 echo "</table>";
	 }
catch(PDOException $e)
{
	echo $e->getMessage();
}
?>

Output is:

Student List

101 Ram BCA
102 Mohan BScIT
103 Ravi MCA