Basic Selects

The majority of the time in most database applications is spent trying to get the information back out of the database. After all, you put the data in there to be used. Depending on how much information you're looking to pull out of the database, you can take some short cuts.

3.1 Selecting A Single Item

The most trival case is retrieving a single item from the database. Usually you've got the key value for some row of the databsae and you only need a single field from it. For example, given the product ID number from a catalog, what is the shipping weight?

Using the example database that you setup just a moment ago, you can select the name of any particular person if you already have the ID. The function call QueryItem returns a scalar value. If there is an error accessing the database, then the error message will be printed out and the PHP script exited.

$name = $sql->QueryItem("select Name from TEST where ID = 4");

$name is now set to "Leroy Longrad".

Note: If the SQL statement you use returns more than a single item, only the first item of the first row is returned.

3.2 Selecting A Single Row

Similar to selecting a single item, many times all you need from the database is a single row of information. If you're printing out a form with a clients information on it, then all you really need is the single row for that particular client.

$sql->QueryRow("select * from TEST where ID = 4");
$row = $sql->data;

In this case, the values of $row are:

$row[0] = 4;
$row[1] = "Leroy Longrad"
$row[2] = 45;
$row[3] = 63000;

One useful aspect of the associated arrays in PHP, you also access the information by referencing the column names.

$row[ID]     = 4;
$row[Name]   = "Leroy Longrad"
$row[Age]    = 45;
$row[Salary] = 63000;

3.3 Selecting Multiple Rows

Finally there's the case where you need to loop through many, many rows of the database. You're generating a list, writing a table, or perhaps populating a pull-down menu.

First you'll need to issue the SQL query and then itterate through the resulting rows. For example, to create a table of Names and ages from the test table, the code would be:

echo("<table>\n");
echo("<tr><th>Name</th><th>Age</th></tr>\n");
$sql->Query("Select Name, Age from TEST order by Age");
for ($i = 0; $i < $sql->rows; $i++) {
    $sql->Fetch($i);
    $name = $sql->data[0];
    $age = $sql->data[1];
    echo("<tr><td>$name</td><td>$age</td></tr>\n");
}
echo("</table>\n");     

3.5 Nested SQL commands

A very common beginner mistake is to nest SQL commands and attempt to use the same MySQL_class for both commands. For example, the following code snippet will NOT work as expected:

$sql = new MySQL_class;
$sql->Create("my_db");
$sql->Query($query);
for ($i = 0; $i $lt; $sql->rows; $i++) {
    $sql->Fetch($i);
    $id = $sql->data[0];
    $sql->Insert("insert into TEST values(0,$id)");
}

The above code snippet works fine if the initial query only returns one row of data. However if it returns more than one, the $sql->Insert call wipes out the data set that you are the midst of going through with the for() loop.

To correctly handle this case, all you need to do is declare another database handle and use that for the SQL commands inside the for() loop. i.e.:

$sql = new MySQL_class;
$sql->Create("my_db");
$sql2 = new MySQL_class;
$sql2->Create("my_db");
$sql->Query($query);
for ($i = 0; $i $lt; $sql->rows; $i++) {
    $sql->Fetch($i);
    $id = $sql->data[0];
    $sql2->Insert("insert into TEST values(0,$id)");
}

3.5 Example code

All of the example code can be found in: select.php.