Customers Bad SQL query

[Jenetic]

I'm using prestashop 1.5.4.1.

 

I just discovered that when I go to Customers > Customers I get the following message:

 

Bad SQL query

Unknown column 'g.id_customer' in 'where clause'

 

I have an old DB backup which I replaced this one with, and there it works like it should.

 

The only problem is that the backup was made BEFORE I added all product images when I imported them. And I really don't feel like or have the time to start editing all of the products once more.

 

Is it possible to copy something from the old DB with the working customers page into the new one with the broken page?

[vekia]

you need customers data? if not, you can truncate customers table and then everything should work as you expect

[Jenetic]

I just want the customers page to work and something tells me it's an easy fix, just replace something from the working DB backup to the active non-working DB.

 

But I have no idea what to replace lol

 

All the other pages under Customers works, so I can go through Groups and see all customers there.

But as I said it's just annoying that it doesn't work as it should.

 

Any idea what to replace to make the " unknown column 'g.id_customer' in 'where clause' " work?

 

I found a link somewhere on the forum with a code to run in phpadmin to truncate customers but I got some error message doing it. So I had to delete the customers under ps_customers instead.

[tdr170]

You could copy the customer tables form the backup database that works and add them to the broken database.

open the backup database in a text editor like Notpade++ (free) and copy the following tables.

 

BACKUP DATABASE FIRST.

 

ps_address

 

pr_customer

pr_customer_group

pr_customer_message

pr_customer_message_sync_imap

pr_customer_thread

 

 

In the database use sql query and paste the copied tables to the text area and run query.

You may need to delete the tables first this depends on the options you chose when you exported the database.

[PascalVG]

Do you have a table ps_guest??

It looks like one of those 2 sql statements give the problem, and both want to use this table:

 

From file: /controllers/admin/AdminCustomersController.php:

 

$this->_select = '

a.date_add,

IF (YEAR(`birthday`) = 0, "-", (YEAR(CURRENT_DATE)-YEAR(`birthday`)) - (RIGHT(CURRENT_DATE, 5) < RIGHT(birthday, 5))) AS `age`, (

SELECT c.date_add FROM '._DB_PREFIX_.'guest g

LEFT JOIN '._DB_PREFIX_.'connections c ON c.id_guest = g.id_guest

WHERE g.id_customer = a.id_customer

ORDER BY c.date_add DESC

LIMIT 1

) as connect';

 

 

or from file classes/Customer.php:

 

 

SELECT DISTINCT c.*

FROM `'._DB_PREFIX_.'customer` c

LEFT JOIN `'._DB_PREFIX_.'guest` g ON g.id_customer = c.id_customer

LEFT JOIN `'._DB_PREFIX_.'connections` co ON g.id_guest = co.id_guest

WHERE co.`ip_address` = \''.ip2long(trim($ip)).'\'');

 

(probably last one)

 

So check if you have this guest table, and this field in there.

 

My 2 cents,

pascal

[vekia]

i think that the problem is a bit different, i suppose that that these two tables and their fields exist, but there is something wrong with the consistency of the database, for example, the missing entries where other one trying to use missed entries index - and this index of course doesn't exist

[kevinseo]

My prestashop is the 1.3.1 version. I encountered the same problem. Just go to the database and check the table ps_connections and ps_connections_page are normally. Just repair them if they crash.

[noodly90210]

I have the same problem. I don't have tables ps_connections and ps_connections_page at all. What I have to do? Why it is not enough of table id_costumer with all emails?

[PascalVG]

Noodly, what is the exact error you get?

[noodly90210]

Noodly, what is the exact error you get?

Bad SQL query

Table 'web43-prestadb.ps_

connections' doesn't exist

Edited December 5, 2013 by noodly90210 (see edit history)

[FernandoLopez]

Could anyone solve this problem??

I have the same problem??

[vekia]

have you checked post #5 above?

[FernandoLopez]

Hi Vekia, yes I've checked it,

 

I think I have detected the problem but I don´t know how to solve it.

 

My prestashop version es 1.4.7.0.

 

In the file \adminxxx\tabs\admincustomers.php  at the begining there is a sentence like this:

 

$this->_select = '(YEAR(CURRENT_DATE)-YEAR(`birthday`)) - (RIGHT(CURRENT_DATE, 5)<RIGHT(`birthday`, 5)) as age, (

SELECT c.date_add FROM '._DB_PREFIX_.'guest g

LEFT JOIN '._DB_PREFIX_.'connections c ON c.id_guest = g.id_guest

WHERE g.id_customer = a.id_customer

ORDER BY c.date_add DESC

LIMIT 1

) as connect';

 

Really I don´t know anything about this sentence, I only know that if I remove the "_ " from the select, like this:

 

$this->select = '(YEAR(CURRENT_DATE)-YEAR(`birthday`))ssssss

 

then all my customers appear when I click in the tab customers, but I have a notice from ny php that says "in the line admintabs.php 1526 there isn´t index connect"

[vekia]

can you check ps_guest table

does the id_customer column exist there?

[FernandoLopez]

Hi vekia,

 

there is any way to ckeck it without entering in the control panel of the website??

 

Any command in prestashop for seeing the columns of the tables??

Edited January 10, 2014 by FernandoLopez (see edit history)

[vekia]

well, it's the best to check it with software like phpmyadmin, have you got an access to it?

[FernandoLopez]

Thanks, I am going to check it. I´ll let you know.