Tips for Building Web Database Applications with PHP and MySQL
Pages: 1, 2
5. Using the header() function for one-component querying.
The PHP header() function is a useful tool for controlling the behavior of a Web browser. Let's look at one way it can be used.
In many Web database applications, functionality is included that allows the user to click on a link that performs an action but allows the user to remain on the same page. I call this one-component querying, where the query input component is displayed, but there is no corresponding page that shows output of the query.
One-component querying works as follows.
A calling page is shown to the user.
This page contains an embedded link or a <form> that is used to
request a second resource. Here's an example calling a page that's saved in
the file calling.php:
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd" >
<html>
<head>
<title>Calling page example</title>
</head>
<body>
<a href="action.php">Click here!</a>
</body>
</html>When the user clicks on the link, the resource action.php is requested. Here's action.php:
<?php
// Database functionality goes here
// Redirect
header("Location: $HTTP_REFERER");
exit;
?>At the server, the script action.php is interpreted by the PHP script engine, and after carrying out the database actions in the script, no output is produced.
Instead (and this is the key to one-component querying) an HTTP Location:
header is sent as a response to the Web browser, and this header causes the
browser to request the original calling.php script.
The result is that the calling page is redisplayed, and the user gets the
impression that they never left it.
Note that I've left out the main body of the action.php script,
in a complete example it would typically write data to the database.
There are two common gotchas to watch for:
You should include an
exitstatement after a call toheader()if you want the script to stop. If you leave out theexitstatement, several additional statements may be executed before the headers are sent and this can lead to unpredictable behavior.The
header()function is the source of a common error message:Warning: Cannot add header information - headers already sent...The
header()function can only be called before HTML is output, and this includes any white space at the top of the file (the output control functions can be used to create an exception to this rule but I won't discuss that here). For example, if there's a blank line or single space character before the script begin tag<?php, then HTML has been output and the error occurs. If you see this error, check your script carefully for characters outside the PHP script tags.
6. The reload problem and how to avoid it.
In the last tip, I showed you how to use the header() function to build a one-component querying module.
In this tip, I'll show you how to use the header() function to avoid another common problem that you'll see in many Web database applications, what I'll call the reload problem.
Let's imagine you've authored the following script that's stored in the
file addcust.php:
<?php
$query = "INSERT INTO customer
SET surname = $surname,
firstname = $firstname";
// Establish a connection to the MySQL DBMS
$connection = mysql_connect("localhost", "fred", "shhh");
// Use the winestore database
mysql_select_db("winestore", $connection);
// Run a query through the connection
$result = mysql_query($query, $connection);
?>
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd" >
<html>
<head>
<title>Customer insert</title>
</head>
<body>
I've inserted the customer for you.
</body>
</html>
?>The script adds a new customer to the customer table and reports its success by outputting an HTML page. Assuming the script is hosted by the Web server at www.webdatabasebook.com, you can request it with the following URL:
http://www.webdatabasebook.com/addcust.php?surname=Smith&firstname=Fred
If you request it once, then the customer is added once. But what happens if you then press refresh or reload? The answer is that a duplicate customer is added! In fact, even resizing the browser or printing the page has the same effect. This is the reload problem.
The reload problem can be solved with the header() function. Here's a new version of addcust.php:
<?php
$query = "INSERT INTO customer
SET surname = $surname,
firstname = $firstname";
// Establish a connection to the MySQL DBMS
$connection = mysql_connect("localhost", "fred", "shhh");
// Use the winestore database
mysql_select_db("winestore", $connection);
// Run a query through the connection
$result = mysql_query($query, $connection);
header("Location: cust_receipt.php");
?>This new script adds the customer and then redirects the browser to a new
resource cust_receipt.php. This new resource looks like this:
<!DOCTYPE HTML PUBLIC
"-//W3C//DTD HTML 4.0 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd" >
<html>
<head>
<title>Customer insert</title>
</head>
<body>
I've inserted the customer for you.
</body>
</html>The overall effect is that the new custadd.php script adds the customer to
the database and then the browser immediately redirects to the receipt page.
The user can then reload the receipt page (or print it, resize it, or bookmark
it), and there are no side effects. The reload problem is solved.
7. Locking for performance in Web database applications.
Locking is primarily used to ensure that database reads and writes can
execute safely.
Tables can be locked with MySQL's default table type by issuing a LOCK
TABLES with either the READ or WRITE option.
When tables are locked with READ, other users can also read, but no one can
WRITE. When a table is locked with WRITE, no one else can read or write the table.
Web
Database Applications with PHP and MySQL discusses the situations where locks should and shouldn't be used in Web database applications.
Locking can also be a useful performance tool to optimize database operations. Imagine a situation where a report is urgently needed. With other users running queries and using system resources, the report may be slow to run. A solution is to use LOCK TABLES with the WRITE option to stop other users running queries or database updates, and to have exclusive access to the report tables for the query duration. This permits better optimization of the query processing by the DBMS, dedication of all of the system resources to the query, and faster disk access.
The downside of locking for performance is that other users can't use the locked tables and, therefore, locking for performance should be sparingly used.
8. Developing fast scripts with mysql_unbuffered_query().
This performance-oriented query function was introduced as an alternative to
mysql_query() in PHP 4.0.6.
Both run an SQL query through a connection, and return a result resource
handle that can be used to process the query results.
The difference between the two functions is that mysql_unbuffered_query()
executes a query and then immediately returns -- the script doesn't block
waiting for the query to finish and the answers to be buffered.
This means that a slow query can be started, and then script processing can
continue while the query runs at the DBMS.
This is a nice feature and, with the few exceptions I discuss next, a call
to mysql_query() can simply be replaced with a call to
mysql_unbuffered_query().
There are a few tricks to using mysql_unbuffered_query():
The number of rows produced by the query can't be checked with
mysql_num_rows()because the total size of the output isn't known.You must finish processing a query on a connection before you issue another. This doesn't mean you have to retrieve all of the results produced by the first query; rather, it means you won't be able to access the results after you run the next query. A workaround is to use two or more DBMS connections opened with
mysql_connect()ormysql_pconnect().A script won't finish until its DBMS connections are no longer active. This is a trap: function calls to
mysql_unbuffered_query()will return immediately, but the script won't end until all of the queries finish running. There's a workaround to this for SELECT queries. If your connection was opened withmysql_connect()then you can close it withmysql_close()and the script will end before the queries do.If your connection was opened with
mysql_pconnect()or the query is an UPDATE, INSERT, or DELETE, then this workaround won't work, and you'll have to wait for the query to complete.
Finding Out More
I've presented a few tips here to help you develop better Web database applications with MySQL and PHP. There's a lot more that PHP and MySQL can do, and our new book, Web Database Applications with PHP and MySQL, covers much of the theory and practice. There's also plenty of information on the Web. Have a look at the PHP Web site as a starting point and, in particular, their Links section.
Hugh E. Williams is a software design engineer at Microsoft's Windows Live Search in Redmond, WA. Previously, he was the Associate Professor in Information Retrieval at RMIT University in Melbourne, Australia.
Return to the ONLamp.com.
You must be logged in to the O'Reilly Network to post a talkback.
Showing messages 1 through 7 of 7.
-
PHP and MySQL vs. WizzyWeb
2003-01-18 13:15:39 anonymous2 [Reply | View]
I'm currently using wizzyweb.com to create web database applications.... wizzyweb generates Perl code with a built-in SQL database engine... would there be an advantage to using PHP and MySQL?
Cheers, Dave C.
-
good article
2002-12-14 01:38:10 anonymous2 [Reply | View]
it's a very good thing to offer such help!
-
Problem using foreach to access mysql_fetch_array
2002-11-28 09:59:33 ddandrea [Reply | View]
The article mentions that you can use the foreach construct to make your code for dumping database query results more readable. Thus:
foreach ($row as $field) echo "<td>$field</td>";
However, if you don't set the result_type parameter of mysql_fetch_array(), this doesn't work properly: each field is accessed and printed twice. I think this is because it goes through each $row element, as numerically indexed, and also as associatively indexed.
To get this to work, set the result_type parameter, like so:
while ($row = mysql_fetch_array($query_result, MYSQL_ASSOC))
Otherwise, the article and the associated book are very good, highly recommended! (at least for newbies like me)
It helped me to finally solve the problem "how to get images into MySQL using PHP",
that I was staring at for a week now, searching all over the web for working examples.
Seeing yours working online made me looking better at why it was not working on my local PC.
I already decided to go write this in Java instead, but I will probably spare myself the trouble now.
Next time I walk into a suitable bookshop, I will consider buying the book.
That said, I needed to make a few changes to make the example work. The code worked fine unchanged on
my website running Linux/PHP4.1.2, but not on my laptop, running XP/PHP4.2.3. The php.ini files
look very similar on both.
1. It seems that you cannot read uploaded files, you have to move them first:
// if (is_uploaded_file($userfile)) ... replaced by:
// Was a file uploaded?
$uploaddir = 'c:/phpdev/uploads/';
$uploadfile = $uploaddir . $_FILES['userfile']['name'];
if (move_uploaded_file($_FILES['userfile']['tmp_name'], $uploadfile))
{
...
2. My jpegs seem to have mime-type image/pjpeg, that is not recognized.
3. On windows you need to read files in binary mode, otherwise the upload looks ok,
but the database contains only part of the file:
$file = fopen($uploadfile, "rb");
4. Register_globals needs to be "On" in php.ini
5. This does not work (gives compile error in Eclipse with PHP plugin) in view.php:
header("Content-Type: {$data["mimeType"]}");
obviously a quote prblem.
6. Similar problem in receipt.php (twice):
<td><?php echo "{$row["shortName"]}";?></td>
After having made these fixes, it works on my laptop too, and I can start adding EXIF and IPTC functionality now.
Thanks again.