PHP Tutorial–Lesson 5: Introduction to PHP- Part II

PHP and MySQL

MySQL query through PHP script:

•Remember rule of single/double quotes when dealing with non-php script inside php script? We’ll apply it now with MySQL queries.

•Each MySQL query will be stored in a variable called for example $sql.

•We’ll use a built-in function that executes the query, the function is mysql_query(), and takes as parameter $sql value i.e. MySQL query.

•The returned value of this function is true or false, we’ll store it in a variable called for example $result.

•Finally, we’ll check whether your query executed successfully or not.

•These steps will be followed at each time we want to run MySQL query using PHP.

•We’ll suppose that you saved your config.php file that includes your connection configuration in an external directory called include. for more information on how to configure connection to MySQL server revise Previous Lesson on the Link: PHP Tutorial–Lesson 5: Introduction to PHP- Part I

Example:

<?php

// Employees.php

// Created in 13/10/2009

require(“../include/config.php”);

$sql = “SELECT * FROM employee”;

$result = mysql_query($sql);

If(!$result) die(“MySql Error: <br />“.mysql_error());

$tableHeader = ‘<table border=“1” width=“100%”>

<tr>

<th width=“10%”>ID</th>

<th width=“30%”>Emplyee Name</th>

<th width=“30%”>Department</th>

<th width=“20%”>Job Title</th>

<th width=“10%”>Salary</th>

<th width=“10%”>Telephone</th>

</tr>’;

echo $tableHeader;

while($row = mysql_fetch_array($result))

{

echo “<tr>”;

echo “<td>”.$row[‘id’].”</td>”;

echo “<td>”.$row[‘name’].”</td>”;

echo “<td>”.$row[‘department’].”</td>”;

echo “<td>”.$row[‘jobTitle’].”</td>”;

echo “<td>”.$row[‘salary’].”</td>”;

echo “<td>”.$row[‘telephone’].”</td>”;

echo “</tr>”;

}

echo “</table>”;

?>

Notes:

•Require: in order to make connection with MySQL server, We used require to insure that this script depends completely on connecting to db. If the file is not found then the script will not work, and prevent it from errors. remember difference between require and include in PHP Tutorial - Lesson 2: Introduction to PHP - Part III at Server Side Include SSI part.  

•../include/config.php: if the included file in a parent directory, and your script in a child directory, then the way to reach the included file is putting ../ At beginning of the address. If both the included file and the script are in same directory then we directly write it like this: require(“config.php”);

•Purpose of this script is displaying all employees information in the company.

•The MySQL query for this case is SELECT.

•$sql is a variable that stores a non-php code, in our case it stores MySQL query.

•Note until this line, your query has not yet been executed.

•$result is a variable that stores MySQL response for your query. Its value depends on the returned value of mysql_query().

•mysql_query($sql) is the function that makes your query become executed. This function takes one parameter which is MySQL query. And returns the response of MySQL. If the query succeeded then response will be stored in $result. Else false value will be stored in $result.

•After that we checked whether $result has been executed successfully or not. If not then we force the program to stop execution by calling die function and printing the error message from MySQL using mysql_error function.

•These couple of lines will be used at every MySQL query executed from php script, regardless what the query is.

•$tableHeader is a variable that holds html format for table.

•We stored this in a variable in order to be able to use it as needed.

•When mysql_query returns MySQL response about a query, it returns all records regardless how much they are in the same variable $result.

•PHP offers a built-in function called mysql_fetch_array() that separate each record in the returned query and save it in an array called $row.

•$row is a multi-dimensional associated array, which means we refer to each cell in it by key value, not index number.The key value of $row is your table columns.

•Finally, we loop through all records using while loop. Until reaching null value to $row which means this is end of records.

To download this lesson as PDF file click here: PHP Tutorial–Lesson 5: Introduction to PHP- Part II

Best Wishes,

Anas Jaghoub