SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with SQL query

    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. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,139
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    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.

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

    Code SQL:
    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
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you - that seems to work OK in phpMyAdmin.

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

    I'm using:

    Code:
    //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>\n";
    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. #4
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,000
    Mentioned
    101 Post(s)
    Tagged
    0 Thread(s)
    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.
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  6. #6
    SitePoint Enthusiast
    Join Date
    Dec 2011
    Posts
    60
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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:


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


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
  •