This page discusses PHP APIs to run SQL queries on a MySQL database server. For the sake of readability, we organize SQL queries into three categories: (a) queries that view existing MySQL information/data (SHOW, DESCRIBE and SELECT queries), (b) queries that create/drop/alter MySQL tables (CREATE, DROP, and ALTER queries), and (c) queries that update data (INSERT, DELETE, UPDATE, and REPLACE queries).
SQL provides several queries that view existing MySQL information/data: SHOW, DESCRIBE, and SELECT. SHOW queries are used to view information about databases, tables, and columns. DESCRIBE queries are used to view information about table columns. SELECT queries are used to view rows from tables.
We use mysql_query() function to run SQL queries. Next, we can use the resource id returned from mysql_query() to get additional information about the query output. First, we can call mysql_num_rows() to get the number of rows present in the query result. Second, we can call mysql_fetch_row() to get the current row from the output; for retrieving all the rows from the queries, we need to run mysql_fetch_row() in a loop.
Functions mysql_num_rows() and mysql_fetch_rows() work only for SQL queries that view information/data (SHOW, DESCRIBE, and SELECT). For queries that update data (INSERT, UPDATE, and DELETE), we need to use different functions -- more on update queries in the next section.
Let us look at a simple example that runs a SELECT query on the table "my_friends" present in the "test" database.
<?php
function show_error($failure_step) {
$errno = mysql_errno();
$error = mysql_error();
echo "$failure_step failed with error: $error ($errno)";
}
/* Connect to the database server */
if (!mysql_connect("localhost", "user1001","password1001")) {
show_error("mysql_connect");
return;
}
/* Connect to the database named "test" */
if (!mysql_select_db("test")) {
show_error("mysql_select_db");
return;
}
/* See all the records from the table "my_friends" */
$query = "SELECT * FROM my_friends";
$result = mysql_query($query);
if ($result) {
$num_rows = mysql_num_rows($result);
echo "<br>Table Values (total_rows: $num_rows): <br>";
while ($row = mysql_fetch_row($result)) {
echo "UserID: ", $row[0], "\tName: ", $row[1], "\tAge: ", $row[2], "<br>";
}
} else {
show_error("mysql_query");
return;
}
mysql_close();
?>
If we view this page (we call it "mysqldb_view_info.php") using a browser (URL: "http://localhost/mysqldb_view_info.php"), the output shows all the records present in the "my_friends" table.
Table Values (total_rows: 7): UserID: jfulton Name: James Fulton Age: 35 UserID: tkipling Name: Thomas Kipling Age: 36 UserID: ksimon Name: Karuna Simon Age: 32 UserID: hjones Name: Henry Jones Age: 37 UserID: amoore Name: Amanda Moore Age: 38 UserID: msimpson Name: Mark Simpson Age: 38 UserID: hstewart Name: Hank Stewart Age: 45
Please note that for our example, we have added the table "my_friends" along with the above data in the "test" database.
SQL provides queries to create new tables, drop existing tables, or modify the structure of existing tables: CREATE, DROP, and ALTER. A CREATE query creates a table (or a database). A DROP query deletes an existing table (or a database). An ALTER query modifies the structure of an existing table.
Once again, we can use mysql_query() function to run these queries.
Next, we present an example to illustrate usage of CREATE and DROP queries. The program drops an existing table ("my_friends") and then recreates it. The program also uses DESCRIBE query to print columns of this table.
<?php
function show_error($failure_step) {
$errno = mysql_errno();
$error = mysql_error();
echo "$failure_step failed with error: $error ($errno)";
}
/* Connect to the database server */
if (!mysql_connect("localhost", "user1001","password1001")) {
show_error("mysql_connect");
return;
}
/* Connect to the database named "test" */
if (!mysql_select_db("test")) {
show_error("mysql_select_db");
return;
}
/* First, let us drop the table named "my_friends" */
$query = "DROP TABLE my_friends";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (drop table)");
return;
}
/* Next, let us (re)create the "my_friends" table */
$query = "CREATE TABLE my_friends (userid VARCHAR(30) unique, name VARCHAR(30), age INT)";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (create table)");
return;
}
/* Let us describe this table */
$query = "DESCRIBE my_friends";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (describe)");
return;
} else {
$num_rows = mysql_num_rows($result);
echo "<br>Table Values (total_rows: $num_rows): <br>";
while ($row = mysql_fetch_row($result)) {
echo "Field: ", $row[0], " Type: ", $row[1], "<br>";
}
}
mysql_close();
?>
Let us view this page (we call it "mysqldb_drop_create.php") using a browser (URL: "http://localhost/mysqldb_drop_create.php"). The output shows the structure of "my_friends" table after it is (re)created; the table consists of three columns: userid, name, and age.
Table Values (total_rows: 3): Field: userid Type: varchar(30) Field: name Type: varchar(30) Field: age Type: int(11)
If the queries are not built correctly, then MySQL would throw an appropriate error. For example, if we pass the name of a non-existing table (let us say "foo") to a DROP query, then MySQL would return an error, "Unknown table 'foo'" with a corresponding error number of 1051. Likewise, if we pass name of a non-existing table (let us say "foo") to an ALTER query, then it also returns an error, although it is a different one! The error returned is "Table 'foo' doesn't exist" and the error number is set to 1146. Thus, even though the errors are of the same type (non-existent table name), mysql_query() returns different errors for different queries!
Lastly, let us look at the third class of MySQL queries: queries that update table data. These queries update table data by adding a new record (INSERT), modifying an existing record (UPDATE/REPLACE), or deleting an existing record (DELETE).
A REPLACE query takes an existing record and replaces it with a new record. In this regard, we can view a REPLACE query as a combination of DELETE and INSERT operations. If a record exists with the same primary key, then it replaces that record with the new values. If we do not use a primary key, then REPLACE functions behaves like an INSERT query and adds a new record. The REPLACE query takes arguments similar to that of INSERT query.
We present a small example that illustrates the usage of these queries and also provides an insight into the format of these queries. Please note that when specifying values for some of these queries, we use an escape character "\" for double-quotes (e.g. \"ksimon\") so that PHP would not complain (or get confused) about having double-quotes inside the bigger double-quotes of the $query variable. And yet, SQL would still to able to receive these values within double-quotes.
<?php
/* Set of data records */
$user_data[] = array("jfulton", "James Fulton", 35);
$user_data[] = array("tkipling", "Thomas Kipling", 36);
$user_data[] = array("ksimon", "Karuna Simon", 32);
$user_data[] = array("msimpson", "Mark Simpson", 38);
/* Trivial function to print table records */
function show_table () {
$query = "SELECT * FROM my_friends";
$result = mysql_query($query);
if ($result) {
$num_rows = mysql_num_rows($result);
echo "Table Values (total_rows: $num_rows): <br>";
while ($row = mysql_fetch_row($result)) {
echo "UserID: ", $row[0], "\tName: ", $row[1], "\tAge: ", $row[2], "<br>";
}
}
}
/* Print Error */
function show_error($failure_step) {
$errno = mysql_errno();
$error = mysql_error();
echo "$failure_step failed with error: $error ($errno)";
}
/* Connect to the database server */
if (!mysql_connect("localhost", "","")) {
show_error("mysql_connect");
return;
}
/* Connect to the database named "test" */
if (!mysql_select_db("test")) {
show_error("mysql_select_db");
return;
}
/* Print table */
echo "<br> Printing table my_friends <br>";
show_table();
/* Add some records to the table "my_friends" */
foreach ($user_data as $element) {
$query = "INSERT INTO my_friends VALUES('$element[0]','$element[1]', '$element[2]')";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (insert)");
}
}
/* Print table */
echo "<br> Printing table my_friends after inserting new records <br>";
show_table();
/* Delete a record */
$query = "DELETE FROM my_friends WHERE userid=\"msimpson\"";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (delete)");
}
/* Print table */
echo "<br> Printing table my_friends after deleting record for userid 'msimpson' <br>";
show_table();
/* Update a record */
$query = "UPDATE my_friends SET age=40 WHERE userid=\"ksimon\"";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (update)");
}
/* Print table */
echo "<br> Printing table my_friends after updating record for userid 'ksimon' <br>";
show_table();
/* Replace a record */
$query = "REPLACE my_friends VALUES (\"ksimon\", \"Karuna Simon\", 26)";
$result = mysql_query($query);
if (!$result) {
show_error("mysql_query (replace)");
}
/* Print table */
echo "<br> Printing table my_friends after replacing record for userid 'ksimon' <br>";
show_table();
mysql_close();
?>
If we view this page (we call it "mysqldb_update.php") using a browser (URL: "http://localhost/mysqldb_update.php"), then the output shows records of the "my_friends" table as and when we update its records.
Printing table my_friends Table Values (total_rows: 0): Printing table my_friends after inserting new records Table Values (total_rows: 4): UserID: jfulton Name: James Fulton Age: 35 UserID: tkipling Name: Thomas Kipling Age: 36 UserID: ksimon Name: Karuna Simon Age: 32 UserID: msimpson Name: Mark Simpson Age: 38 Printing table my_friends after deleting record for userid 'msimpson' Table Values (total_rows: 3): UserID: jfulton Name: James Fulton Age: 35 UserID: tkipling Name: Thomas Kipling Age: 36 UserID: ksimon Name: Karuna Simon Age: 32 Printing table my_friends after updating record for userid 'ksimon' Table Values (total_rows: 3): UserID: jfulton Name: James Fulton Age: 35 UserID: tkipling Name: Thomas Kipling Age: 36 UserID: ksimon Name: Karuna Simon Age: 40 Printing table my_friends after replacing record for userid 'ksimon' Table Values (total_rows: 3): UserID: jfulton Name: James Fulton Age: 35 UserID: tkipling Name: Thomas Kipling Age: 36 UserID: ksimon Name: Karuna Simon Age: 26