woolade

Magic! Thanks avvllvva.

I'm trying to generate a hyperlink within a fetch_array based on a field value for each row. Currently I have; echo "<td align=\"center\"><a href='" . $seasname . "_results.php?seasid='>" . $row['Name'] . "</a></td>"; which returns : _results.php?seasid= I need to add a value next to the = sign by using : $row['Season_id'] so that the hyperlink returns : _results.php?seasid=1 or _results.php?seasid=2 etc depending on the season_id I've tried; echo "<td align=\"center\"><a href='" . $seasname . "_results.php?seasid=" . $row['Season_id']'>" . $row['Name'] . "</a></td>"; but that returns an error. I'm sure I'm just puttin " or ' in the wrong place, I've tried other scenarios but just can't seem to get it right, can someone please help. Thanks

All ok, worked it out; $query = "SELECT Fixtures.Season_id, Seasons.Name, AVG(Fixtures.OurGrossScore)-AVG(Fixtures.OurWktsLost*6) AS OurNetScore, AVG(Fixtures.OppGrossScore)-AVG(Fixtures.OppWktsLost*6) AS OppNetScore, COUNT(If(WinLossDraw='W', 1, null)) as TotWin, COUNT(If(WinLossDraw='L', 1, null)) as TotLoss FROM Fixtures LEFT JOIN Seasons ON Fixtures.Season_id = Seasons.id GROUP BY Name ORDER BY Season_id";

I'm trying to get a COUNT on a number of records using; COUNT(*) AS TotWin WHERE WinLossDraw = 'W', COUNT(*) AS TotLoss WHERE WinLossDraw = 'L' within a mysql query. It also includes a LEFT JOIN and GROUP BY. I have no idea where I am going wrong here, but I get the following error when I run it; You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE WinLossDraw = 'W', COUNT(*) AS TotLoss WHERE WinLossDraw = 'L' FROM Fixtu' at line 4. Please help. <?php $con = mysql_connect("localhost","xxxxx","xxxxx"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("xxxxx", $con); $query = "SELECT Fixtures.Season_id, Seasons.Name, AVG(Fixtures.OurGrossScore)-AVG(Fixtures.OurWktsLost*6) AS OurNetScore, AVG(Fixtures.OppGrossScore)-AVG(Fixtures.OppWktsLost*6) AS OppNetScore, COUNT(*) AS TotWin WHERE WinLossDraw = 'W', COUNT(*) AS TotLoss WHERE WinLossDraw = 'L' FROM Fixtures LEFT JOIN Seasons ON Fixtures.Season_id = Seasons.id GROUP BY Name ORDER BY Season_id"; $result = mysql_query($query) or die(mysql_error()); $seasname = 'http://www.xxxxxxx'; echo "<table align=\"center\"> <tr> <th>Season</th> <th>Won</th> <th>Lost</th> <th>Our Ave Score</th> <th>Opp Ave Score</th> </tr>";while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td align=\"center\"><a href='" . $seasname . $row['Season_id'] . "_results.php'>" . $row['Name'] . "</a></td>"; echo "<td align=\"center\">" . $row['TotWin'] . "</td>"; echo "<td align=\"center\">" . $row['TotLoss'] . "</td>"; echo "<td class=\"grey\" align=\"center\">" . Round($row['OurNetScore'],2) . "</td>"; echo "<td class=\"grey\" align=\"center\">" . Round($row['OppNetScore'],2) . "</td>"; echo "</tr>"; } echo "</table>"; echo "<br>"; mysql_close($con); ?>

I've still been searching for an answer to this and just cannot seem to find it. Is there anyone who can help me with this or point me in the right direction as to where I can start to try and work this out. If easier I could just have the selection redirect to another page based on that selection. Thanks in advance.

