PHP, MySQL query integration within code

[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);

?>

 

[Broccoli2]

OK, a couple of points:

1. You need to send the queries one at a time, ie

$sql = "DROP TABLE seedtmp1";
if (!mysql_query($sql)) echo mysql_error();
$sql = "CREATE TEMPORARY TABLE seedtmp1";
$sql .= " SELECT ...";
if (!mysql_query($sql)) echo mysql_error();

2. You could do the job simpler with the following code, without the need for seedtmp1 and seedtmp2 (unless you need these tables for something else):

$sql = "DROP TABLE seedtmp3";
if (!mysql_query($sql)) echo mysql_error();
$sql = "SELECT DISTINCT Team FROM Teams";
if (!$r=mysql_query($sql)) {echo mysql_error();} else {
  $flag=false;
  while ($row=mysql_fetch_assoc($r)) {
    $sql = ($flag?"INSERT INTO seedtmp3(TRN,Team,Fastest_Time,Date,Venue)":"CREATE TEMPORARY TABLE seedtmp3");
    $flag=true;
    $sql .= " SELECT";
    $sql .= " Ttimes.TRN";
    $sql .= ", Teams.Team";
    $sql .= ", Ttimes.Fastest_Time";
    $sql .= ", Venues.Date";
    $sql .= ", Venues.Venue";
    $sql .= " FROM Teams";
    $sql .= " LEFT JOIN Ttimes ON Teams.TRN=Ttimes.TRN";
    $sql .= " LEFT JOIN Venues ON Ttimes.Tournament_ID=Venues.Tournament_ID";
    $sql .= " WHERE Teams.Team=".$row["Team"];
    $sql .= " AND Venues.Date>DATE_SUB(CURDATE(),INTERVAL 120 DAY)";
    $sql .= " ORDER BY Venues.Date DESC";
    $sql .= " LIMIT 3";
    if (!mysql_query($sql)) echo mysql_error();
  }
  mysql_free_result($r);
}

Hope this at least gives some pointers.

-Rob

 

[NigeB]

Rob, thanks for the pointers I have had to create seperate temporary tables and query seperatly as the query will not bring back the correct data, I am however receiving a parse error on line 48 (indicated by ****) The other 3 sections work fine, but the important one is based on your thoughts, any ideas? Here is the crux of the problem section, the whole code is listed below.

TIA

Nigel.

_________________________________________________________
// Create seedtmp3 to give table with latest 3 records for each team

$sql = &quot;SELECT DISTINCT Team FROM seedtmp1&quot;;
if (!$r=mysql_query($sql)) {echo mysql_error();}
else {
$flag=false;
while ($row=mysql_fetch_assoc($r)) {
$sql = ($flag?&quot;INSERT INTO seedtmp3
(TRN, Team, Fastest_Time, Date, Venue)&quot;:&quot;CREATE TEMPORARY TABLE seedtmp3&quot;
$flag=true;
$sql .=&quot; SELECT&quot;;
$sql .=&quot; TRN&quot;;
$sql .=&quot; Team&quot;;
$sql .=&quot; Fastest_Time&quot;;
$sql .=&quot; Date&quot;;
$sql .=&quot; Venue&quot;;
$sql .=&quot; FROM seedtmp2&quot;;
$sql .=&quot; LEFT JOIN seedtmp2 ON seedtmp2.TRN=seedtmp1.TRN&quot;;

**** $sql .=&quot; WHERE seedtmp2.Team=&quot;.$row[&quot;Team&quot;]&quot;;
$sql .=&quot; LIMIT 3&quot;;
if (!mysql_query(sql)) echo mysql_error();
}
mysql_free_result($r);
}
__________________________________________________________

Full code

// 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);

// 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);


// Create seedtmp3 to give table with latest 3 records for each team

$sql = &quot;SELECT DISTINCT Team FROM seedtmp1&quot;;
if (!$r=mysql_query($sql)) {echo mysql_error();}
else {
$flag=false;
while ($row=mysql_fetch_assoc($r)) {
$sql = ($flag?&quot;INSERT INTO seedtmp3
(TRN, Team, Fastest_Time, Date, Venue)&quot;:&quot;CREATE TEMPORARY TABLE seedtmp3&quot;
$flag=true;
$sql .=&quot; SELECT&quot;;
$sql .=&quot; TRN&quot;;
$sql .=&quot; Team&quot;;
$sql .=&quot; Fastest_Time&quot;;
$sql .=&quot; Date&quot;;
$sql .=&quot; Venue&quot;;
$sql .=&quot; FROM seedtmp2&quot;;
$sql .=&quot; LEFT JOIN seedtmp2 ON seedtmp1.TRN=seedtmp2.TRN&quot;;
$sql .=&quot; WHERE seedtmp2.Team=&quot;.$row[&quot;Team&quot;]&quot;;
$sql .=&quot; LIMIT 3&quot;;
if (!mysql_query(sql)) echo mysql_error();
}
mysql_free_result($r);
}


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

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


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

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=150 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=90 align=center><font color=#000080><big><b>Seed Time</b></big></font></td>
<td width=120 align=center><font color=#000080><big><b>Date</b></big></font></td>
<td width=150 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=left>%s</td><td align=center>%s</td><td align=center>%s</td>
<td align=center>%s</td><td align=left>%s</td></tr>\n&quot;, $i, $myrow[&quot;Team&quot;], $myrow[&quot;TRN&quot;],
$myrow[&quot;Seed_Time&quot;], date(&quot;d M Y&quot;,$myrow[&quot;unix_date&quot;]), $myrow[&quot;Venue&quot;]);

$i++;

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


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

} else {

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

}

 

[Broccoli2]

Oops! Remove the trailing quote on your **** line - should be:

$sql .=" WHERE seedtmp2.Team=".$row["Team"];

-Rob