Transcript PHP_Bible

PHP Bible
Chapter 24: PHP Efficiency and Style
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
1
 Wiley and the book authors, 2002
Summary



Using database resources efficiently
Making the database work for you
Timestamping insertions and updates
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
2
 Wiley and the book authors, 2002
PHP Efficiency and style


This chapter is for people making database-enabled PHP web
sites who suspect that they are doing things awkwardly or
inefficiently
Included are some tips and tricks for making things run faster
and show some common ways that database systems can save
you from writing unnecessary PHP code
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
3
 Wiley and the book authors, 2002
Connections – Reduce, Reuse, Recycle

One important thing to realize is that establishing an initial connection with a
database is never a cheap operation



Unless your PHP script is doing some unusually computationally-intensive work,
the overall database interaction will be the most time and resource-intensive part
of your code, and it is frequently true that the establishment of a connection is
the most expensive part of code that interacts with a database, even if the
connection is only established once in serving the page
One thing that is surprising is that with many database programs, it is
possible to even retain the results from more than one query at once, even
though only one connection has been opened
If the overhead of opening new database connections is killing your
performance, you may want to investigate opening persistent connections



Unlike regular db connections ,these connections are not automatically killed
when your page executes, but are saved in a pool for future use
The first time a script opens a connection in this manner, it is opened in the same
was as with a regular db connection, the next script will get the same connection
if the parameters of the new request are identical
To create a persistent connection to a MySQL db, use mysql_pconnect()
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
4
 Wiley and the book authors, 2002
Make the database work for you



As when writing code in a programming language, writing code
that interacts with a database is an exercise in appropriate
division of labor
People who write programming languages and databases have
agreed to automate, standardize, and optimize certain tasks that
come up over and over again in programming, so that
programmers don't have to constantly reinvent the wheel when
making their individual applications
As with sorting and other common tasks used within
programming, if you can find a function that can perform that
task, use it instead of writing your own. It will generally be
much faster than what you can write and it will almost always
be easier
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
5
 Wiley and the book authors, 2002
Make the database work for you (cont.)


In particular, any searching or sorting of the contents of a
database is best done within that database, rather than by your
own code
In this example, the query returns the entire dataset of the
author table, instead of just what is needed for the application
function print_first_name ($lastname, $dbconnection)
{
$query = 'select firstname, lastname from author';
$result_id = mysql_query($query, $dbconnection) or die ('can\'t query');
while ($row = mysql_fetch_array($result_id))
if ($row['lastname'] == $lastname)
print('The first name is '.$row['firstname']);
}

When this function is executed, it will print out every associated
firstname for the lastname that is passed in as an argument
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
6
 Wiley and the book authors, 2002
Make the database work for you (cont.)

The problem is that we don't need to grab all the data in this
table, pull it through the connection, and then pick and choose
from it on our side of the pipe
Instead, we should restrict the query with a WHERE clause

function print_first_name ($lastname, $dbconnection)
{
$query = 'select firstname from author WHERE (lastname="'.$lastname.'")';
$result_id = mysql_query($query, $dbconnection) or die ('can\'t query');
while ($row = mysql_fetch_assoc($result_id))
print('The first name is '.$row['firstname']);
}
The WHERE clause ensures that only the rows we care about
are selected in the first place
 Not only does this cut down on the data passed over the SQL
connection, the code used to locate the correct rows on the
database side is almost certainly quicker than the previous PHP
code
_______________________________________________________________________________________________________________

PHP Bible, 2nd Edition
7
 Wiley and the book authors, 2002
Sorting and aggregating




Exactly the same argument applies if you find yourself writing code to sort results
that have been returned from your database, or to count, average, or otherwise
aggregate those results
In general, the ORDER BY syntax in SQL will allow you to presort your retrieved
rows by any prioritized list of columns in the query, and that sort will probably be
more efficient than either homegrown code or the PHP array-sorting functions
Similarly, rather than looping through DB rows to count, sum, or average a value,
investigate whether the syntax of your particular database's flavor of SQL supports
the GROUP BY construct, and in-query functions like count, sum, and average
In calculated fields especially, you can use the AS keyword to specify/change the
field name that gets returned in the query
$sql = 'SELECT count(ID) AS ID_Count FROM author';

Will return a one-field (ID_Count) record which counts the occurrences of ID in the table author
$sql = 'SELECT sum(charge) AS Charge_Sum FROM order_details GROUP BY
order_id';

Will return a record for each unique order_id in order_details calculating the sum of the charge field
within each group
$sql = 'SELECT ID FROM author ORDER BY ID LIMIT 1';
$sql = 'SELECT MIN(ID) AS Min_ID FROM author';

Both of these queries will return the minimum author ID from the author table, but the second query will
execute faster since once the minimum ID is found, it will not have to re-order the remaining records
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
8
 Wiley and the book authors, 2002
Creating date and time fields





It is very common to want to associate a date and/or time with a row's data
(e.g. your table rows may represent requests made by your Web users and
you want to store the date and time that request was made)
One way to accomplish this task would be to include a string that represents
the desired date in a format parsable by your database
This will work as long as the format you are attempting to store is readable
as a date by your db
This also requires you to query the system to find the current date and time
before you can construct the appropriate string
This is unnecessary in most databases since they have a current-date function
$sql = 'UPDATE mytable SET mydate = now() WHERE (record_id = 1);

This can also be supplanted by the usage of certain data formats in the
creation of your tables in your database
 MySQL includes a data type called TIMESTAMP which will
automatically store the current date and time that a record is added or
updated if the field in the UPDATE or INSERT query is not populated or
not specified
_______________________________________________________________________________________________________________
PHP Bible, 2nd Edition
9
 Wiley and the book authors, 2002