Help with Table Join concept...

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!

toma42

select cat_name, eq_name from cats, equip where equip.cat_id = cats.cat_id order by cat_name, eq_name

then in php check for a change of category as you iterate through the results.

jplush76

hey Tom, thanks mucho

its that php part that I'm stuck on... I get the data I need in the SQL call.

Its the whole change everytime you come to a new cat_id thats holding me up.

Anon

Well, this is NOT the best way to do it (the best way is using templates (see http://www.phpinsider.com/php/code/Smarty/ for more info)), but:

we'll let $db represent our abstracted database object and $db->f() fetches a value from the result set

$lastcat = '';
while ($db->next_record()) {
if ($db->f('cat_name') != $lastcat) echo '<BR>' . $db->f('cat_name') . '<BR>';

echo $db->f('eq_name') . '<BR>';
$lastcat = $db->f('cat_name');
}

jplush76

here is the code I have so far, but it doesnt work. I tried to follow the logic you gave but I know I screwed up 🙂

<?
$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;
}

Anon

$rows = mysql_fetch_array($result);

$lastcat = \'\';

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

try
while ($rows = mysql_fetch_array($result)) {

}

jplush76

I GOT IT!

I stripped it down to the basics and I found out I was way overanylizing the problem. Thanks for your help Tom

$query = "select cats.cat_id,cat_name, equip.cat_id,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);

$i=0;
$cat2 = '';

while ($i < $num_rows)
{
$cat_name=mysql_result($result,$i,"cat_name");
$cat_id=mysql_result($result,$i,"cats.cat_id");
$ecat_id=mysql_result($result,$i,"equip.cat_id");
$eq_name=mysql_result($result,$i,"equip.eq_name");

if ($cat2 != $cat_id)
	{
		echo "<BR><B>$cat_name"."</b><BR>";
	}

if($cat_id == $ecat_id)
	{
		echo "$eq_name"."<BR>";
	}

$cat2 = $cat_id;
++$i;
}