How to Export Customers just using MySQL, which tables are corresponding

[KamranMK]

I am not sure if I am doing it right, that's why decided to ask in order to export the database of customers what should I do?

 

 

There is a big pdf file in documentation regarding database structure of PS 1.5.5 and there i have customer & statistics

 

is that something i need to export and then import into my database?

[vekia]

what informations you want to export exactly? only customer info (name surname etc.) ?

[KamranMK]

what informations you want to export exactly? only customer info (name surname etc.) ?

Yes just that information, name, surname, email, phone number and also if possible their passwords, how can i do that?

[vekia]

you can't export password because passwords are encrypted (but you can of course export password hash - but in this case you will have to use the same cookie key)

select firstname,lastname,email from ps_customer 

to get phone number you will have to LEFT JOIN ps_address table

select A.firstname,A.lastname,A.email,B.phone,B.phone_mobile from ps_customer AS A
LEFT JOIN ps_address AS B ON (B.id_customer = A.id_customer) 

remember that customers can have many addresses and many phone numbers defined!

[KamranMK]

you can't export password because passwords are encrypted (but you can of course export password hash - but in this case you will have to use the same cookie key)

select firstname,lastname,email from ps_customer 

to get phone number you will have to LEFT JOIN ps_address table

select A.firstname,A.lastname,A.email,B.phone,B.phone_mobile from ps_customer AS A
LEFT JOIN ps_address AS B ON (B.id_customer = A.id_customer) 

remember that customers can have many addresses and many phone numbers defined!

 

you can't export password because passwords are encrypted (but you can of course export password hash - but in this case you will have to use the same cookie key)

select firstname,lastname,email from ps_customer 

to get phone number you will have to LEFT JOIN ps_address table

select A.firstname,A.lastname,A.email,B.phone,B.phone_mobile from ps_customer AS A
LEFT JOIN ps_address AS B ON (B.id_customer = A.id_customer) 

remember that customers can have many addresses and many phone numbers defined!

 

Vekia thank you for helping me out, so if i can't export password does that mean that customers will not be able to log in to their accout on my site and if so, how does it work? will they have to write passwords again?

[KamranMK]

Also what if in my case each customer has one adress and one phone number defined? What can i do with the code which you have wrote here

[vekia]

Vekia thank you for helping me out, so if i can't export password does that mean that customers will not be able to log in to their accout on my site and if so, how does it work? will they have to write passwords again?

 

I mean that you can only export encrypted passwords, if you will want to use their data in other prestashop websites, customers will not be able to log in to the store with own passwords because each shop has different code to encrypt password (this code is named SALT). You can only change shop SALT code, then they will log in with own passwords.

[vekia]

Also what if in my case each customer has one adress and one phone number defined? What can i do with the code which you have wrote here

ok, i pasted above mysql queries.

you can run these queries in software like PHPMyAdmin (or other database managers) - then you will see results of these queries, you can export them to .sql file

[KamranMK]

ok, i pasted above mysql queries.

you can run these queries in software like PHPMyAdmin (or other database managers) - then you will see results of these queries, you can export them to .sql file

 

Vekia, I am doing eveyrthing as you said, but in my phpMyadmin i don't have an option of exporting like sql?

[KamranMK]

ok, i pasted above mysql queries.

you can run these queries in software like PHPMyAdmin (or other database managers) - then you will see results of these queries, you can export them to .sql file

With the first query i was able to export to sql but with the second, there is not such option

[vekia]

below query results there is a button "export"

[KamranMK]

below query results there is a button "export"

Believe it or not i am doing the same thing but i don't have any export as .sql file maybe my phpmyadmin is old can this be?

[vekia]

sure, it might be related to old version of phpmyadmin

 

are you able to connect to your mysql server, for example, from your computer ? if so, you can use software like heidiSQL to export queries

[KamranMK]

sure, it might be related to old version of phpmyadmin

 

are you able to connect to your mysql server, for example, from your computer ? if so, you can use software like heidiSQL to export queries

Yes I am able to connect to it, great i will try this software tonight and see if i can export the information

[vekia]

im using this software everyday, it is very usefull and you can easily manage own database there  

waiting for your response hope that you will get it !

[KamranMK]

im using this software everyday, it is very usefull and you can easily manage own database there

waiting for your response hope that you will get it !

 

