jmcall10

Ok I think I have sorted this out. I have posted a new topic in the php forum to see if it is the most efficient way. For those that were tracking this thread you can find the above mentioned post at: http://www.phpfreaks.com/forums/index.php/topic,216877.0.html Thanks for all your help guys

Hi, I have 3 tables: tbl_people (pid, name); pidname 1Tom 2Dick 3Harry 4Roger 5Victor 6Daffy 7Mickey 8Morse 9Pete 10Dave tbl_events (eid, date); eiddate 12008-05-15 22008-05-16 32008-05-17 42008-05-18 52008-05-19 62008-05-20 72008-05-21 82008-05-22 92008-05-23 102008-05-24 tbl_people_events (peid, pid, eid); peidpideid 111 121 131 142 152 162 173 183 194 1105 196 187 178 a also have the following code: <table border="1" align="center"> <tr> <td></td> <?PHP $events = mysql_db_query($database, "SELECT * FROM tbl_events ORDER BY date asc") or die ("$DatabaseError"); $i=1; while ( $qry1 = mysql_fetch_array($events)) { echo "<td>$qry1[date]</td>"; ?> </tr> <tr> <?PHP $players = mysql_db_query($database, "SELECT * FROM tbl_people") or die ("$DatabaseError"); while ( $qry2 = mysql_fetch_array($players)) { echo "<td>$qry2[name]</td>"; $events2 = mysql_db_query($database, "SELECT * FROM tbl_events ORDER BY date asc") or die ("$DatabaseError"); while ( $qry3 = mysql_fetch_array($events2)) { // find if player has played for a match $playerPlayed = "False"; $people_events = mysql_db_query($database, "SELECT * FROM tbl_people_events where eid=$qry3[eid]") or die ("$DatabaseError"); while ( $qry4 = mysql_fetch_array($people_events)) { if($qry4[pid] == $qry2[pid]) { $playerPlayed = "True"; break; } else { $playerPlayed = "False"; } } if($playerPlayed == "True"){ echo "<td align=\"center\">Y</td>"; }else{ echo "<td align=\"center\">N</td>"; } } echo "</tr>"; } ?> </table> This works. However I want to know if this is the best way to code this as I am rather rotten at php and mysql Please can someone test and advise. Thanks in advance jmcall10

lol and that is where I am lost

hmm your on the verge of losing me I think These dates are in a table called tbl_events. So the above code loops through and echo's each date in that table. Only dates within this table are dates that an event will be on

yea I have dynamically created a table like: <table border="1" align="center"> <tr> <td></td> <?PHP $events = mysql_db_query($database, "SELECT * FROM tbl_events ORDER BY date asc") or die ("$DatabaseError"); while ( $qry1 = mysql_fetch_array($events)) { echo "<td>$qry1[date]</td>"; } ?> </tr>

I am really struggling lol I use php, I have created a table and the first thing I have done is made a row with all the dates. Now I need to generate the grid

well to be perfectly honest I am really rotten at sql so when it comes to joining tables I am completely lost. So I guess I have nothing. I tried using the above qwuery and couldnt get it to execute. I dont have enough knowledge or experience to edit it. Could you please help me on this one?

Can anyone help me get the corect query? Thanks in advance

Hmm I cant get this to work Im so struggling lol

lol, no this is not homework (im 26) I want to track who has attended events and how many in total they have attended. Do you know the query that would do this?

I have 3 tables: tbl_people (pid, name); tbl_events (eid, date); tbl_people_events (peid, pid, eid); so, tbl_people stores peoples names; tbl_events stores event dates; tbl_people_events stores who attended what event; I want to display a grid like effect with a 'Y' if someone has attended the event and a 'N' if they havnt (pls see attachement) Does this make sense? [attachment deleted by admin]

I see where you are going with this. I reckon I will have to see my old lecturer on how this database should be built. Thanks for all your help

ok i think I follow you my problem is I am useless at mysql queries My "score" table looks something like this: id playerName tournamentid points dateadded 1 Andy 130908130403 3 13 September 2008 2 Barry 130908130403 3 13 September 2008 3 Robert 130908130403 3 13 September 2008 4 David 130908130403 3 13 September 2008 5 Eddie 130908130403 3 13 September 2008 6 George 130908130403 0 13 September 2008 7 Jonny 130908130403 0 13 September 2008 8 Mark 130908130403 0 13 September 2008 9 Paul 130908130403 0 13 September 2008 10 Steve 130908130403 0 13 September 2008 11 Andy 140908120610 3 14 September 2008 12 Barry 140908120610 3 14 September 2008 13 Robert 140908120610 3 14 September 2008 14 David 140908145343 3 14 September 2008 15 Eddie 140908145343 3 14 September 2008 16 George 140908145343 0 14 September 2008 17 Jonny 140908145343 0 14 September 2008 18 Mark 140908145343 0 14 September 2008 19 Chris 140908145343 0 14 September 2008 20 Joe 140908145343 0 14 September 2008 So how do I code/query that?

that is the question I guess. What is a fair way to rank these players? and how would you go about it? im stuck in a moral dilema

Ah thats not so simple For example, if one player plays 10 games and wins 9 and another plays 8 and wins 8. The person who has played 8 and won 8 wont have as many points as the other. Even tho they have won 100% of their games. Does that make sense? Im trying to make the league ladder show a fair representation of how people are doing.

Hi people, Here is one to think about, as my inferior brain cant get the maths or the query right.... Basically I am building a database for a five aside football match that my friends and I play each week. So each week five people play five other people. However the teams are never the same and the ten people are never the same. I am trying to come up with a way to log each week the results of the games and display a ladder of all the people. My problem is coming up with a fair solution. You cant go on win percentage alone because if one person only ever plays one game and wins then they would have a 100% win rate. Clearly this would be wrong. Any suggestions of the tables and sql involved would be much appreciated. Thanks in advance jmcall10

I have managed to sort out the how many times people have played column. Can someone please assist with a query that will be able to show how many times they have won. To break the tables down for you. Basically there is the score table which has the following fields: id, playerid, tournamentid, points, dateadded, So a player can play in many tournaments and will get points. They may win a tournament therefore would have the most points allocated to them under that tournament. So what I guess I need the query to do is work out how many times someone has been allocated the most points. thanks in advance jmcall10

Hi, I have the following: $rows = $sql->execute ( "SELECT playerid,SUM(points) AS points FROM ".$score_table." GROUP BY playerid ORDER BY points DESC", SQL_RETURN_ASSOC ) or die ("$DatabaseError"); echo "<table><tr><td>Pos.</td><td>Player</td><td>Played</td><td>Won</td><td>Points</td></tr>"; $num = sizeof ( $rows ); for ( $i = 0; $i < $num; ++$i ) { $points = $rows [ $i ] [ "points" ]; $playerid = $rows [ $i ] [ "playerid" ]; $pos = $i + 1; echo "<tr> <td>$pos</td> <td>$playerid"</td> <td align=\"center\"></td> <td align=\"center\"></td> <td align=\"center\">$points</td> </tr>"; Now my points table looks like this: id, playerid, tournamentid, points, dateadded As I hope you can see I ahve added two column, one us "Played", and the other is "Won" At the moment the above query sorts out who has the most points and orders them accordingly. What I want to add in is the details of these two columns. Could someone please assist Thanks in advance jmcall10

lol I mean something that auto creates databases and the relevant forms

Hi, I am currently looking for software that can build php/mysql driven databases. I have stumbled across one called "phpmagic" I was wondering if there are any others an what peoples recommendations and thoughts would be. Thanks in advance jmcall10

You are on the right track, however as it is a five asides game the teams change every week and are not fixed. So there is no need to keep track of team points as there are no official teams. I only want to track the players points depending on if they win lose or draw. I hope this makes sense to someone somewhere Thanks again

Hi, Ok I am starting a project again as I think my database design was wrong from the start. The project is to do with five a-aside football. What I am after is to be able to store the following then refference it later on using php. For just now I am interested on how I should design the database. I.e. tables fields etc I want to store the following and be able to make calculations on the data: Players names (unlimited number of players can be stored) Game info (i.e what 10 players played a game on a certain date and which 5 won and which 5 lost, or if it was a draw) What I am wanting to be able to do is create a leage with this info and display the top players. Once I have got a good database design I will then move on to the php section and start to create the script for that. Thanks in advance jmcall10

Ok I was originally going to ask my questions in stages, however I feel it would be best if I try and give the bigger picture. I have 2 tables: players (pid, name) gameinfo (gid, name, date, wld) The "players" table stores the player id (pid) and the players name (name). The "gameinfo" table stores the gameinfo id (gid) the player name (name) the date(date) and the result (wld) <--- win, lost or drawn So an example of the players table would be: [table][tr][td]1 Bob 2frank 3Joe[/trd] 4Steve 5John 6Mick 7Pat 8Dave 9Andy 10Paul and an example of the gameinfo table would be: 1Bob2008-08-04Won 2Frank2008-08-04Won 3Joe2008-08-04Won 4Steve2008-08-04Won 5John2008-08-04Won 6Mick2008-08-04Lost 7Pat2008-08-04Lost 8Dave2008-08-04Lost 9Andy2008-08-04Lost 10Paul2008-08-04Lost 11Bob2008-08-11Lost 12Frank2008-08-11Lost 13Joe2008-08-11Lost 14Steve2008-08-11Lost 15John2008-08-11Lost 16Mick2008-08-11Won 17Pat2008-08-11Won 18Dave2008-08-11Won 19Andy2008-08-11Won 20Paul2008-08-11Won 21Bob2008-08-18Draw 22Frank2008-08-18Draw 23Joe2008-08-18Draw 24Steve2008-08-18Draw 25John2008-08-18Draw 26Mick2008-08-18Draw 27Pat2008-08-18Draw 28Dave2008-08-18Draw 29Andy2008-08-18Draw 30Paul2008-08-18Draw What I want is a query to order the gameinfo data so that it shows who has the highest win rate. So not who has won the most games, but who has the highest win rate percentage of the amount of times they have played. Does that make sense? It might even be that I have designed these tables wrong from the start. Any assistance would be great Thanks in advance jmcall10

thank you guys for your response If I develope any apps i will use the <php Thanks Can now close thread

Hi all, I was wondering if someone could answer this question for me? I recently installed a small php app. It would not work until I changed <? into <?php I thot it was the same thing? Can someone tell me why this is? Thanks in advance jmcall10