Main MySQL Page

Selecting Information

Once the MySQL server has been accessed and the database has been selected, you can request information from a table in the database.

There are three basic steps.

1. Compose a query
2. Send the query to the server
3. Process the data returned by the server

We will look at the various cases listed in the Quick Links above. They vary in complexity.

Back to Top

Simple One-Table Examples

List All First Names (Unsorted)

As a simple initial example, we will print a list of all of the first names in the 1960 table in the pirates database.

It is good practice to construct a query as a string before sending it to the MySQL server. In this case, we will ask that all first names from the 1960 table be returned to us in an array.

$sql_query = "SELECT fname FROM `1960` ";

The mysql_query() function sends its argument to the MySQL server and returns a Resource id for the array created.

$result = mysql_query($sql_query ) or die ("cannot find 1960");

For each record in the database table, mysql_fetch_array() returns an array using the MySQL field labels as subscripts. In the loop below, we get a one-element array because we requested only the fname field in our query.

for($i = 1; $row = mysql_fetch_array($result); $i++) {
   echo "<P>$i " . $row['fname'] . "</P>";
}

This code is used in the Player First Names script to print an enumerated unsorted list of all first names in the 1960 table.

Back to Top

List All Surnames (Sorted Alphabetically)

The data in the 1960 table was intentionally entered in non-sorted order in order to demonstrate how the MySQL server can be requested to sort it based on a given field/column.

$sql_query = "SELECT lname FROM `1960` ORDER BY lname ";
$result = mysql_query($sql_query ) or die ( "cannot find 1960");

Here again we are requesting the data from a single column in the table, this time the field of surnames. However, instead of accepting the order in which the records were entered into the table, we will ask that they be given in reverse alphabetical order.

This code is used in the List Surnames (Sorted Alphabetically) script.

Back to Top

List All Surnames (Sorted in Descending Order)

Here again we are requesting the surname data, but we will ask that they be given in reverse alphabetical order.

$sql_query = "SELECT lname FROM `1960` ORDER BY lname DESC ";

This code is used in the List Surnames (Sorted in Descending Order) script.

Back to Top

Get and Print All Info for One Player

One of the things we would want to do is allow a visitor to our site to ask for information from a particular record. Here we will look at code which uses a surname as a means of accessing a record. The following line of code is designed to get all of this information placed in an array called $playerinfo.

$playerinfo = getplayerinfo($playersurname);

The function called, getplayerinfo(), uses the player's surname in a WHERE clause to restrict what is retrieved from the database.

function getplayerinfo($playersurname)
{
   $sql_query = "SELECT * FROM `1960` WHERE lname='$playersurname' ";
   $result = mysql_query($sql_query ) or die ("$playersurname not found");
   return mysql_fetch_array($result);
} // end getplayerinfo

The field names in the table are still used in $playerinfo, the array to which the data is assigned. The following code prints the labeled information.

echo "<P>Name: {$playerinfo['fname']} {$playerinfo['lname']}</P>";
echo "<P>At Bats: {$playerinfo['ab']}</P>";
echo "<P>Hits: {$playerinfo['hits']}</P>";
// print average in .nnn format
echo "<P>Average: ";
$ave = sprintf("%.3f", $playerinfo['ave']);
echo ltrim($ave, "0");
echo "</P>";

This code is used in the listplayerinfo.php script which is called from the forms in Get Player Info Using a Text Box and Get Player Info Using a Select Box.

Back to Top

Print All Players & Info in an HTML Table

One of the main tasks which programmers perform is to display all of the information found in a database table in an HTML table. This is actually a composite created from code similar to that discussed above.

$sql_query = "SELECT * FROM `1960` ORDER BY lname ";
$result = mysql_query($sql_query ) or die ( "Table 1960 not found");

This code provides access to an array with the records sorted by the surnames of the players.

The following HTML/PHP code generates the HTML table.

<table align=center cellpadding=3 border=1>
<tr><th colspan=4>1960 Pittsburgh Pirates</th></tr>
<tr>
<th>Name</th>
<th>AB</th>
<th>H</th>
<th>BA</th>
</tr>
<?php
while($player = mysql_fetch_array($result)) {
   echo "<tr>\n";
   echo "<td align=left>{$player['fname']} {$player['lname']}</td>\n";
   echo "<td align=right>{$player['ab']}</td>\n";
   echo "<td align=right>{$player['hits']}</td>\n";
   echo "<td align=right>";
   $ave = sprintf("%.3f", $player['ave']);
   echo ltrim($ave, "0");
   echo "</td>\n";
   echo "</tr>\n";
} // end of while
echo "</table>\n";
?>

This code is used in the script to produce the Complete Player List.

Back to Top

List All Info (Sorted in Descending Order by Average and then Hits)

One of the problems with sorts is that there can be multiple occurrences of en element in a particular field. Just think of the phonebook, where there can be pages and pages of Smiths in a large North American city. Overall the entries are sorted by surname and then by first name. This can easily be done with SQL.

In baseball you can have several players with the same batting average, and the players can be listed by average in descending order. Those players who have the same average can be listed either by surname in alphabetical order or by the number of hits in descending order. Either way can be considered useful in its own way. In this set of data, two of the players, Bill Mazeroski and Bill Skinner, hit .273.