Hi Vekia, so after a long time i am finally transferin the customer database and i was able to execute the first query and take it from the database and isert into the new one, though what i was not able to do that is to take the phone numbers which you have suggested to execute the second command, which worked but when i exported it using the heidisql and then inserted into the new db it did not work, so how can i take the phone numbers from the database in a different way and then put them into the new one so that it matches the customers ids, also take into account that i am having a different customer id from that i had in the old database, i hope i was clear

[vekia]

"i exported it using the heidisql and then inserted into the new db it did not work"

 

you've got some error communicate?

[PrestaShark]

I mean that you can only export encrypted passwords, if you will want to use their data in other prestashop websites, customers will not be able to log in to the store with own passwords because each shop has different code to encrypt password (this code is named SALT). You can only change shop SALT code, then they will log in with own passwords.

I mean that you can only export encrypted passwords, if you will want to use their data in other prestashop websites, customers will not be able to log in to the store with own passwords because each shop has different code to encrypt password (this code is named SALT). You can only change shop SALT code, then they will log in with own passwords.

I mean that you can only export encrypted passwords, if you will want to use their data in other prestashop websites, customers will not be able to log in to the store with own passwords because each shop has different code to encrypt password (this code is named SALT). You can only change shop SALT code, then they will log in with own passwords.

And the question is? How to change shop SALT code?

[velisa]

According to Vekias guide I exported successfully those data.

Is there a way to export by customers how much money they spend. I searched through tables and did not find this info. I guess that I should combine few queries.

 

Update:

Found that this query is working for me:

SELECT id_customer, SUM( total_paid_real ) 
FROM ps_orders
GROUP BY id_customer

Can somebody help me with nested query to combine above query with query that Vekia posted as I need one unique CSV:

 

SELECT A.firstname, A.lastname, A.email, A.birthday, B.phone, B.phone_mobile, B.address1, B.address2, B.postcode, B.city, C.name
FROM ps_customer AS A
LEFT JOIN ps_address AS B ON ( B.id_customer = A.id_customer ) 
LEFT JOIN ps_country_lang AS C ON ( C.id_country = B.id_country )

 

Thanks

Edited April 1, 2015 by velisa (see edit history)

[velisa]

I found solution, so if anybody needs how much their customers spent:

SELECT A.firstname, A.lastname, A.email, A.birthday, B.phone, B.phone_mobile, B.address1, B.address2, B.postcode, B.city, C.name, SUM(D.totail_paid_real)
FROM ps_customer AS A
LEFT JOIN ps_address AS B ON ( B.id_customer = A.id_customer ) 
LEFT JOIN ps_country_lang AS C ON ( C.id_country = B.id_country ) 
LEFT JOIN ps_orders AS D ON (A.id_customer=D.id_customer) GROUP BY 1,2

[biteme]

hi all i want to ask a question.

 

i want to export my costumers numbers who wants to get news on our website. can i do ?

[PrestaShark]

hi all i want to ask a question.

 

i want to export my costumers numbers who wants to get news on our website. can i do ?

 

Hi,

You need just to use Newsletter Module. There You can export file with customers that want to receive newsletter from Your site (Newsletter Block or registred user with newsletter subscribtion

[biteme]

i used the newsletter module. my costumers are select the sucscribe tick. bu t i want to phone numbers who click the sucscribe tick. i want to send a text messege (SMS) to my customers who wants a sucscribe news..

Edited April 28, 2015 by biteme (see edit history)

[PrestaShark]

Ah that trick...

In that case You should use free Export Customers module by PhpMadman

from here https://www.prestashop.com/forums/topic/220774-free-module-export-customers-with-address/

 

This is highly effective and recomended tool

[biteme]

Prestashark thank you so much. thats really great module . and its free.

[PrestaShark]

You welcome
Happy selling!

[Shabas]

hi,

 

I need a help here. I need to export order of specific date. Export orders only exports little info. I need address pincode order number price customer name payment process in different columns. Can anyone suggest a query to export. Any help appreciated. thanx

[ajaykumarsingh395]

I mean that you can only export encrypted passwords, if you will want to use their data in other prestashop websites, customers will not be able to log in to the store with own passwords because each shop has different code to encrypt password (this code is named SALT). You can only change shop SALT code, then they will log in with own passwords.

So how can change SALT code ?