array compiling from mysql help required.

Question

Hi

This has been bugging me, I can't get my head around it. I will use the foodstuffs analogy to try and simplify my probelm.

1000 members of the public where asked to pick a variety from each of 13 categories of footstuff. These selections were then stored in a mysql database against their name.

e.g. billy mary etc. etc.

milk....semi. .skimmed...

bread...white...brown....

cheese..edam.....edam....

fruit...apple...orange...

veg....potato...sprout...

meat....beef.....beef....

sweet..bonbons..liquorice..

fish...trout....salmon...

crisp....s&v....plain....

biscuit..hovis..rich tea..

wine.....red.....red.....

beer....stella..carlsburg..

carb....coke.....pepsi....

One of those 1000 was then asked to select anywhere from zero to 13 of their selections via checkboxes.

By searching the database how many others selected the same varieties?

Display in a table showing all their names and what they selected for all 13 varieties.

Does that make sense? I hope so 'cause it's driving me mad.

Thanks in advance.

Answer 1

partial answer:

• compose a linear set of selections by sorting and concatenating
• comparing then becomes a simple WHERE clause

so you would first do a calculation run, putting strings into some field like "milk|semi|skimmed" literally.

Answer 2

Assuming you have a simple layout, then you would have something like this (I'll restrict myself to three of the categories):

PersonId  What_Milk  What_Bread  What_Cheese
       1  Semi       Wheat       Swiss
       2  Skimmed    Rolls       French
       3  Soy        Brown       Smelly
       4  Low Fat    Wheat       Swiss

If I understood correctly, your problem is this:

When the person 4 is asked to choose 0 .. 3 of her food items, she may select the "Bread" and the "Cheese" checkbox, which means the query should yield person 1 as a match. Right?

SELECT
  PersonId,
  What_Milk,
  What_Bread,
  What_Cheese
FROM
  FoodPreference
WHERE
  PersonId != ?
  AND What_Milk   = IFNULL(NULLIF(?, ''), What_Milk)
  AND What_Bread  = IFNULL(NULLIF(?, ''), What_Bread)
  AND What_Cheese = IFNULL(NULLIF(?, ''), What_Cheese)

Your checkbox values will later go where the question mark placeholders are. (I've replaced the CASE WHEN constructs that used to be here with IFNULL/NULLIF, that has the same effect but is friendlier for PHP prepared statements.)

If a checkbox has not been checked (thus fixing the value to something), the corresponding column is compared to itself. That means it's value does not influence the result. If the other columns match, the row will be selected.

That also means that if zero checkboxes are selected by the user, all rows will be returned. The more food items a person selects, the closer the match will be.

In PHP, I'd recommend you use mysqli_prepare() to create a prepared statement from the query string, and mysqli_stmt_bind_param() to bind actual values to the question mark placeholders. That is much safer than building the SQL string directly. The PHP documentation has a whole lot of info on mysqli, have a look at it.

Answer 3

SELECT  
    PersonId,  
    milk,  
    bread,  
    cheese
FROM  FoodPreference
WHERE  PersonId != :chosen_person_id  
    AND $milk= CASE WHEN :isset($_POST["milk"]))> '' THEN :isset($_POST["milk"]))   ELSE milk END  
    AND $bread= CASE WHEN :isset($_POST["bread"]))> '' THEN :isset($_POST["bread"]))  ELSE bread END  
    AND $cheese= CASE WHEN :isset($_POST["cheese"]))> '' THEN :isset($_POST["cheese"])) ELSE cheese END

Am I on the right track?

Answer 4

Ok, I posted one response but Tomalak quite rightly pointed out a logical flaw. I'll try again:

An alternative to listing the categories as thirteen columns is to list them as thirteen rows in a table like the following:

CREATE TABLE FoodPreference (
  PersonID   INT NOT NULL REFERENCES People,
  FoodCat    VARCHAR(10) NOT NULL REFERENCES FoodCategories,
  FoodChoice VARCHAR(10) NOT NULL,
  PRIMARY KEY (PersonID, FoodCat)
);
INSERT INTO FoodPreference VALUES
  (123, 'bread', 'white'),
  (123, 'milk', 'skim'),
  (123, 'cheese', 'edam'), ...
  (321, 'bread', 'brown'),
  (321, 'milk', 'whole'),
  (321, 'cheese', 'edam'), ...

Then you can use a query like the following, matching any row from the chosen person (p1) to a row with the same food choice from another person (p2) and from there to all the other choices of that person (p3):

SELECT DISTINCT p3.*
FROM FoodPreference AS p1
  JOIN FoodPreference AS p2 
    ON (p1.FoodCat = p2.FoodCat AND p1.FoodChoice = p2.FoodChoice 
      AND p1.PersonID != p2.PersonID)
  JOIN FoodPreference AS p3 
    ON (p2.PersonID = p3.PersonID AND p2.FoodCat != p3.FoodCat)
WHERE p1.PersonID = {(int)$chosen_person_id}
  AND p1.FoodCat IN ('milk', 'bread', 'cheese');