Please bear with me as I'm new to this and not sure if this can even be done. I have a dropdown box that is populated from a field in a mysql database 'Season'. $query = "SELECT DISTINCT Season FROM Fixtures"; $result = mysql_query($query); echo '<select Season="Season_From_Fixtures">'; while( $row = mysql_fetch_array($result) ) { echo '<option>'.$row['Season'].'</option>'; } echo '</select>'; mysql_free_result( $result ); All the information on my page is based on a particular season, i.e. Season 1, Season 2 etc. The Season is initially selected from my SQL query; $query = "SELECT Fixtures.Season, Fixtures.Round, date_format(Fixtures.Date, '%D %b %Y' ) as GameDate, Teams.Name, Fixtures.WinLossDraw, Fixtures.OurGrossScore-Fixtures.OurWktsLost*6 AS OurNetScore, Fixtures.OppGrossScore-Fixtures. OppWktsLost*6 AS OppNetScore FROM Fixtures LEFT JOIN Teams ON Fixtures.vs_Teams_id = Teams.id WHERE Season = 1"; $result = mysql_query($query) or die(mysql_error()); At this stage the Season is selected within the code by typing the Season number, in the example above Season = 1, obviously this will need to be changed to a variable of some sort. How do I get my SQL query to re-run based on the Season selected from the dropdown box and then refresh the information on the page? I have searched this forum and the web but still can't seem to find the answer, is there a simple solution or does it involve a more complex way to go about it? FYI, this is the full code for my page; <html> <head> <link rel="stylesheet" type="text/css" href="phpthepests.css" /> <body> <h1>RESULTS - SEASON 1 - WINTER 2006</h1> <h1 class='small'>Click on Round Number to view Scorecard</h1> <?php $con = mysql_connect("localhost","xxxxxx","xxxxx"); if (!$con) { die('Could not connect: ' . mysql_error()); } mysql_select_db("xxxxx_pests", $con); $query = "SELECT DISTINCT Season FROM Fixtures"; $result = mysql_query($query); echo '<select Season="Season_From_Fixtures">'; while( $row = mysql_fetch_array($result) ) { echo '<option>'.$row['Season'].'</option>'; } echo '</select>'; mysql_free_result( $result ); $query = "SELECT Fixtures.Season, Fixtures.Round, date_format(Fixtures.Date, '%D %b %Y' ) as GameDate, Teams.Name, Fixtures.WinLossDraw, Fixtures.OurGrossScore-Fixtures.OurWktsLost*6 AS OurNetScore, Fixtures.OppGrossScore-Fixtures. OppWktsLost*6 AS OppNetScore FROM Fixtures LEFT JOIN Teams ON Fixtures.vs_Teams_id = Teams.id WHERE Season = 1"; $result = mysql_query($query) or die(mysql_error()); $scd = 'http://www.xxxxxx.com.au/pests/scd'; echo "<table align=\"center\"> <tr> <th>Round</th> <th>Date</th> <th></th> <th></th> <th>Result</th> <th></th> <th></th> </tr>";while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td align=\"center\"><a href='" . $scd . $row['Season'] . $row['Round'] . ".html'>" . $row['Round'] . "</a></td>"; echo "<td align=\"center\">" . $row['GameDate'] . "</td>"; echo "<td align=\"center\">" . "The Pests" . "</td>"; echo "<td class=\"grey\" align=\"center\">" . $row['OurNetScore'] . "</td>"; if($row['OurNetScore'] > $row['OppNetScore']) echo "<td align=\"center\" bgcolor=\"#04B404\">" . "defeated"; else echo "<td align=\"center\" bgcolor=\"#FF0000\">" . "defeated by" . "</td>"; echo "<td align=\"center\">" . $row['Name'] . "</td>"; echo "<td class=\"grey\" align=\"center\">" . $row['OppNetScore'] . "</td>"; echo "</tr>"; } echo "</table>"; echo "<br>"; $query="SELECT COUNT(*) AS TotWins FROM Fixtures WHERE WinLossDraw = 'W' AND Season = 1"; $result = mysql_query($query) or die(mysql_error()); echo "<table align=\"center\">"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td class=\"green\" align=\"center\">" . "Won : " . $row['TotWins'] . "</td>"; echo "</tr>"; } echo "</table>"; $query="SELECT COUNT(*) AS TotLoss FROM Fixtures WHERE WinLossDraw = 'L' AND Season = 1"; $result = mysql_query($query) or die(mysql_error()); echo "<table align=\"center\">"; while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td class=\"red\" align=\"center\">" . "Lost : " . $row['TotLoss'] . "</td>"; echo "</tr>"; } echo "</table>"; mysql_close($con); ?> </body> </head> </html>

Thanks MasterACE14, u r a legend.

Can someone please help. I'm very new at php and just finding my feet, have searched your forum but still can't work out why this doesn't work. I'm trying to use an if... else statement within fetch_array but keep getting the following error:- Parse error: syntax error, unexpected T_IF in /home/d*****/public_html/php_test.php on line 42 Here's my code $query = "SELECT Fixtures.Season, Fixtures.Round, Fixtures.Date, Teams.Name, Fixtures.WinLossDraw, Fixtures.OurGrossScore-Fixtures.OurWktsLost*6 AS OurNetScore, Fixtures.OppGrossScore-Fixtures. OppWktsLost*6 AS OppNetScore FROM Fixtures LEFT JOIN Teams ON Fixtures.vs_Teams_id = Teams.id WHERE Season = 1"; $result = mysql_query($query) or die(mysql_error()); echo "<table align=\"center\"> <tr> <th>Round</th> <th>Date</th> <th></th> <th></th> <th>Result</th> <th></th> <th></th> <th>W/L/D</th> </tr>";while($row = mysql_fetch_array($result)) { echo "<tr>"; echo "<td align=\"center\">" . $row['Round'] . "</td>"; echo "<td align=\"center\">" . $row['Date'] . "</td>"; echo "<td align=\"center\">" . "The Pests" . "</td>"; echo "<td align=\"center\">" . $row['OurNetScore'] . "</td>"; echo "<td align=\"center\">" . "vs" . "</td>"; echo "<td align=\"center\">" . if($row['OurNetScore'] > $row['$OppNetScore']) echo "defeated"; else echo "defeated by" . "</td>"; echo "<td align=\"center\">" . $row['Name'] . "</td>"; echo "<td align=\"center\">" . $row['OppNetScore'] . "</td>"; echo "<td align=\"center\">" . $row['WinLossDraw'] . "</td>"; echo "</tr>"; } echo "</table>";