A collection of 18 tips on managing tables and data rows with PHP scripts. Clear explanations and tutorial exercises are provided on creating and dropping tables; inserting, updating, and deleting rows, selecting and looping through data rows by pages.

A collection of 18 tips on managing tables and data rows with PHP scripts. Clear explanations and tutorial exercises are provided on creating and dropping tables; inserting, updating, and deleting rows, selecting and looping through data rows by pages; setting auto-increment IDs.

How To Create a New Table?

If you want to create a table, you can run the CREATE TABLE statement as shown in the following sample script:

<?php
include "mysql_connection.php";
\$sql = "CREATE TABLE fyi_links ("
. " id INTEGER NOT NULL" 
. ", url VARCHAR(80) NOT NULL"
. ", notes VARCHAR(1024)"
. ", counts INTEGER"
. ", time TIMESTAMP DEFAULT CURRENT_TIMESTAMP()"
. ")";
if (mysql_query($sql, $con)) {
print("Table fyi_links created.\\n");
} else {
print("Table creation failed with error:\n");
print(mysql_errno($con).": ".mysql_error($con)."\n");
}
mysql_close($con); 
?>

Remember that mysql_query() returns TRUE/FALSE on CREATE statements. If you run this script, you will get something like this:

Table fyi_links created.

How To Get the Number of Rows Selected or Affected by a SQL Statement?

There are two functions you can use the get the number of rows selected or affected by a SQL statement:

• mysql_num_rows($res) - Returns the number of rows selected in a result set object returned from SELECT statement.
• mysql_affected_rows() - Returns the number of rows affected by the last INSERT, UPDATE or DELETE statement.

How To Insert Data into an Existing Table?

If you want to insert a row of data into an existing table, you can use the INSERT INTO statement as shown in the following sample script:

<?php
include "mysql_connection.php";
$sql = "INSERT INTO fyi_links (id, url) VALUES ("
. " 101, 'dev.fyicenter.com')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed with error:\n");
print(mysql_errno($con).": ".mysql_error($con)."\n");
}
mysql_close($con); 
?>

Remember that mysql_query() returns integer/FALSE on INSERT statements. If you run this script, you could get something like this:

SQL statement failed with error:
1142: INSERT command denied to user 'dev'@'localhost' 
for table 'fyi_links'

How To Fix the INSERT Command Denied Error?

The reason for getting the "1142: INSERT command denied" error is that your user accound does not have the INSERT privilege on the table or database. To resolve this error, you need to ask your MySQL DBA to grant you the right privilege.

Your DBA will need to run commands like these to grant you enough privileges :

Once you got the right privileges, run the following tutorial scrip again:

The script should return this:

How To Insert Multiple Rows with a SELECT Statement?

If want to insert rows into a table based on data rows from other tables, you can use a sub-query inside the INSERT statement as shown in the following script example:

If you run this script, you will get something like this:

2 rows inserted.

What Is a Result Set Object?

A result set object is a logical representation of data rows returned by mysql_query() function on SELECT statements. Every result set object has an internal pointer used to identify the current row in the result set. Once you get a result set object, you can use the following functions to retrieve detail information:

• mysql_free_result($res) - Closes this result set object.
• mysql_num_rows($res) - Returns the number rows in the result set.
• mysql_num_fields($res) - Returns the number fields in the result set.
• mysql_fetch_row($res) - Returns an array contains the current row indexed by field position numbers.
• mysql_fetch_assoc($res) - Returns an array contains the current row indexed by field names.
• mysql_fetch_array($res) - Returns an array contains the current row with double indexes: field position numbers and filed names.
• mysql_fetch_lengths($res) - Returns an array contains lengths of all fields in the last row returned.
• mysql_field_name($res, $i) - Returns the name of the field of the specified index.

How To Query Tables and Loop through the Returning Rows?

The best way to query tables and loop through the returning rows is to run the SELECT statement with the mysql_query() function, catch the returning object as a result set, and loop through the result with the mysql_fetch_assoc() function in a while loop as shown in the following sample PHP script:

