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?