Jump to content

Archived

This topic is now archived and is closed to further replies.

doodlebug

Sorting query output

Recommended Posts

Hi Guys,

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

[code=php:0]
$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>");
}
}
[/code]

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

Share this post


Link to post
Share on other sites
Why can't you do this all in a single pass?

Share this post


Link to post
Share on other sites
[quote]Why can't you do this all in a single pass?[/quote]

'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. ;)

Share this post


Link to post
Share on other sites
It looks to me like you want...

[code]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[/code]

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)

Share this post


Link to post
Share on other sites
That's basically the idea, though I personally prefer an explicit ON clause, and using GROUP BY instead of DISTINCT.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.