PHPBuilder - MySQL and PHP: How to make it work without killing your server Page 3

RSS Twitter

MySQL and PHP: How to make it work without killing your server - Page 3

by: PHP Builder Staff
May 13, 2009

Data is data
When you get to the point that you are using the same data over and over on the same page, the time has come to save it into an array which can be referenced over repeatedly without having the resource of query after query wasted.
Begin by building the query from what you know you will need from the database. Let us assume we are going to print out user profile information for a specific user from the user table of our database.

  $query = $db->getArray("select id, name, surname, age, email, country, city from user where id=6 limit 1");

You will have noticed the following from my code:

I did not use select *
This is important. If you dont need every single field from the database, dont waste server time by asking for it. Select only the fields you need.

I used limit 1
This speeds your query up nicely. If you are only looking for a single row as a result, using limit 1 simply tells mysql to stop looking once it has found a result. Not doing so means that MySQL will continue looping through the table anyway, wasting time and resources for no reason.
Now I have all the table data I will require safely stored in an array that I can reference any time I need it. The format of the array will look like this:

  $id      =$query[0]['id'];
  $name    =$query[0]['name'];
  $surname =$query[0]['surname'];
  $age     =$query[0]['age'];
  $email   =$query[0]['email'];
  $country =$query[0]['country'];
  $city    =$query[0]['city'];

In a similar vain, if we wanted to list all of the users in a list of lets say name, surname and age, we could do the following:

  $query = $db->getArray("select name, surname, age from user order by name");

This query will return an array that can be manipulated as follows:

  foreach($query as $user){
  echo $user['name'].' '.$user['surname'].' '.('. $user['age'] .')';
  echo '<br />';

The above will print a neat list like this:

John Arbuckle (32)
Kevin Smith (28)
Mike Carrigan (43)

And of course you could do tons more with that array, and you should not need much more than that in order to get the results you need.

In Conclusion
In this article we explored a few techniques that will not only improve our coding style, but help to make our scripts execute faster. We also had a short peak at a database management class that we will hopefully be able to have a closer look at in the near future. Part of our database optimization showed how to store database results in an array, thereby making scripts execute faster because there is no need for repeat queries. We also had a brief look at how to manipulate the resulting arrays.

Until next time, Good Luck!

Marc Steven Plotz

« Previous Page

Comment and Contribute

Your comment has been submitted and is pending approval.




(Maximum characters: 1200). You have characters left.