Help with complex query involving a Count

Databases
1

Hope someone can help with this.

The idea currently is that I have a table for Candidates, and a table of Profiles, as well as a linking table CandidateProfiles, with just the tow ID fields.

The way it works is that when a new Candidate is added, various Profiles can be added to each one by checking a series of checkboxes, which are displayed using a loop.

I then have a search page where the same profiles can be searched by checking the boxes for the relevant profiles.

This then returns a results page, listing any matching candidates, which counts the matching profiles, and lists them in order of matching profiles.

eg

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

etc

The query that does that looks like this:


SELECT *, Count(*) As rank FROM Candidates, CandidateProfiles WHERE Candidates.CandidateID = 

CandidateProfiles.CandidateID AND CandidateProfiles.ProfileID IN(' . implode(',', $ckbox).') GROUP BY 

Candidates.CandidateID ORDER BY rank DESC, Candidates.CandidateID

My question is this:

How would I change that query so that it just returned the Candidates that matched ALL rather than ANY profiles?

i.e. the same search as above to just return:

eg

Candidate…Matches
Candidate 1…3
Candidate 2…3

Hope that makes sense, and any help much appreciated.

2 
SELECT Candidates.*
     , COUNT(*) AS rank 
  FROM CandidateProfiles
INNER
  JOIN Candidates
    ON Candidates.CandidateID = CandidateProfiles.CandidateID 
 WHERE CandidateProfiles.ProfileID 
       IN(' . implode(',', $ckbox).') [COLOR="#B22222"]-- this number of profiles[/COLOR]
GROUP 
    BY Candidates.CandidateID 
HAVING COUNT(*) = 3 [COLOR="#B22222"]-- this number[/COLOR]
ORDER 
    BY Candidates.CandidateID

p.s. just for the sake of interest, could you do this please –


SHOW CREATE TABLE CandidateProfiles
3

Thank you - that certainly works, although it wouldn’t necessarily always be 3 profiles that are searched on.

Ideally it would return candidates that matched all of the profiles checked, rather than any of them.

With the SHOW CREATE TABLE CandidateProfiles I’m not sure what you are after - I entered it in the SQL tab in phpMyAdmin, and it just showed:

Table…Create Table
CandidateProfiles…CREATE TABLE ‘CandidateProfiles’ (‘CandidateID’…

4

yes, that is in fact the way it works

all you have to do is count the number of elements in your “implode” (php surely has a function for the number of items in an array, right?), and stick that number in the HAVING clause

basically, i wanted everything after the dot-dot-dot, i.e. the entire CREATE TABLE statement down to the closing semi-colon

5

OK, will have a rummage in the PHP, although arrays is where I start to feel a bit out of my depth, and it was quite a few years ago I got as far as I did with this project.

Found the rest of that CREATE TABLE text in the print view:

CREATE TABLE CandidateProfiles (
CandidateID int(11) NOT NULL,
ProfileID int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

6

you are missing two indexes

please run these (in the SQL tab of phpmyadmin, if that’s what you’re using) –

ALTER TABLE CandidateProfiles
ADD PRIMARY KEY ( CandidateID , ProfileID )

ALTER TABLE CandidateProfiles
ADD INDEX ( ProfileID , CandidateID )
7

OK, it let me add the index OK, but when I added the PK, I got the error:

#1062 - Duplicate entry ‘620-1’ for key ‘PRIMARY’

8

OK - looking at that I can see why that is - there are candidates with duplicate profiles in that table, which there obviously shouldn’t be.

9

ain’t PKs wonderful, eh :smiley:

10

Absolutely - thanks for going down this avenue with me - I am still learning all this stuff. Not sure why, but I may have had in my head I just needed the integers to match the PKs of the main tables.

Anyway - I’ve been through that, and deleted any duplicates, and run the PK line again.

So now the SHOW CREATE TABLE CandidateProfiles looks like:

CREATE TABLE CandidateProfiles (
CandidateID int(11) NOT NULL,
ProfileID int(11) NOT NULL,
PRIMARY KEY (CandidateID,ProfileID),
KEY ProfileID (ProfileID,CandidateID)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Still not sure about outputting that count value instead of the hard coded 3 though…

11

Have been trying variations of things like:

Ok, am trying variations of things like:

HAVING COUNT(*) = \“echo $ckbox\”

HAVING COUNT(*) = \“echo(’ . implode(‘,’, $ckbox).')\”

but no joy - is this getting close?

12

sorry, i have no idea, i don’t do php

but yeah, the idea is, if you want a candidate that matches a list of profiles, and you stuff that list of profiles into the SQL with php, then the HAVING clause should be stuffed with the number items in that list

13

OK, tried just echoing that 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;\” ?