Post by strobes » Mon Mar 25, 2013 8:40 pm

Hi All

I have upgraded to the latest version of opencart 1.5.5.1 and have encountered the following error..

This error comes from going to sales>>>customers>>>customers in the admin and then searching under the "name" if i search from company or email its fine this only happens when the name field is used

Notice: Error: Column 'firstname' in where clause is ambiguous
Error No: 1052
SELECT COUNT(*) AS total FROM shop_customer c LEFT JOIN shop_address a ON (c.customer_id = a.customer_id) WHERE CONCAT(firstname, ' ', lastname) LIKE '%peter%' in /home/www/mysite/system/database/mysql.php on line 49


if anyone can help i would really appreciate it

Thanks

Steve

New member

Posts

Joined
Fri Sep 02, 2011 7:13 pm

Post by butte » Wed Mar 27, 2013 11:13 am

Among the sql commands is "join"--which affects tables, whose top rows (showing fields) and far-left columns (showing records) are v. special, because they index the records and fields, purposely so that data can be segregated among tables rather than put in one massive table, but can be joined on the fly for lookups and other purposes. Otherwise, tables are very much alike ordinary spreadsheets.

You appear to have a box somewhere whose in-line "name" in code is not "firstname"; missing, misspelled, whatever. Initially, try looking for exactly that discrepancy, and remember that you're looking at machine readable syntax and punctuation, meaning that every single or double quotation mark and every semicolon is important.

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am

Post by scottm » Thu Mar 28, 2013 12:54 am

Hello strobes,

The error you are receiving occurs when a query uses more than one table that has the same column name. In the case of the query you are showing, the WHERE CONCAT(firstname, ' ', lastname) section is not identifying which table to use for those column names. Since they are present in both the shop_customer and shop_address tables, the server is confused.

I actually have written an article on this error type here, basing the example on your error message:
Error 1052 Column in where clause is ambiguous

I was unable to duplicate the error in my opencart, (which is a fresh install) even when trying to use different fields in addition to the name field. Did you have more than one address for the user or anything extra (even an extension) that may have played a part in constructing the search?

Scott M


Newbie

Posts

Joined
Wed Mar 27, 2013 9:44 pm

Post by butte » Fri Mar 29, 2013 3:48 am

The two foregoing posts differ between "where from" and "where to" the error sprang, and would tend to differ between whether it can or cannot be replicated elsewhere. Hopefully, the relatively quicker and simpler second solution will fix it. If that doesn't work, you can then try the relativly slower and tedious first solution.

Guru Member

Posts

Joined
Wed Mar 20, 2013 6:58 am
Who is online

Users browsing this forum: No registered users and 6 guests