Printing different number of data from DB

[Rommeo]

I was using simple queries and printing them like :

<?php
$query="SELECT * FROM category";
$result=mysql_query($query);
$total=mysql_num_rows($result);

$c = 0; 
while($c < $total) 		{
$name= mysql_result($result,$c,"name");
$info=  mysql_result($result,$c,"info");
$c++;
}
?>

 

Now i need to print the different values of data;

like :

$query = "select * from subcategory where catid = ( select id, from category )";

lets say it has to return "20 subcategory and 7 categories".

 

Now how am i gonna use the values ?

if i use while loop here again, it wont be right i guess since there are just 7 categories.

 

My problem is actually i want to print the categories like

-cat1

--subcat1

--subcat2

-cat2

--subcat2-1

--subcat2-2

 

[gwolgamott]

I think maybe using multidimensional arrays may work for this, then your able to loop each array and it's sub-arrays using nested foreach loops... not quite sure how you'd want to set that up. But may be worth looking in to.

[Rommeo]

My DB is like :

 

Category :

cid | cname | cinfo

 

Subcategory

scid |scname | scinfo | categoryid

 

The result should be like :

cat1

--subcat1 ( belongs to cat1 )

--subcat2 ( belongs to cat1 )

cat2

--subcat1 ( belongs to cat2 )

--subcat2 ( belongs to cat2 )

[Rommeo]

any ideas ?

[Psycho]

$query = "SELECT cname, scname
          FROM Category c
          JOIN Subcategory sc ON c.cid = sc.categoryid";
$result = mysql_query($query);

$current_category = '';
while($record = mysql_fetch_assoc($result))
{
    //Display category header (if new)
    if($current_category != $record['cname'])
    {
        $current_category = $record['cname'];
        echo "<b>{$current_category}</b><br />\n";
    }

    //Display subcategory
    echo "--{$record['scname']}<br />\n";
}

 

You should really consider making the field names consistent. For example, in the Category table you use "cid" but in the Subcategory table you use "categoryid" for the foreign key value. If they were the same, then it is apparent that they are related. Also, it allows you to do a JOIN using the USING keyword. For example, if you used cat_id in both tables the query could look like this:

SELECT cname, scname
FROM Category c
JOIN Subcategory sc USING (cat_id)

[Rommeo]

Thank you so much mjdamato,

now i m trying it.

[Rommeo]

its almost done,

the query is working but the problem is, it gives me a result like :

 

cat1

--subcat1 ( belongs to cat1 )

cat2

--subcat1 ( belongs to cat2 )

 

it does not print the second subcategories, like :

 

cat1

--subcat1 ( belongs to cat1 )

--subcat2 ( belongs to cat1 )

cat2

--subcat1 ( belongs to cat2 )

--subcat2 ( belongs to cat2 )

[Psycho]

Let's see the code you implemented. I see no reason it would do as you say.

[Rommeo]

Yes you were right, i changed it back to "topic solved" mode.

Sorry that i can not delete or edit the post i sent.

It works right, i just noticed that i wrote somewhere wrong.