CODEWITHSUNDEEP
phpmysqlsqldatabase
Grant Carlisle
Grant Carlisle

Reputation: 175

Create a MYSQL Select statement combining information from shopping basket and products tables

I've been trying to create a shopping basket class and have hit a brick wall when trying to output by shopping basket contents.

My users basket information is stored in a table with the following structure.

     CREATE TABLE IF NOT EXISTS basket (
            `ID` int PRIMARY KEY AUTO_INCREMENT,
            `ProductID` int,
            `Quantity` int,
            `OptionID` int,
            `Cookie` varchar(40)
            ;

Where the Cookie field is a unique identifier for the user stored in a cookie.

My products tables is structured as follows:

    CREATE TABLE IF NOT EXISTS products (
            ID int PRIMARY KEY AUTO_INCREMENT,
            Title varchar(200),
            Description text,
            Specification text,
            Price decimal(10,2),
            CategoryID int,
            Weight int,
            Options text,
            OptionValues text,
            OptionCost text,
            Discount int(2),
            Featured boolean,
            Images text,
            Stock int,
            Related text,
            Offer boolean,
            OfferDetails boolean,
            Language int
            ;

What I want to do SELECT the items from my basket with their respective product Title and price information from the products table.

So in essence I need a sql statement that will output the following using the productID to link the two tables:

ProductID From basket table

Quantity From basket table

Title From products table

Description From products table

Price From products table

I have done a similar thing before by looping through the basket array and then querying the products table with the current ProductID but surely this can be done with one query?

Upvotes: 2

Views: 268

Answers (2)

Nikos Tsirakis
Nikos Tsirakis

Reputation: 739

SELECT b.ProductID, b.Quantity, p.Title, p.Description, p.Price 
FROM basket b 
     LEFT JOIN products p ON (b.ProductID = p.ID);

Also have in mind this:

An INNER JOIN excludes from the results set any records that do not exist in both tables.

A LEFT JOIN includes all records from the first table and all matching records from the second table, assuming the query does not exclude null values from the second table.

Upvotes: 0

Mahmoud Gamal
Mahmoud Gamal

Reputation: 79969

Try this:

SELECT
  b.ProductID,
  b.Quantity,
  p.Title,
  p.Description,
  p.price
From basket b
INNER JOIN products p ON b.ProductID = p.Id;

Please read these:

Upvotes: 2

Related Questions