Data can be fetched from MySQL tables by executing SQL SELECT statement through PHP function mysql_query. You have several options to fetch data from MySQL.

The most frequently used option is to use function mysql_fetch_array(). This function returns row as an associative array, a numeric array, or both. This function returns FALSE if there are no more rows.

Below is a simple example to fetch records from employee table.

Example

Try out following example to display all the records from employee table.

<?php   $dbhost = 'localhost:3036';   $dbuser = 'root';   $dbpass = 'rootpassword';      $conn = mysql_connect($dbhost, $dbuser, $dbpass);      if(! $conn ) {      die('Could not connect: ' . mysql_error());   }      $sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';   mysql_select_db('test_db');   $retval = mysql_query( $sql, $conn );      if(! $retval ) {      die('Could not get data: ' . mysql_error());   }      while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {      echo "EMP ID :{$row['emp_id']}  <br> ".         "EMP NAME : {$row['emp_name']} <br> ".         "EMP SALARY : {$row['emp_salary']} <br> ".         "--------------------------------<br>";   }      echo "Fetched data successfully\n";      mysql_close($conn);?>

The content of the rows are assigned to the variable $row and the values in row are then printed.

NOTE − Always remember to put curly brackets when you want to insert an array value directly into a string.

In above example the constant MYSQL_ASSOC is used as the second argument to mysql_fetch_array(), so that it returns the row as an associative array. With an associative array you can access the field by using their name instead of using the index.

PHP provides another function called mysql_fetch_assoc() which also returns the row as an associative array.

Example

Try out following example to display all the records from employee table using mysql_fetch_assoc() function.

<?php   $dbhost = 'localhost:3036';   
$dbuser = 'root';   
$dbpass = 'rootpassword';      
$conn = mysql_connect($dbhost, $dbuser, $dbpass);      
if(! $conn ) 
{      
die('Could not connect: ' . mysql_error());   
}      
$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';   
mysql_select_db('test_db');   $retval = mysql_query( $sql, $conn );      
if(! $retval ) 
{      
die('Could not get data: ' . mysql_error());   
}      
while($row = mysql_fetch_assoc($retval)) 
{      
echo "EMP ID :{$row['emp_id']}  <br> ". "EMP NAME : {$row['emp_name']} <br> "."EMP SALARY : {$row['emp_salary']} <br> ".         "--------------------------------<br>";   
}      
echo "Fetched data successfully\n";      mysql_close($conn);
?>

You can also use the constant MYSQL_NUM, as the second argument to mysql_fetch_array(). This will cause the function to return an array with numeric index.

Example

Try out following example to display all the records from employee table using MYSQL_NUM argument.

<?php   
$dbhost = 'localhost:3036';   
$dbuser = 'root';   $dbpass = 'rootpassword';      
$conn = mysql_connect($dbhost, $dbuser, $dbpass);     
if(! $conn ) {      die('Could not connect: ' . mysql_error());   
}      
$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';   
mysql_select_db('test_db');   $retval = mysql_query( $sql, $conn );     
if(! $retval ) {      die('Could not get data: ' . mysql_error());  
}      while($row = mysql_fetch_array($retval, MYSQL_NUM)) 
{      
echo "EMP ID :{$row[0]}  <br> ".         "EMP NAME : {$row[1]} <br> ".   "EMP SALARY : {$row[2]} <br> ".         "--------------------------------<br>";   
}     
 echo "Fetched data successfully\n";      
mysql_close($conn);
?>

All the above three examples will produce same result.

Releasing Memory

Its a good practice to release cursor memory at the end of each SELECT statement. This can be done by using PHP function mysql_free_result(). Below is the example to show how it has to be used.

Example

Try out following example

