Displaying Content From A Second Table In Repeating Region

[cdoggg94]

This is a little hard to explain, but I am going to try my best.

 

I have a table that repeats a <td> for all the records in a query.

 

mysql_select_db($database_myConnect, $myConnect);
$query_Jackson = "SELECT * FROM Sheet1 WHERE pro_name LIKE  '%Jackson%' ORDER BY pro_name ASC";
$Jackson = mysql_query($query_Jackson, $myConnect) or die(mysql_error());
$row_Jackson = mysql_fetch_assoc($Jackson);
$totalRows_Jackson = mysql_num_rows($Jackson);

 

 

The table that displays it has a form to change the QTY.

 

The table code is here:

 

 <table width="770" border="0">
 <tr></tr>
 <tr>
   <th colspan="9" class="titlesList" style="background-color:#333333">Jackson</th>
 </tr>
<tr align="center">
	 <th width="60" align="left">LCBO #</th>
	  <th width="250" align="left">Product</th>
	    <th width="40">Size</th>
	    <th width="50">Btl/cs</th>
	    <th width="54">LTO</th>
	  <th width="79">Price</th>
	    <th width="103">Qty</th>
	    <th width="50">Current</th>
	    <th width="91">


	    </th>
   </tr>


<?php  do { ?>
    <form action="NewOrder.php?Details=<?php echo $detnum ;?>" id="form" name="form" method="POST">
 <tr>

   <td><?php echo $row_Jackson['lcbo_num'] ;?></td>
   <td>
   <a href="#" onclick="MyWindow=window.open('<?php echo "product5.php?Product=".$row_Jackson['pro_id']."&Client=".$detnum;?>','MyWindow','toolbar=no,location=no,directories=no,status=no,menubar=no,scrollbars=yes,resizable=no,width=800,height=886'); return false;"><?php echo $row_Jackson['pro_name'];?></a>
   </td>
   <td align="center"><?php echo $row_Jackson['pro_size']."mL" ;?></td>
   <td align="center"><?php echo $row_JacksonTriggs['pro_btl'] ;?></td>
   <td align="center">
   <span class="LTOlist">
 <?php
  if($row_Jackson['F7']=="LTO"){
   echo "LTO";
  }else{
   echo "";
  }
 ?>
   </span>
   </td>
   <td align="center">
<?php
if($row_Jackson['F7']=="LTO"){
echo "<span class='LTOlist'>$".number_format($row_Jackson['F10'], 2)."</span>";
}else{
 echo "$".number_format($row_Jackson['pro_price'], 2);
}
?>
   </td>

   <td align="center"><input name="qty" id="qty" type="text" size="5" /></td>
   <td align="center">
 <?php
 if($myQty['wish_case'] >= 1){
  echo $myQty['wish_case'];
 }else{
  echo "0";
 }
 ?>
   </td>
   <td align="right">
 <input type="hidden" name="date" id="date" value="<?php echo $mymessage ;?>" />
 <input type="hidden" name="product" id="product" value="<?php echo $row_Jackson['pro_name'] ;?>" />
 <input type="hidden" name="client" id="client" value="<?php echo $detnum ;?>" />
 <input type="hidden" name="id" id="id" value="" />
 <input type="hidden" name="normal" id="normal" value="<?php echo $row_Jackson['pro_price'] ;?>" />
 <input type="hidden" name="normalCase" id="normalCase" value="<?php echo "" ;?>" />
 <input type="hidden" name="LTO" id="LTO" value="<?php echo $row_Jackson['lcbo_num'] ;?>" />
 <input type="hidden" name="LTOCase" id="LTOCase" value="<?php echo $row_Jackson['pro_size'] ;?>" />
   <input name="submit" type="submit" value="Add" /></form></td>
 </tr>
 <?php } while ($row_Jackson = mysql_fetch_assoc($Jackson)); ?>
 <tr>
	 <td colspan="8" align="right"> </td>
    </tr>
 </table>

 

This is all fine.

 

My issue is that I want to display (if there is one) the QTY a person has ordered already for each <td>

 

So..

 

I have made this query:

 

$qtyinfo = mysql_query("SELECT* FROM wish_list WHERE wish_bottle_LTO=".$row_Jackson['lcbo_num']);
$myQty = mysql_fetch_array($qtyinfo);

 

wish_bottle_LTO AND $row_Jackson['lcbo_num'] both display the same product numbers.

 

when a number is entered into the form above and saved the product number from $row_jackson's table, along with the QTY entered go into the wish_ table.

 

that being said,

 

i put this in the table above to display the QTY:

 

 <?php
 if($myQty['wish_case'] >= 1){
  echo $myQty['wish_case'];
 }else{
  echo "0";
 }
 ?>

 

It DOES display content, but the problem is that its only displaying it for the first record in the Jackson query and not for each of them separate..

 

Any ideas?

 

Am I way off ?

[cdoggg94]

This is the row I want to display the QTY (if there is one) for each product in a different table

[Christian F.]

What you need in order to do this is to JOIN your second table, based upon the item ID. I recommend that you start with a MySQL tutorial showing how to construct these JOINs.

In this case an INNER JOIN sounds to be what you need.

[cdoggg94]

I just quickly looked at an example of it, and it looks to be what I need.

 

Thanks for steering me in the right direction!

 

Ill post back on this thread if I need anything more, and hopefully you can see where I was off course again.

 

Cheers.

[Christian F.]

You're welcome, and good luck on your project.

[cdoggg94]

So basically I put this into my table:

 

<td>
 <?php



$myQTY = "SELECT*".
"FROM Sheet1, wish_list ".
"WHERE ".$row_Jackson['lcbo_num']." = wish_list.wish_bottle_LTO ORDER BY wish_bottle_LTO DESC";

$resultQTY = mysql_query($myQTY) or die(mysql_error());


while($row = mysql_fetch_array($resultQTY)){

echo $row['wish_case'];


}
 ?>
</td>

 

1) It IS printing the the correct information, but it seems to repeat over and over when I want it to display one time.

 

2) In one case right now, there is an instance where there are 2 records that have the same product number (they are the same product) but one has a QTY of 10, and the other 1. so it displays it like this:

 

101101101101101101101101101101101...etc...

 

Is there a way to add them to together AND only display it one time ?

[Jessica]

SUM

[cdoggg94]

I tried to combine the JOIN and SUM together like this:

 

<?php

$myQTY = "SELECT SUM(wish_bottle_LTO)"." AS summed_wish_bottle_LTO "."FROM Sheet1, wish_list "." WHERE ".$row_JacksonTriggs['lcbo_num']." = wish_list.wish_bottle_LTO ORDER BY wish_bottle_LTO DESC";


$resultQTY = mysql_query($myQTY) or die(mysql_error());

while($row = mysql_fetch_array($resultQTY)){
 echo mysql_result($row['wish_case'], 0);

?>

 

First of all I don't really know if the CAN be done together.

 

Can you let me know if I am on the right track ?

[Christian F.]

What you have there is a Cartesian product, due to a malformed JOIN statement.

I recommend actually writing out your JOIN statements, instead of using the implicit JOIN that you've got here. That'll help you avoid situations like these, and make the query a lot easier to read.