Jump to content

Sorting query output


doodlebug

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
Link to comment
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)
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.