SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    How to count items in array / implode

    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:

    Code:
    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:

    PHP Code:
    //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 !=) { echo "</tr>\n"; }
            
    $current_category $keyword['Category'];
            echo 
    "<tr class=\"categorycellnew\"><td colspan=\"2\"><p>$current_category</p></td></tr>\n";
            
    $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>\n";

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

        
    //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. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by duklaprague View Post
    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.
    fixed it for you...

  3. #3
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, tried just echoing $ckbox out in the body of the page, and just got out 'Array'.

    I've now added in:

    PHP Code:
    $total count($ckbox); 
    Just beneath:

    PHP Code:
    $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. #4
    SitePoint Addict
    Join Date
    Aug 2006
    Posts
    375
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    RESOLVED:

    HAVING COUNT(*) = '.$total.'


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •