Hi,
I want to connect cscart_users_profiles to cscart_profile_fields_data because in the last table company name is set.
Does somebody has a solution for that or tips?
Kind regards,
Bas
For what purpose? Duplicated data only makes redundant data at the end of the day.
This should get you started:
[sql]SELECT b_firstname, b_lastname, value
FROM cscart_user_profiles
INNER JOIN cscart_profile_fields_data ON cscart_profile_fields_data.object_id = cscart_user_profiles.profile_id[/sql]
@Magpie Don
Thanks, its a good start!
Query:
SELECT b_firstname, value
FROM cscart_user_profiles
INNER JOIN cscart_profile_fields_data ON cscart_profile_fields_data.object_id = cscart_user_profiles.profile_id
where cscart_user_profiles.user_id = 4671
ORDER BY cscart_user_profiles.b_firstname ASC
Output:
b_firstname value
B. L… 123456789
B. L… 123456789
B. L…
B. L…
B. L… Bedrijf Bas
B. L… Bedrijf Bas
B. L… 123
B. L… 123
B. L… Bas
B. L… Bas
But if its possible i only want return 1 row like this (with the value Bedrijf Bas):
b_firstname value
B. L… Bedrijf Bas
Is that possible?
Kind regards,
Bas
There is an 'object_type' and a 'field_id' in the cscart_profile_fields_data table.
I add in company_name to the profile fields. There is a database record for each of the Billing and Shipping values.
You need to look up what the field_id is for the company_name. It's in the cscart_profile_field_descriptions table. In my case the company name is in profile_field_description.field_id 36 and 37 (one is the Billing company name the other is the Shipping company name). NOTE: You can also see the field_ID in the URL of the EDIT hyperlink on the Customers > Profile Fields page.
So, for the Billing Company Names only (which I added), I would be selecting this:
[sql]
SELECT b_firstname, value
FROM cscart_user_profiles
RIGHT JOIN cscart_profile_fields_data ON cscart_profile_fields_data.object_id = cscart_user_profiles.profile_id
WHERE cscart_profile_fields_data.value NOT LIKE '' AND cscart_profile_fields_data.field_id = '36' AND cscart_user_profiles.profile_id = 4671
ORDER BY cscart_user_profiles.b_firstname ASC
[/sql]
There is also a Company field in the cscart_profile_fields table with the field ID of 8. I think it's been there since ver 1, because I have old data for it in the cscart_profile_fields_data table. IF you are asking for the Company name in the Contact Information, then the field_ID would be 8 in the query. It depends upon which Profile Field ID you are selecting.
You need to be able to browse the tables in your CS-Cart database. You should have something like cPanel for your website, and phpMyAdmin is commonly included with cPanel. It's used to connect to the database and browse the table structures, run queries, etc.
Thanks Magpie Don. It works!
@Magpie Don,
I have now this query, which works, but now i want also add the following fields.
cscart_profile_fields_data.field_id = '73' and cscart_profile_fields_data.field_id = '75'
Is that possible and how can i do that?
Kind regards,
Bas
SELECT
cscart_user_profiles.user_id as Code,
cscart_user_profiles.b_firstname as Name,
cscart_user_profiles.b_title as ContactTitle,
cscart_user_profiles.b_firstname as ContactLastName,
cscart_user_profiles.b_address as AddressLine1,
cscart_user_profiles.b_address_2 as AddressNumber,
cscart_user_profiles.b_zipcode as Postcode,
cscart_user_profiles.b_city as City,
cscart_user_profiles.b_country as Country,
value as Company
FROM cscart_user_profiles
RIGHT JOIN cscart_profile_fields_data ON cscart_profile_fields_data.object_id = cscart_user_profiles.profile_id
WHERE
cscart_user_profiles.b_title not like ''
AND
cscart_user_profiles.user_id NOT LIKE ''
AND
cscart_profile_fields_data.field_id = '69'
AND
user_id = 4252
Try this:
[sql]SELECT b_firstname ,value
FROMcscart_user_profiles
RIGHT JOIN cscart_profile_fields_data ON cscart_profile_fields_data.object_id = cscart_user_profiles.profile_id
WHERE cscart_user_profiles.b_title NOT LIKE ''
AND cscart_user_profiles.profile_id = '4252'
AND (cscart_profile_fields_data.field_id ='69' OR cscart_profile_fields_data.field_id ='73' OR cscart_profile_fields_data.field_id ='75')
ORDER BY cscart_user_profiles.b_firstname ASC[/sql]
You might want to look up some of the syntax for more queries via Google Search or by visiting http://dev.mysql.com
Hi Magpie!
Thanks for your reply!
The query works fine but it gives me 3 records back, is it also possible that it will return 1 row with 3 cscart_profile_fields_data.field.id?
Kind regards,
Bas
This is the final query! Thanks for your help!
SELECT b_firstname,
pfd69.value as value69,
pfd73.value as value73,
pfd75.value as value75
FROM cscart_user_profiles AS up
RIGHT JOIN cscart_profile_fields_data AS pfd69
ON pfd69.object_id = up.profile_id
AND pfd69.field_id ='69'
RIGHT JOIN cscart_profile_fields_data AS pfd73
ON pfd73.object_id = up.profile_id
AND pfd73.field_id ='73'
RIGHT JOIN cscart_profile_fields_data AS pfd75
ON pfd75.object_id = up.profile_id
AND pfd75.field_id ='75'
WHERE up.b_title NOT LIKE ''
AND up.profile_id = '4252'