Basically my aim is to provide a one to many relationship. I.e. One customer has many orders.
I have set the mysql tables out as so:
create table customers
(
customer_id varchar(50) NOT NULL,
date varchar(50),
title varchar(50),
surname varchar(50),
contact_number varchar(50),
email varchar(50),
PRIMARY KEY (customer_id)
);
create table orders
(
plu varchar(50),
description varchar(100),
qty int,
price varchar(50),
customer_id varchar(50) NOT NULL
);
Ok? or have I messed up?
Using this I have used the following select query:
$query = "SELECT customers.title, customers.date, customers.surname, customers.contact_number, customers.email
FROM customers INNER JOIN orders ON customers.customer_id=orders.customer_id";
$result = mysql_query($query);
My aim and taget is to display multiple tables, each table holding a seperate customer and their orders.
Within each table I used this query:
$query2 = "SELECT orders.plu, orders.description, orders.qty, orders.price
FROM customers INNER JOIN orders ON customers.customer_id=orders.customer_id";
$result2 = mysql_query($query2);
and it successfully displays the orders of a customer. BUT! I get multiple copies of the same table depending on the number of items in the orders table. aaaaaahhh!
If anyone could please advise on my tables in the database and also the select queries. or even ultimately advise on how to acomplish my aim.
