Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

PHP CRUD: CRUD operation in PHP

In this article we will discuss about how to perform CRUD(Create, Read, Update, Delete) operations using PHP and MYSQL.

PHP is a programming language which can do all sorts of things: evaluate form data sent from a browser, build custom web content to serve the browser, talk to a database, and even send and receive cookies

PHP started out as a small open source project that evolved as more and more people found out how useful it was. We can see some important informations about this server-side scripting language.

PHP is a recursive acronym for "PHP: Hypertext Preprocessor" and is a server-side scripting language that is embedded in HTML. It is used to manage dynamic content, databases, session tracking, even build entire e-commerce sites and is integrated with a number of popular databases, including MySQL, PostgreSQL, Oracle, Sybase, Informix, and Microsoft SQL Server.

PHP also supports a large number of major protocols such as POP3, IMAP, and LDAP. PHP4 added support for Java and distributed object architectures (COM and CORBA), making n-tier development a possibility for the first time.

PHP performs system functions, i.e. from files on a system it can create, open, read, write, and close them. Can also handle forms, i.e. gather data from files, save data to a file thru email. You can send data, return data to the user, access cookies variables and set cookies, restrict users to access some pages of your website and encrypt data.

You can add, delete, modify elements within your database thru PHP and that’s what we will learn in this article, let’s get started.

Pre-requisites:

Following versions are used for executing the examples in this article:

  • PHP 5.0;
  • AMPPS Server;
  • MYSQL 5.

In the following sections I'll be covering the following topics:

  • Create database table through PHP
  • Insert records to tables through PHP
  • Display records of a particular person using “where” clause in PHP
  • Display records in a particular order using “ORDER BY” keyword in PHP
  • Delete the records from the database using PHP
  • Update the records in the database using PHP
  • Deleting multiple records at a time using PHP
  • Update multiple records at a time using PHP

Create Table

The below program is to create a database table employees via php code:

Listing 1: createtable.php

<?php
$host = "localhost";
  	 $username = "test";
   	$conn = mysql_connect($host, $username)
	or die("Could not connect");
 	  echo "Connected successfully";
//execute the SQL query and return records
	mysql_select_db('test');
// Create table
$sql =mysql_query("CREATE TABLE employees(id int not null primary key auto_increment, name char(25), phone_no  varchar(25),address char(50));")
or die(mysql_error());
// Execute query
$retval = mysql_query($sql);
echo("Table created successfully");
mysql_close($conn);
?>

Details of the code:

  • In the above code we have created a table using “create” query in the php code.
  • $sql=mysql_query("CREATE TABLE employees(id int not null primary key auto_increment, name char(25), phone_no varchar(25),address char(50));") is used to create a new table in the database. We have created a new table named “employees” using “create” query in the code.
  • We have created four columns in the table by specifying the name of each column with its datatype.

When the database is created, then we can check the list of the database in the command prompt by using the following query:

desc employees;

The database list table is displayed in the command prompt as shown below:

Database list table

Figure 1: Database list table

Insert Records

The following program is to insert records into the database table (employees) via PHP code:

Listing 2: insertrecords.php

<?php
$host = "localhost";
$username = "test";
$conn = mysql_connect($host, $username)
or die("Could not connect");

echo "Connected successfully";

mysql_select_db('test');
    // inserting the data by using insert queries
mysql_query("INSERT INTO employees(id,name,phone_no,address)VALUES ('1','Maruti', 6875875746,'Khanapur')");
mysql_query("INSERT INTO employees(id,name,phone_no,address)VALUES ('2','Vikrant', 9874563210,'Shahapur')");

mysql_query("INSERT INTO employees(id,name,phone_no,address)VALUES ('3','Ganesh', 9874258960,'Sadhashiv nagar ')");
mysql_query("INSERT INTO employees(id,name,phone_no,address)VALUES ('4','Mahantesh', 7852369845,'Balekundri')");
mysql_query("INSERT INTO employees (id, name,phone_no,address)VALUES ('5','Rushali', 'rushali@tekbuds.com','Tilakwadi')");

