If table1.field1 == table2.field1 DISPLAY table1.field2.

[Jumpy09]

The following code is an attempt to pull information from one table in the database, match one field from it to another field in another table.. then display a 2nd field from the first table.  Looks right, but currently won't display anything.

 

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$cid="1";
$cid1=mysql_query("SELECT cid FROM `categories` WHERE cid = '$cid'");
$cid2=mysql_query("SELECT cid FROM `items` WHERE cid = '$cid'");
$cname=mysql_query("SELECT catname FROM `categories` WHERE cid = '$cid2'");

If ($cid1==$cid2) {echo "mysql_result($cname);";};

 

Basically I am using cid as a category indicator which is also put into the items table.  Instead of it showing a 1, 2, 3 or 4, I want it to display the category name.

So the table layout is like.

 

categories = [cid][catname]  EX: 1 Electric Guitar

items = [itemid][cid][bid][itemname]  EX 1 1 2 Ibanez IJX121

So if cid from items = cid from categories DISPLAY catname from categories.

 

So the new item display would be like EX: 1 Electric Guitar 2 Ibanez IJX121

I'm going to do the same thing to bid which is the brand id.

 

Note: Took this from a prior post of mine, needed to start a new thread for a different thing.

 

Thanks for the help ><.

[jl5501]

why not do this

 

$result = mysql_query("SELECT c.cid,c.catname FROM categories c,items i WHERE c.cid=i.cid and c.cid = '$cid'");

[Jumpy09]

Cause I'm a nub and I'm trying to make everything difficult. 

 

It worked...  I'm relatively surprised cause it looked weird.

 

$cid="1";
$i=0;
$result = mysql_query("SELECT c.cid,c.catname FROM categories c,items i WHERE c.cid=i.cid and c.cid = '$cid'");
$result1 = mysql_result($result,$i,"catname");
echo "$result1";

 

Just in case anyone else needs to know how to use two tables to match two fields and display another field.

 

Thanks a bunch, now I get to mark this one solved and open another in a few minutes lol.

[Jumpy09]

Okay so it sort of works, and sort of doesn't!

 

Here is an image of it in use: I dunno how to scale the picture down.

 

<table width="700px"border="2" style="border-color:#400000;" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">ID</font></th>
<th><font face="Arial, Helvetica, sans-serif">Category</font></th>
<th><font face="Arial, Helvetica, sans-serif">Brand</font></th>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Description</font></th>
<th><font face="Arial, Helvetica, sans-serif">Price</font></th>
<th><font face="Arial, Helvetica, sans-serif">In-Stock?</font></th>
<th><font face="Arial, Helvetica, sans-serif">Serial Code</font></th>
</tr>

<?
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM items";
$result=mysql_query($query);
$num=mysql_numrows($result);
$cat = mysql_query("SELECT c.cid,c.catname FROM categories c,items i WHERE c.cid=i.cid and c.cid = '$cid'");
$i=0;


while ($i < $num) {
$itemid=mysql_result($result,$i,"itemid");
$cid=mysql_result($result,$i,"cid");
$bid=mysql_result($result,$i,"bid");
$itemname=mysql_result($result,$i,"itemname");
$itemdesc=mysql_result($result,$i,"itemdesc");
$itemprice=mysql_result($result,$i,"itemprice");
$itemstock=mysql_result($result,$i,"itemstock");
$itemsc=mysql_result($result,$i,"itemsc");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $itemid; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? $cat = mysql_query("SELECT c.cid,c.catname FROM categories c,items i WHERE c.cid=i.cid and c.cid = '$cid'");
$cat1 = mysql_result($cat,$i,"catname");
echo $cat1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? $brand = mysql_query("SELECT b.bid,b.brandname FROM brands b,items i WHERE b.bid=i.bid and b.bid = '$bid'");
$brand1 = mysql_result($brand,$i,"brandname");
echo $brand1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $itemname; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $itemdesc; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif">$<? echo $itemprice; ?></a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $itemstock; ?></a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $itemsc; ?></a></font></td>
</tr>

<?
$i++;
}


echo "</table>";

?>

 

It works for everything except the last table, which is weird.

 

<td><font face="Arial, Helvetica, sans-serif"><? $cat = mysql_query("SELECT c.cid,c.catname FROM categories c,items i WHERE c.cid=i.cid and c.cid = '$cid'");
$cat1 = mysql_result($cat,$i,"catname"); //Line 50 <<<
echo $cat1; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? $brand = mysql_query("SELECT b.bid,b.brandname FROM brands b,items i WHERE b.bid=i.bid and b.bid = '$bid'");
$brand1 = mysql_result($brand,$i,"brandname"); //Line53  <<<
echo $brand1; ?></font></td>

 

I am planning on moving all the formating into CSS, I snagged most this stuff off of google, copy and pasted, also just changed what I needed for it to look right.

Also going to refine code later to be more to standard like <? ?> to <?php ?>, just have not gotten there yet.

 

So any ideas?