Please help with $variable accessing MySQL

This script I have works perfectly and I have it accessesing a MySQL database with no problem. However, when I intoduce a variable the script no longer works...

If I type this into the php script (as a test):

echo [COLOR="Blue"][B]$state[/B][/COLOR]

Code (markup):

it will produce state intials that are visible in the HTML source code like this: CT

A portion of the working script looks like this:

$result = mysql_query("SELECT * 
FROM `data` 
WHERE (
`table`.`state` = [B][COLOR="Red"]'CT'[/COLOR][/B]
)"
)

Code (markup):

(table is the name of the table, and state is the name of the column.)
Again, this works perfectly.

The main point is echo $state produces CT, and the script works perfectly with CT.

Now if I change the above code to:

$result = mysql_query("SELECT * 
FROM `data` 
WHERE (
`table`.`state` =[COLOR="Blue"]$state[/COLOR]
)"
)

Code (markup):

I get the error:
Unknown column 'IA' in 'where clause'

The only change is 'CT' to $state. I have changed 'CT' and $state back and forth a couple 100 times, sometimes with little variations, working , not working, working, not working.....

Sorry if this is obvious; I'm a php newb.

 

There are a few things wrong with your clause....

First of all, what MySql will see with your code is this (after substitution of your variable)...

SELECT * FROM 'data' WHERE ('table'.'state' = CT)

not...

SELECT * FROM 'data' WHERE ('table'.'state' = 'CT')

note the missing single quotes around CT?

So, your code needs changing to this....

$result = mysql_query("SELECT *
FROM `data`
WHERE (
`table`.`state` ='$state'
)"
)

But, your query is a bit of a mess. The other single quotes around table, state etc are unnecessary. As is the brackets around the where clause. So, it should be....

$result = mysql_query("SELECT *
FROM data
WHERE
table.state ='$state'
")

BUT! if you're selecting from a table called "table", why does you select statement say SELECT * from data?

Is the table called table, or data?

SELECT * FROM <tablename> WHERE <tablename>.<columnname>='<value>'

 

Thanks ecenticNick, but the single quotes did not work. I also tried redoing everything, and the script stopped working with CT as well as $state. I saw a similar coment on the net suggesting single quotations around the variable, but when I tried, it did nothing, no error, nothing, so then I tried '$random_nonsense' and gave the same result,, nothing, not even an error.

 

Ok, a few other simple possibilities...

There should be a semi-colon on the end of the statement, and you might want to put it all on one line in case there's a strange new line character got in there somehow.

$result = mysql_query("SELECT * FROM data WHERE table.state ='$state'");

And of course, you must have already opened the DB connection.

Also, if embedding the variable doesn't work, try concatenation...

$result = mysql_query("SELECT * FROM data WHERE table.state ='".$state."'");

For clarity, that is ' " . $state . " ' " but without the spaces!

And, as I asked before, what's your table called? "table" or "data"?

 

Sorry about that ecentricNick; the table is called data. <edit>I also should have mentioned that the php is inside of a javascript; I found a post that makes me think that is the source of the problem</edit>
Your suggestions have helped to clean up the code (rep added for that) but unfortunately this script is still not accepting a variable; I am persistent though. I have been inching up to your suggestion with small incremental changes to note where it stops working:

Works:

$result = mysql_query("SELECT * FROM data WHERE data.state ='ca'");  

Code (markup):

Does not work:

$result = mysql_query("SELECT * FROM data WHERE data.state ='".$state."'");  

Code (markup):

(the only difference is the parenthasis.)

 

Have you proved that $state actually has a value in it?

Do something like...

echo "State:".$state.":";

...before the select statement to confirm there is something in it!

 

Yes I have. It echoes exactly what is needed for it to work.
I have reproduced several several lines of javascript with php and it has sucessfuly gotten table data from MySQL, I just cant seem to acess MY SQL with a variable

Here is another working line; it shows what I did to get php to work inside a javascript:

while($row = mysql_fetch_array( $result )) {
echo "var point = new location(" .     $row['column6'] . "," .  $row['column7']   .   ");" .   "\n";
echo 'var marker = anotherVariable(point, [' . '"' .   $row['name'] . "<br>" . $row['fuel'] . "<br>"     .       $row['phone']  . "<br>" . $row['address']  . "<br>" . $row['city']  . ", " . $row['state']  . ", " . $row['zip'] .  '","' . $row['hours'] .   "<br>" .  $row['intersection'] .               '","' .   $row['payment']   .    '"],["'  .  "Address"  . '","'.  "Hours, Directions"     .          '","'.  "Payment"    . '"]);'.  "\n";

Code (markup):

again, the above code works perfectly when I access with any state initials, i.e., CA, FL, NY etc with

$result = mysql_query("SELECT * FROM data WHERE data.state ='ca'");

Code (markup):

Although that example works perfectly, I'm thinking PHP and javascript are conflicting somehow when I swap a state initial with $state.