How to count items in array / implode

PHP
1

I have a set up as follows (bear with me!)

A table of Candidates, and table of Profiles, and a joining table CandidateProfiles.

The idea is that one form is used to enter the Candidate details (name, address, etc).

Then a second form is used immediately afterwords which displays all the Profiles using an array, and the user simply ticks checkboxes for each Profile they want to associate with the Candidate being added.

From this, its possible to search for Candidates using the same list of Profiles and checkboxes, which currently returns any Candidates which have any matching profiles, like this:

Candidate…Matches
Candidate 1…3
Candidate 2…3
Candidate 3…2
Candidate 4…2
Candidate 5…1

I’m trying to change it so that it only returns Candidates which match all the Profiles ticked, so if 3 profiles are ticked, the results would just be:

Candidate…Matches
Candidate 1…3
Candidate 2…3

The SQL query on the results page currently looks like:


SELECT Candidates.*
     , COUNT(*) AS rank 
  FROM CandidateProfiles
INNER
  JOIN Candidates
    ON Candidates.CandidateID = CandidateProfiles.CandidateID 
 WHERE CandidateProfiles.ProfileID 
       IN(' . implode(',', $ckbox).') 
GROUP 
    BY Candidates.CandidateID 
HAVING COUNT(*) = 3
ORDER 
    BY Candidates.CandidateID

Which returns any Candidates with 3 matches.

But obviously that will depend on how many are ticked for any given search.

So what I’m trying to figure out is how to count all the Profiles in the array / implode, and echo that out instead of a hard coded number where the 3 is above.

The PHP code on the search page currently looks like this:


//Get all the keywords
$sql = "SELECT * FROM Profiles ORDER BY Category, Profile";
$query = mysql_query($sql); 

$current_category = ""; 
$row_type = "";
$column = 1;

while ($keyword=mysql_fetch_assoc($query)) {

    //If new category close previous row & display new category
    if ($keyword['Category']!=$current_category) {
        if ($current_category && $column !=1 ) { echo "</tr>\
"; }
        $current_category = $keyword['Category'];
        echo "<tr class=\\"categorycellnew\\"><td colspan=\\"2\\"><p>$current_category</p></td></tr>\
";
        $column = 1; $row_type="";
    }

//Create new row if 1st keyword
    if ($column == 1) {
        $row_type = ($row_type=="odd")?"even":"odd";
        echo "<tr class=\\"".$row_type."\\">"; 
    }

    //Display the checkbox
    echo "<td width=\\"2%\\">";
    echo "<input type=\\"checkbox\\" class=\\"tickbox_".$row_type."\\"";
    if (in_array($keyword['ProfileID'],$photokeywords)) { echo " checked"; }
    echo " name=\\"ckbox[".$keyword['ProfileID']."]\\" id=\\"ckbox[".$keyword['ProfileID']."]\\">";
    echo "</td>\
";

    //Display the Keyword
    echo "<td width=\\"14%\\" align=\\"left\\" class=\\"profilecellnew\\">" .$keyword['Profile']."</td>\
";

    //Close the row if 5th keyword OR increase column count
    if ($column == 6) { echo "</tr>"; $column = 1; }
    else { $column++; }
}

if ($column != 1) { echo "</tr>"; }
?>

Hope that all makes sense and that someone can help out.

2

fixed it for you…

3

OK, tried just echoing $ckbox out in the body of the page, and just got out ‘Array’.

I’ve now added in:

$total = count($ckbox);

Just beneath:

$ckbox = array_keys($_GET['ckbox']);

And then echoed that out in body if the page, and that’s now at least giving me the count number I’m after.

So now I just need to figure out the syntax to echo that out in the middle of the SQL.

Should it be something like:

HAVING COUNT(*) = \“echo $total;\” ?

4

RESOLVED:

HAVING COUNT(*) = ‘.$total.’