CODEWITHSUNDEEP
phpmysql
Naim
Naim

Reputation: 463

temporary table and foreach

$date['"01.06.2012"'] = '2012-06-01'; 
$date['"01.05.2012"'] = '2012-05-01'; 
$date['"01.04.2012"'] = '2012-04-01';
$date['"01.03.2012"'] = '2012-03-01';
$date['"01.02.2012"'] = '2012-02-01';
$date['"01.01.2012"'] = '2012-01-01';     

foreach($date as $month => $actual_date)
{  /* start foreach loop */
$query = "select Player, SUM(points) AS score from rankings WHERE
`drop_date` > '$actual_date' 
AND `drop_date` <= DATE_ADD('$actual_date',  INTERVAL 1 YEAR) 
GROUP BY Player
ORDER BY SUM(sa_points) LIMIT 0,5";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_array($result)){
echo $row['player'];
echo $row['score']; }

} /* end foreach loop */

Now I just want add another query to this resulting table, I have tried create temporary table but didnt know where to put it according to the foreach loop and kept getting either parse errors or last $actual_date results.

Upvotes: 0

Views: 448

Answers (1)

Yosh
Yosh

Reputation: 145

Is this what you are trying to reach?

$date['"01.06.2012"'] = '2012-06-01'; 
$date['"01.05.2012"'] = '2012-05-01'; 
$date['"01.04.2012"'] = '2012-04-01';
$date['"01.03.2012"'] = '2012-03-01';
$date['"01.02.2012"'] = '2012-02-01';
$date['"01.01.2012"'] = '2012-01-01';     

mysql_query('CREATE TEMPORARY TABLE results (player varchar(100), score int)');

foreach($date as $month => $actual_date)
{  /* start foreach loop */

    $query = "insert into results (player, score) 
    select Player, SUM(points) AS score from rankings WHERE
    `drop_date` > '$actual_date' 
    AND `drop_date` <= DATE_ADD('$actual_date',  INTERVAL 1 YEAR) 
    GROUP BY Player
    ORDER BY SUM(sa_points) LIMIT 0,5";
    mysql_query($query) or die(mysql_error());
} /* end foreach loop */

$result = mysql_query('select * from results') or die(mysql_error());
while($row = mysql_fetch_array($result)){
    echo $row['player'];
    echo $row['score']; 
}

Upvotes: 2

Related Questions