[SOLVED] php string variables in a MySQL clause

[twistedmando]

I must be looking in the wrong places for answers, but I'm not finding anything.  My Web host is using MySQLv5, and PHP5.  The following is the offending code.

// The value of $color is 'Red'

$extract="SELECT amount FROM colors WHERE color = $color";
$amount=mysql_query($extract) or die(mysql_error());

With this code I get a notification of

"Unknown column 'Red' in 'where clause'"

But my field is called color

If I try '$color', or '"$color"', or '{$color}' etc. I get syntax errors.

Clearly what I want is to get the value of amount from the entry where color is Red.

Any suggestions?

twistedmando

[drifter]

$extract="SELECT amount FROM colors WHERE color = '" . $color . "'";

[HuggieBear]

You need single quotes around your value...

[color=blue]$extract="SELECT amount FROM colors WHERE color = [color=red]'[/color]$color[color=red]'[/color]";[/color]

Regards
Huggie

[twistedmando]

HMM

When I try

$extract="SELECT amount FROM colors WHERE color = '" . $color . "'";
$amount=mysql_query($extract) or die(mysql_error());

or

$extract="SELECT amount FROM colors WHERE color = '$color' ";
$amount=mysql_query($extract) or die(mysql_error());

I get the following error message.

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 'id #3 WHERE color = 'Red'' at line 1

I'm making sure to start with a new browser each time I test.

Thanks for looking!

I'm stumped.

[drifter]

this is an SQL error - do you have a column names color and amount in a table named colors?

[twistedmando]

Yes, I have a column named color and a column named amount in a table called colors.  The database is also called colors.

[twistedmando]

I changed my table name to my_colors, and I still get the same error.

[twistedmando]

Still no succes.  Anybody have a guess?

[HuggieBear]

If you're trying to get the amount then you need to fetch that data too.  Try this code (error checking included this time)...

[code]<?php
$sql = "SELECT amount FROM colors WHERE color = '$color'";
$result = mysql_query($sql);
if (!$result){
  echo "Unable to run query: $sql<br>\n" . mysql_error();
}
else {
  $row = mysql_fetch_array($result, MYSQL_ASSOC);
  echo $row['amount'];
}
?>[/code]

Regards
Huggie

[twistedmando]

;D
Sweet!  Thanks HuggieBear!

Twistedmando

[HuggieBear]

If that worked then mark the topic as solved so others can take advice from it.

Regards
Huggie