Here we will sort first by average then by hits, both in descending order.

$sql_query = "SELECT * FROM `1960` ORDER BY ave DESC, hits DESC ";
$result = mysql_query($sql_query ) or die ( "Table 1960 not found");

This code is used in the script to produce the Bi-Level Sort.

Back to Top

One to Many

Back to Top

List a Customer's Orders

If we were to have data about the Pittsburgh Pirates for multiple years, we would need to reorganize the simple database we used above. For each year, we could have a separate record for each player. This would result in needless repetition of the players' names. Instead we could set up a table with each individual's personal information, e.g., name, birthday, place of birth, etc. Then, in a another table, there would be a separate record for each player's statistics for a given year.

Instead of continuing with the Pirates' data, let us have a look at another situation. A company uses a database for keeping track of its customers' orders. One table has all of the personal information about each customer. Each customer is assigned a unique five-digit customer ID.

A second table has a record for each time an item is ordered. It includes the item, its ID, the order date, the quantity ordered and the price. It also has the ID of the person who made the order.

To list the orders made by a John Gray of Lynden, Washington, the following query could be used:

$sql_query = "SELECT customers.firstname, customers.lastname, customers.city, customers.state, "
$sql_query .= "items_ordered.order_date, items_ordered.item, items_ordered.quantity, items_ordered.price ";
$sql_query .= "FROM customers, items_ordered ";
$sql_query .= "WHERE customers.customerid = items_ordered.customerid AND customers.customerid = 10101 ";
$sql_query .= "ORDER BY items_ordered.order_date ";

This code is used in the script to produce the Customer's Orders.

Back to Top

Many to Many

Very frequently situations arise where there are multiple associations between items in two groups, i.e., each item in one group could be associated with multiple items in the other.

Here we will look at a list of six authors which are in the authors_tbl table.

authors_tbl
id  lname      fname   mname  other
1   Bross      Irwin   D.J.
2   Shapiro    Paul
3   Stermole   David   F.
4   Anderson   Barbara
5   Gleason    H.      A.     Jr.
6   Priore     Roger

Each of the authors has (co-)written at least one of the six articles/books which are in the works_tbl table.

works_tbl
id  title
1   Computer-Assisted Discourse Analysis of a Jargon
2   Acorn (Automated Coder Report Narrative): An Automated Natural-Language Question-Answering System for Surgical Reports
3   Feasibility of Automated Information Systems in the Users' Natural Language
4   The Gutenberg Word Processor User Reference Manual
5   Word Processing with Gutenberg
6   American Ethnonyms for Slovenians

To keep track of who wrote what, a table called authwork_tbl is used. It pairs up an author ID with a publication ID.

authwork_tbl
id  author_id  work_id
1   1             1
2   3             1
3   2             2
4   3             2
5   1             3
6   6             3
7   2             3
8   4             3
9   3             3
10  3             4
11  5             4
12  3             5
13  5             5
14  3             6

Thus, it can be seen that authors 1 and 3, Bross and Stermole, respectively, were co-authors of work 1, "Computer-Assisted Discourse Analysis of a Jargon", record IDs 1 and 2 in authwork_tbl. It can also be seen that author 1, Bross, was a co-author of works 1 and 3.

Back to Top

List All Authors of an Item

Let us find all of the authors for a specific item, here The Gutenberg Word Processor User Reference Manual. Its ID from the works_tbl is 4 and has been passed from one script, whowrotethis.php to another using whowrotethis2.php?workid=4. Since just the ID for the work has been passed, the following query is used to get the work's title.

SELECT works_tbl.title FROM works_tbl WHERE works_tbl.id=4

This allows the title to printed out. Now we can issue a query to get the list of the authors' names.

SELECT authors_tbl.fname, authors_tbl.mname, authors_tbl.lname, authors_tbl.other 
   FROM authors_tbl, authwork_tbl 
   WHERE authwork_tbl.work_id=4 AND authwork_tbl.author_id=authors_tbl.id 
   ORDER BY authors_tbl.lname

Thus, the following could be the resultant response.

Title: The Gutenberg Word Processor User Reference Manual
   H. A. Gleason Jr.
   David F. Stermole

Back to Top

List All Items by an Author

Let us find all items authored by Paul Shapiro. His ID in the authors_tbl is 2. The following query will obtain the IDs in the works_tbl of all of the works that Shapiro worked on. This is a two-step process where the second step requires a separate query for each title.

SELECT works_tbl.id FROM works_tbl, authwork_tbl 
   WHERE (authwork_tbl.author_id=2 AND authwork_tbl.work_id=works_tbl.id)

The result of this query would be a list of two works' IDs, 2 and 3. To get the titles, we would issue the following pair of queries.

SELECT works_tbl.title FROM works_tbl WHERE works_tbl.id=2
SELECT works_tbl.title FROM works_tbl WHERE works_tbl.id=3

A simple printout of the information could be the following.

Paul Shapiro
   Acorn (Automated Coder Report Narrative): An Automated Natural-Language Question-Answering System for Surgical Reports
   Feasibility of Automated Information Systems in the Users' Natural Language

This accessing of information from the bibliography database can be seen where the author's ID, 2, is passed from one script, getauthorsworks.php, to a second script, listauthorsworks2.php?authorid[]=2.