<?php   
$dbhost = 'localhost:3036';   
$dbuser = 'root';   
$dbpass = 'rootpassword';     
$conn = mysql_connect($dbhost, $dbuser, $dbpass);      
if(! $conn ) {      
die('Could not connect: ' . mysql_error());  
}      
$sql = 'SELECT emp_id, emp_name, emp_salary FROM employee';   
mysql_select_db('test_db');   $retval = mysql_query( $sql, $conn );     
if(! $retval ) {      die('Could not get data: ' . mysql_error());   
}      while($row = mysql_fetch_array($retval, MYSQL_NUM)) 
{      
echo "EMP ID :{$row[0]}  <br> ". "EMP NAME : {$row[1]} <br> ". "EMP SALARY : {$row[2]} <br> ".         "--------------------------------<br>";   }      mysql_free_result($retval);  
echo "Fetched data successfully\n";     
mysql_close($conn);
?>

While fetching data you can write as complex SQL as you like. Procedure will remain same as mentioned above.

Using Paging through PHP

Its always possible that your SQL SELECT statement query may result into thousand of records. But its is not good idea to display all the results on one page. So we can divide this result into many pages as per requirement.

Paging means showing your query result in multiple pages instead of just put them all in one long page.

MySQL helps to generate paging by using LIMIT clause which will take two arguments. First argument as OFFSET and second argument how many records should be returned from the database.

Below is a simple example to fetch records using LIMIT clause to generate paging.

Example

Try out following example to display 10 records per page.

<html>      
<head>      
<title>Paging Using PHP</title>   
</head>      
<body>      
<?php         
$dbhost = 'localhost:3036';         
$dbuser = 'root';         
$dbpass = 'rootpassword';                  
$rec_limit = 10;         
$conn = mysql_connect($dbhost, $dbuser, $dbpass);                 
if(! $conn ) 
{           
 die('Could not connect: ' . mysql_error());         
}         
mysql_select_db('test_db');                 
/* Get total number of records */        
$sql = "SELECT count(emp_id) FROM employee ";         
$retval = mysql_query( $sql, $conn );                 
if(! $retval ) 
{            
die('Could not get data: ' . mysql_error());        
}         
$row = mysql_fetch_array($retval, MYSQL_NUM );         
$rec_count = $row[0];                  
if( isset($_GET{'page'} ) ) {          
$page = $_GET{'page'} + 1;           
$offset = $rec_limit * $page ;        
}else {            $page = 0;            
$offset = 0;         
}                 
$left_rec = $rec_count - ($page * $rec_limit);         
$sql = "SELECT emp_id, emp_name, emp_salary ".    "FROM employee ".   "LIMIT $offset, $rec_limit";                     
$retval = mysql_query( $sql, $conn );                  
if(! $retval ) 
{            
die('Could not get data: ' . mysql_error());         
}                  
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) 
{            
echo "EMP ID :{$row['emp_id']}  <br> ".  "EMP NAME : {$row['emp_name']} <br> ".   EMP SALARY : {$row['emp_salary']} <br> ".               "--------------------------------<br>";         }                  if( $page > 0 ) {            $last = $page - 2;            echo "<a href = \"$_PHP_SELF?page = $last\">Last 10 Records</a> |";            echo "<a href = \"$_PHP_SELF?page = $page\">Next 10 Records</a>";         }else if( $page == 0 ) {            echo "<a href = \"$_PHP_SELF?page = $page\">Next 10 Records</a>";        
 }else if( $left_rec < $rec_limit ) 
{            
$last = $page - 2;            
echo "<a href = \"$_PHP_SELF?page = $last\">Last 10 Records</a>";         
}                  
mysql_close($conn);      
?>         
</body></html>

Updating Data into MySQL Database

Data can be updated into MySQL tables by executing SQL UPDATE statement through PHP function mysql_query.

Below is a simple example to update records into employee table. To update a record in any table it is required to locate that record by using a conditional clause. Below example uses primary key to match a record in employee table.

Example

Try out following example to understand update operation. You need to provide an employee ID to update an employee salary.

