SQL error help needed.

[strelitzia]

Hi,

I'm getting an error from an sql query that i can't seem to fix.
This error is in a mod i was porting from OSC, and up until recently this section of the port was complete, but since my host upgraded there php and MySQL version it is showing the error.

The error message reported by ZC is:

1054 Unknown column 'fg.products_id' in 'on clause'
in:
[select fg.*, p.products_id, pd.products_name from free_gifts fg, products p LEFT JOIN products_description pd ON (pd.products_id=fg.products_id) WHERE pd.language_id = '1' AND p.products_id = fg.products_id ORDER BY fg.threshold ASC]


The query being made is:

$gift = $db->Execute("select fg.*, p.products_id, pd.products_name from " . TABLE_CARROT . " fg, products p LEFT JOIN products_description pd ON (pd.products_id=fg.products_id)
WHERE pd.language_id = '" . (int)$_SESSION['languages_id'] . "' AND p.products_id = fg.products_id ORDER BY fg.threshold ASC");
while (!$gift->EOF) {

Site is running zen cart version 1.3.7
DB patch level 1.3.7
PHP version 5.1.2
SQL verion 5.0.18

Any suggestions on a fix for this?

Thanks in advance
Steve

[DrByte]

It means that your "free_gifts" table, or whatever the name denoted by TABLE_CARROTS is, doesn't have a products_id field in it, but you're telling the SQL to use the products_id field when matching records between the free-gifts table and your product-descriptions table.

[strelitzia]

It means that your "free_gifts" table, or whatever the name denoted by TABLE_CARROTS is, doesn't have a products_id field in it, but you're telling the SQL to use the products_id field when matching records between the free-gifts table and your product-descriptions table.

That's the assumption most would make from the error message, except it does have that field.
The "free_gifts" table has three fields in it. gift_id, threshold, and products_id.

I'm stumped to be honest, and i can only assume it's related to the change to sql 5.

Any other ideas?

Thanks

Steve

[DrByte]

Actually, yes, it does have everything to do with MySQL5 ... and poorly formed queries.
MySQL 5 enforces strict syntax correctness by default.

Thus, your left join between 2 tables that are *not* your fg table will trigger the error message.

1054 Unknown column 'fg.products_id' in 'on clause'
in:
[select fg.*, p.products_id, pd.products_name from free_gifts fg, products p LEFT JOIN products_description pd ON (pd.products_id=fg.products_id) WHERE pd.language_id = '1' AND p.products_id = fg.products_id ORDER BY fg.threshold ASC]

You'll need to rewrite the query so that your "ON" clause criteria contains values that are available in the join. (ie: right now you're joining "p" and "pd" tables, but trying to join them on "fg" and "pd")