PHP tutorials
Question:
On the left is the output from a query joining two tables. The first field is the fruit category, the second field is the fruit item.
Stone
Plums
Nectarines
Peaches
Citrus
Lemons
Limes
Oranges
Grapefruits
Stone Plums Stone Nectarines Stone Peaches Citrus Lemons Citrus Limes Citrus Oranges Citrus Grapefruits
None of the items are repeated in the tables, and the query works in regards to extracting the right information, however I want to display the category once, and then list all the fruit for that category under it as shown on the right. Here is the query used.
SELECT fruit_category_desc, fruit_item_name FROM fruit_item, fruit_category WHERE fruit_item_category_id = fruit_category_id
Answer:
Fruit Category Table
CREATE TABLE fruit_category (
fruit_category_id smallint(3) NOT NULL auto_increment,
fruit_category_desc varchar(30) NOT NULL default '',
PRIMARY KEY (fruit_category_id),
KEY fruit_category_desc (fruit_category_desc)
) TYPE=MyISAM
COMMENT='Fruit Category Validation Table';
The query yields the correct results, however the result format is not suitable for display on the web. Even though this is a simple table join, being able to display the results like that shown on the right hand side only requires a small tweak with the code.
First we create and execute the query. We also declare a variable, $prev_cat, and assign an empty string to it. This variable will be used to determine if the fruit_category_desc field is printed.
$sql = "SELECT fruit_category_desc,
fruit_item_name
FROM fruit_item, fruit_category
WHERE fruit_item_category_id = fruit_category_id";
$result = mysql_query($sql);
$prev_cat = '';
Fruit Item Table
CREATE TABLE fruit_item (
fruit_item_id smallint(5) NOT NULL auto_increment,
fruit_item_category_id smallint(6) NOT NULL default '0',
fruit_item_name varchar(30) NOT NULL default '',
PRIMARY KEY (fruit_item_id),
KEY fruit_item_category_id (fruit_item_category_id)
) TYPE=MyISAM
COMMENT='Stores a List of all the Fruit';
If the $prev_cat value is different from the value of the fruit_category_description, then that fruit_category_description is displayed on the browser. If they are the same, then nothing happens, the field is not displayed on the browser.
while($row = mysql_fetch_array($result, MYSQL_NUM))
{
if($prev_cat != $row[0])
{
$prev_cat = $row[0];
echo "<p><b>$prev_cat</b></p>";
}
echo "{$row[1]}<br />";
}
Output - shown on the right hand side above.
Additional Notes:
Another technique is to break the query into two separate queries and have nested while loops. This can be useful for complex queries.
PHP Reference Manual
mysql_fetch_array - Fetch 1 row of data from the result resource
mysql_query - Execute query
MySQL
How do you display the contents of a table on the browser?
Learn how to connect to a database, write and
execute a query and display the results on the browser
Comments or questions relating to this article have been disabled. They will be back soon.
Copyright
Let notice be given that this is copyright information