// display the table
$result = mysql_query("SELECT * FROM employees");

echo "<table border='1'>
<tr>
<th>id</th>
<th>Name</th>
<th>Phone_no</th>
<th>Address</th>
</tr>";

//fetch the data from the database
while ($row = mysql_fetch_array($result)) {
echo"<tr>";
echo "<td>" .$row{'id'}."</td>";
echo "<td>" .$row{'name'}."</td>"; //display the results
echo "<td>" .$row{'phone_no'}."</td>";
echo "<td>" .$row{'address'}."</td>";
echo "</tr>";
}
echo "</table>";
mysql_close($conn);
?>

Details of the code:

The above php code is used to insert the values in the table and display the complete table on php page.

  • $username = "root"; is used to define the username of the server.
  • $hostname = "localhost"; is used to define the name of the server.
  • $conn = mysql_connect($hostname, $username) is a php function which is used to connect to mysql .
  • die("Could not connect"); is used to display message when the connection is not established between code.
  • echo "Connected successfully"; is used to display the message when the application connects to the database successfully.
  • mysql_select_db('test'); is used to select the database that works in mysql by giving the database name “test”.
  • mysql_query("INSERT INTO employees (id, name,phone_no,address)VALUES ('1','Maruti', 6875875746,'Khanapur')"); is used to insert the details into the database table using “insert” query. Here we have inserted details of a person in the table employees by giving the column names and the values to be displayed in the table.

While inserting the values in a table, we should insert the values in the same order as the columns are defined in the database table.

