problem specifying alphabetical listing from table

horstuff

Hello all. I have three tables, product_status, warranty, and return. The code below works except that it will insert the highest alphabetical entry of both warranty and return in the product_status table. It is not letting me specify which entry from warranty or return to insert into product_status. It is, but when I refresh the page after inserting, it shows the highest alphabetical entry in either warranty or return. Ideas? Thanks.

$strSql = "select * from product_status,warranty where product_status.warranty = warranty.aw_id and product_id = $pid";
$result = mysql_query($strSql);
while ($row = mysql_fetch_array($result)){ 
$warranty_comment = $row["warranty_comment"];   

}


$post1 = mysql_query ("SELECT aw_id,warranty_comment FROM warranty ORDER BY warranty_comment ASC"); 
while ($row=mysql_fetch_array($post1) ) { 
$fid = $row["aw_id"]; 
$fname = $row["warranty_comment"]; 
$trimmed = trim($fname); 
$option_value1 .= "<OPTION value=\"$fid\">$fname</option>";  

}
             print "<tr bgcolor='#000066'><td width='150'><font face='Verdana' size='1' color='#FFFFFF'>Warranty<br>
<font color='#cccccc'>(View Warranties Below)</font></font>
</td><td width='600'><select name='warranty'><option selected value='$fid'>$warranty_comment</option>
<? echo $option_value1; ?></select></td>";

$strSql = "select * from product_status,return where product_status.return = return.ar_id and product_id = $pid";
$result = mysql_query($strSql);
while ($row = mysql_fetch_array($result)){ 
$return_comment = $row["return_comment"]; 
} 


$post2 = mysql_query ("SELECT ar_id,return_comment FROM return ORDER BY return_comment ASC"); 
while ($row=mysql_fetch_array($post2) ) { 
$ffid = $row["ar_id"]; 
$fname1 = $row["return_comment"]; 
$trimmed = trim($fname1); 
$option_value121 .= "<OPTION value=\"$ffid\">$fname1</option>";  

}

         print "<tr bgcolor='#000066'><td width='150'><font face='Verdana' size='1' color='#FFFFFF'>Return Policy<br>
<font color='#cccccc'>(View Return Policies Below)</font></font></td><td width='600'>
<select name='return'><option selected value='$ffid'>$return_comment</option>
<? echo $option_value121; ?></select></td>";

amchargue

in that loop where you build $optionvalue1, $fid is set over and over again, so that its final value is the aw_id of the last row returned by the query . so, i would think that you really want $pid instead of $fid in

<option selected value='$fid'>

Same thing for $optionvalue121

horstuff

That wont do it because the fid is the id of the aw_id or ar_id fields which exist in tables warranty and return, and it is not the product id. The pid is used only to reference the products id with the values that are inserted into fields warranty and return in table product_status.

amchargue

Oh, my bad. But you see the problem I'm pointing out about using $fid outside the loop?

Just sticking with warranty_comment, it appears you're grabbing warranty_comment based on $pid , but then in

<select name='warranty'><option selected value='$fid'>$warranty_comment</option>

you're associating that comment with $fid, which is the $aw_id of the comment that comes last alphabetically (based on $post1). That seems odd. Ditto for the <select> named return, further down.

But that's just a problem with building the selects -- I don't think I understand the exact problem you're having inserting into database. Are you saying no matter which option you select, when you submit that form to a script that inserts it into the db, it will only insert a single value?

My guess is you're inserting $fid and $ffid when you should be inserting $warranty and $return -- better yet $POST['warranty'] and $POST['return'] , or something like that. If you'd like to share the code you're using to insert, I'd be interested in taking a look.

horstuff

Sorry if this didn't make any sense. This one has been a bear, and I didn't know quite how to describe it. I fixed it, though. Here is the code, in case anyone wants to know.

The point of all this was to be able to pull text from one table, using an id number tied to that text in that table, that corresponds with an id number in another table, then print the results in a dropdown. Hopefully that makes some sense.

$post1 = mysql_query ("SELECT aw_id,warranty_comment FROM warranty ORDER BY warranty_comment ASC");
print "<tr bgcolor='#000066'><td width='150'><font face='Verdana' size='1' color='#FFFFFF'>Warranty<br><font color='#cccccc'>(View Warranties Below)</font></font></td><td width='600'><select name='warranty'>";
while ($row=mysql_fetch_array($post1) ) {
$fid = $row["aw_id"];
$fname = $row["warranty_comment"];
$trimmed = trim($fname);
echo "<option";
if ($warranty_comment == $fname) {
	echo " selected";
}
echo " value='$fid'>$fname</option>";
}
echo "</select></td>";