PHP, MySQL - Temp Table and iteration of query

[NigeB]

The code as it appears on the web page is listed below, it is not complete as I am having problems towards the end, basically I create a Temp table place corresponding records from two existing tables into it (seedtmp1)

I then create a second temporary table and filter the first one so that all records greater than 4 months old are ignored.

What I then need to do but am having problems is take the latest 3 records for each team and place them in another temporary table (seedtmp3) What I can not do is seem to incorporate the while statement to "iterate" the query so it fills seedtmp3 with the latest 3 records for each team.

Any help in the right direction is greatly appreciated.

Nigel.


<?
$db = mysql_connect(&quot;Server&quot;, &quot;User&quot;, &quot;Password&quot;

mysql_select_db(&quot;Database&quot;, $db);

$query = &quot;DROP TABLE IF EXISTS seedtmp1&quot;;
$query = &quot;CREATE TEMPORARY TABLE seedtmp1&quot;;
$query = &quot;SELECT Ttimes.TRN, Teams.Team, Ttimes.Fastest_Time, Venues.Date, Venues.Venue
FROM Teams
LEFT JOIN Ttimes ON Teams.TRN = Ttimes.TRN
LEFT JOIN Venues ON Ttimes.Tournament_ID = Venues.Tournament_ID &quot;;
$query = &quot;CREATE TEMPORARY TABLE seedtmp2&quot;;
$query = &quot;SELECT TRN, Team, Fastest_Time, Date, Venue
FROM seedtmp1
WHERE Date>DATE_SUB(CURDATE(), INTERVAL 120 DAY)
ORDER BY Date DESC&quot;;
$query1 = &quot;Select TRN from seedtmp2&quot;;
$result = mysql_query($query, $db);
$phtres = mysql_query($query1, $db);
$query2 = &quot;CREATE TEMPORARY TABLE seedtmp3&quot;;
$query2 = &quot;SELECT TRN, Team, Fastest_Time, Date, Venue
FROM seedtmp2
WHERE TRN = '$rst'
Order by Date DESC LIMIT 3&quot;;
$rst = mysql_fetch_array($phtres);
$result1 = mysql_query($query2, $db);

?>

 

[Hokey]

excuse me if I am way off base, but it appears from your code that your create the string varible $query and then over wright it 4 times then run it. At the time you run $query it has the follwing value:
$query = &quot;SELECT TRN, Team, Fastest_Time, Date, Venue
FROM seedtmp1
WHERE Date>DATE_SUB(CURDATE(), INTERVAL 120 DAY)
ORDER BY Date DESC&quot;;

$query1 is created only once and run with the value:
$query1 = &quot;Select TRN from seedtmp2&quot;;

and then $query 2 is created, over written once and run with the value of:
$query2 = &quot;SELECT TRN, Team, Fastest_Time, Date, Venue
FROM seedtmp2
WHERE TRN = '$rst'
Order by Date DESC LIMIT 3&quot;;

The last possible problem (and I may be completely wrong since there may be more code than just this) is the variable $rst is refrenced as $Rst, but is also refrenced before it is created two lines later as an array.

 

[NigeB]

I _don't_ think you are way of base, I think I may have misunderstood the concept of creating a Tempory Table and populating it from a query.

The code is complete all but calculating the last bit which would be to get the MIN value of Fastest_time for each team from the 3rd TEMPORARY TABLE.

Two bits of help I would appreciate -

1. How do I create TEMPORARY TABLES the populate form a query

and

2. How do I integrate an array into the code to bring back the $rst value for each team?

Any help or pointing in the right direction is appreciated.

Nigel.

 

[NigeB]

This is the latest stage I am at, Section 3 is still giving me a headache, whilst 1, 2 and 4 are working fine.

Any help would be appreciated.

Nigel.

//1. create first temporary table and populate from Teams, Ttimes and Venues

$query = &quot;CREATE TEMPORARY TABLE seedtmp1 SELECT Ttimes.TRN, Teams.Team, Ttimes.Fastest_Time, Venues.Date, Venues.Venue
FROM Teams
LEFT JOIN Ttimes ON Teams.TRN = Ttimes.TRN
LEFT JOIN Venues ON Ttimes.Tournament_ID = Venues.Tournament_ID &quot;;
$mysql_result = mysql_query ($query, $db);

//2. create second temporary table and filter out records over 4 months old

$query1 = &quot;CREATE TEMPORARY TABLE seedtmp2 SELECT TRN, Team, Fastest_Time, Date, Venue
FROM seedtmp1
WHERE Date>DATE_SUB(CURDATE(), INTERVAL 120 DAY)
ORDER BY Date DESC&quot;;
$mysql_result1 = mysql_query ($query1, $db);

//3. create third temporary table and populate with latest 3 entries from each team

$phtres = mysql_query(&quot;Select Team from seedtmp2&quot;

while ($rst = mysql_fetch_array($phtres))

{
CREATE TEMPORARY TABLE seedtmp3 Select TRN, SELECT TRN, Team, Fastest_Time, Date, Venue
FROM seedtmp2
WHERE Team = '$rst'
ORDER BY Date LIMIT 3

}

//4. Query seedtmp3 to give MIN Fastest Time for each team

$resultf = mysql_query(&quot;TRN, SELECT TRN, Team, Min(Fastest_Time) AS Seed_Time, Date, Venue
FROM seedtmp3
GROUP BY TRN, Team
ORDER BY Seed_Time&quot;, $db);


if ($myrow = mysql_fetch_array($resultf)) {

echo &quot;</p>
<table align=centre border=0>\n&quot;;

echo &quot;<td width=90 align=center><font color=#000080><big><b>Seed</b></big></font></td>
<td width=315 align=center><font color=#000080><big><b>Team</b></big></font></td>
<td width=90 align=center><font color=#000080><big><b>TRN</b></big></font></td>
<td width=137 align=center><font color=#000080><big><b>Seed Time</b></big></font></td></tr>\n&quot;;
<td width=90 align=center><font color=#000080><big><b>Date</b></big></font></td>
<td width=137 align=center><font color=#000080><big><b>Venue</b></big></font></td></tr>\n&quot;;

$i =1;

do {


printf(&quot;<tr><td align=center>%s</td><td align=center>%s</td><td align=center>%s</td><td align=center>%s</td>
<td align=center>%s</td><td align=center>%s</td></tr>\n&quot;, $i, $myrow[&quot;Team&quot;], $myrow[&quot;TRN&quot;],
$myrow[&quot;Seed_Time&quot;], $myrow[&quot;Date&quot;], $myrow[&quot;Venue&quot;]);

$i++;

} while ($myrow = mysql_fetch_array($resultf));


echo &quot;</table>\n&quot;;

} else {

echo &quot;Sorry, no records were found!&quot;;

}