Learning Objectives – after this chapter you will be able to:
- Explain how PHP talks to MySQL and which connection parameters are needed.
- Read and understand classic PHP/MySQL code written with the
mysql_*functions. - Follow the standard steps: connect, select database, query, fetch, close.
- Know why this code fails on PHP 7/8 and where its MySQLi versions are in this book.
How PHP Fits with MySQL
PHP and MySQL work together seamlessly: you can use SQL commands inside PHP code almost as easily as HTML. A PHP script sends an SQL statement to the MySQL server, receives the result and turns it into HTML for the browser.
Figure 33.1 – PHP and MySQL
Before PHP can use any database functions, the corresponding extension must be enabled in php.ini. With PHP 5 the classic mysql extension was used; in XAMPP 8 the mysqli extension is enabled by default (extension=mysqli).
The Classic Functions
Older PHP notes – including the original notes of this book – use these functions:
| Function | Purpose |
|---|---|
| mysql_connect("hostname", "user", "pass") | Connects to the MySQL server. |
| mysql_select_db("database name") | Equivalent of the SQL command USE – makes the database active. |
| mysql_query("query") | Sends any SQL command to the server. |
| mysql_fetch_row($result) | Returns one row of the result as a numeric array. |
| mysql_fetch_array($result) | Returns one row as an associative and numeric array; called in a loop to get all rows. |
| mysql_num_rows($result) | Number of rows in the result. |
| mysql_error() | The error message returned by the MySQL server. |
| mysql_close() | Closes the connection. |
These functions do not exist in PHP 7 or PHP 8. The listings in this chapter are printed so that you can read and understand old code (you will meet it in many existing projects and tutorials). They cannot be run on XAMPP 8 – every one of them stops with "Fatal error: Uncaught Error: Call to undefined function mysql_connect()" (see Chapter 27). Each listing has a number such as L33.3; exactly the same program, converted to MySQLi procedural style, is Program 34.3 in Chapter 34, and in MySQLi object-oriented style Program 35.3 in Chapter 35 – both with their real output.
Connecting to the MySQL Server
Before you can do anything with MySQL you must connect to the server using your connection variables:
| Parameter | Meaning |
|---|---|
| Host name | In your case localhost, because everything is installed locally. Change it to the host that runs your MySQL server. |
| User name | The XAMPP default user is root, with no password. In classroom sessions a separate user (for example ict with password bhopal) is used. |
| Password | The password of that user. |
| Database | The database to work with (selected separately in the classic API). |
You can put the information into variables, or write it directly in the function call. Most people use variables – and often keep them in a separate file that is included wherever a connection is needed:
<?php
$host = "localhost";
$user = "ict";
$pass = "bhopal";
$connect = mysql_connect($host, $user, $pass)
or die("Could not connect: " . mysql_error());
echo "Connected successfully";
mysql_close($connect);
?>The following statement has the same effect: $connect = mysql_connect("localhost", "ict", "bhopal");
Selecting the Database
<?php
$connection = mysql_connect("localhost", "root", "")
or die("Could not connect");
$database = mysql_select_db("phpbook", $connection)
or die("couldn't find the database");
echo "Database phpbook selected";
?>Creating a Table from PHP
Any SQL command can be sent with mysql_query(), either as a variable or as text directly in the function call:
$query = "SELECT * FROM students";
$results = mysql_query($query);
// or
$results = mysql_query("SELECT * FROM students");<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("phpbook") or die(mysql_error());
$sql = "CREATE TABLE IF NOT EXISTS enquiry (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
mobile VARCHAR(10),
course VARCHAR(30),
enquired_on DATETIME DEFAULT CURRENT_TIMESTAMP)";
mysql_query($sql) or die("Error creating table: " . mysql_error());
echo "Table enquiry created";
?>Inserting Records
<?php
mysql_connect("localhost", "root", "");
mysql_select_db("phpbook");
$name = mysql_real_escape_string($_POST['name']);
$mobile = mysql_real_escape_string($_POST['mobile']);
$course = mysql_real_escape_string($_POST['course']);
$sql = "INSERT INTO enquiry (name, mobile, course)
VALUES ('$name', '$mobile', '$course')";
if (mysql_query($sql)) {
echo "Record saved. Enquiry number: " . mysql_insert_id();
} else {
echo "Error: " . mysql_error();
}
?>Retrieving Data from the Database
The results of a query are placed in a temporary result set ($results), which you read row by row with mysql_fetch_array() in a while loop:
<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("phpbook") or die(mysql_error());
$result = mysql_query("SELECT roll, name, city FROM students ORDER BY roll")
or die("Couldn't find the table");
echo "Total students: " . mysql_num_rows($result) . "<br>";
echo "<table border='1'><tr><th>Roll</th><th>Name</th><th>City</th></tr>";
while ($row = mysql_fetch_array($result)) {
echo "<tr><td>" . $row['roll'] . "</td><td>" . $row['name']
. "</td><td>" . $row[2] . "</td></tr>";
}
echo "</table>";
mysql_close();
?>mysql_fetch_array() returns each row both as an associative array ($row['name']) and as a numeric array ($row[2]).
Updating and Deleting Records
<?php
mysql_connect("localhost", "root", "");
mysql_select_db("phpbook");
mysql_query("UPDATE students SET city = 'Sehore' WHERE roll = 103")
or die(mysql_error());
echo mysql_affected_rows() . " record(s) updated";
?><?php
mysql_connect("localhost", "root", "");
mysql_select_db("phpbook");
$roll = (int) $_GET['roll'];
mysql_query("DELETE FROM enquiry WHERE id = $roll") or die(mysql_error());
echo mysql_affected_rows() . " record(s) deleted";
?>Searching with a Form
<?php
mysql_connect("localhost", "root", "");
mysql_select_db("phpbook");
$city = mysql_real_escape_string($_GET['city']);
$result = mysql_query("SELECT name, city FROM students WHERE city LIKE '%$city%'");
if (mysql_num_rows($result) == 0) {
echo "No student found";
}
while ($row = mysql_fetch_assoc($result)) {
echo $row['name'] . " – " . $row['city'] . "<br>";
}
?>Running Classic Code on XAMPP 8
If you open any of the listings above on XAMPP 8 you get the same result as in Chapter 27:
<?php
$connect = mysql_connect("localhost", "root", "") or die("Could not connect");
echo "Connected";
?>Fatal error: Uncaught Error: Call to undefined function mysql_connect() in /opt/lampp/htdocs/phpbook/ch33/legacy.php:2 Stack trace: #0 {main} thrown in /opt/lampp/htdocs/phpbook/ch33/legacy.php on line 2Converting classic code to MySQLi
| Step | Classic | MySQLi procedural |
|---|---|---|
| 1 | $c = mysql_connect(h, u, p); | $c = mysqli_connect(h, u, p, db); |
| 2 | mysql_select_db(db); | (included in step 1) or mysqli_select_db($c, db); |
| 3 | $r = mysql_query($sql); | $r = mysqli_query($c, $sql); |
| 4 | mysql_fetch_array($r) | mysqli_fetch_array($r) |
| 5 | mysql_num_rows($r) | mysqli_num_rows($r) |
| 6 | mysql_error() | mysqli_error($c) |
| 7 | mysql_real_escape_string($s) | prepared statements (Chapter 36) or mysqli_real_escape_string($c, $s) |
| 8 | mysql_close(); | mysqli_close($c); |
Key Takeaways
- PHP connects to MySQL with a host, user, password and database, sends SQL and fetches the results.
- Classic code uses
mysql_connect,mysql_select_db,mysql_query,mysql_fetch_array,mysql_num_rows,mysql_errorandmysql_close. mysql_fetch_array()in awhileloop reads all rows; rows are available by name and by number.- These functions were removed in PHP 7 – the code fails on XAMPP 8.
- Every classic listing L33.n is repeated as a working MySQLi program 34.n (procedural) and 35.n (object-oriented).
Program File
Extract the ZIP into C:\xampp\htdocs. It creates the folder phpbook/ch33 used in the file labels above; open the programs at http://localhost/phpbook/… with Apache (and MySQL, where needed) started in the XAMPP Control Panel.
Question / Answer (15 questions)
MCQ Test : Check Your Understanding (5 questions)
Select an option and press Check Answer, or answer all questions and press Submit Test to see your score. Each answer comes with an explanation.
Bibliography/References
The notes on this page are the author’s own compilation from the e-book PHP: Basics to Advance. Every program was run on Apache with PHP 8 and MariaDB, and the output shown is the real result of that run. The works listed below were consulted and are cited for attribution and further reading. Product names and trademarks belong to their respective owners.
- The PHP Group. (n.d.). PHP Manual – Original MySQL API (removed). https://www.php.net/manual/en/book.mysql.php
- The PHP Group. (n.d.). PHP Manual – Choosing an API (MySQL). https://www.php.net/manual/en/mysqlinfo.api.choosing.php
- The PHP Group. (n.d.). PHP Manual – MySQLi Quick start guide. https://www.php.net/manual/en/mysqli.quickstart.php
- Oracle Corporation. (n.d.). MySQL Connector/PHP – Overview of the MySQL PHP drivers. https://dev.mysql.com/doc/apis-php/en/