The same way we have inserted some more details in the table by using the insert query.

  • $result = mysql_query("SELECT * FROM employees"); is used to display the information in the table on the php page. This “select” query displays all the records that are inserted into the ‘employees’ table.
  • echo " is used to display the information in the correct table format and with border that we have inserted in the above code.
  • while ($row = mysql_fetch_array($result)) { } is used to fetch the information from the database which is stored in variable “$result”.
  • echo "
  • "; is used to fetch the row from the “employees” table and display it on the php page.

    Run the above code on the Ampps server by giving the correct path of php in the browser. After successful connection of the database a message gets displayed along with a table as shown below:

    Connected Successfully

    Figure 2: Connected Successfully

    Reading data

    Next is to connect to the database through PHP page and fetch details from a table using queries. The PHP code is as written below:

    Listing 3: retrievedata.php

    <?php
    $host = "localhost";
    $username = "test";
    $conn = mysql_connect ($host, $username)
    or die("Could not connect");
    
    echo "Connected successfully";
    
    $selected = mysql_select_db("test",$conn)
    or die("Could not select anything");
    
    //execute the SQL queries and returns records
    $result = mysql_query("SELECT * FROM employees");
    
    echo "<table border='1'>
    <tr>
    <th>id</th>
    <th>Name</th>
    <th>Phone_no</th>
    <th>Address</th>
    </tr>";
    
    //fetch the data from the database
    while ($row = mysql_fetch_array($result)) {
    echo"<tr>";
    echo "<td>" .$row{'id'}."</td>";
    echo "<td>" .$row{'name'}."</td>";  //display the results
    echo "<td>" .$row{'phone_no'}."</td>";
    echo "<td>" .$row{'address'}."</td>";
    echo "</tr>";
    }
    echo "</table>";
    
    mysql_close ($conn);
    ?>
     

    Details of the above code:

    The above php code is used to establish the connection and fetch the details of the databases and display the details of the employees table on the php output page.

    • $selected = mysql_select_db("test",$dbhandle) is used to select the database that works in mysql by giving the database name “test”.
    • die("Could not select examples"); is used to display a message when it could not find any examples in the databases.
    • $result = mysql_query("SELECT *FROM employees"); is used to display the complete employees table which is present in mysql database which is stored in a variable “$result”.
    • echo "
    "; is used to fetch the row from the employees table and display it on the php page.
  • mysql_close($dbhandle); is used to close the connection to the server.
  • Run the above code on the Ampps server by giving the correct path of php in the browser. After successful connection to the database, a message gets displayed along with a table.

    Reading data using WHERE clause

    The below program is to display records of a particular person using “where” clause in php code:

    Listing 4: retrieveusingwhere.php

    <?php
       $host = "localhost";
       $username = "test";
       $conn = mysql_connect($host, $username)
    	or die("Could not connect");
       echo "Connected successfully";
    //execute the SQL query and return records
    	mysql_select_db('test');
    
    $sql = mysql_query('SELECT * FROM employees where name="Rushali"')
     or  die (mysql_error());
    
     echo "<table border='1'>
      <tr>
      <th>id</th>
      <th>Name</th>
        <th>Phone_no</th>
          <th>Address</th>
    </tr>";
       //fetch the data from the database
       while ($row = mysql_fetch_array($sql)) {
          echo"<tr>";
          echo "<td>" .$row{'id'}."</td>";
          echo "<td>" .$row{'name'}."</td>"; //display the results
    	  echo "<td>" .$row{'phone_no'}."</td>";
          echo "<td>" .$row{'address'}."</td>";
    
          echo "</tr>";
    }
    echo "</table>";
    
    $retval = mysql_query('$sql');
    mysql_close($conn);
    ?>
     

    Details of the code:

    The above program is used to display record of a particular person in the database table using “where” clause in the php code.

    • $sql = mysql_query('SELECT * FROM employees where name="Rushali"') is used to show a record of the particular person from the existing table by using select query “where” clause. Here we have shown the information about the person “Rushali”. The select query is being stored in the variable “$sql”.
    • $retval = mysql_query( $sql); is used to execute the “select” query that is declared in the variable “$sql”.

    After successful connection to the database by using “where” clause, the table is as shown below:

    Select data with where

    Figure 3: Select data with where

    Display data using ORDER BY

    The below program is to display records in a particular order using “ORDER BY” keyword in php code:

    Lisiting 5: displayusingorderby.php

    <?php
      $host = "localhost";
       $username = "test";
       $conn = mysql_connect($host, $username)
    	or die("Could not connect");
    
       echo "Connected successfully";
    
       //execute the SQL query and return records
    	mysql_select_db('test');
    
     $sql = mysql_query('SELECT * FROM employees ORDER by name')
     or  die (mysql_error());
    
    echo "<table border='1'>
      <tr>
     	 <th>id</th>
      	<th>Name</th>
       	 <th>Phone_no</th>
          	<th>Address</th>
    
    </tr>";
      	 //fetch the data from the database
       	while ($row = mysql_fetch_array($sql)) {
         	 echo"<tr>";
          	echo "<td>" .$row{'id'}."</td>";
          	echo "<td>" .$row{'name'}."</td>"; //display the results
    	  echo "<td>" .$row{'phone_no'}."</td>";
          	echo "<td>" .$row{'address'}."</td>";
    
          	echo "</tr>";
    }
    echo "</table>";
    $retval = mysql_query('$sql');
     mysql_close($conn);
    ?>
     

    Details of the code:

    The above program is used to display record in a particular order in the database table using “order by” clause in the php code.

    • $sql = mysql_query('SELECT * FROM employees ORDER by name') is used to sort the details in a particular order by using “order by” keyword in the select query. Here we have sort the records according to the “name” column using ‘order by’ keyword in “select” query.

    After successful connection of the database by using “order by” keyword, the table is as shown below:

    Using Order BY

    Figure 4: Using Order BY

    Delete Record

    The below program is to delete the records from the database using php code:

    Listing 6: deleterecord.php

    <?php
    $host = "localhost";
    $username = "test";
    $conn = mysql_connect($host, $username)
    or die("Could not connect");
    
    echo "Connected successfully";
    
    $selected = mysql_select_db("test",$conn)
    or die("Could not select examples");
    
    //execute the SQL queries and returns records
    $result = mysql_query("SELECT * FROM employees");
    $sql = 'DELETE FROM employees
    WHERE id=2';
    
    echo "<table border='1'>
    <tr>
    <th>id</th>
    <th>Name</th>
    <th>Phone_no</th>
    <th>Address</th>
    </tr>";
    //fetch tha data from the database
    while ($row = mysql_fetch_array($result)) {
    echo"<tr>";
    echo "<td>" .$row{'id'}."</td>";
    echo "<td>" .$row{'name'}."</td>"; //display the results
    echo "<td>" .$row{'phone_no'}."</td>";
    echo "<td>" .$row{'address'}."</td>";
    echo "</tr>";
    }
    echo "</table>";
    mysql_select_db('test');
    $retval = mysql_query( $sql, $conn );
    if(! $retval )
    {
    die('Could not delete data: ' . mysql_error());
    }
    echo "Deleted data successfully\n";
    mysql_close($conn);
    ?>
     

    The above program is used to do the connection to the database and later delete the records from the database by writing the “delete” query in the php code.

    • $sql = 'DELETE FROM employees WHERE id=2'; is used to delete the particular record from the database table which we want to delete it. Here we have deleted the information for the employee whose “id=2”.
    • echo "Deleted data successfully\n"; is used to display the message when the deletion is been done successfully.

    After successful connection and deletion of the particular records from the database, a message gets displayed along with a table as shown below:

    Deleting Data

    Figure 5: Deleting Data

    Update Record

    The below program is to update the records in the database using php code:

    Listing 7: updaterecord.php

    <?php
    $host = "localhost";
    $username = "test";
    $conn = mysql_connect($host, $username)
    or die("Could not connect");
    
    echo "Connected successfully";
    
    $selected = mysql_select_db("test",$conn)
    or die("Could not select examples");
    
    //execute the SQL queries and returns records
    $result = mysql_query("SELECT * FROM employees");
    
    echo "<table border='1'>
    <tr>
    <th>id</th>
    <th>Name</th>
    <th>Phone_no</th>
    <th>Address</th>
    </tr>";
    
    //fetch the data from the database
    while ($row = mysql_fetch_array($result)) {
    echo"<tr>";
    echo "<td>" .$row{'id'}."</td>";
    echo "<td>" .$row{'name'}."</td>"; //display the results
    echo "<td>" .$row{'phone_no'}."</td>";
    echo "<td>" .$row{'address'}."</td>";
    echo "</tr>";
    }
    echo "</table>";
    
    $sql = "UPDATE employees ".
    "SET phone_no = ' 8865478932' ".
    "WHERE id = 5" ;
    
    mysql_select_db('test');
    $retval = mysql_query( $sql, $conn );
    if(! $retval )
    {
    die('Could not update data: ' . mysql_error());
    }
    echo "Updated data successfully\n";
    mysql_close($conn);
    ?>

    Details of the code:

    The above program is used to do the connection to the database and later update the records from the database by writing the update query in the php code.

    • $sql = "UPDATE employees "."SET phone_no = ' 8865478932' "."WHERE id = 5" ;
    • is used to update the record from the database table i.e. we have updated the phone_no “rushali@tekbuds.com” to “8865478932” in the record list by giving its id number I.e. “id=5”. The update query is being stored in the variable “$sql”.
    • $retval = mysql_query( $sql, $conn ); is used to execute the update query that is declared in the variable “$sql”.

    After successful connection and updating of the particular “id” in the database we will get a table as shown below:

    Updating Data

    Figure 6: Updating Data

    Delete Multiple Records

    Program to deleting multiple records at a time using php code:

    Listing 8: deletemultiplerecords.php

    <?php
    $host = "localhost";
    $username = "test";
    $conn = mysql_connect($host, $username)
    or die("Could not connect");
    
    echo "Connected successfully";
    
    $selected = mysql_select_db("test",$conn)
    or die("Could not select examples");
    
    //execute the SQL queries and returns records
    $result = mysql_query("SELECT * FROM employees");
    $sql = 'delete from employees where id IN (2, 3)';
    
    echo "<table border='1'>
    <tr>
    <th>id</th>
    <th>Name</th>
    <th>Phone_no</th>
    <th>Address</th>
    </tr>";
    //fetch tha data from the database
    while ($row = mysql_fetch_array($result)) {
    echo"<tr>";
    echo "<td>" .$row{'id'}."</td>";
    echo "<td>" .$row{'name'}."</td>"; //display the results
    echo "<td>" .$row{'phone_no'}."</td>";
    echo "<td>" .$row{'address'}."</td>";
    echo "</tr>";
    }
    echo "</table>";
    mysql_select_db('test');
    $retval = mysql_query( $sql, $conn );
    if(! $retval )
    {
    die('Could not delete data: ' . mysql_error());
    }
    echo "Deleted data successfully\n";
    mysql_close($conn);
    ?>

    The above program is used to do the connection to the database and to delete multiple records at a time by writing the “delete” query in the php code.

    • $result = mysql_query("SELECT * FROM employees"); is used to display the complete employees table which is present in mysql database which is stored in a variable “$result”.
    • $sql = 'delete from employees where id IN (2, 3)'; is used to delete multiple records from the table employees where we have deleted the records for the employees whose ids are “2” and “3”.
    • $retval = mysql_query( $sql, $conn ); is used to execute the “delete” query that is declared in the variable “$sql”.

    After successful connection and deletion of the multiple rows in the database we will get the following output table as shown below:

    Deleting multiple records

    Figure 7: Deleting multiple records

    Update Multiple Records

    Using the php code below, you can update multiple records at a time:

    Listing 9: updatemultiplerecords.php

    <?php
    $host = "localhost";
    $username = "test";
    $conn = mysql_connect($host, $username)
    or die("Could not connect");
    
    echo "Connected successfully";
    
    $selected = mysql_select_db("test",$conn)
    or die("Could not select examples");
    
    //execute the SQL query and return records
    $result = mysql_query("SELECT * FROM employees");
    
    
    echo "<table border='1'>
    <tr>
    <th>id</th>
    <th>Name</th>
    <th>Phone_no</th>
    <th>Address</th>
    </tr>";
    //fetch tha data from the database
    while ($row = mysql_fetch_array($result)) {
    echo"<tr>";
    echo "<td>" .$row{'id'}."</td>";
    echo "<td>" .$row{'name'}."</td>"; //display the results
    echo "<td>" .$row{'phone_no'}."</td>";
    echo "<td>" .$row{'address'}."</td>";
    echo "</tr>";
    }
    echo "</table>";
    
    
    $sql = “UPDATE employees ".
    "SET address = 'Belgaum' ".
    "WHERE id IN(2,3)" ;
    
    mysql_select_db('test');
    $retval = mysql_query( $sql, $conn );
    if(! $retval )
    {
    die('Could not update data: ' . mysql_error());
    }
    echo "Updated data successfully";
    
    mysql_close($conn);
    ?>
    

    The above program is used to do the connection to the database and to update multiple records at a time by writing the “update” query in the php code.

    • $sql = “UPDATE employees ". "SET address = 'Belgaum' "."WHERE id IN(2,3,5)" ; is used to update multiple records in a single “update” query, where in this program we have updated the records for 2 rows i.e. for id #2,3,.
    • $retval = mysql_query( $sql, $conn ); is used to execute the “update” query that is declared in the variable “$sql”.

    After successful connection and updating of the multiple rows in the database we will get the following output table as shown below:

    Updating multiple records

    Figure 8: Updating multiple records

    This article covered some basic CRUD operations on a database via PHP and MYSQL. Hope this article helped you to get some basic idea of how to make database connection, create a table, insert, update, read and delete records. With this basic knowledge you can go ahead and do various complex queries on the database via PHP.

    " .$row{'name'}." ".$row{'id'}."


I''m a full stack developer with around 10+ yrs of experience. I enjoy writing technical articles on upcoming technical trends.

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login