[Jay6390]

Hi,
I have a table that I am storing values in (suprisingly ) and I have a field that i will need to seperate multiple values in. I need some sort of system to be able to use so that when i come to search the values, i can do a query without having to read the values 1 by one in php and then search to see if the value is in it. I was thinking of doing something along the lines of storing the data like this in a field

Code:

[VALUE A][VALUE B][VALUE C][VALUE Z]

and then when i wanted to query it use something like

SELECT * FROM `table` WHERE `fieldname` LIKE '%[VALUE C]%' 

I hope that is clear enough to explain what it is i am trying to do. Does anyone know of a better way of accomplishing this? Please note that the number of values is not fixed, and the data inside them can contain spaces etc

Thanks in advance

Jay

[omniman]

Please don't do that - you're asking for trouble.

Rather, create another table and foreign-key the primary key from the original table. That way you can store an unlimited number of values corresponding to that "field" and not have to worry about it.

[Jay6390]

Hi Ominman. Could you be a bit more specific please. I understand that I could creat a new table, and have all the values stored in multiple fields, but the problem is that the number varies. Or am I misunderstanding what it is you are trying to say?

Jay

[omniman]

See, that's the thing with having a foreign key relationship. Depending on your business logic, the relationship could be 0-N. So, you could have as few as 0 entries in the 2nd table, or as many as you want. That way, you can treat each entry separately.

I don't know your exact implementation, so let's assume the following case:
You have a user table that stores basic information. You want the ability to store multiple phone numbers for one user, but don't want to limit the amount of numbers. You are suggesting creating a phone field, and put multiple values in that. Rather than doing that, create a separate table, and utilize the primary key from the first table to reference the userID.

Say your user table has the following fields:

userID (primary key)
name
email
address

Your phone table would have something like this:
phoneID (primary key)
userID (foreign key - references user table)
phoneNumber
numberType

Sample data may be as follows:

Code:

User Table:
+++++++++++++++++++++++++++++++++++++++++++++++
userID	name		email			address 
----------------------------------------------------------
1	Steve		steve@steve.com	    123 My St
2	Joe		Joe@steve.com		34 His Place
3	John		test@test.com		333 Here Etc
+++++++++++++++++++++++++++++++++++++++++++++++


Phone Table:
+++++++++++++++++++++++++++++++++++++++++++++++
phoneID		userID		number		type
------------------------------------------------------------
1		1		555-1212	home
2		1		888-9987	cell
3		1		333-4423	work
4		2		223-2342	home
+++++++++++++++++++++++++++++++++++++++++++++++

This way, you can see that phone numbers 1,2, and 3 all belong to userID 1(Steve)

Then, to get all of Steve's phone numbers, you could do the following:

SELECT * FROM phone_table WHERE userID = '1' 

This should help - or at least get you started in the right direction. I suggest doing some reading on relational databases and in particular, JOINS (how to select multiple tables at once and "link" together).

Good Luck!

[Jay6390]

Ah ok I see what you mean omniman. Thanks for explaining it a bit clearer. I will definitely look into JOINS a bit more. I've heard of them but I'm not all that up on mysql, only the basics of how to use SELECTS

Jay