|
|
Joined: Mar 2000
Posts: 528
Junior Member
|
|
Junior Member
Joined: Mar 2000
Posts: 528 |
So these things get intricate and my eyes start crossing. Hey, still not bad for a Project Manager that has no coding training! 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: ladder_recordsR_Id = autonum R_Ladder = INT (this is a digit that represents what ladder this ties to) R_Win = INT (this is the USER NUMBER of the winner) R_Loss = INT (this is the USER NUMBER of the loser) R_Type = CHAR (either 'W' or 'F' for win or forfeit) R_Date = datestamp field. So thing of the R_Win and R_Loss field as the same exact User numbers we have with Threads (ie U_Number). ladder_membersM_Id = autonum M_Name = User name input by them .....rest should not matter 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. In the below example, $mid is my MEMBER ID variable called prior to this select query. $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 />}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? Thanks.
|
|
|
|
|
Joined: Oct 2003
Posts: 2,305
Old Hand
|
|
Old Hand
Joined: Oct 2003
Posts: 2,305 |
SELECT t1.U_Username, t2.R_Id,t2.R_Win,t2.R_Loss,t2.R_Type,t2.R_Date
FROM w3t_Users AS t1
LEFT JOIN dl_Records AS t2 ON t1.U_Number = t2.R_Win
LEFT JOIN dl_Records AS t3 ON t1.U_Number = t2.R_Loss
WHERE t1.U_Username='$mid'
ORDER BY t2.R_Date
providing
R_Id,R_Win,R_Loss,R_Type,R_Date are all in dl_Records
should work but without working with an actual database mileage may vary
|
|
|
|
|
Joined: Oct 2003
Posts: 2,305
Old Hand
|
|
Old Hand
Joined: Oct 2003
Posts: 2,305 |
now that would list every member in the user table say you wanna refine it and just show users with a win or loss..
SELECT t1.U_Username, t2.R_Id,t2.R_Win,t2.R_Loss,t2.R_Type,t2.R_Date
FROM w3t_Users AS t1
LEFT JOIN dl_Records AS t2 ON t1.U_Number = t2.R_Win
LEFT JOIN dl_Records AS t3 ON t1.U_Number = t2.R_Loss
WHERE t1.U_Username='$mid' && (t2.R_Win != '' || t2.R_Loss != '')
ORDER BY t2.R_Date
that would just display those users with something in win or loss...
|
|
|
|
|
Joined: Mar 2000
Posts: 528
Junior Member
|
|
Junior Member
Joined: Mar 2000
Posts: 528 |
Thanks scroungr! I am getting much closer, and using your arcade code as a textbook, LOL. $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 /> }This outputs: Statistics ID - DATE - - - UID / UID - LOSER - TYPE 17 - 03/14/2005 - 1/8 - Win over Medar (W) 20 - 03/14/2005 - 1/8 - Win over Medar (W) 22 - 03/14/2005 - 1/3 - Win over Medar (W) 24 - 03/14/2005 - 1/7 - Win over Medar (W) 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). 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. Still messing with it...but in theory it should show up as this: Statistics ID - DATE - - - UID / UID - OTHER USER (TYPE) 17 - 03/14/2005 - 1/8 - Win over Joe (W) 19 - 03/14/2005 - 8/1 - Loss to Joe (W) 20 - 03/14/2005 - 1/8 - Win over Joe (W) 22 - 03/14/2005 - 1/3 - Win over Bob (W) 23 - 03/14/2005 - 3/1 - Loss to Bob (W) 24 - 03/14/2005 - 1/7 - Win over Fred (W) 25 - 03/14/2005 - 2/1 - Loss to Sue (W) 26 - 03/14/2005 - 9/1 - Loss to Kate (W)
|
|
|
|
|
Joined: Mar 2000
Posts: 528
Junior Member
|
|
Junior Member
Joined: Mar 2000
Posts: 528 |
Not that anyone cares to 'see' this per se...but here is the VERY simple'd up view of it (I have not prettied it up yet). http://www.bladekeep.com/ladder/Basically it is a challenge ladder I can use in any games we play that allows people to receive a challenge notification in a PM, accept it within 48 hours or forfeit, and then post results within 48 hours of accepting. If the lower-ranked challenger wins, he swaps places with the higher-ranked winner. A challenger can only challenge up to 3 ranks above him. But if he loses, he drops one notch. The part I am working on above is the 'myladder' page that will show your record against all opponents, it is pretty much the last piece I need to figure out!
|
|
|
|
|
Joined: Oct 2003
Posts: 2,305
Old Hand
|
|
Old Hand
Joined: Oct 2003
Posts: 2,305 |
cause I am guessing $mid is your current number if you want all then leave that WHERE t1.U_Number='$mid' out
|
|
|
|
|
Joined: Mar 2000
Posts: 528
Junior Member
|
|
Junior Member
Joined: Mar 2000
Posts: 528 |
Tried that, and yep, got all records for everyone. The $mid stands for Member_Id...which I am trying to have that query show all statistics for me (or whoever is looking).
Sort of a character sheet with past matches listed...
I just wiped all data on the ladder, going to have some of my members start banging it and get some better test data in there. Using that I can hopefully get this displaying correctly.
If anyone is bored...Threadsdev / threadsdev
|
|
|
|
|
Joined: Sep 2003
Posts: 803
Coder
|
|
Coder
Joined: Sep 2003
Posts: 803 |
Hey Medar, Is this something you are going to release? (or want to release)
I was tring to get something like this started with threads but never found anyone interested that had the know how. I had someone here (forgot who it was. its been awhile) but never heard anything.
This is a great addition to a gaming forum. Is there an option to add other game types like halo and starcraft?
|
|
|
|
|
Joined: Oct 2003
Posts: 2,305
Old Hand
|
|
Old Hand
Joined: Oct 2003
Posts: 2,305 |
yeah I had about 99.9% completed but never had a chance to fix the brackets
|
|
|
|
|
Joined: Sep 2003
Posts: 803
Coder
|
|
Coder
Joined: Sep 2003
Posts: 803 |
snif, snif. I still lub you  lol
|
|
|
|
|
Joined: Mar 2000
Posts: 528
Junior Member
|
|
Junior Member
Joined: Mar 2000
Posts: 528 |
I can guarantee that Scroungr's is a hell of a lot more polished than mine...as I basically can hack out a few things here and there, but have no conventional PHP training.
Simply put, sometimes my stuff works, but it is never optimized and certainly not pretty on the inside.
But sure...I can drop the code out here, there are a ton of people that could make it better. BUT - there are no brackets with this, it is a classic ladder-style setup that we used to use in an old game called Neverwinter Nights back in the early 90's.
|
|
|
|
|
Joined: Sep 2003
Posts: 803
Coder
|
|
Coder
Joined: Sep 2003
Posts: 803 |
Do you know what a fully intergrated Tournament or player vs player mod would g for? I will prolly never be able to afford photopost and if there was an intergrated tournament mod for threads, IM positive i could not afford what it would be worth.
|
|
|
Donate to UBBDev today to help aid in Operational, Server and Script Maintenance, and Development costs.
Please also see our parent organization VNC Web Services if you're in the need of a new UBB.threads Install or Upgrade, Site/Server Migrations, or Security and Coding Services.
|
|
|
Posts: 5,833
Joined: January 2000
|
|
|
Forums63
Topics37,573
Posts293,925
Members13,849
| |
Most Online5,166
Sep 15th, 2019
|
|
Currently Online
Topics Created
Posts Made
Users Online
Birthdays
|
|
|
|
