Selecting from MySQL Databases

SELECT * FROM

The SQL for retrieving information from our example table would look something like this:

• <?php
  • $sql = "SELECT * FROM `comments`";
• ?>

"SELECT * FROM `tablename`" means give me every row from `tablename`. If we only wanted to get the `name` column we could say "SELECT `name` FROM `comments`"

WHERE

If we don't want to get all of the rows, we can specify which rows we want using "WHERE". After the WHERE clause, you insert conditions like "name = 'Greg'". Multiple conditions are used with having AND or OR between them. This is really awesome because searching for just one record in a file would be much too difficult to explain here. So if we wanted to find everyone that filled out this form in the last 24 hours it would be very simple. First we need to mess around with the time thing. Since time() is set up in seconds and it returns the time now, we should be able to subtract the number of seconds in 24 hours. We are also allowed to use < and > comparisons. The resulting SQL would look like the following:

• <?php
  • $sql = "SELECT * FROM `comments` WHERE `created` > ($time - 24*60*60)";
• ?>

ORDER BY

If we have a lot of people that have used our form in the last day, we might want to sort them by their name. To do this we add "ORDER BY" to the end of our SQL. After that you need to specify which column you want it to be sorted by and then say either ACS or DESC. If we wanted to sort by names A to Z the SQL would look like the following:

• <?php
  • $sql = "SELECT * FROM `comments` WHERE `created` > ($time - 24*60*60) ORDER BY `name` ASC";
• ?>

LIMIT

If we have thousands of rows in a database we might not want to get all of them. Using LIMIT we can specify how many we want to see and which record to start counting from. The first number after LIMIT is where to start from. Then you use a comma and say how many to get. If we wanted to add on to the previous SQL and show records 10 to 20 it would look like the following:

• <?php
  • $sql = "SELECT * FROM `comments` WHERE `created` > ($time - 24*60*60) ORDER BY `name` ASC LIMIT 10, 10";
• ?>

mysql_fetch_array

Now that we have stuff from the database we need to use it. There is a cool php function called mysql_fetch_array that takes the results and puts them in an array for us. We use this function and a while loop to go through everything returned from the database. The code will look like the following:

• <?php
  • $link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
  • mysql_select_db('db_name', $link);
  • $sql = "SELECT * FROM `comments` WHERE `created` > ($time - 24*60*60) ORDER BY `name` ASC LIMIT 10, 10";
  • $result = mysql_query($sql, $link);
  • while ($row = mysql_fetch_array($result)) {
    • echo "Name: ".$row['name'].", E-mail: ".$row['email']."<br />";
  • }
• ?>