A database + PHP design question: How to store this array
Posted: Wed Apr 21, 2010 3:16 am
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
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
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?
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
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
}
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?