<html>      
<head>      
<title>Update a Record in MySQL Database</title>   
</head>      
<body>      
<?php         
if(isset($_POST['update'])) 
{            
$dbhost = 'localhost:3036';            
$dbuser = 'root';            
$dbpass = 'rootpassword';                        
$conn = mysql_connect($dbhost, $dbuser, $dbpass);                        
if(! $conn ) 
{               
die('Could not connect: ' . mysql_error());            
}                       
$emp_id = $_POST['emp_id'];           
$emp_salary = $_POST['emp_salary'];                        
$sql = "UPDATE employee ". "SET emp_salary = $emp_salary ".  "WHERE emp_id = $emp_id" ;
mysql_select_db('test_db');            
$retval = mysql_query( $sql, $conn );                        
if(! $retval ) 
{               
die('Could not update data: ' . mysql_error());            
}            echo "Updated data successfully\n";                        
mysql_close($conn);         }else {            
?>              
 <form method = "post" action = "<?php $_PHP_SELF ?>">                  
<table width = "400" border =" 0" cellspacing = "1"       cellpadding = "2">
 <tr>                       
<td width = "100">Employee ID</td>                        
<td><input name = "emp_id" type = "text"       id = "emp_id"></td>                     
</tr>                                       
<tr>                        
<td width = "100">Employee Salary</td>                        
<td><input name = "emp_salary" type = "text"  id = "emp_salary"></td>                     
</tr>                                       
<tr>                        
<td width = "100"> </td>                        
<td> </td>                     
</tr>                                      
 <tr>                        <td width = "100"> </td>                        
<td>                           
<input name = "update" type = "submit"     id = "update" value = "Update">  
 </td>                     
</tr>                                    
</table>               </form>           
 <?php         }      
?>         
</body>
</html>

Deleting Data from MySQL Database

Data can be deleted from MySQL tables by executing SQL DELETE statement through PHP function mysql_query.

Below is a simple example to delete records into employee table. To delete a record in any table it is required to locate that record by using a conditional clause. Below example uses primary key to match a record in employee table.

Example

Try out following example to understand delete operation. You need to provide an employee ID to delete an employee record from employee table.

<html>      
<head>      
<title>Delete a Record from MySQL Database</title>   
</head>     
<body>      
<?php        
if(isset($_POST['delete'])) 
{            
$dbhost = 'localhost:3036';            
$dbuser = 'root';            
$dbpass = 'rootpassword';            
$conn = mysql_connect($dbhost, $dbuser, $dbpass);                        
if(! $conn ) 
{               
die('Could not connect: ' . mysql_error());            
}                                                                         
$emp_id = $_POST['emp_id'];                        
$sql = "DELETE FROM employee WHERE emp_id = $emp_id" ;           
mysql_select_db('test_db');            
$retval = mysql_query( $sql, $conn );                        
if(! $retval ) {               
die('Could not delete data: ' . mysql_error());            
}                        
echo "Deleted data successfully\n";                        
mysql_close($conn);        
}else
{          
?>               
<form method = "post" action = "<?php $_PHP_SELF 
?>">                  
<table width = "400" border = "0" cellspacing = "1"  cellpadding = "2"> 
                                         
<tr>                        
<td width = "100">Employee ID
</td>                        
<td><input name = "emp_id" type = "text"   id = "emp_id"></td>                     
</tr>                                          
<tr>                        
<td width = "100"> </td>                        
<td> </td> </tr>                                          
<tr> <td width = "100"> </td>                        
<td>                           
<input name = "delete" type = "submit"  id = "delete" value = "Delete">                        
</td>                   
</tr>                                      
</table>              
</form>            
<?php         
}     
?>         
</body>
</html>

So, this brings us to the end of blog. This Tecklearn ‘CRUD Operations in MySQL DB using PHP’ blog helps you with commonly asked questions if you are looking out for a job in PHP Programming. If you wish to learn Java and build a career Java Programming domain, then check out our interactive, Java and JEE Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

