Pin Me

Inserting and Retrieving Rows From Database Tables using PHP

written by: bmharwani•edited by: Amy Carson•updated: 5/19/2011

Every web application requires insertion and retrieval of rows from back end database tables. In this article, we will learn how to insert and retrieve rows from the database table of a MySQL server through PHP.

  • slide 1 of 2

    Inserting Rows In The Database Table

    This article is an introduction to PHP MySQL Web Development and explains insertion and retrieval of information stored in MySQL tables through PHP.

    We assume that a database exists by name “shopping" with a table named “customers" in it. We will learn how to insert a row in customers table step by step. The customers table contains two columns: userid and address.

    Insertion of records in a table through PHP program requires the following steps:

    1. Establishing connection with the MySQL server

    2. Selecting the database containing the table in which record is supposed to be inserted

    3. Collecting information to be stored in the table

    4. Writing the SQL statement for inserting the record

    5. Executing the SQL statement

    Let us observe the following PHP program where a step-by-step approach is taken for inserting the information (userid and add) entered by the user via a form into a table named customers. The program is as shown below:

    Program name: insertrec.php

    <?php

    connect = mysql_connect("localhost", "john", "gold") or die ("Please,

    check the server connection.");

    mysql_select_db("shopping");

    userid = $_POST['userid'];

    add = $_POST['add'];

    $sql = "INSERT INTO customers (userid, address) VALUES ('$userid',

    '$add')";

    $result = mysql_query($sql) or die(mysql_error());

    echo "Record is saved";

    ?>

    Explanation

    The five steps of inserting records can be seen:

    1. Establishing connection with MySQL server

    connect = mysql_connect("localhost", "john", "gold") or die ("Please, check the server connection.");

    The above statement establishes the connection with the local MySQL server with the userid as “john" and the password of the john is assumed to be “gold" (any text).

    2. Selecting the database

    mysql_select_db("shopping");

    This statement selects the database “shopping" (i.e. loads it in memory) so that we can insert record in its “customers" table (We assume that a customers table exists in the shopping table and the table has two fields (columns): userid and address).

    3. Collecting information to be stored in the table

    userid = $_POST['userid'];

    add = $_POST['add'];

    These two statements are for collecting information from the $_POST array (containing the information – userid and add entered by the user and passed via a form).

    4. Writing SQL statement for inserting the record

    $sql = "INSERT INTO customers (userid, address) VALUES ('$userid', '$add')";

    This is the SQL statement for inserting the record into customers table of shopping database. It is a simple SQL statement where $userid and $add represent the information entered by the visitor and are enclosed in single quotes as both contain text matter.

    5. Executing the SQL statement

    $result = mysql_query($sql) or die(mysql_error());

    This statement executes the SQL statement stored in $sql variable and the result of the execution (whether record insertion was successful or failure) is stored in $result variable. This finishes the first half of our PHP/MySQL web development article i.e. How to Insert information in back end MySQL tables through PHP. In the next half, we'll see how to retrieve information stored in MySQL tables.

    Note: Inserting records in a table requires two PHP programs: one gathers information from the visitor and passes it to the other program, while the second program processes the information received from the earlier program. Processing here means any task such as inserting the information into the table, searching information from the table, deleting information from the table etc.

  • slide 2 of 2

    Retrieving Rows From The Table

    The information stored in back end database tables is often required to be retrieved for displaying it to visitors to a website. Good examples of records retrieval include the list of the products for sale on a website, display of facilities provided on different plans (postpaid or prepaid) on a telecommunication website or display of examination results on a university website, etc. Hence, in this second half of our PHP/MySQL Web Development article, we will learn how to retrieve information from MySQL tables through PHP.

    Retrieving all records from the table requires following five steps:

    1. Establishing a connection with the MySQL server

    2. Selecting the database containing the table whose records have to be retrieved

    3. Writing the SQL statement for selecting the desired fields from the table

    4. Executing the SQL statement and storing the fetched records in a resultset

    5. Fetching one row at a time from the result set and displaying it one by one

    Let us look at the following program that fetches all the records from the customers table of shopping database and displays them on the screen in tabular format. All the steps followed in retrieval of records can be clearly seen in the program below:

    Program name: customers_list.php

    <?php

    $connect = mysql_connect("localhost", "john", "gold")

    or die("Please, check your server connection.");

    mysql_select_db("shopping");

    $query = "SELECT userid, address from customers ";

    $results = mysql_query($query) or die(mysql_error());

    echo "<table border=\"1\">\n";

    echo "<th>Userid</th><th>Address</th>\n";

    while ($row = mysql_fetch_array($results)) {

    extract($row);

    echo "<tr>";

    echo "<td>";

    echo $userid;

    echo "</td>";

    echo "<td>";

    echo $address;

    echo "</td>";

    echo "</tr>\n";

    }

    echo "</table>\n";

    ?>

    Explanation

    The five steps of retrieving all records from the table are clearly visible:

    1. Establishing a connection with the MySQL server

    $connect = mysql_connect("localhost", "john", "gold")

    or die("Please, check your server connection.");

    This statement establishes the connection with the local MySQL server with the userid as “john" and the password of the root is assumed to be “gold" (any text).

    2. Selecting the database

    mysql_select_db("shopping");

    This statement selects the database “shopping" so that we can retrieve records from its customers table. We assume that a customers table exists in the shopping table and it consists of two fields (columns): userid and address.

    3. Writing the SQL statement for selecting the desired fields

    $query = "SELECT userid, address from customers ";

    This is the SQL statement that will retrieve userid and address fields (columns) from the customers table.

    4. Executing the SQL statement

    $results = mysql_query($query) or die(mysql_error());

    This statement executes the SQL statement, and all the fetched records (rows) from the customers table are stored temporarily in the resultset: $results. The resultset is a sort of array where rows of the tables are fetched and temporarily stored.

    5. Fetching one row at a time from the result set

    while ($row = mysql_fetch_array($results)) {

    The mysql_fetch_array() is a function used for extracting one record (row) at a time from the array of records - $results (resultset). In the above statement, we are using a while loop for extracting one row at a time from the resultset and storing it in a variable $row. When all rows from the resultset are extracted (i.e. when there are no rows left in the $results array), mysql_fetch_array() function will return false, hence will come out of the while loop. The row returned by the mysql_fetch_array() from the resultset will be in a form of an associative array or a numeric array. In other words, the $row that will contain one row (record) at a time is an associative array or a numeric array.

    In the while loop, we use extract function to extract the fields (columns) from array $row that is assumed to contain one row at a time of the table:

    extract($row);

    The extract function extracts all the fields (columns) stored in the specified array. After getting all the fields extracted from $row array, they are displayed one by one with the help of the following statements:

    echo "<tr>";

    echo "<td>";

    echo $userid;

    echo "</td>";

    echo "<td>";

    echo $address;

    echo "</td>";

    echo "</tr>\n";

    The $userid and $address are the variables or fields that are extracted from $row array (by using extract() function) which contain the information of the userid and address.

    So, in this PHP/MySQL Web Development article, we have seen the step by step approach to insert and retrieve information from MySQL tables through PHP.