Categories

Articles in MySQL Category

Using MySQL Select Statement in PHP

MySQL SELECT statement is used quite heavily in PHP applications since most of them are database driven and one of their main functionalities is retrieving, and displaying data.

For examples in this article, we are going to use `employee` table mentioned below. SQL commands for creating the table and inserting data are available here.

id first_name last_name job_title salary notes
1 Robin Jackman Software Engineer 5500  
2 Taylor Edward Software Architect 7200  
3 Vivian Dickens Database Administrator 6000  
4 Harry Clifford Database Administrator 6800  
5 Eliza Clifford Software Engineer 4750  
6 Nancy Newman Software Engineer 5100  
7 Melinda Clifford Project Manager 8500  
8 Harley Gilbert Software Architect 8000  

Once your web server and MySQL server are up and running, you can run the PHP scripts in this article.

<?php

/* Change database details according to your database */
$dbConnection = mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

$query  = "SELECT `first_name`, `last_name` AS `surname` FROM `employee` WHERE `job_title` = 'Software Engineer'";
$result = mysqli_query($dbConnection, $query);

if (mysqli_num_rows($result) > 0) {

    echo "<ul>";

    while ($row = mysqli_fetch_array($result, MYSQLI_ASSOC)) {

        echo "<li>{$row['first_name']} {$row['surname']}</li>";

    }

    echo "</ul>";

} else {

    echo "Query didn't return any result";

}

?>
  • `last_name` is renamed to `surname` in the query to show how to use renamed columns in the result set. Actually there is no difference in usage of not-renamed and renamed columns.
  • If else condition and mysqli_num_rows() are used to have two different displays when the result set has rows and when it doesn’t have any row.
  • In this example, mysqli_fetch_array() retrieves a row as an associate array. You can also retrieve a row as an indexed array. To know how mysqli_fetch_array() loops through the result set and to see its alternative functions, refer the article on MySQLi Procedural Functions.
  • Curly brackets are used around array elements since otherwise it will lead to a syntax error (If you want to use array elements and method calls inside an expression enclosed with double quotes, you need to use curly brackets).

Constructing WHERE IN()

Sometimes you would find that you need to dynamically generate the WHERE clause of the query. For an example think that you have used IN() function in WHERE clause. You can dynamically generate the parameters for the function as shown in following example.

<?php

/* Change database details according to your database */
$dbConnection = mysqli_connect('localhost', 'robin', 'robin123', 'company_db');

$employeeIds        = array(6, 7, 8);
$whereInParameters  = implode(',', $employeeIds);

$query  = "SELECT `first_name`, `last_name` AS `surname` FROM `employee` WHERE `id` IN($whereInParameters)";
$result = mysqli_query($dbConnection, $query);

if (mysqli_num_rows($result) > 0) {

    echo "<table>";

    while ($row = mysqli_fetch_assoc($result)) {

        echo "<tr>";
        echo "<td>{$row['first_name']}</td><td>{$row['surname']}</td>";
        echo "</tr>";

    }

    echo "</table>";

} else {

    echo "Query didn't return any result";

}

?>
  • $employeeIds array is populated inside the script. However usually values for WHERE clause will come from other means like user inputs.
  • MySQL IN() function needs the parameters as comma separated values. PHP implode() function is used for creating the comma separated value string (6,7,8).
  • In this example, an HTML table is used for displaying data instead of an unordered list which was the case in previous example.
  • mysqli_fetch_assoc() function is used. It’s equivalent to passing MYSQLI_ASSOC parameter to mysqli_fetch_array().

In both examples of this article, HTML is generated inside PHP script. But most PHP applications follow more sophisticated architectures like Model View Controller (MVC) which allow you to separate displaying of data from processing of data.

Where to Head from Here...
Share with Your Peers...

We Value Your Feedback...

We love to hear what you think about this article. Please provide your opinion, suggestions and improvements using following form. Note that submitted feedback is not displayed but we will get back to you if it needs a reply.