I’ve got a small problem which is probably very easy to solve, but my PHP/MySQL skills are still in development!
Here’s the problem, I’ve got a shopping site (Cotswold Engineering Supplies) that I’ve been working on for ages (inbetween other jobs) and have been given a new excel spreadsheet with all the data from their product database. On the website they don’t want all the products listed, so I’ve based the sections on the previous version (static), but since then there have been additions to the products and they’re not in the same order.
I have 2 tables in the database one with all the product info, description and product Code and the other database with the Product code and the correct Price, which comes from the Main database.
All I need to do is link the Product codes together to get the correct price, but I can’t quite work out how to do it.
I’m using the PHP Block Action with the MySQL Get Records Action, I can get the info from one database but haven’t quite worked out how to link the two together to get the info onto the page.
I’m not sure how you would do this in the Actions, but the SQL is
pretty straightforward. You have two tables, and they have something
in common in the Product code, so what you want is a “union join”:
SELECT products.*, prices.price FROM products, prices WHERE
products.ProductCode = prices.ProductCode;
What you’ll get back is a combined table of results that includes the
prices. (Obviously, adjust the above to reflect the actual names of
your tables and columns – I’m just guessing from your description.)
Walter
On Oct 23, 2008, at 1:34 PM, SteveB wrote:
I have 2 tables in the database one with all the product info,
description and product Code and the other database with the
Product code and the correct Price, which comes from the Main
database.
All I need to do is link the Product codes together to get the
correct price, but I can’t quite work out how to do it.
Thanks for the reply, I’ll have a crack at that in the morning (it’s late here! ) and let you know how I get on.
Regards
Steve.
On 23 Oct 2008, 9:16 pm, waltd wrote:
I’m not sure how you would do this in the Actions, but the SQL is
pretty straightforward. You have two tables, and they have something
in common in the Product code, so what you want is a “union join”:
SELECT products.*, prices.price FROM products, prices WHERE
products.ProductCode = prices.ProductCode;
What you’ll get back is a combined table of results that includes the
prices. (Obviously, adjust the above to reflect the actual names of
your tables and columns – I’m just guessing from your description.)
Walter
On Oct 23, 2008, at 1:34 PM, SteveB wrote:
I have 2 tables in the database one with all the product info,
description and product Code and the other database with the
Product code and the correct Price, which comes from the Main
database.
All I need to do is link the Product codes together to get the
correct price, but I can’t quite work out how to do it.
Hi Walter,
Just to update you, all is now hunky dory with the database. The web
pages now have the correct prices from the second database. I’m in the
process of updating the other product databases to include the new
prices, and formatting a new section with coils of tubing in different
colours organised by colour and type.
Thanks again for your help and for making it clear.
Regards
Steve.
On 23 Oct 2008, at 22:21, SteveB wrote:
Hi Walter,
Thanks for the reply, I’ll have a crack at that in the morning (it’s
late here! ) and let you know how I get on.
Regards
Steve.
On 23 Oct 2008, 9:16 pm, waltd wrote:
I’m not sure how you would do this in the Actions, but the SQL is
pretty straightforward. You have two tables, and they have something
in common in the Product code, so what you want is a “union join”:
SELECT products.*, prices.price FROM products, prices WHERE
products.ProductCode = prices.ProductCode;
What you’ll get back is a combined table of results that includes the
prices. (Obviously, adjust the above to reflect the actual names of
your tables and columns – I’m just guessing from your description.)
Walter
On Oct 23, 2008, at 1:34 PM, SteveB wrote:
I have 2 tables in the database one with all the product info,
description and product Code and the other database with the
Product code and the correct Price, which comes from the Main
database.
All I need to do is link the Product codes together to get the
correct price, but I can’t quite work out how to do it.