Jump to content


Photo

Sorting query output


  • Please log in to reply
4 replies to this topic

#1 doodlebug

doodlebug
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 14 September 2006 - 09:31 AM

Hi Guys,

First of all, here is the query i am running:

$query = "select * from plmatchdata where lid='$lid' and matchdate>='$todaysdate' and week='$ww' order by matchdate";
$result = $dbase->query($query);
while ($l = mysql_fetch_array($result)) { 

      $matchid = $l["matchid"];
      $nextmatch = $matchid;

$querystand="SELECT userid FROM pluserlids WHERE lid='$lid' AND userid NOT IN (SELECT DISTINCT userid FROM plpredictiondata WHERE matchid='$nextmatch')";
$resultstand = mysql_query($querystand,$db); 
while ($latest_array = mysql_fetch_array($resultstand)) { 

      $user= $latest_array["userid"]; 

        $query2 = "SELECT email FROM pluserdata WHERE userid='$user'"; 
        $res2 = mysql_query($query2,$db); 
        $l2 = mysql_fetch_array($res2); 
        $name2 = $l2["email"]; 

        $query3 = "SELECT username FROM pluserdata WHERE userid='$user'"; 
        $res3 = mysql_query($query3,$db); 
        $l3 = mysql_fetch_array($res3); 
        $name3 = $l3["username"];

print("<tr><td class='TBLROW'><font class='TBLROW'>$name3</font></td><td class='TBLROW'><font class='TBLROW'>$user</font></td><td class='TBLROW'><font class='TBLROW'>$name2</font></td></tr></font>"); 
}
} 

Based on the structure of the query as it stands at the moment, it is returning the correct results. I am getting a list of userids, usernames and emails where the criteria is met. The problem i have, is that it is returning a list of names, emails etc.. for every criteria met in the first part of the query aswell, so i am getting duplicates.

What i would like to know is how to structure the query so it will only return a username etc.. once if it comes up anywhere in the second part of the query.......but i'm stuck  :-(

Hope some one you knowledgable chaps can help me out :-)

Best regards
Doodlebug

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 14 September 2006 - 02:56 PM

Why can't you do this all in a single pass?
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 doodlebug

doodlebug
  • Members
  • PipPip
  • Member
  • 10 posts

Posted 15 September 2006 - 07:54 AM

Why can't you do this all in a single pass?


'cos i don't know how to. I don't fully understand joins  in queries yet. I am looking at them, but i haven't got my head around them. Any help you could give would be greatly appreciated. ;)


#4 btherl

btherl
  • Staff Alumni
  • Advanced Member
  • 3,893 posts
  • LocationAustralia

Posted 15 September 2006 - 08:33 AM

It looks to me like you want...

SELECT DISTINCT username, userid, email
    FROM plmatchdata
    JOIN plpredictiondata USING (matchid, lid)
    JOIN pluserlids USING (userid)
    JOIN pluserdata USING (userid)
    WHERE lid = '$lid'
    AND matchdate >= '$todaysdate'
    AND week = '$ww'
    ORDER BY matchdate

Essentially, the USING (userid) means "Join these tables where userid matches).

First you join plmatchdata and plpredictiondata where the matchid column matches.
Then you join with pluserlids and pluserdata where the userid matches.

The "DISTINCT" removes duplicates, if there are any.

If that query doesn't work, post the error message here (and if possible, post the schema for your tables.  I've had to guess some parts of the query because I don't know what columns are where, and am not 100% sure which ones you want to match)

#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 15 September 2006 - 12:40 PM

That's basically the idea, though I personally prefer an explicit ON clause, and using GROUP BY instead of DISTINCT.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users