getting dates in one query and using them in another

PHP_Idiot · 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

PHP_Idiot · 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.

Sign In

getting dates in one query and using them in another

Recommended Posts

PHP_Idiot

Link to comment

Share on other sites

PHP_Idiot

Link to comment

Share on other sites

Archived

Browse

Activity

Important Information