PHP_Idiot Posted February 14, 2010 Share Posted February 14, 2010 Hi Freaks I'm sure this is easier than I'm making it! Basically I have a table that lists start and end dates for game season. I want to query that table and extract the dates for a given season using a combo box. then I want to take those dates and use them in a second query to show a leader board for a venue chosen from a second combo box, between the two dates taken from the first. I have the two boxes showing and displaying the correct values, but I cant get the associated date ranges to function. You can see the output http://www.gbpokerclub.co.uk/beta/seasons.php The query for displaying the results works except for the variables used in the 'between dates' part (I know this as I have it running on other pages with the dates manually inputted to the query) Heres what I have so far. <head> </head> <body> <?php // Make a MySQL Connection mysql_connect("XXXX", "XXXX", "XXXX") or die(mysql_error()); mysql_select_db("gbpokerclub_benn") or die(mysql_error()); $query = "SELECT SeasonName, StartDate, date_format(StartDate,'%d.%m.%y') as sdate, EndDate, date_format(EndDate,'%d.%m.%y') as edate FROM Seasons ORDER BY sdate"; $result = mysql_query($query) or die("Couldn't execute query because: ".mysql_error()); echo '<form action="" method="post">'; echo "<select name='Season'>"; // printing the list box select command while($nt=mysql_fetch_array($result)) {//Array or records stored in $nt echo "<option "; if($_POST['Season'] == $nt['SeasonName']) echo "selected=\"selected\""; echo " value=\"$nt[seasonName]\">$nt[seasonName]</option>"; /* Option values are added by looping through the array */ } echo "</select>";// Closing of list box $query2="SELECT VenueName FROM Venue ORDER BY VenueName"; $result2 = mysql_query ($query2); echo '<form action="" method="post">'; echo "<select name='Venue'>"; // printing the list box select command while($nt2=mysql_fetch_array($result2)) {//Array or records stored in $nt echo "<option "; if($_POST['Venue'] == $nt2['VenueName']) echo "selected=\"selected\""; echo " value=\"$nt2[VenueName]\">$nt2[VenueName]</option>"; /* Option values are added by looping through the array */ } echo "</select>";// Closing of list box ?> <input type="submit" value="Go" /> <h3><?php echo $_POST['Venue'] ?> League Positions</h3> <?php $startdate = $nt['StartDate']; $enddate = $nt['EndDate']; 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 '$startdate' AND '$enddate' 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"); 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>Points</strong></td> <td align="center"><strong>Played</strong></td> </tr> html; //Now start the loop. $pos=1; while($r = mysql_fetch_array($result)){ //and echo each new row echo <<<html <tr><td align="center">$pos</td> <td align="center">{$r['FirstName']}</td> <td align="center">{$r['LastName']}</td> <td align="center">{$r['SUM(Position.Points)']}</td> <td align="center">{$r['COUNT(Results.MembershipNo)']}</td> </tr> html; $pos++; } //And close the table. echo "</table>"; } ?> </body> </html> All help greatly appreciated Quote Link to comment https://forums.phpfreaks.com/topic/192029-getting-dates-in-one-query-and-using-them-in-another/ Share on other sites More sharing options...
PHP_Idiot Posted February 14, 2010 Author Share Posted February 14, 2010 Perhaps I should try to explain a bit clearer. I have a list box that is produced by this code: $query = "SELECT SeasonName, StartDate, date_format(StartDate,'%d.%m.%y') as sdate, EndDate, date_format(EndDate,'%d.%m.%y') as edate FROM Seasons ORDER BY sdate"; $result = mysql_query($query) or die("Couldn't execute query because: ".mysql_error()); echo '<form action="" method="post">'; echo "<select name='Season'>"; // printing the list box select command while($nt=mysql_fetch_array($result)) {//Array or records stored in $nt echo "<option "; if($_POST['Season'] == $nt['SeasonName']) echo "selected=\"selected\""; echo " value=\"$nt[seasonName]\">$nt[seasonName], $nt[startDate] to $nt[EndDate]</option>"; /* Option values are added by looping through the array */ } echo "</select>";// Closing of list box As you can see the list box displays Season Name, and the start and end dates of that season ("2009 Season 1, 2010-01-01 to 2010-04-03") I want to put the Start date and end date into another variable to use in a seperate query later on in the page. I know how to include the variables AND Date BETWEEN '$start' AND '$end' But I can't get the actual dates into the variables in the first place. I've tried: $start = $nt[startDate]; $end = $nt[EndDate]; $start = $nt[startDate]; $end = $nt[EndDate]; and various others but I can't seem to get the dates I need into the variable!! Please help. Quote Link to comment https://forums.phpfreaks.com/topic/192029-getting-dates-in-one-query-and-using-them-in-another/#findComment-1012208 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.