Jump to content

PHP_Idiot

Members
 View Profile  See their activity

  • Posts

    131

  • Joined

  • Last visited

 Content Type 

Everything posted by PHP_Idiot

  1. PHP_Idiot
    Ok now I know I'm being thick here, but I have the following loop running on my page and I want it to show the first 50 results from the array, which works fine. However at times there are less than 50 results in the array so it throws up errors for each of the blank lines. How do I limit it to 50 when there are more than 50 in the array, and show all without errors for when there are less than 50? for($i = 1; $i < 51; ++$i){ echo" <tr><td align=\"center\">$pos</td> <td align=\"center\">" . $arr[$i]['1'] . "</td> <td align=\"center\">" . $arr[$i]['2'] . "</td> <td align=\"center\">" . $arr[$i]['3'] . "</td> <td align=\"center\">" . $arr[$i]['4'] . "</td> <td align=\"center\">" . $arr[$i]['5'] . "</td> <td align=\"center\">" . $arr[$i]['6'] . "</td> </tr> "; $pos++; }
  2. PHP_Idiot
    Perfect that's what I saw earlier but couldn't remember the syntax. Thanks a lot
  3. PHP_Idiot
    I have a table that displays the combined results of two mysql queries after performing some additional maths calculations. The table displays players results from various venues and their relevant score. However, I need to only show the players best venue and points, not every venue they play in. Because an important part of the calculation is done in php AFTER the mysql queries have run, I need to limit the display of the array to only the unique players (by membership number). Earlier today I stumbled across some code examples of the use of DISTINCT in php script (not in mysql query) but I can't find it again now! Can anyone help? cheers
  4. PHP_Idiot
    I have a page that runs two large mysql queries and saves the results into arrays, in php I then perform a simple multiplication between the two arrays and the results is displayed in a dynamic html table. However, what I need to do is rather than display this in a table I need it saved in another array that can then be sorted in different ways and limit the table to the top 40 results. Can anyone point me in the right direction?
  5. PHP_Idiot
    Got the answer on another forum, for anyone interested it was resolved by having another WHILE loop before the one I had, where all the rows from $result2 are fetched and stored in a hash array that is indexed into by $r['Venue'] inside the WHILE loop I already had $average_by_venue = array(); while ($r2 = mysql_fetch_array($result2) ) { $average_by_venue[ $r2['VenueName'] ] = $r2['Average']; } ... ... then in the loop I have ... $GBPCPoints = $r['Venue_Points'] * $average_by_venue[$r['Venue']]; ...
  6. PHP_Idiot
    Ok So far I have the below code following the queries: $r2 = mysql_fetch_array($result2); echo <<<html <table border="1" width="480" cellpadding="1" cellspacing="1"> <tr><td align="center"><strong>Pos.</strong></td> <td align="center"><strong>First</strong></td> <td align="center"><strong>Last</strong></td> <td align="center"><strong>Venue</strong></td> <td align="center"><strong>Points</strong></td> <td align="center"><strong>Played</strong></td> <td align="center"><strong>GBPC Points</strong></td> </tr> html; //Now start the loop. $pos=1; while($r = mysql_fetch_array($result)){ //and echo each new row $GBPCPoints = $r['Venue_Points'] * $r2['Average']; echo <<<html <tr><td align="center">$pos</td> <td align="center">{$r['FirstName']}</td> <td align="center">{$r['LastName']}</td> <td align="center">{$r['Venue']}</td> <td align="center">{C}</td> <td align="center">{$r['Venue_Play_Count']}</td> <td align="center">$GBPCPoints</td> </tr> html; $pos++; } This is almost working, but it is multiplying all $r['Venue_Points'] by the first returned $r2['Average'] and not the corresponding average! Any ideas how I can get it to multiply all $r['Venue_Points'] by the $r2['Average'] of the correct venue?
  7. PHP_Idiot
    You may have spotted the obvious mistake in the title!! It's not a division I need but a multiplication, though I doubt that changes things much!
  8. PHP_Idiot
    Wow and there's me still trying to work out how to do simple division across two tables!!
  9. PHP_Idiot
    What I need to do is multiply the players Chips from the first table, by the average players from the corresponding venue in the second table. EG. Multiple Joe's Points by the GB Poker Club average, and Janes by The Pub's average. Then store the result in a variable I can use again in a later query! First (HUGE) query outputs this (for the top 40 players): Pos First Last Venue Chips Played 1 Joe Bloggs GB Poker Club 1250 4 2 Jane Smith The Pub 1025 3 The Second smaller query outputs this (for all venues): Venue Total Players Games Played Ave Players/Game The Pub 28 2 14 GB Poker CLub 24 2 12
  10. PHP_Idiot
    Hi Guru's I have two mysql queries that return a bunch of different records and figures. but I want to divide one figure from one query by another fingure from the second query. I'm sure it can't be that hard, but this is a first for me so any help would be massively appreciated! Here are the two queries: (they return exactely what I need, I'll post a follow up showing the output of each...) $top40 = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue, SubSelect.sum_points AS Venue_Points, SubSelect.count_results AS Venue_Play_Count, SubSelect3.sum_points3 AS Total_Points FROM Player, ( SELECT Player1.MembershipNo, Venue1.VenueName, SUM( Position1.Points ) AS sum_points, COUNT( Player1.MembershipNo ) AS count_results FROM Player Player1, Results Results1, Position Position1, Venue Venue1 WHERE Player1.MembershipNo = Results1.MembershipNo AND Results1.Position = Position1.Position AND Venue1.VenueID = Results1.VenueID AND Results1.Date BETWEEN '$BeginDate' AND '$EndDate' GROUP BY Player1.MembershipNo, Venue1.VenueName)SubSelect, ( SELECT Player3.MembershipNo, SUM( Position3.Points ) AS sum_points3 FROM Player Player3, Results Results3, Position Position3 WHERE Player3.MembershipNo = Results3.MembershipNo AND Results3.Position = Position3.Position AND Results3.Date BETWEEN '$BeginDate' AND '$EndDate' GROUP BY Player3.MembershipNo)SubSelect3 WHERE Player.MembershipNo = SubSelect.MembershipNo AND SubSelect.sum_points = ( SELECT MAX( SubSelect1.sum_points2 ) FROM ( SELECT Player2.MembershipNo, Venue2.VenueName, SUM( Position2.Points ) AS sum_points2 FROM Player Player2, Results Results2, Position Position2, Venue Venue2 WHERE Player2.MembershipNo = Results2.MembershipNo AND Results2.Position = Position2.Position AND Venue2.VenueID = Results2.VenueID AND Results2.Date BETWEEN '$BeginDate' AND '$EndDate' GROUP BY Player2.MembershipNo, Venue2.VenueName ) SubSelect1 WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo) AND Player.MembershipNo = SubSelect3.MembershipNo AND SubSelect.sum_points >=25 ORDER BY SubSelect.sum_points DESC LIMIT 0 , 40"; $result = mysql_query($top40) or die("Couldn't execute query because: ".mysql_error()); $AvePlayers = "SELECT Venue.VenueName, COUNT( Results.MembershipNo ) , COUNT( DISTINCT Results.Date ) , cast( coalesce( COUNT( Results.MembershipNo ) / COUNT( DISTINCT Results.Date ) ) AS decimal( 10, 1 ) ) AS 'Average' FROM Position, Player, Results, Venue WHERE Player.MembershipNo = Results.MembershipNo AND Results.Position = Position.Position AND Venue.VenueID = Results.VenueID AND Results.Date BETWEEN '$BeginDate' AND '$EndDate' GROUP BY Venue.VenueName ORDER BY Average DESC LIMIT 0 , 30"; $result2 = mysql_query($AvePlayers) or die("Couldn't execute query because: ".mysql_error());
  11. PHP_Idiot
    Don't worry folks I found a ifferent way of doing it: $result = "INSERT into Player values (\"$_POST[MembershipNo]\", \"$_POST[FirstName]\", \"$_POST[NickName]\", \"$_POST[LastName]\", \"$_POST[Town]\", \"$_POST[Email]\")";
  12. PHP_Idiot
    Hi All I have the following query but I'm struggling to get the quotes in the right order or to escape the right one to make it work! Any help would be greatly appreciated. mysql_query("INSERT INTO Player (MembershipNo, FirstName, NickName, LastName, Town, Email) VALUES(\"$_POST[MembershipNo]\", \"$_POST[FirstName]\", \"$_POST[NickName]\", \"$_POST[LastName]\"', \"$_POST[Town]\", \"$_POST[Email]\") ")
  13. PHP_Idiot
    Jibberish you hero That's exactly what I wanted thanks a lot
  14. PHP_Idiot
    I have a list box that has four options in it. When an option is selected and the 'Go' button is hit the table on the page populates with their information, but the list box reset to it show the first in the list again. Is it possible to select option 3 and then when the page has refreshed have the listbox have option 3 as it's new default value? this way the list box value reflects the contents of the newly populated table! This code include the creation of the list box and the sql query that populates the table: $query="SELECT VenueName FROM Venue ORDER BY VenueName"; $result = mysql_query ($query); echo '<form action="" method="post">'; echo "<select name='Venue'>"; // printing the list box select command while($nt=mysql_fetch_array($result)) {//Array or records stored in $nt echo "<option value=\"$nt[VenueName]\">$nt[VenueName]</option>"; /* Option values are added by looping through the array */ } echo "</select>";// Closing of list box ?> <input type="submit" value="Go" /> </form> </p> <h3><?php echo $_POST['Venue'] ?> League Positions</h3> <?php if (isset($_POST['Venue']) && !empty($_POST['Venue'])) { //mySQL queries $query = "SELECT SUM(Position.Points) , Results.Date, Player.FirstName, Player.LastName, COUNT(Results.MembershipNo) FROM Position, Player, Results, Venue WHERE Player.MembershipNo = Results.MembershipNo AND Date BETWEEN '2009-07-05' AND '2009-10-03' AND Results.Position = Position.Position AND Venue.VenueID = Results.VenueID AND Venue.VenueName = '".$_POST['Venue']."' GROUP BY Player.MembershipNo ORDER BY SUM(Position.Points) DESC"; $result=mysql_query($query) or die ("couldn't execute query");
  15. PHP_Idiot
    Hi I need to sort this query to only select records from between two dates! Anyone got any idea how to do it? //mySQL queries $query = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue, SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points FROM Player, (SELECT Player1.MembershipNo, Venue1.VenueName, SUM(Position1.Points) AS sum_points, COUNT(Player1.MembershipNo) AS count_results FROM Player Player1, Results Results1, Position Position1, Venue Venue1 WHERE Player1.MembershipNo = Results1.MembershipNo AND Results1.Position = Position1.Position AND Venue1.VenueID = Results1.VenueID GROUP BY Player1.MembershipNo, Venue1.VenueID) SubSelect, (SELECT Player3.MembershipNo, SUM(Position3.Points) AS sum_points3 FROM Player Player3, Results Results3, Position Position3 WHERE Player3.MembershipNo = Results3.MembershipNo AND Results3.Position = Position3.Position GROUP BY Player3.MembershipNo) SubSelect3 WHERE Player.MembershipNo = SubSelect.MembershipNo AND SubSelect.sum_points=(SELECT MAX(SubSelect1.sum_points2) FROM (SELECT Player2.MembershipNo, Venue2.VenueName, SUM(Position2.Points) AS sum_points2 FROM Player Player2, Results Results2, Position Position2, Venue Venue2 WHERE Player2.MembershipNo = Results2.MembershipNo AND Results2.Position = Position2.Position AND Venue2.VenueID = Results2.VenueID GROUP BY Player2.MembershipNo, Venue2.VenueID) SubSelect1 WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo) AND Player.MembershipNo = SubSelect3.MembershipNo AND SubSelect.sum_points >= 750 ORDER BY SubSelect.sum_points DESC"; The Date field is held in the Results Table by the way, just in case you needed to know! The query calculates a players total points in each venue then lists all the players and the venue names of where they score of over 750 points, if the player has more than 750 in multiple venues, it only shows the highest scoring venue. But now I need it to do this but for specific date periods, so I can show season 1 or season 2 etc.. Thanks in advance
  16. PHP_Idiot
    Any ideas anyone?
  17. PHP_Idiot
    I know this is possible because I have seen similar set ups on other sites, I've googled the life out of it and still not found anything the covers what I need. If anyone can offer any advice, or point me to any specific sites that might help I'd really appreciate it. Cheers
  18. PHP_Idiot
    Thanks for the advice from you both, unfortunately neither option works, although I think I know why. In the opening statements there is no call to the 'Results' table which is where the field 'Date' is stored. I have tried adding Results.Date to $query = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue, SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points to make it: $query = "SELECT Player.MembershipNo, Results.Date, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue, SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points But that didn't work I also tried adding it after this bit: FROM Player, (SELECT Player1.MembershipNo, again no success. I might be barking up the wrong tree (so to speak!) so if I'm way off please let me know. Thanks a lot
  19. PHP_Idiot
    I have the below uber query which I have had a lot of help putting it together. However I now need it to sort the results between two dates. I know I need to insert something like this which already works in my other queries: AND Date BETWEEN '2009-07-05' AND '2009-10-04' My Query (below) is very complex and well beyond my understanding unfortunately, I've tried various things, and still can't get it to work, can anyone please tell me where I need to add the above code in this.... $query = "SELECT Player.MembershipNo, Player.FirstName, Player.LastName, SubSelect.VenueName AS Venue, SubSelect.sum_points as Venue_Points, SubSelect.count_results as Venue_Play_Count, SubSelect3.sum_points3 as Total_Points FROM Player, (SELECT Player1.MembershipNo, Venue1.VenueName, SUM(Position1.Points) AS sum_points, COUNT(Player1.MembershipNo) AS count_results FROM Player Player1, Results Results1, Position Position1, Venue Venue1 WHERE Player1.MembershipNo = Results1.MembershipNo AND Results1.Position = Position1.Position AND Venue1.VenueID = Results1.VenueID GROUP BY Player1.MembershipNo, Venue1.VenueID) SubSelect, (SELECT Player3.MembershipNo, SUM(Position3.Points) AS sum_points3 FROM Player Player3, Results Results3, Position Position3 WHERE Player3.MembershipNo = Results3.MembershipNo AND Results3.Position = Position3.Position GROUP BY Player3.MembershipNo) SubSelect3 WHERE Player.MembershipNo = SubSelect.MembershipNo AND SubSelect.sum_points=(SELECT MAX(SubSelect1.sum_points2) FROM (SELECT Player2.MembershipNo, Venue2.VenueName, SUM(Position2.Points) AS sum_points2 FROM Player Player2, Results Results2, Position Position2, Venue Venue2 WHERE Player2.MembershipNo = Results2.MembershipNo AND Results2.Position = Position2.Position AND Venue2.VenueID = Results2.VenueID GROUP BY Player2.MembershipNo, Venue2.VenueID) SubSelect1 WHERE SubSelect1.MembershipNo = SubSelect.MembershipNo) AND Player.MembershipNo = SubSelect3.MembershipNo AND SubSelect.sum_points >= 750 ORDER BY SubSelect.sum_points DESC";
  20. PHP_Idiot
    Hi I need to use a listbox selection to repopulate a table from a seperate mysql query. I have a dynamically populated list box that works fine, I also have a series of queries to populate tables that also works fine. I have a few league tables I want people to be able to view, and rather than add a link to each page as I have now I need to move to a more dynamic way as I will be generating more and more tables! I would like to learn how I can make the selection in the list box generate new content in the table, either by modifying the queries (this is the prefered option) or by implementing a different query for each. Any help would be greatly appreciated. Cheers
  21. PHP_Idiot
    They don't call me PHP_Idiot for nothing you know Thanks a lot, I guessed it was something simple. Works a treat now. Cheers
  22. PHP_Idiot
    Sure thing, this is how I had added it, as I said this just returns '2' on every line! //Now start the loop. $pos=1; $pos++; while($r = mysql_fetch_array($result)){ //and echo each new row echo <<<html <tr><td align="center">$pos</td> <td align="center">{$r['SUM(Position.Points)']}</td> <td align="center">{$r['FirstName']}</td> <td align="center">{$r['LastName']}</td> <td align="center">{$r['COUNT(Results.MembershipNo)']}</td> </tr> html; } //And close the table. echo "</table>";
  23. PHP_Idiot
    Hi All I'm sure this is simple but I can't get it working. I have an html table on my site that draws data from my database it all works very nicely, but I've decided that I now need to number the rows 1,2,3,4 etc I know this will need a loop and something like a $counter++ but I can't get it working. This is my current table including the query: //mySQL queries $query = "SELECT SUM(Position.Points) , Player.FirstName, Player.LastName, COUNT(Results.MembershipNo) FROM Position, Player, Results, Venue WHERE Player.MembershipNo = Results.MembershipNo AND Results.Position = Position.Position AND Venue.VenueID = Results.VenueID GROUP BY Player.MembershipNo ORDER BY SUM(Position.Points) DESC"; $result=mysql_query($query) or die ("couldn't execute query"); echo <<<html <table border="1" width="400" cellpadding="2" cellspacing="1"> <tr><td align="center"><strong>Position</strong></td> <td align="center"><strong>Total Points</strong></td> <td align="center"><strong>First Name</strong></td> <td align="center"><strong>Last Name</strong></td> <td align="center"><strong>Games Played</strong></td> </tr> html; //Now start the loop. while($r = mysql_fetch_array($result)){ //and echo each new row echo <<<html <tr><td align="center">I WANT TO AUTONUMBER THIS ROW</td> <td align="center">{$r['SUM(Position.Points)']}</td> <td align="center">{$r['FirstName']}</td> <td align="center">{$r['LastName']}</td> <td align="center">{$r['COUNT(Results.MembershipNo)']}</td> </tr> html; } //And close the table. echo "</table>"; I have tried adding $pos=1; $pos++; and then $pos in the table row but that returns '2' on every row. Thanks in advance
  24. PHP_Idiot
    Just realised I should probably have put this in the MySQL forum, sorry. But if anyone can help I'd appreciate it.
  25. PHP_Idiot
    Hi I have a table which displays the results of this query: $query = "SELECT SUM(Position.Points) , Player.FirstName, Player.LastName FROM Position, Player, Results, Venue WHERE Player.MembershipNo = Results.MembershipNo AND Results.Position = Position.Position AND Venue.VenueID = Results.VenueID GROUP BY Player.MembershipNo ORDER BY SUM(Position.Points) DESC"; $result=mysql_query($query) or die ("couldn't execute query"); As you can see this shows the total points for each player along with their name. I want to update this to only show players with greater or equal to 750 from a specific venue, and display the points, player name and venue where those points where scored. To do this i would need to add all the points scored by each player at each venue and compare them, if a player has a total of 200 points in venue A and 800 points in venue B, I need just the total points for Venue B to show. However, if a player has 800 total points in Venue A and 900 points at Venue B I need only the highest of these results to show (venue B results). I presume I need to run this in a couple of different queries and save the results in different arrays, is this the right way to go or can it be done in a single query? Thanks alot
×
×
  • 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.