<?php
include "mysql_connection.php";
$sql = "SELECT id, url, time FROM fyi_links";
$res = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($res)) {
print($row['id'].",".$row['url'].",".$row['time']."\n");
}
mysql_free_result($res);
mysql_close($con); 
?>

Using mysql_fetch_assoc() is better than other fetch functions, because it allows you to access field values by field names. If you run this script, you will see all rows from the fyi_links table are printed on the screen:

101, dev.fyicenter.com, 2006-07-01 22:29:02
102, dba.fyicenter.com, 2006-07-01 22:29:02
1101, dev.fyicenter.com, 2006-07-01 22:29:02
1102, dba.fyicenter.com, 2006-07-01 22:29:02

Don't forget to call mysql_free_result($res). It is important to free up result set objects as soon as you are done with them.

How To Break Output into Pages?

If you have a query that returns hundreds of rows, and you don't want to present all of them to your users on a single page. You can break output into multiple pages, and only present 10 rows per page like what Google Website is doing. To do this, you need to modify your query with the LIMIT clause to return rows starting from the correct row number, and limited to 10 rows in the output.

The tutorial exercise below shows you how to break output into pages with 2 rows per page. It also calcualtes the total number of pages with a simple query criteria and order by condition. As an example, rows for page number 2 are returned.

<?php
include "mysql_connection.php";
$rowsPerPage = 2;
$where = " WHERE url LIKE '%co%'";
$order = " ORDER BY time DESC";
$curPage = 2; 
$start = ($curPage-1) * $rowsPerPage; 
$sql = "SELECT COUNT(*) AS count FROM fyi_links" . $where . $order;
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
$row = mysql_fetch_assoc($rs);
$numberOfPages = $row['count'] / $rowsPerPage; 
print("Number of pages = $numberOfPages\n");
mysql_free_result($rs);
$sql = "SELECT * FROM fyi_links" . $where . $order
. " LIMIT ".$start.", ".$rowsPerPage;
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print($row['id'].", ".$row['url'].", "
. $row['notes'].", ".$row['time']."\n");
}
mysql_free_result($rs);
mysql_close($con); 
?>

If you run this script, you will get something like this:

SQL = SELECT COUNT(*) AS count FROM fyi_links 
WHERE url LIKE '%co%' ORDER BY time DESC
Number of pages = 3
SQL = SELECT * FROM fyi_links WHERE url LIKE '%co%' 
ORDER BY time DESC LIMIT 2, 2

How To Update Existing Rows in a Table?

Updating existing rows in a table requires to run the UPDATE statement with a WHERE clause to identify the row. The following sample script updates one row with two new values:

If you run this script, you will get something like this:

How To Delete Existing Rows in a Table?

If you want to remove a row from a table, you can use the DELETE statement with a WHERE clause to identify the row. The following sample script deletes one row:

If you run this script, you will get something like this:

How To Quote Text Values in SQL Statements?