The list 'milk', 'bread', 'cheese' in the WHERE clause is something you need to build in your PHP code, based on the $_POST variable in your application. If a checkbox is checked, include that food category in the list.

<?php
$food_cat_array = array("'none'");
$legal_food_cats = array('milk'=>1, 'bread'=>1, 'cheese'=>1, ...);
foreach (array_intersect_key($_POST, $legal_food_cats) as $key => $checked) {
  if ($checked) {
    $food_cat_array[] = "'$key'"; 
  }
}
$in_predicate = join(',', $food_cat_array);

Answer 5

$sql = mysql_query("        
    SELECT  
    *
    FROM  ".$prefix."_users
    WHERE  username !='$username'  
    AND req_country   = IFNULL(NULLIF($bcountry, ''), req_country)  
    AND req_region  = IFNULL(NULLIF($bregion, ''), req_region)  
    AND req_type = IFNULL(NULLIF($btype, ''), req_type)
    AND req_beds = IFNULL(NULLIF($bbeds, ''), req_beds)
    AND req_value = IFNULL(NULLIF($bvalue, ''), req_value)
    AND country   = IFNULL(NULLIF($scountry, ''), country)  
    AND region  = IFNULL(NULLIF($sregion, ''), region)  
    AND type = IFNULL(NULLIF($stype, ''), type)
    AND beds = IFNULL(NULLIF($sbeds, ''), beds)
    AND value = IFNULL(NULLIF($svalue, ''), value)
    AND pool = IFNULL(NULLIF($spool, ''), 'Yes')
    AND garage = IFNULL(NULLIF($sgarage, ''), >0) 
         AND disabled = IFNULL(NULLIF($sdisabled, ''), 'Yes')");
$num = mysql_num_rows($sql);
echo "Total matches ($num): <br><br>";
        while($row = mysql_fetch_array($sql)){...etc

Answer 6

case "display_results":

if ($bcountry = !isset($_POST["bcountry"])){
   $bcountry = "No";
   }else {
   $bcountry = "Yes";
   }
if ($bregion = !isset($_POST["bregion"])){
   $bregion = "No";
   }else {
   $bregion = "Yes";
   }
if ($btype = !isset($_POST["btype"])){
   $btype = "No";
   }else {
   $btype = "Yes";
   }
if ($bbeds = !isset($_POST["bbeds"])){
   $bbeds = "No";
   }else {
   $bbeds = "Yes";
   }
if ($bvalue = !isset($_POST["bvalue"])){
   $bvalue = "No";
   }else {
   $bvalue = "Yes";
   }
if ($scountry = !isset($_POST["scountry"])){
   $scountry = "No";
   }else {
   $scountry = "Yes";
   }
if ($sregion = !isset($_POST["sregion"])){
   $sregion = "No";
   }else {
   $sregion = "Yes";
   }
if ($stype = !isset($_POST["stype"])){
   $stype = "No";
   }else {
   $stype = "Yes";
   }
if ($sbeds = !isset($_POST["sbeds"])){
   $sbeds = "No";
   }else {
   $sbeds = "Yes";
   }
if ($svalue = !isset($_POST["svalue"])){
   $svalue = "No";
   }else {
   $svalue = "Yes";
   }
if ($spool = !isset($_POST["spool"])){
   $spool = "No";
   }else {
   $spool = "Yes";
   }
if ($sgarage = !isset($_POST["sgarage"])){
   $sgarage = "No";
   }else {
   $sgarage = "Yes";
   }
if ($sdisabled = !isset($_POST["sdisabled"])){
   $sdisabled = "No";
   }else {
   $sdisabled = "Yes";
   }

     $result = mysql_query("SELECT * FROM  ".$prefix."_users WHERE  username!='$username' 
AND (('$bcountry'='Yes' AND req_country= '$country') OR ('$bcountry'='No')) 
AND (('$bregion'='Yes' AND req_region= '$region') OR ('$bregion'='No'))
AND (('$btype'='Yes' AND req_type= '$type') OR ('$btype'='No'))
AND (('$bbeds'='Yes' AND req_beds= '$beds') OR ('$bbeds'='No'))
AND (('$bvalue'='Yes' AND req_value= '$value') OR ('$bvalue'='No'))
AND (('$scountry'='Yes' AND country= '$req_country') OR ('$scountry'='No'))
AND (('$sregion'='Yes' AND region= '$req_region') OR ('$sregion'='No'))
AND (('$stype'='Yes' AND type= '$req_type') OR ('$stype'='No'))
AND (('$sbeds'='Yes' AND beds= '$req_beds') OR ('$sbeds'='No'))
AND (('$svalue'='Yes' AND value= '$req_value') OR ('$svalue'='No'))
AND (('$spool'='Yes' AND pool= 'Yes') OR ('$spool'='No'))
AND (('$sgarage'='Yes' AND garage>=1 ) OR ('$sgarage'='No'))
AND (('$sdisabled'='Yes' AND disabled= 'Yes') OR ('$sdisabled'='No'))
")or die("MySQL ERROR: ".mysql_error());
$number = mysql_num_rows($result);