mysql_fetch_array, would like to fetch certain information

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Bios___
Forum Newbie
Posts: 5
Joined: Sat Jan 17, 2009 12:39 pm

mysql_fetch_array, would like to fetch certain information

Post by Bios___ »

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:

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'];
            }
}   
?>          
            
 
 
But this failed, and only printed the category names.

Thanks in advance.
watson516
Forum Contributor
Posts: 198
Joined: Mon Mar 20, 2006 9:19 pm
Location: Hamilton, Ontario

Re: mysql_fetch_array, would like to fetch certain information

Post by watson516 »

Try replacing

Code: Select all

$query2 = mysql_query("SELECT * from subcategories where categoryId='".$i++."'");
With

Code: Select all

$query2 = mysql_query("SELECT * from subcategories where categoryId='{$row['categoryId']}'");
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: mysql_fetch_array, would like to fetch certain information

Post by Burrito »

why not just do it with one query

Code: Select all

 
$query = "SELECT `c`.`cateogry`,`sc`.`subcategory` FROM `categories` AS `c` INNER JOIN `subcategories` AS `sc` ON `c`.`id` = `sc`.`categoryid`";
 
Bios___
Forum Newbie
Posts: 5
Joined: Sat Jan 17, 2009 12:39 pm

Re: mysql_fetch_array, would like to fetch certain information

Post by Bios___ »

What are the C, and SC meant to represent?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: mysql_fetch_array, would like to fetch certain information

Post by Eran »

Those are table aliases. They aren't necessary, but useful when you want use multiple tables in a query and would like to avoid verbosing the entire table names everywhere.
Bios___
Forum Newbie
Posts: 5
Joined: Sat Jan 17, 2009 12:39 pm

Re: mysql_fetch_array, would like to fetch certain information

Post by Bios___ »

I'm sorry, but that has confused me. What should the statement look like?

Also, thank you watson516, but your query didn't work.
Bios___
Forum Newbie
Posts: 5
Joined: Sat Jan 17, 2009 12:39 pm

Re: mysql_fetch_array, would like to fetch certain information

Post by Bios___ »

My apologies, watson, your code did work...But gave me this message : -

Code: Select all

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'Resource id #8' at line 1
Bios___
Forum Newbie
Posts: 5
Joined: Sat Jan 17, 2009 12:39 pm

Re: mysql_fetch_array, would like to fetch certain information

Post by Bios___ »

Bump, help please! :D
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Re: mysql_fetch_array, would like to fetch certain information

Post by Burrito »

what else do you need help with?

I gave you a sample query that will achieve exactly what you're looking to do.
Post Reply