mysql_fetch_array, would like to fetch certain information
Posted: Sat Jan 17, 2009 1:12 pm
I have a database, with two tables.
Category table: -
+------------+----------+
| categoryId | category |
+------------+----------+
| 1 | Games |
| 2 | Software |
| 3 | Hardware |
| 4 | Film |
+------------+----------+
Subcategories table: -
+---------------+------------+-----------------+
| subcategoryId | categoryId | subcategoryName |
+---------------+------------+-----------------+
| 1 | 1 | xBox |
| 2 | 1 | Playstation 3 |
| 3 | 1 | Wii |
| 4 | 1 | PC |
| 5 | 2 | Mac |
| 6 | 2 | Windows |
| 7 | 2 | Linux |
| 8 | 3 | Laptop |
| 9 | 3 | Desktop |
| 10 | 4 | Action |
| 11 | 4 | Comedy |
| 12 | 4 | Romance |
| 13 | 4 | Horror |
+---------------+------------+-----------------+
I have been trying to use different PHP statements to display the category and subcategory names in a particular order. So that one category name would be listed, then all subcategories relating to that category would be listed. But to no avail.
I had hoped for the statements to produce the following: -
Games
xBox
Playstation 3
Wii
PC
Software
MAC
Windows
Linux
Etc etc.
I am new to PHP and have only been studying for about 4 months, so require assistance in this matter, I tried this:
But this failed, and only printed the category names.
Thanks in advance.
Category table: -
+------------+----------+
| categoryId | category |
+------------+----------+
| 1 | Games |
| 2 | Software |
| 3 | Hardware |
| 4 | Film |
+------------+----------+
Subcategories table: -
+---------------+------------+-----------------+
| subcategoryId | categoryId | subcategoryName |
+---------------+------------+-----------------+
| 1 | 1 | xBox |
| 2 | 1 | Playstation 3 |
| 3 | 1 | Wii |
| 4 | 1 | PC |
| 5 | 2 | Mac |
| 6 | 2 | Windows |
| 7 | 2 | Linux |
| 8 | 3 | Laptop |
| 9 | 3 | Desktop |
| 10 | 4 | Action |
| 11 | 4 | Comedy |
| 12 | 4 | Romance |
| 13 | 4 | Horror |
+---------------+------------+-----------------+
I have been trying to use different PHP statements to display the category and subcategory names in a particular order. So that one category name would be listed, then all subcategories relating to that category would be listed. But to no avail.
I had hoped for the statements to produce the following: -
Games
xBox
Playstation 3
Wii
PC
Software
MAC
Windows
Linux
Etc etc.
I am new to PHP and have only been studying for about 4 months, so require assistance in this matter, I tried this:
Code: Select all
<?php
$query = "SELECT category FROM category";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result))
{
echo "<p>";
echo $row['category'];
echo "</p>";
$i=1;
$query2 = mysql_query("SELECT * from subcategories where categoryId='".$i++."'");
$result2 = mysql_query($query2) or die(mysql_error());
while($row2 = mysql_fetch_array($result2))
{
echo $row2['subcategoryName'];
}
}
?>
Thanks in advance.