https://www.tecklearn.com/course/java-and-jee-training/

Java and JEE Training

About the Course

Java and JEE Certification Training is designed by professionals as per the industrial requirements and demands. This training encompasses comprehensive knowledge on basic and advanced concepts of core Java & J2EE along with popular frameworks like Hibernate, Spring & SOA. In this course, you will gain expertise in concepts like Java Array, Java OOPs, Java Function, Java Loops, Java Collections, Java Thread, Java Servlet, and Web Services using industry use-cases and this will help you to become a certified Java expert.

Why Should you take Java and JEE Training?

Java developers are in great demand in the job market. With average pay going between $90,000/- to $120,000/- depending on your experience and the employers.
Used by more than 10 Million developers worldwide to develop applications for 15 Billion devices.
Java is one of the most popular programming languages in the software world. Rated #1 in TIOBE Popular programming languages index (15th Consecutive Year)

What you will Learn in this Course?

Introduction to Java

Java Fundamentals
Introduction to Java Basics
Features of Java
Various components of Java language
Benefits of Java over other programming languages
Key Benefits of Java

Installation and IDE’s for Java Programming Language

Installation of Java
Setting up of Eclipse IDE
Components of Java Program
Editors and IDEs used for Java Programming
Writing a Simple Java Program

Data Handling and Functions

Data types, Operations, Compilation process, Class files, Loops, Conditions
Using Loop Constructs
Arrays- Single Dimensional and Multi-Dimensional
Functions
Functions with Arguments

OOPS in Java: Concept of Object Orientation

Object Oriented Programming in Java
Implement classes and objects in Java
Create Class Constructors
Overload Constructors
Inheritance
Inherit Classes and create sub-classes
Implement abstract classes and methods
Use static keyword
Implement Interfaces and use it

Polymorphism, Packages and String Handling

Concept of Static and Run time Polymorphism
Function Overloading
String Handling –String Class
Java Packages

Exception Handling and Multi-Threading

Exception handling
Various Types of Exception Handling
Introduction to multi-threading in Java
Extending the thread class
Synchronizing the thread

File Handling in Java

Input Output Streams
io Package
File Handling in Java

Java Collections

Wrapper Classes and Inner Classes: Integer, Character, Boolean, Float etc
Applet Programs: How to write UI programs with Applet, Java.lang, Java.io, Java.util
Collections: ArrayList, Vector, HashSet, TreeSet, HashMap, HashTable

Java Database Connectivity (JDBC)

Introduction to SQL: Connect, Insert, Update, Delete, Select
Introduction to JDBC and Architecture of JDBC
Insert/Update/Delete/Select Operations using JDBC
Batch Processing Transaction
Management: Commit and Rollback

Java Enterprise Edition – Servlets

Introduction to J2EE
Client Server architecture
URL, Port Number, Request, Response
Need for servlets
Servlet fundamentals
Setting up a web project in Eclipse
Configuring and running the web app with servlets
GET and POST request in web application with demo
Servlet lifecycle
Servlets Continued
Session tracking and filter
Forward and include Servlet request dispatchers

Java Server Pages (JSP)

Fundamentals of Java Server Page
Writing a code using JSP
The architecture of JSP
JSP Continued
JSP elements: Scriptlets, expressions, declaration
JSP standard actions
JSP directives
Introduction to JavaBeans
ServletConfig and ServletContext
Servlet Chaining
Cookies Management
Session Management

Hibernate

Introduction to Hibernate
Introduction to ORM
ORM features
Hibernate as an ORM framework
Hibernate features
Setting up a project with Hibernate framework
Basic APIs needed to do CRUD operations with Hibernate
Hibernate Architecture

POJO (Plain Old Java Object)

POJO (Plain Old Java Object)
Persistent Objects
Lifecycle of Persistent Object

Spring

Introduction to Spring
Spring Fundamentals
Advanced Spring

Got a question for us? Please mention it in the comments section and we will get back to you.

330