Page 1 of 1
sql help (joins i think)
Posted: Mon Aug 06, 2012 2:43 am
by kevgais
Hi Guys,
I'm in the process of integrating my store with a service called StoreFeeder. I'm 90% of the way there just have this final task to do.
I've been given this SQL code which is in a file that I've uploaded to the OC server. I need to amend code to be compatible with OC field schema. I'm not at all good with SQL and joins and looking at the fields they need it seems to be across multiple tables.
I'm running OC 1.4.9.3
Really grateful for any advice or guidance.
Code: Select all
/*
* Product Download Query
*
* This query is responsible for fetching all products from your website.
*
* It should return all products that you want to send to StoreFeeder.
*
* Required fields are sku, name and price_ex_vat. Other fields also have to be present in
* the output of the query, but may be left empty (see example in the comments
* to the Orders Query about empty fields).
*
* Note that you can 'hardcode' some of the fields, e.g. you could enter:
*
* ... 'Default Warehouse' as 'warehouse'...
* if you want all your products to be assigned to the 'Default Warehouse' inside StoreFeeder
*
*/
$productDownloadQuery = "SELECT
`product.[sku]` AS 'sku',
` product_description.name` AS 'name',
`` as 'description',
`product.[price]` as 'price_ex_vat',
`vat_percent` as 'vat_percent',
`` AS 'rrp',
`` as 'cost',
`` as 'weight',
`` AS 'mpn',
`` as 'supplier',
`` as 'warehouse',
`` as 'inventory',
`` as 'ean',
`` as 'upc',
`` as 'image_url',
`` as 'website_url'
FROM product INNER JOIN product_description ON product.product_id = product_description.product_id";
Re: sql help (joins i think)
Posted: Mon Aug 06, 2012 3:37 am
by upl8t
My first suggestion is for you to get your hands on a good mysql management tool where you can run queries like this.
One I use every day, and it's free, is HeidiSql. It allows you(with the right permissions set on in your user table) to log in remotely from your pc and manage the db. You can also backup databases from server to server, dump to a backup file. I build all of my database stuff from scratch in Heidi. You can download it here...
http://www.heidisql.com/
The join is already in the sql query that you posted. All you have left to do is assign the appropriate field names to each of their labels.
Code: Select all
/*
* Product Download Query
*
* This query is responsible for fetching all products from your website.
*
* It should return all products that you want to send to StoreFeeder.
*
* Required fields are sku, name and price_ex_vat. Other fields also have to be present in
* the output of the query, but may be left empty (see example in the comments
* to the Orders Query about empty fields).
*
* Note that you can 'hardcode' some of the fields, e.g. you could enter:
*
* ... 'Default Warehouse' as 'warehouse'...
* if you want all your products to be assigned to the 'Default Warehouse' inside StoreFeeder
*
*/
$productDownloadQuery = "SELECT
`product.[sku]` AS 'sku',
` product_description.name` AS 'name',
`` as 'description',
`product.[price]` as 'price_ex_vat',
`vat_percent` as 'vat_percent',
`` AS 'rrp',
`` as 'cost',
`` as 'weight',
`` AS 'mpn',
`` as 'supplier',
`` as 'warehouse',
`` as 'inventory',
`` as 'ean',
`` as 'upc',
`` as 'image_url',
`` as 'website_url'
FROM product INNER JOIN product_description ON product.product_id = product_description.product_id";
In each line of the query where there are backticks you need to fill in the appropriate field name from the opencart product table.... You're basically mapping your opencart fields to their field names/labels.
here's a couple of examples
`weight` as `weight`,
`image` as `image_url`,
Use phpmyadmin or HediSql or whatever query tool you have. Paste your finished query into the query tool, run it and dump the result into a csv file or whatever file format they require. This is of course based on dumping the data and uploading it somewhere. If you're actually looking for a real-time integration that's a process way beyond constructing an sql query.
It's often much faster to develop queries like this in a tool like Heidi.. quick tests and then adapt the query to fit any php variables as required.
Hope this helps.
Re: sql help (joins i think)
Posted: Mon Aug 06, 2012 3:46 am
by zuhenry
Probably you should try the Commercial Support forum,
http://forum.opencart.com/viewforum.php?f=88
zh
Re: sql help (joins i think)
Posted: Mon Aug 06, 2012 4:57 am
by kevgais
Hi zuhenry,
Thanks very much for the reply. I've taken your advice and downloaded heidi.
I'm connected to the database all ok so I'd thought I'd start with simple query.
select
product.sku AS 'sku'
product_description.name as 'name'
FROM product
JOIN product_description
ON product.product_id = product_description.product_id
but I get an error pop-up, any idea why?
the table name and columns are all correct. When I run
select
product.sku AS 'sku'
FROM product
it runs no problem, same with product_description.
any ideas?
Kev
Re: sql help (joins i think)
Posted: Mon Aug 06, 2012 5:20 am
by upl8t
Field lists need to be comma separated
eg... sku,name,..... no comma after the last one
Re: sql help (joins i think)
Posted: Mon Aug 06, 2012 4:46 pm
by kevgais
upl8t wrote:Field lists need to be comma separated
eg... sku,name,..... no comma after the last one
bloody excellent. cheers
