[SOLVED] Multi Table Query [Hard]

[Chevy]

Well I am making a trading system that allows up to ten items to be traded.

 

I am trying to display these [the MySQL rows for the `trades` is `item1`, `item2` all the way to `item6`]

 

I can insert it no problem, but displaying it is another issuse...I have to go through 2 tables to get the images...here is the code.

 

while ($trade = mysql_fetch_array($trade_select)){
    
     if ($trade['item1'] != "0"){
         $item1 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item1]'"));
         $r_item_array1 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='$item1[itemid]'"));
     }
     if ($trade['item2'] != "0"){
         $item2 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item2]'"));
         $r_item_array2 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='$item2[itemid]'"));
     }
     if ($trade['item3'] != "0"){
         $item3 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item3]'"));
         $r_item_array3 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='$item3[itemid]'"));
     }
     if ($trade['item4'] != "0"){
         $item4 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item4]'"));
         $r_item_array4 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='$item4[itemid]'"));
     }
     if ($trade['item5'] != "0"){
         $item5 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item5]'"));
         $r_item_array5 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='$item5[itemid]'"));
     }
     if ($trade['item6'] != "0"){
         $item6 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item6]'"));
         $r_item_array6 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='$item6[itemid]'"));
     }

}

 

Any suggestions? The results are not displaying.

[MadTechie]

change

<?php
$r_item_array1 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='$item1[itemid]'"));
?>

to

<?php
$r_item_array1 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='{$item1['itemid']}'"));
?>

 

on all items

[Chevy]

Nope still did not work...

 

This is my results displaying (or trying):

 

              if ($r_item_array1['name']){
                  echo '<td align="center"><img src="'.$r_item_array1['image'].'" width="35" height="35" /><br><b>'.$r_item_array1['name'].'</b></td>';
              }
              if ($r_item_array2['name']){
                  echo '<td align="center"><img src="'.$r_item_array2['image'].'" width="35" height="35" /><br><b>'.$r_item_array2['name'].'</b></td>';
              }
              if ($r_item_array3['name']){
                  echo '</tr><tr><td align="center"><img src="'.$r_item_array3['image'].'" width="35" height="35" /><br><b>'.$r_item_array3['name'].'</b></td>';
              }
              if ($r_item_array4['name']){
                  echo '<td align="center"><img src="'.$r_item_array4['image'].'" width="35" height="35" /><br><b>'.$r_item_array4['name'].'</b></td>';
              }
              if ($r_item_array5['name']){
                  echo '</tr><tr><td align="center"><img src="'.$r_item_array5['image'].'" width="35" height="35" /><br><b>'.$r_item_array5['name'].'</b></td>';
              }
              if ($r_item_array6['name']){
                  echo '<td align="center"><img src="'.$r_item_array6['image'].'" width="35" height="35" /><br><b>'.$r_item_array6['name'].'</b></td>';
              }

[MadTechie]

can you re-post the updated first code, also is ID a char or an int ?

[teng84]

i believe we can make this codes upto 6 0r five lines just explain it well and give sample results and db info

[Chevy]

id is an int.

 

     if ($trade['item1'] != "0"){
         $item1 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item1]'"));
         $r_item_array1 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='{$item1['itemid']}'"));
     }
     if ($trade['item2'] != "0"){
         $item2 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item2]'"));
         $r_item_array2 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='{$item2['itemid']}'"));
     }
     if ($trade['item3'] != "0"){
         $item3 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item3]'"));
         $r_item_array3 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='{$item3['itemid']}'"));
     }
     if ($trade['item4'] != "0"){
         $item4 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item4]'"));
         $r_item_array4 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='{$item4['itemid']}'"));
     }
     if ($trade['item5'] != "0"){
         $item5 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item5]'"));
         $r_item_array5 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='{$item5['itemid']}'"));
     }
     if ($trade['item6'] != "0"){
         $item6 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`='$trade[item6]'"));
         $r_item_array6 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`='{$item6['itemid']}'"));
     }

 

What I am trying to do is search in all 6 rows of the trade table if the field = 0 then it will not display, but any field that does = 0 will.

[teng84]

ok i see i think i got it

[MadTechie]

the code should be

 

<?php

     if ($trade['item1'] != "0"){
         $item1 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`={$trade['item1']}"));
         $r_item_array1 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`={$item1['itemid']}"));
     }
     if ($trade['item2'] != "0"){
         $item2 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`={$trade['item2']}"));
         $r_item_array2 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`={$item2['itemid']}"));
     }
     if ($trade['item3'] != "0"){
         $item3 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`={$trade['item3']}"));
         $r_item_array3 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`={$item3['itemid']}"));
     }
     if ($trade['item4'] != "0"){
         $item4 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`={$trade['item4']}"));
         $r_item_array4 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`={$item4['itemid']}"));
     }
     if ($trade['item5'] != "0"){
         $item5 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`={$trade['item5']}"));
         $r_item_array5 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`={$item5['itemid']}"));
     }
     if ($trade['item6'] != "0"){
         $item6 = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` WHERE `id`={$trade['item6']}"));
         $r_item_array6 = mysql_fetch_array(mysql_query("SELECT * FROM `items` WHERE `id`={$item6['itemid']}"));
     }

?>

[teng84]

ok this might not work for i dont know the real data but this will give good sample or start

 

while ($trade = mysql_fetch_array($trade_select)){
++$ctr;
$item= 'item1'.$ctr;
if ($trade[$item] !=0){
	$items.="'".$trade[$item]."'";
}
}
$itemz = mysql_fetch_array(mysql_query("SELECT * FROM `useritems` inner join`items` on itemid =items.id  WHERE `id` in ($items)"));

[MadTechie]

i think your missing a comma (,) teng84

[Chevy]

Thank you all, I finally got this to work

[Barand]

You could get that data with

$trade_items = join(',', array_values($trade));
$sql = "SELECT i.* 
        FROM useritems u
            INNER JOIN items i ON i.id = u.itemid
        WHERE u.id IN ($trade_items)";

[teng84]

i think your missing a comma (,) teng84

 

yah actually no comma its the emplode thAt i missed

 

implode(',',$items); lol