PHP (PHP 2018) is often used as the front-end of a Web site, which interacts with a database engine, such as MySQL, at the back-end for storing and retrieving data online through dynamic Web pages. The combination of PHP and MySQL provides a wide range of options to create all kinds of Web pages, from small personal contact forms to large corporate portals. This section covers basic programming of MySQL in PHP.
In order to do MySQL programming through PHP, the reader must have access to both a HTTPD server, which can interface with a MySQL server. For Linux users, this is not a problem at all since both HTTPD and MySQL are available on the same Linux machine. Although the HTTPD server and the MySQL server do not have to be on the same IP host, for convenience we shall assume that they are on the same computer, i.e. the same localhost. The following discussions assume the following.
- The reader’s Linux machine has both HTTPD and MySQL server running, and the HTTPD server is configured to support PHP. The reader may consult Chap. 13 for how to configure HTTPD for PHP.
- The reader has a user account and can host Web pages, which can be accessed as http://localhost/ ~user_name
If not, consult Chap. 13 for how to set up personal Web pages.
- A Web browser which can access Web pages on the Internet.
1. Connect to MySQL Server in PHP
When programming PHP with MySQL, the first step is to connect to a MySQL server. For convenience, we shall assume that the MySQL server is on the same Linux machine. The following program P14.1 shows a PHP script, which connects to a MySQL server to create a new database cs362.
// P14.1: PHP script, connect to a MySQL Server
<html>
<head>
<title>Creating MySQL Database</title>
</head>
<body>
<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = “; // replace with YOUR MySQL root password
$con = mysql connect($dbhost, $dbuser, $dbpass);
if(! $con ) {
die(‘Can not connect: ‘ . mysql_error());
}
echo ‘Connected successfully<br />’;
$sql = ‘DROP DATABASE IF EXISTS cs362’;
$retval = mysql_query( $sql, $con );
if ($retval)
echo “dropped database cs362 OK<br>”;
$sql = ‘CREATE DATABASE cs362’;
$retval = mysql_query( $sql, $con );
if(! $retval ) {
die(‘Could not create database: ‘ . mysql_error());
}
echo “Database cs362 created successfully\n”;
mysql_close($con);
?>
</body>
</html
As in C, interface between PHP and MySQL is supported by a set of MySQL PHP API functions (MySQL PHP API 2018). The PHP API functions are usually simpler in syntax than their C counterparts. When calling mysql functions in PHP, it is customary to define PHP variables with values, rather than using hard coded strings or numbers as parameters. As shown, the program uses mysql_connect() to connect to the MySQL server. Since creating/deleting databases in MySQL requires the root user privilege, the program connects to the MySQL server as the root user. As in C programming, the standard PHP query function is also mysql_query(). When the program ends, it issues mysql_close() to close the connection.
2. Create Database Tables in PHP
The next program shows how to create tables in an existing database. For ease of identification, the PHP code which drops a table (if it exists) and creates a new table are highlighted.
// P14.2 : PHP program Create Table
<html>
<head>
<title>Creating MySQL Tables</title>
</head>
<body>
<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = “;
$con = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $con ) {
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘Connected to MySQL Server OK<br>’;
mysql_select_db( ‘cs362’ ); // use cs362 created earlier
$sql = “DROP TABLE IF EXISTS students”;
$retval = mysql_query( $sql, $conn );
echo “create table in cs362<br>”;
$sql = “CREATE TABLE students( “.
“student_id INT NOT NULL PRIMARY KEY AUTO_INCREMENT, “.
“name CHAR(20) NOT NULL, “.
“score INT, “.
“grade CHAR(2)); “;
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die(‘Could not create table: ‘ . mysql_error());
}
echo “Table created successfully\n”;
mysql_close($conn);
?>
</body>
</html
Figure 14.1 shows the results of running the P14.1 program from a Web browser.
3. Insert Records into Table in PHP
The following program, P14.2 shows how to insert records into a table in a database using PHP.
// P14.2: Insert records into table
<html>
<head>
<title>Insert Record to MySQL Database</title>
</head>
<body>
<?php
if ( isset($_POST[‘submit’]) ){ // if user has submitted data
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = “;
$con = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $con ) {
die(‘Could not connect:’. mysql_error());
}
echo “Connected to server OK<br>”;
mysql_select_db(‘cs362’); // use cs362 database
$student_id = $_POST[‘student_id’];
$name = $_POST[‘name’];
$score = $_POST[‘score’];
echo “ID=” . $student_id . ” name=” . $name .
” score=”.$score.”<br>”;
if ($student_id == NULL || $name == NULL || $score == NULL){
echo “ID or name or score can not be NULL<br>”;
}
else{
$sql = “INSERT INTO students “.
“(student_id, name, score) “.”VALUES “.
“(‘$student_id’,’$name’,’$score’)”;
$retval = mysql_query( $sql, $con );
if(! $retval ) {
echo “Error ” . mysql_error() . “<br>”;
}
else{
echo “Entered data OK\n”;
mysql_close($con);
}
}
}
?>
<br>
// a FORM for user to enter and submit data
<form method = “post” action = “<?php $_PHP_SELF ?>”>
ID number : <input name=”student_id” type=”text” id=student_id”><br>
name    : <input name=”name” type=”text” id=”name”><br>
score   : <input name=”score” type=”text” id=”score”><br><br>
<input name=”submit” type=”submit” id=”submit” value=”Submit”>
</form>
</body>
</html>
Figure 14.2 shows the FORM generated by PHP for user to input data and submit request.
Figure 14.3 shows the results of the insert operation. If the operation is successful, it returns another form for the user to enter and submit more insertion requests
4. Retrieve Results of MySQL Queries in PHP
The following program P14.3 shows how to retrieve and display query results in PHP.
// P14.3: Select query and display results
<html>
<head>
<title>Select Records from MySQL Database</title>
</head>
<body>
<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = “;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $con ) {
die(‘Could not connect: ‘ . mysql_error());
}
mysql_select_db(‘cs362’);
echo “select 1 row<br>”;
$sql = ‘SELECT studentid, name, score FROM students
WHERE name=”Baker”‘;
$retval = mysql_query( $sql, $con );
if(! $retval ) {
die(‘Could not retrieve data: ‘ . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC)) {
echo “student_id = {$row[‘student_id’]} “.
“name = {$row[‘name’]} “.
“score = {$row[‘score’]} <br> “.
“—————————– <br>”;
}
echo “select all rows<br>”;
$sql = ‘SELECT * FROM students’;
$retval = mysql_query( $sql, $con );
if(! $retval ) {
die(‘Could not retrieve data: ‘ . mysql_error());
}
while($row = mysql_fetch_assoc($retval)) {
echo “student_id = {$row[‘student_id’]} “.
“name = {$row[‘name’]} “.
“score = {$row[‘score’]}<br> “;
}
echo “—————————– <br>”;
echo “selected data successfully\n”; mysql_close($con);
?>
</body>
</html>
The program P14.3 shows how to select one row from a database. It retrieves the row by the mysql_fetch_assoc() function as an associative array $row, and displays the contents of $row by name. It also shows how to select all rows and retrieves the rows by the same mysql_fetch_assoc() function and display the contents of each row as an associative array. Associative arrays are standard features in PHP and Perl but they are not available in C.
5. Update Operation in PHP
The next program P14.4 shows how to update MySQL tables in PHP.
// P14.4: Update operations in PHP
<html>
<head> <title>Update tables in NySQL Database</title></head>
<body>
<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = “;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $con ) {
die(‘Could not connect: ‘ . mysql_error());
}
echo ‘connected to Server OK<br>’;
mysql_select_db(‘cs362’);
$sql = ‘UPDATE students SET grade = \’A\’
WHERE score >= 90′;
$retval = mysql_query( $sql, $conn );
if(! $retval ) {
die(‘Could not update data: ‘ . mysql_error());
}
echo “Updated data successfully\n”;
mysql_close($conn);
?>
</body>
</html>
Exercise Modify the C14.4 program to assign letter grades ‘B’ to ‘F’ based on student score ranges. After updating the grades, run the PHP program P14.3 to verify the results.
6. Delete Rows in PHP
The following PHP program P14.5 deletes a row from a MySQL table.
<?php
$dbhost = ‘localhost:3036’;
$dbuser = ‘root’;
$dbpass = “;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $con ) {
die(‘Could not connect: ‘ . mysql_error());
}
mysql_select_db(‘cs362’);
$sql = ‘DELETE FROM students WHERE name=\’Zach\”;
$retval = mysql_query( $sql, $con );
if(! $retval ) {
die(‘Could not delete data: ‘ . mysql_error());
}
echo “Deleted data OK\n”;
mysql_close($conn);
?>
Source: Wang K.C. (2018), Systems Programming in Unix/Linux, Springer; 1st ed. 2018 edition.