SQL Nightmare

[andychev]

Can anyone help with the following as its driving me insane ! Probably something really obvious but it has fried my brain now!

I have two tables, Categories_stores, Categories_description.

Ok put in english this is what i want to happen:

I want to look into the stores table and select all the rows that are equal to a variable 'GLOBAL_CURRENT_STORE_NAME' obviously theae are all in the same column (categories_storename)

This will give a big long list of numbers from this table under categories_id.

I then want this column to be used to select all the corresponding records out of the categories description_table

The sql i currently have is:

$categories_query = tep_db_query("SELECT cd.categories_id, categories_name FROM " . TABLE_CATEGORIES_DESCRIPTION . " cd, " . TABLE_CATEGORIES_STORES . " cs WHERE language_id = $this_language_id and cd.categories_id = cs.categories_id and cs.categories_storename = '" . GLOBAL_CURRENT_STORE_NAME . "' ORDER BY categories_name");

This just brings up the following error:

1064 - 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 'and cd.categories_id = cs.categories_id and cs.categories_store


Am i missing something really obvious ! Any help would be much apreciated!

[jmarcv]

Generally I have found that the stuff right before the printed error is USUALLY the real cause, so I look at this:

WHERE language_id = $this_language_id

and I see that if $this_language_id is null, which I bet it is, it will throw an error.
Always a good idea to do this:
language_id = '$this_language_id'
... for one thing, many a hack was done with a quoteless ID. Look up SQL-injection for the gory details.

Anyway, may I suggest putting this in your toolbox:


$sql="some query";
$db=mysql_query($sql);
$ref = mysql_fetch_row($db);
if (mysql_error()){
print mysql_error()."<br>$sql<br>";
exit();
}

Seeing the Actual SQL after an error can usually speed up debugging by thousands!

[andychev]

excellent excellent and excellent! Thaks so much! Often the way you look at something for so long it ends up being something so simple. Exactly what you said, put the $this_language_id in quotes and hey presto all working.

Thanks so much

Andy