Recurring drop down boxes

[Nipper10850]

I am almost done with a long project and just a little trouble with the last part.

I have created a table with information from a one table that contains the info for the product which works great.

The last column in the row has a drop down box that is popultated by another table in the db that has the a corosponding column (the item number) that also has a description and price.

My problem is that the drop down box are not be populated correctly. The same information is in the drop down boxes for all the rows instead of the individual information for each row.

Basically I need to have each loop populate the row one at a time instead of just taking the last loop to populate all the boxes.

//query Product table begins to get correct value for drop down box
$query = mysql_query("SELECT * FROM `Product` WHERE Cat_Index = '$cat' and Sub_Cat = '$cat3' "); 
//pull the value to pass to drop down box
if ($row = mysql_fetch_array($query))
{ 
$price = $row["Item_Num"];
}

// Get data from price table for drop down box
$quer1=mysql_query("SELECT * FROM `Price` WHERE Item_Num = '$price' "); 


// Creat drop down box 
$strDrop = '<select name="price" onchange="reload(this.form)">'; 
if ($row = mysql_fetch_array($quer1)) 
{
     $strDrop .= '<option value="' .$row['Price']. '">' .$row['Desc']. '&nbsp;&nbsp;  $' .$row['Price']. '</option>'; 
} 
$strDrop .= '</select>';


//query product table second time for the rest of the display
$query2 = mysql_query("SELECT * FROM `Product` WHERE Cat_Index = '$cat' and Sub_Cat = '$cat3' "); 

while ($row = @mysql_fetch_array($query2)) 
{ 
$variable1=$row["Pic"];
$variable2=$row["Item_Num"];
$variable3=$row["Name"]; 
$variable4=$row["Short_Des"]; 
//table layout for results 
print ("<tr align=\"center\">");
print ("<td><img src=\"images/products/$variable1.jpg\" height=\"64\" width=\"64\"></td>"); 
print ("<td>$variable2</td>"); 
print ("<td>$variable3</td>"); 
print ("<td>$variable4</td>");
print ("<td>$strDrop</td>");
print ("<td><form target=\"paypal\" action=\"https://www.paypal.com/cgi-bin/webscr\" method=\"post\">
			<input type=\"hidden\" name=\"cmd\" value=\"_ext-enter\">
			<input type=\"hidden\" name=\"redirect_cmd\" value=\"_xclick\">
			<input type=\"hidden\" name=\"business\" value=\"*..*\">
			<input type=\"hidden\" name=\"item_name\" value=\"$variable3\">
			<input type=\"hidden\" name=\"currency_code\" value=\"USD\">
			<input type=\"hidden\" name=\"amount\" value=\"$cat4\">
			<input type=\"image\" src=\"http://www.paypal.com/en_US/i/btn/x-click-but01.gif\" name=\"submit\" alt=\"Secure payments by paypal\">
</form></td>");
print ("</tr>"); 
}
//end
?>

[bpat1434]

Perhaps a join or Union is better suited here? Not too sure but I think joining the tables based on the last row would be better....

<?php

$query = "SELECT * FROM `Product` as t1
JOIN `Price` as t2 ON (t1.Item_Num=t2.Item_Num) 
WHERE Cat_Index = '$cat' AND Sub_Cat = '$cat3'";

print_r mysql_query($query);

?>

See if that helps you... I'm pretty sure that on the join, you'd get an array of values that you can loop through on each row rather than executing mulitple SQL queries for each bit of information.

~Brett

[Nipper10850]

Thanks for the type.

If you could give me a little more help.

I wasn't sure what the point of the line after the query was for.

print_r mysql_query($query); 

any help would be greatful.

Thanks,

[bpat1434]

Well, it was there to print the results of the query so that you can see what you get.. I guess it would be better to put it in a loop so you can see what is returned with each row.

If you print the results, you can then take that information and format it how you want. That's what that line was there for.

~Brett

[Nipper10850]

Thanks for the explanation. Here is what is happening that I'm not sure how to correct.

Basically I'm trying to get a single row for each item in table 1. In table 2 I have the corrosponding Key plus two other fields which may be in table 2 more than once. So what I have is a row for every time the key is in table 2 instead of just in table 1.

Example

Table 1
Key | Other Field 1 | Other Field 2

111 Stuff Stuff
222 Stuff Stuff
333 Stuff Stuff

Table 2

Key | Other Field 3 | Other Field 4
111 Info More Info
111 Info More Info
111 Info More Info
222 Diffrent Info Still More Info
222 Diffrent Info Still More Info

With the way the the query is now

query("SELECT DISTINCT * FROM `Product` as t1 JOIN `Price` as t2 ON (t1.Item_Num=t2.Item_Num) WHERE Cat_Index = '$cat' AND Sub_Cat = '$cat3'")

When I loop through and display the information I get per the example below

5 rows

What I want to do is get

2 rows with the last column in each row to have a drop down box that list the OtherField3 and OtherField4 with the corrosponding information.

I have my PHP code so far any suggestions whould be greatly appreciated.

//query product table and price table
$query2 = mysql_query("SELECT DISTINCT * FROM `Product` as t1 JOIN `Price` as t2 ON (t1.Item_Num=t2.Item_Num) WHERE Cat_Index = '$cat' AND Sub_Cat = '$cat3'"); 

while ($row = @mysql_fetch_array($query2))

{ 
$variable1=$row["Pic"];
$variable2=$row["Item_Num"];
$variable3=$row["Name"]; 
$variable4=$row["Short_Des"]; 
$variable5=$row["Desc"];
$variable6=$row["Price"];
//table layout for results 
print ("<tr align=\"center\">");
print ("<td><img src=\"images/products/$variable1.jpg\" height=\"64\" width=\"64\"></td>"); 
print ("<td>$variable2</td>"); 
print ("<td>$variable3</td>"); 
print ("<td>$variable4</td>");
print ("<td>$variable5&nbsp;&nbsp;$variable6</td>");
print ("</tr>"); 
}
//end
?>