Text values in SQL statements should be quoted with single quotes ('). If the text value contains a single quote ('), it should be protected by replacing it with two single quotes (''). In SQL language syntax, two single quotes represents one single quote in string literals. The tutorial exercise below shows you two INSERT statements. The first one will fail, because it has an un-protected single quote. The second one will be ok, because a str_replace() is used to replace (') with (''):

If you run this script, you will get something like this:

SQL statement failed. 1 rows inserted.

How To Quote Date and Time Values in SQL Statements?

If you want to provide date and time values in a SQL statement, you should write them in the format of "yyyy-mm-dd hh:mm:ss", and quoted with single quotes ('). The tutorial exercise below shows you two INSERT statements. The first one uses a hard-code date value. The second one uses the date() function to return a date value.

<?php
include "mysql_connection.php";
$notes = "Added long time ago!";
$time = "1999-01-01 01:02:03";
$sql = "INSERT INTO fyi_links (id, url, notes, time)" 
. " VALUES ("
. " 301, 'netscape.com', '".$notes."', '".$time."')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
$notes = "Added today!";
$time = date("Y-m-d H:i:s");
$sql = "INSERT INTO fyi_links (id, url, notes, time)"
. " VALUES ("
. " 302, 'myspace.com', '".$notes."', '".$time."')";
if (mysql_query($sql, $con)) {
print(mysql_affected_rows() . " rows inserted.\n");
} else {
print("SQL statement failed.\n");
}
mysql_close($con); 
?>

If you run this script, you will get something like this:

1 rows inserted.
1 rows inserted.

How To Display a Past Time in Days, Hours and Minutes?

You have seen a lots of Websites are displaying past times in days, hours and minutes. If you want to do this yourself, you can use the TIMEDIFF() SQL function. Note that the TIMEDIFF() function can only handle time range within 839 hours (about 33 days). So it works only for past times within one month or so.

The following tutorial exercise shows you how to use TIMEDIFF() to present a past time in days, hours, and minutes:

<?php
include "mysql_connection.php";
$pastTime = "2006-06-29 04:09:49";
$sql = "SELECT HOUR(timeDiff) AS hours,"
. " MINUTE(timeDiff) AS minutes FROM ("
. " SELECT TIMEDIFF(NOW(), '".$pastTime."')"
. " AS timeDiff FROM DUAL) subQuery";
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print("$pastTime was ".$row['hours']." hours, "
. $row['minutes']." minutes ago.\n");
}
mysql_free_result($rs);
$sql = "SELECT (HOUR(timeDiff) DIV 24) AS days,"
. " (HOUR(timeDiff) MOD 24) AS hours,"
. " MINUTE(timeDiff) AS minutes FROM ("
. " SELECT TIMEDIFF(NOW(), '".$pastTime."')"
. " AS timeDiff FROM DUAL) subQuery";
print("SQL = $sql\n");
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print("$pastTime was ".$row['days']." days, "
. $row['hours']." hours, "
. $row['minutes']." minutes ago.\n");
}
mysql_free_result($rs);
mysql_close($con); 

If today is you run this script, you will get something like this:

SQL = SELECT HOUR(timeDiff) AS hours, 
MINUTE(timeDiff) AS minutes FROM (
SELECT TIMEDIFF(NOW(), '2006-06-29 04:09:49') 
AS timeDiff FROM DUAL) subQuery
2006-06-29 04:09:49 was 115 hours, 2 minutes ago.
SQL = SELECT (HOUR(timeDiff) DIV 24) AS days, 
(HOUR(timeDiff) MOD 24) AS hours,
MINUTE(timeDiff) AS minutes FROM (
SELECT TIMEDIFF(NOW(), '2006-06-29 04:09:49')
AS timeDiff FROM DUAL) subQuery
2006-06-29 04:09:49 was 4 days, 19 hours, 2 minutes ago.

Warning again, this script only works if the past time is less than 33 days ago.

How To Perform Key Word Search in Tables?

The simplest way to perform key word search is to use the SELECT statement with a LIKE operator in the WHERE clause. The LIKE operator allows you to match a text field with a keyword pattern specified as '%keyword%', where (%) represents any number of any characters. Any single quote (') in the keyword needs to be protected by replacing them with two single quotes (''). The tutorial exercise below shows you how to search for records whose "notes" contains "e":

<?php
include "mysql_connection.php";
$key = "e";
$key = str_replace("'", "''", $key);
$sql = "SELECT id, url, notes FROM fyi_links"
. " WHERE notes LIKE '%".$key."%'";
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print($row['id'].", ".$row['url'].", "
. $row['notes']."\n");
}
mysql_free_result($rs);
mysql_close($con); 
?>

If you run this script, you will get something like this:

102, dba.fyicenter.com, Nice site.
202, www.yahoo.com, It's another search engine!
301, netscape.com, Added long time ago!
302, myspace.com, Added today!

How To Build WHERE Criteria with Web Form Search Fields?

