Help with SQL query

Databases
1

Think this one is beyond my knowledge of SQL…

The table structure is:

Table - revision

RevisionID (PK)
Project_Name
etc

Table - profiles

ProfileID (PK)
Profile
etc

Table - revisionProfiles

RevisionID
ProfileID

Sample records:

Table - revision

1 Transport Interchange
2 Railway Scheme
3 Light Railway Scheme
4 Metro Scheme

Table - profiles

1 Railways
2 Light Railways
3 Metros

Table - revisionProfiles

1 1
1 2
1 3
2 2
3 3
4 4

Ie Transport Interchange is associated with Railways, Light Railways and Metros

and Railways Scheme, Light Railway Scheme and Metro are only associated with Railways, Light Railways and Metros respectively.

So…

The query I’m trying for is to return records from table revision where that record has a particular set of profiles.

Eg where ProfileID = 1 AND 2 AND 3 it would return RevisionID = 1, ie Transport Interchange.

I hope that makes sense - any help would be much appreciated.

Thank you.

2

If those are the sole requirements having can be used to filter out all revisions without the 3 or however many profiles. You would just adjust the COUNT() = ? based on number of revisions that should be matched. Of course this assumes that the revisionProfiles table (RevisionID,ProfileID) make up a unique or primary key.


SELECT
     r.RevisionID
  FROM
     revision r
 INNER
  JOIN
     revisionProfiles rp
    ON
     r.RevisionID = rp.RevisionID
 INNER
  JOIN
     profiles p
    ON
     rp.ProfileID = p.ProfileID
 WHERE
     p.ProfileID IN (1,2,3)
 GROUP
    BY
     r.RevisionID
HAVING
     COUNT(*) = 3

You can probably get a better understanding of how this works if you remove the groupby and having clause. If you were to do that you would notice that revisions are repeated based on the number of profiles they are associated with. So you can than use to group by to aggregate a count than filter using having by counting the number of duplicates.

I should also point out that the last join could be eliminated since the second table exposes that data. Though if you needed to filter by data other than the primary key than the third table (profiles) would be necessary.


SELECT
     r.RevisionID
  FROM
     revision r
 INNER
  JOIN
     revisionProfiles rp
    ON
     r.RevisionID = rp.RevisionID
 WHERE
     rp.ProfileID IN (1,2,3)
 GROUP
    BY
     r.RevisionID
HAVING
     COUNT(*) = 3
3

Thank you - that seems to work OK in phpMyAdmin.

The practical use of this is in a search form using checkboxes.

I’m using:


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

To display the checkboxes, so in place of (1, 2, 3) above it needs to be the ProfileIDs passed from that and stored as ‘ckbox’.

Which is where I really get stumped unfortunately.

4

On the page itself, its using a search extension by Web Assist. (Sorry, I know that’s probably bad form with serious coders, but my background is mostly design.)

The search code generates a WHERE clause, and after playing around with it, its not going to fly using HAVING.

The problem, as far as I can tell, is that there are multiple records in the revisionProfiles table, which somehow need to be grouped together.

Another thing that might be an option is creating a temp table.

I got as far as creating a table, and inserting the string of Profile IDs using:

<?php
mysql_select_db($database_connCollins, $connCollins);
mysql_query(“CREATE TABLE profilesTemp ( profileIDS VARCHAR(100), project VARCHAR(250))”);
$profilevalues = $_GET[‘ckbox’];
mysql_query(“INSERT INTO profilesTemp VALUES(‘$profilevalues’);”);
?>

‘ckbox’ is the array that stores the profile IDs on the search page.

So for example, if profiles with IDs 17, 18 and 19 were checked on the search page, that would create the record:

17, 18, 19

in that profilesTemp table.

But I’m a bit stuck with what to do next to tie it back to the revision table.

5

Are you aware that the mysql_* extension has been deprecated as of version 5.5.x of PHP? You should be migrating over to either the mysqli_* extension or PDO. You should use prepared statements to prevent attack via SQL Injection.

If you’re not already doing so, any user submitted data submitted in any way should be considered dangerous until it has been sanitized.

6

Yeah - its been in the back of my mind for a while. Unfortunately my background is design, so for my sins use Dreamweaver, and Adobe haven’t updated their database server behaviour stuff to either. How much is involved in migrating this sort of stuff? At the very least I assume the connection file needs to be rewritten.

So at the moment its something like:


<?php
# FileName="Connection_php_mysql.htm"
# Type="MYSQL"
# HTTP="true"
$hostname_connectionName = "localhost";
$database_connectionName = "database_name";
$username_connectionName = "username";
$password_connectionName = "password";
$connSafari = mysql_pconnect($hostname_connectionName $username_connectionName, $password_connectionName) or trigger_error(mysql_error(),E_USER_ERROR); 
?>

Should it be rewritten as it appears here:

http://php.net/manual/en/mysqli.select-db.php