Page 1 of 1

A database + PHP design question: How to store this array

Posted: Wed Apr 21, 2010 3:16 am
by siko
Say I have a table for users, each user may to selects his preference of any number of fruits out of a total of 100 different kinds of fruits, which will be later used in my search form to search for all users who eats apples, or all users who eats pears, or all users who eats pears and apples etc.

How should I store this data?

What I am currently doing is

Code: Select all

implode ("~", $_POST["myFruits"]);
//STORE in the user table in a field called fruit
So what I have in the field looks something like apple~orange~pear etc.

In this case however, if I want to perform a search, SQL cant specify the criterias I need for fruits.

I.E.

SELECT * FROM tbl_users WHERE gender = "male" (this is pretty straightforward)
but
SELECT * FROM tbl_users WHERE gender = "male" AND fruit = "???" (I cant specify the fruits)

And to solve this problem I execute the select statement, and manually run through all records using

Code: Select all

$result = mysql_query("SELECT * FROM tbl_users WHERE gender = 'male'")
while($r=mysql_fetch_array($result)) {
  myFruits = explode ($r["fruit"]);
  //run loop to check for fruits that this guy prefers
  If (match)
    //code... yada yada
}
This is running for me, but I just started trying out pagination, and that is where things are starting to look out of hand:

To perform pagination, first I need to count the number of records that fulfills my search criteria, so I'll have to run the SQL once (and I cant count the SQL result now because it is not the final results - cant specify the fruits criteria in SQL), then do the while loop and filter in users who eats the fruits in my search criteria, then count the records.

I'll then have to repeat the above again, SQL + while loop and filtering to print out the relevant page of records.

Is there a better way?

Re: A database + PHP design question: How to store this arra

Posted: Wed Apr 21, 2010 3:30 am
by dejvos
Hi.

Long story:
Study Normal Forms a bit: http://en.wikipedia.org/wiki/Database_n ... rmal_forms.

Shortly:
You have to put your data into another table. Then you will have one table which contains a columns only with single information about user (like name, surname, date of birth, etc.). Fruits will be stored in second table and then you connect those two tables by third which will contain only three columns - id(PK), id_user, id_fruit