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

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