www.webdeveloper.com
Results 1 to 7 of 7

Thread: MySQL query help needed

  1. #1
    Join Date
    Jan 2006
    Posts
    14

    MySQL query help needed

    Here is my current query within a php script that executes perfectly fine:

    PHP Code:
    <?
    $getdade 
    date("Y-m-d");
    $query = @mysql_query("SELECT ID,TITLE,URL,MEMBER FROM sites WHERE ACTIVE='1' AND APPROVED='1' AND HOURSTOPPER != '$hour' AND AVLCREDITS='1' AND MEMBER != '$surfid' AND ID != '$ds'");
    $num = @mysql_num_rows($query);

    while (
    $i $num) {
        
        
    $e = @mysql_fetch_array($query);

        
    $query2 = @mysql_query("SELECT ID FROM track WHERE OWNER=$e[MEMBER] AND MEMBER='$surfid' AND DATE='$getdate'");
        
    $n = @mysql_fetch_array($query2);
        
        if (!
    $n[ID]) {
            echo 
    "$e[ID],$e[TITLE],$e[URL],$e[MEMBER]<br>";
        }

    $i++;
    }
    ?>
    There has to be a more effiecient way to execute this as just 1 query and bypass using this type of php script routine to pull the rows out of the database that I want. Anyone know what the query would look like to combine the above process into one mysql query?

    And one more thing, I want to rows that it finds to be distinct for the MEMBER column in the SITES table. So that the rows do not contain more than one row that contains the same "MEMBER" number. Not sure if you would use distinct or not...

  2. #2
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    you could easily use a join. however, are you trying to just find the members that don't have tracks? is that the logic that this is trying to follow?

  3. #3
    Join Date
    Jan 2006
    Location
    London
    Posts
    50
    You can run a select across multiple tables, the only thing i'm confused about is that in the first query you search MEMBER != '$surf_id' and in the second you search for MEMBER = '$surf_id'. I am assuing that MEMBER is independant in each data. In which case you can link the tables on MEMBER (1st query) with OWNER (2nd querty) like this.

    SELECT
    sites.ID AS SiteID, sites.TITLE, sites.URL
    tracks.ID AS TrackID
    FROM sites, tracks
    WHERE 1
    AND sites.MEMBER != '$surf_id'
    AND sites.AVLCREDITS = '1'
    AND sites.HOURSTOPPER = '$hour'
    AND sites.APPROVED = '1'
    AND sites.ACTIVE = '1'
    AND tracks.OWNER = sites.MEMBER
    AND tracks.MEMBER = '$surf_id'
    AND tracks.ID = '$ds'
    AND tracks.DATE = '$getdate'
    GROUP BY (sites.MEMBER);

    The most important line here is 'AND tracks.OWNER = sites.MEMBER ' as it links the two tables.

    The 'GROUP BY sites.MEMBER' will producs a recordset which has been grouped by that field, therefore removing duplicates. Alternativly you could put 'SELECT DISITNCT(sites.MEMBER)' as long as it is the first element on the selection list.

    This will return a single recordset as if it was all drawn fromthe same table. I have refernenced the ID columns as SiteID and TrackID to avoid amibiguity when retrieving the data.
    Last edited by Brooksie155; 01-16-2006 at 06:37 AM.

  4. #4
    Join Date
    Jan 2006
    Posts
    14
    Hey thanks for the help, but its not working when I changed it to my actual titles of the data, here is what I have...

    SELECT sites.ID, sites.TITLE, sites.URL, sites.MEMBER FROM sites WHERE sites.MEMBER != '$i[ID]' AND sites.MEMBER != '$ls' AND sites.AVLCREDITS = '1' AND sites.HOURSTOPPER != '$hour' AND sites.APPROVED = '1' AND sites.ACTIVE = '1' AND track.OWNER = sites.MEMBER AND track.MEMBER != '$i[ID]' AND track.DATE != '$gdade' GROUP BY (sites.MEMBER) ORDER BY RAND() LIMIT 20

    It will work perfectly fine, but some rows do not have a connecting line in the track table, so if I remove this...

    AND track.OWNER = sites.MEMBER AND track.MEMBER != '$i[ID]' AND track.DATE != '$gdade'

    it works fine, but does not check the entries in the track table. Is there any way to do something like an if statement in a query? so that if it contains a link between tables of "track.OWNER = sites.MEMBER" it will then connect them and look at things, and if not, it wont....

  5. #5
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Code:
    SELECT sites.ID, sites.TITLE, sites.URL, sites.MEMBER FROM sites WHERE sites.MEMBER != '$i[ID]' AND sites.MEMBER != '$ls' AND sites.AVLCREDITS = '1' AND sites.HOURSTOPPER != '$hour' AND sites.APPROVED = '1' AND sites.ACTIVE = '1' AND track.OWNER = sites.MEMBER AND track.MEMBER != '$i[ID]' AND track.DATE != '$gdade' GROUP BY (sites.MEMBER) ORDER BY RAND() LIMIT 20
    you can't reference track.col_name if you're not including it in your select statement.

  6. #6
    Join Date
    Jan 2006
    Posts
    14
    Oops I knew that. (I've done this stuff over 100 times before lol)

    Ok, here is my query and it still isnt working to how I want it to be, my main question is, sometimes there will not be a joining row in the "track" table to join up with the "sites" table. Is there a way to just say if there is then... kinda thing in a mysql query?


    SELECT sites.ID, sites.TITLE, sites.URL, sites.MEMBER FROM sites,track WHERE sites.MEMBER != '$i[ID]' AND sites.MEMBER != '$ls' AND sites.AVLCREDITS = '1' AND sites.HOURSTOPPER != '$hour' AND sites.APPROVED = '1' AND sites.ACTIVE = '1' AND track.OWNER = sites.MEMBER AND track.MEMBER != '$i[ID]' AND track.DATE != '$gdade' GROUP BY (sites.MEMBER) ORDER BY RAND() LIMIT 20


    Because in a certain case it will not work, but if I remove the...


    AND track.OWNER = sites.MEMBER AND track.MEMBER != '$i[ID]' AND track.DATE != '$gdade'


    it will work due the fact that there was no joining row in the track table.

    Any input is greatly appreciated...

  7. #7
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    I would go with a solution like in this thread actually
    http://www.webdeveloper.com/forum/sh...ad.php?t=90634
    he's using a left join and only showing null id's from the second table (IE his only shows the results that aren't in the other table)
    you're doing something very similar. you should be able to adapt it.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles