[SOLVED] mysql tables with hyphens, commas, single, and double quotes - problems in query

[esscher]

I have tables that have to have all kinds of punctuation in them.  I am able to select the tables just fine using backticks (`), such as in this query:

mysql_query("select plant_source from `$chemical`");

 

however, when I try to select them in a query such as this:

 

mysql_query("select * from compounds where chemical='$allchems[$c]' ")

$allchems is a an array of strings created from these troublesome tables.  For example, $allchems includes strings such as:

formic-acid

eleutheroside-b-4

2,,3,3',4,4',5,7-heptahydroxyflavanbioside

 

These do not appear in the results, only chemicals that DO NOT have any punctuation (apigenin, fiber, protein, magnesium)

 

Help?

 

Scott

 

[esscher]

Also to add one more thing: When I run a query such as this:

select * from compounds where chemical="2,,3,3',4,4',5,7-heptahydroxyflavanbioside"

The query works.  Now, it seems obvious to me that the problem is that the chemical contained in double-quotes works as a query.  However, using php, I cannot use double quotes in a query, or else it dies with an error.  How do I use double-quotes in a php script.. For example:

$result3=mysql_query("select * from compounds where chemical="$allchems[$c]"");

Is what I want, but won't work.

 

 

[haku]

$result3=mysql_query("select * from compounds where chemical='" . $allchems[$c] . "'");

[aschk]

Hence are a couple of nicer notations:

 

$result3=mysql_query("select * from compounds where chemical='{$allchems[$c]}' ");
==== AND ====
$sql = "select * from compounds where chemical='%s' ";
$query = sprintf($sql,$allchems[$c]);
$result3=mysql_query($query);

[esscher]

neither haku's, or aschk's suggestions did anything any different.

 

here is the output for the errors that occur:

vitexin-4',7-di-d-glucoside
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 '7-di-d-glucoside'' at line 1
Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\wamp\www\phytobase\p2\chemicals_in_plant_with_phys_prop.php on line 40

 

It appears to me that the problem is in bold: vitexin-4',7-di-d-glucoside

In other words the comma quote (,") part of the chemical.  To troubleshoot, I manually changed the name of that table to exclude the ', so that it comes out as vitexin-47-di-d-glucoside and it works just fine. 

 

Someone else, on phpBuilder forums said that I should use the mysql_real_escape_string() function.  I'm not quite sure how to use it, so if someone could make an example? 

 

 

Scott

[phpSensei]

Post the Array Var please, just need to take a look.

[rhodesa]

You need to run those values through mysql_real_escape_string:

 

mysql_query("select * from compounds where chemical='".mysql_real_escape_string($allchems[$c])."'")

 

That function will properly escape any special mysql characters

[esscher]

Yep.. that did work, and is exactly what I was looking for.