 |
 |
 |
 |
#284298 - 03/15/05 09:04 AM
Help with a SELECT Query that is beyond me
|
Junior Member
Registered: 03/19/00
Posts: 571
|
So these things get intricate and my eyes start crossing. Hey, still not bad for a Project Manager that has no coding training!<br /><br />So I am coding sort of a "challenge ladder", and everything works fine, but I am to a point where I want to display all past records. Here is the layout of my records table:<br /><br /> ladder_records<br />R_Id = autonum<br />R_Ladder = INT (this is a digit that represents what ladder this ties to)<br />R_Win = INT (this is the USER NUMBER of the winner)<br />R_Loss = INT (this is the USER NUMBER of the loser)<br />R_Type = CHAR (either 'W' or 'F' for win or forfeit)<br />R_Date = datestamp field.<br /><br />So thing of the R_Win and R_Loss field as the same exact User numbers we have with Threads (ie U_Number).<br /><br /> ladder_members<br />M_Id = autonum<br />M_Name = User name input by them<br />.....rest should not matter<br /><br />So......I am trying to write a query that will pull all Wins AND Losses of a given member, order it by DATE, but display the User NAMES instead of their ID's. I am getting expectedly goofy results.<br /><br />In the below example, $mid is my MEMBER ID variable called prior to this select query.<br /><br /> $result = mysql_query("SELECT R_Id,R_Win,R_Loss,R_Type,R_Date,M_Name <br /> FROM dl_Members,dl_Records <br /> WHERE R_Win=$mid OR R_Loss=$mid AND M_Id=$mid <br /> ORDER BY R_Date");<br /> if ($myrow = mysql_fetch_array($result)) {<br /> <br /> do {<br /> <br /> $date = date("m/d/Y", $myrow["R_Date"]); <br /> if ($myrow["R_Win"] == $mid) {<br /> printf("<td>%s - %s - Win over %s (%s)</td></tr>\n", $myrow["R_Id"], $date, $myrow["M_Name"], $myrow["R_Type"]);<br /> }<br /> elseif ($myrow["R_Loss"] == $mid) {<br /> printf("<td>%s - %s - Lost to %s (%s)</td></tr>\n", $myrow["R_Id"], $date, $myrow["M_Name"], $myrow["R_Type"]);<br /> }<br /> else {}<br /> }<br /> <br /> while ($myrow = mysql_fetch_array($result));<br />}<br /><br />This obviously does not work, and sadly I have tried about 10 different variations based off stuff I am trying to glean off the internet. Anyone know off the top of their head the PROPER way to write this select query?<br /><br />Thanks.
|
|
Top
|
|
|
|
 |
 |
 |
 |
|
|
|
|
|
#284299 - 03/15/05 09:22 AM
Re: Help with a SELECT Query that is beyond me
[Re: Intel -*RW*-]
|
Old Hand
Registered: 10/17/03
Posts: 2409
Loc: Richmond, VA
|
SELECT t1.U_Username, t2.R_Id,t2.R_Win,t2.R_Loss,t2.R_Type,t2.R_Date<br />FROM w3t_Users AS t1<br />LEFT JOIN dl_Records AS t2 ON t1.U_Number = t2.R_Win<br />LEFT JOIN dl_Records AS t3 ON t1.U_Number = t2.R_Loss<br />WHERE t1.U_Username='$mid'<br />ORDER BY t2.R_Date<br /><br />providing <br />R_Id,R_Win,R_Loss,R_Type,R_Date are all in dl_Records<br /><br />should work but without working with an actual database mileage may vary
_________________________
Couchtomatoe - www.couch-tomatoe.ccMy abilities are for hire for installs, upgrades, custom themes and custom modifications.
|
|
Top
|
|
|
|
|
|
|
|
|
|
|
|
|
#284301 - 03/15/05 10:59 AM
Re: Help with a SELECT Query that is beyond me
[Re: 234234]
|
Junior Member
Registered: 03/19/00
Posts: 571
|
Thanks scroungr! I am getting much closer, and using your arcade code as a textbook, LOL.<br /><br /> $query = "<br /> SELECT t1.U_Username, t2.R_Id,t2.R_Win,t2.R_Loss,t2.R_Type,t2.R_Date<br /> FROM w3t_Users AS t1<br /> LEFT JOIN dl_Records AS t2 ON t1.M_Id = t2.R_Win<br /> LEFT JOIN dl_Records AS t3 ON t1.M_Id = t2.R_Loss<br /> WHERE t1.U_Number='$mid'<br /> ORDER BY t2.R_Date<br /> ";<br /> $sth = $dbh -> do_query($query,__LINE__,__FILE__);<br /> while (list($name,$rid, $rwin, $rloss, $rtype, $rdate) = $dbh -> fetch_array($sth)) {<br /> $date = date("m/d/Y", $rdate);<br /> echo "<tr><td>$rid - $date - $rwin/$rloss - $charname ($rtype)</td></tr>";<br /> }<br /><br />This outputs:<br /><br />Statistics <br />ID - DATE - - - UID / UID - LOSER - TYPE<br />17 - 03/14/2005 - 1/8 - Win over Medar (W) <br />20 - 03/14/2005 - 1/8 - Win over Medar (W) <br />22 - 03/14/2005 - 1/3 - Win over Medar (W) <br />24 - 03/14/2005 - 1/7 - Win over Medar (W)<br /><br />My first problem is that it is pulling MY username instead of my opponent (I am user 1, my opponents are listed above as users 8, 3, and 7).<br /><br />There should also be 8 lines total, the ones above are all of my WINS...so the losses are not being pulled in by my statement. I have 4 losses that should display in the same format.<br /><br />Still messing with it...but in theory it should show up as this:<br /><br />Statistics <br />ID - DATE - - - UID / UID - OTHER USER (TYPE)<br />17 - 03/14/2005 - 1/8 - Win over Joe (W) <br />19 - 03/14/2005 - 8/1 - Loss to Joe (W) <br />20 - 03/14/2005 - 1/8 - Win over Joe (W) <br />22 - 03/14/2005 - 1/3 - Win over Bob (W) <br />23 - 03/14/2005 - 3/1 - Loss to Bob (W) <br />24 - 03/14/2005 - 1/7 - Win over Fred (W)<br />25 - 03/14/2005 - 2/1 - Loss to Sue (W) <br />26 - 03/14/2005 - 9/1 - Loss to Kate (W)
|
|
Top
|
|
|
|
|
|
|
|
|
|
Previous Topic
Index