table join question.....

[jplush76]

hey guys, just a simple question for most of you... I'm just learning about php + mysql and joining of tables

I have 2 tables:
cats(categories) fields are: cat_id, cat_name

equip(equipment) fields are: eq_id, eq_name, eq_qty, cat_id

If I want to display a list of all the rows of equipment but have them broken into category sections, how would I go about doing that?

Would I need to make a loop within a loop?

I'd like it to display like this:

TAPE MACHINES
studer std 4999 1"
panasonic gtd 14mm

MONITORS
jbl 13ft cable headphones
MIIK power amp

etc.....

thanks all!

[[deleted]]

If you want to display it like in the example, then the simplest way is to use a loop.

If you use a JOIN you will just get rows containing all the data, and you will have to filter manually that to see where the category changes from 'TAPE MACHINES' to 'MONITORS' etc.

[zalath]

Suppose you want to list all the categories availabe first. You'd do a SELECT to the category table, and print, say, links where you'd pass the category id as a GET parameter - like <a href="scriptname.php?catid=$cat_id"> and so forth. Then the new script (or the same script even) could list all the products in teh category by simply ding a SELECT * FROM tablename WHERE cat_id = $catid"

Am I doing any good?

-L-

[jplush76]

do I need to do a loop within a loop?

like....

loop 1
echo categories

loop 2
then check for products that have that cat_id
echo products
end loop 2

end loop 1

?

[jplush76]

here is the code I have so far but its not working...

<?
$db = mysql_connect("localhost", "jimbo", "passhide");
mysql_select_db("4th");

$query = "select cat_name, eq_name from cats, equip where equip.cat_id = cats.cat_id order by cat_name, eq_name";

$result = mysql_query($query);
$num_rows = mysql_numrows($result);

$rows = mysql_fetch_array($result);

$lastcat = '';

$i = 0;
while ($i < $num_rows)
{

if ($lastcat != ($rows["cat_id"]))

{
	echo ($rows["cat_name"]);
}

$lastcat = ($rows["cat_id"]);
++$i;
}

?>

[zalath]

If you want to list them all on one page but separate the categories:

$SQL = "SELECT *,c.name FROM equip e, cats c ORDER BY cat_id,eq_name WHERE c.cat_id = e.cat_id";
$result = mysql_query($SQL);

while ($row = mysql_fetch_array($result))
{
$cat1 = $row["cat_name"];
if ($cat1 != $cat2) print $cat1."<br>"; //supposing you want a line break here after the category name
print $row["eq_name"]."<br>";
$cat2 = $cat1;
}

If I understood you correctly, that's pretty much what you want. Try it.
-L-

[zalath]

Oops! errata that. Replace the in the SQL with e.

[[deleted]]

That is a possibility, but the functionality will be different from what Jim wanted.

[[deleted]]

That's one solution yes.