473,329 Members | 1,214 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,329 software developers and data experts.

PHP MySql 4 table query

Hi,
I have these 3 tables in my DB.

1. tbl.Member

a. name
b. email
c. username(pri key) ...and other stuff

2. tbl.Product

a. id (pri key)
b. product name
c. category .... and other stuff
d. company(from table maker)

3. tbl.Maker

a. id (pri key)
b. company
c. email and other stuff

When the members are browing products they choose and purchase most of
the products(atleast they apply). These orders will be stored in the
4th table.

4. orders

a. id
b. product name
c. maker
d. username (from tbl.members)....and other stuff.

The question is when a member with username jaunty is browsing the
products I want to show him an indication that he has already purchased
item no 5,9,15 etc.

assuming that I use the following query on the browse product page..

$query = select * from products;

how should I modify $query so that I can also indicate that jaunty has
purchased some of them.

I am very sure this should be a small trick I just want to make sure my
DB structure and queries are proper.

Please help me
Thanks

Dec 27 '05 #1
1 1436
SELECT
ProductName
,(SELECT CASE
WHEN ProductName IN
(SELECT
ProductName
FROM
ORDERS
WHERE
Name = 'Jaunty'
)
Then 'Yes'
Else 'No'
END
) AS 'Order History'
FROM
PRODUCT

A few suggestions:

- with your design a product can belong to only one category. You might want
to create another table called CATEGORIES and
an intesection table called PRODUCT_CATEGORIES to handle a m:m
- don't use "tbl" as a prefix for table names
- it is typical to break out your ORDER table into two table 1)ORDERS
2)ORDER_DETAILS.
<sm***********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
Hi,
I have these 3 tables in my DB.

1. tbl.Member

a. name
b. email
c. username(pri key) ...and other stuff

2. tbl.Product

a. id (pri key)
b. product name
c. category .... and other stuff
d. company(from table maker)

3. tbl.Maker

a. id (pri key)
b. company
c. email and other stuff

When the members are browing products they choose and purchase most of
the products(atleast they apply). These orders will be stored in the
4th table.

4. orders

a. id
b. product name
c. maker
d. username (from tbl.members)....and other stuff.

The question is when a member with username jaunty is browsing the
products I want to show him an indication that he has already purchased
item no 5,9,15 etc.

assuming that I use the following query on the browse product page..

$query = select * from products;

how should I modify $query so that I can also indicate that jaunty has
purchased some of them.

I am very sure this should be a small trick I just want to make sure my
DB structure and queries are proper.

Please help me
Thanks

Dec 27 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
by: Lenz Grimmer | last post by:
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Hi, MySQL 4.0.14, a new version of the popular Open Source/Free Software Database, has been released. It is now available in source and binary...
0
by: Philip Stoev | last post by:
Hi all, Please tell me if any of this makes sense. Any pointers to relevant projects/articles will be much appreciated. Philip Stoev http://www.stoev.org/pivot/manifest.htm ...
0
by: Mike Chirico | last post by:
Interesting Things to Know about MySQL Mike Chirico (mchirico@users.sourceforge.net) Copyright (GPU Free Documentation License) 2004 Last Updated: Mon Jun 7 10:37:28 EDT 2004 The latest...
1
by: jlee | last post by:
I'm pretty much a newbie on mysql, and I need some help. I am running mysql Ver 12.22 Distrib 4.0.24, for portbld-freebsd5.4 (i386) on a server hosting an active website. The site's developer...
1
by: Good Man | last post by:
Hi there I've noticed some very weird things happening with my current MySQL setup on my XP Laptop, a development machine. For a while, I have been trying to get the MySQL cache to work....
3
by: Juan Antonio Villa | last post by:
Hello, I'm having a problem replicating a simple database using the binary log replication, here is the problem: When the master sends an update to the slave, an example update reads as follows:...
1
by: Ike | last post by:
Recently, I began using a different MySQL verver (i.e. different machine as well as different version#, going from 4.12a to 4.1.9 max). The following query used to work: select firstname,...
6
Atli
by: Atli | last post by:
This is an easy to digest 12 step guide on basics of using MySQL. It's a great refresher for those who need it and it work's great for first time MySQL users. Anyone should be able to get...
221
Atli
by: Atli | last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.