PDA

Click to See Complete Forum and Search --> : [RESOLVED] Setting the 'key' field as the value in a dynamically generated select menu

lightnb
07-09-2006, 04:36 AM
I have the following code:

<html>
<body>
<?php

$host='localhost';
$name='my_username';
$pass='mypassword';
$dbn='mydatabase_name';

$db = mysql_connect($host, $name, $pass);
mysql_select_db($dbn, $db);

?>



<select name = "color_name" size="1" style="width:180px;">
<Option Value="not_set">Please Choose a Color:</option>
<?php
$select_string = "SELECT key, color_number, color_name FROM roscolux_color";
$sql = mysql_query($select_string);
echo(mysql_error());

while($r = mysql_fetch_assoc($sql)){
?>
<option value="<?php echo($r['key']); ?>"><?php echo($r['color_number']).' - '.($r['color_name']); ?></option>
<?php
}
?>

</select><br>
</form>


</body>
</html>

It works fine, until I try to make the 'key' field from the database the value of the option. If I replace the 'key' field with a different field, it works fine. Any ideas why this field is behaving strangely?

Thanks,

Nick
NogDog
07-09-2006, 10:29 AM
Can you elaborate on what it does not do (or does incorrectly) when you use the 'key' field?
lightnb
07-09-2006, 05:41 PM
When using the key field, it creates a drop down box, but only with the initial hardcoded option. (ie. it doesn't fill in the rest of the options from the database) It also generates a SQL syntax error within the HTML source. The HTML code that is generated is:

<html>
<body>



<select name = "color_name" size="1" style="width:200px;">
<Option Value="not_set">Please Choose a Color:</option>
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'key, color_number, color_name FROM roscolux_color' at line 1<br />
<b>Warning</b>: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in <b>/home/.sites/22/site13/web/mytest.php</b> on line <b>27</b><br />

</select><br>
</form>


</body>
</html>

I can't understand why it would be a syntax error though- If I use the exact same syntax, but with a different field, the code executes coreclty. for example:



<select name = "color_name" size="1" style="width:200px;">
<Option Value="not_set">Please Choose a Color:</option>
<?php
$select_string = "SELECT transmission_value_percent, color_number, color_name FROM roscolux_color";
$sql = mysql_query($select_string);
echo(mysql_error());

while($r = mysql_fetch_assoc($sql)){
?>
<option value="<?php echo($r['transmission_value_percent']); ?>"><?php echo($r['color_number']).' - '.($r['color_name']); ?></option>
<?php
}
?>

</select><br>
</form>


</body>
</html>



It creates a drop down box that works correctly, and the HTML look like this:


<html>
<body>



<select name = "color_name" size="1" style="width:200px;">
<Option Value="not_set">Please Choose a Color:</option>
<option value="100">00 - Clear</option>
<option value="56">01 - Light Bastard Amber</option>
<option value="78">02 - Bastard Amber</option>
<option value="62">03 - Dark Bastard Amber</option>
<option value="66">04 - Medium Bastard Amber</option>

<option value="79">304 - Pale Apricot</option>
<option value="80">05 - Rose Tint</option>
<option value="75">305 - Rose Gold</option>
<option value="">3410 - Roscosun 1/8 CTO</option>
NogDog
07-09-2006, 06:44 PM
Try putting "back ticks" around the field name, as "key" might be a reserved word in MySQL:

$query = "SELECT `key`, color_number, color_name FROM roscolux_color";
lightnb
07-09-2006, 06:46 PM
That fixed the problem. Thank you :)