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 $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.4 Example code All of the example code can be found in: select.php3.