PHP SQL - Re: help in formulating a query

Author

Re: help in formulating a query

J.O. Aho

2004-04-24, 4:31 pm

davidklonski wrote:
> Hi
>
> I am new to SQL and are faced with my first complex query.
> I would appreciate if someone could help me with the syntax for the
> following query:
>
> I have two tables: People & Pets
> Here are the columns for the People table:
> ID int(11)
> Name varchar(20)
> Dogs varchar(255)
> Cats varchar(255)
>
> Here are the columns for the Pets table:
> ID int(11)
> Name varchar(255)
>
> Here is how people are related to pets:
> The Dogs column is a camma-delimited list of all the dogs IDs that the
> person owns.
> The Cats column is a camma-delimited list of all the cats IDs that the
> person owns.
>
> If the following row exists in the People table:
> ID = 1
> Dogs = 1,2,
> Cats = 4,5,
> It means that person 1 has two dogs (the dogs IDs are 1 & 2) and
> two cats (the cats IDs are 4 & 5).
>
> Here is what I am trying to do:
> extractd all the people who have a dog, and sort the list by the
> dog name.
>
> I would appreciate any help.
> If someone thinks that a better arrangement for the tables is in
> order, please let me know. I am still learning.

First of all I would made three tables

Owner table
ID int(11)
Name varchar(20)

Pet table
ID int(11)
Name varchar(255)
Type varchar(3) (save here cat or dog)

Relation table
OwnerID int(11)
PetID int(11)

This way you can then make easy searches for what you want, you will need to
join the tables, you get good information in the online manual with user
comments that can be found at http://dev.mysql.com/doc/mysql/en

The only way I can see that you can still keep your table setup is to use
quite a lot of PHP code, first fetch all lines where an owner has a dog, then
make a string of the dog IDs and use

SELECT * Pets WHERE ID IN($longstring) ORDER BY Name

You will then have to manually pair the dog and the owner together.

//Aho

vornn

2004-04-24, 4:31 pm

Mmm, I think the database needs a little redesign, heres why:

The people table required the following columns:

Person ID
Person Name

That's it.

The pets table should have:

Pet ID
Pet Type
Pet Owner

The design of the database allows you to just have the information you
want to store, and retrieve it quickly.

The pet information is linked to the people table via the Pet Owner
field, and the pet is defined under the type field (Cat, Dog, etc...)

This will allow you to have people who do not have a cat, for
instance.

Retrieving the information from the database is simple from there.

I really do like database design.

Hope this helps...

:)

'Who are you? Who slips into my robot body and whispers to my
ghost?'

----------------------------------------
The post originated from PHP Freaks:
----------------------------------------
http://www.phpfreaks.com
http://www.phpfreaks.com/forums

Sponsored Links