If your PHP script is linked to a Web form which takes search key words for multiple data fields. For example, your Web form asks your visitor to search for Website links with a URL search field, a Website title search field, a description search field, and a comment search field.

Now you have to build a nice WHERE criteria string that meets the following requirements:

• Search fields with no data entered by visitors should not be included in the criteria.
• Search values entered by visitors should be trimmed to remove leading and trailing space characters.
• Empty search values after trimming should not be included in the criteria.
• Single quote (') characters in search values should be protected.
• backslash (\) characters in search values should be protected.

The tutorial script below shows you a good sample that meets the above requirements:

If you run this script, you will get something like this:

SQL statement:
SELECT * FROM siteLinks WHERE 1=1 
AND title LIKE '%Joe''s brother\\''s%'
AND description LIKE '%c:\\windows\\system%'
AND comment LIKE '%best%'

You should learn a couple of things in this script:

• isset($_REQUEST[$p]) is used to detect if the visitor has actually entered any value or not to a field.
• trim($s) is used to trim off leading and trailing space characters.
• str_replace("'", "''",$s) is used to replace single quote (') characters with ('').
• str_replace("\\", "\\\\",$s) is used to replace backslash (\) characters with (\\). You need to repeat backslashes because PHP string literals can not take backslashes as is.
• getFormParam($p) is created do all the input value processing work in a single function. getFormParam($p) also makes sure that all input values are defined as strings, even if they are not defined.
• The WHERE clause is initialized with a dummy condition "1=1", so that all other conditions can be prefixed with the key word "AND".
• $_REQUEST() is created for testing purpose only. You need to remove it, when you move this script to a real Web page.
• The final WHERE criteria generated in the output SQL statement seems to be correct.

How To Query Multiple Tables Jointly?

If you want to query information stored in multiple tables, you can use the SELECT statement with a WHERE condition to make an inner join. Assuming that you have 3 tables in a forum system: "users" for user profile, "forums" for forums information, and "posts" for postings, you can query all postings from a single user with a script as shown below:

<?php
include "mysql_connection.php";
$userID = 101;
$sql = "SELECT posts.subject, posts.time, users.name,
. " forums.title"
. " FROM posts, users, forums"
. " WHERE posts.userID = ".$userID
. " AND posts.userID = users.id"
. " AND posts.forumID = forums.id";
$rs = mysql_query($sql, $con);
while ($row = mysql_fetch_assoc($rs)) {
print($row['subject'].", ".$row['time'].", "
.$row['name'].", ".$row['title']."\n");
}
mysql_free_result($rs);
mysql_close($con); 
?>

How To Get the ID Column Auto-Incremented?

Many tables require an ID column to assign a unique ID number for each row in the table. For example, if you have a table to hold forum member profiles, you need an ID number to identify each member. To allow MySQL server to automatically assign a new ID number for each new record, you can define the ID column with AUTO_INCREMENT and PRIMARY KEY attributes as shown in the following sample script:

<?php
include "mysql_connection.php";
$sql = "CREATE TABLE fyi_users ("
. " id INTEGER NOT NULL AUTO_INCREMENT" 
. ", name VARCHAR(80) NOT NULL"
. ", email VARCHAR(80)"
. ", time TIMESTAMP DEFAULT CURRENT_TIMESTAMP()"
. ", PRIMARY KEY (id)"
. ")";
if (mysql_query($sql, $con)) {
print("Table fyi_users created.\n");
} else {
print("Table creation failed.\n");
}
mysql_close($con); 
?>

If you run this script, a new table will be created with ID column defined as auto-increment. The sample script below inserts two records with ID values assigned by MySQL server:

If you run this script, you will get something like this:1 rows inserted.
1 rows inserted.
1, John King, 2006-07-01 23:02:39
2, Nancy Greenberg, 2006-07-01 23:02:39

How To Get the Last ID Assigned by MySQL?

If you use an ID column with AUTO_INCREMENT attribute, you can use the mysql_insert_id() function to get the last ID value assigned by the MySQL server, as shown in the sample script below:

If you run this script, you will get something like this: