mysql query in a function

[Redempt1on]

having problems with the following function. When called once it works no problem, but on multiple calls, it fails on the second run of the query. The file outputs the first run then nothing. Any help is appreciated.

PHP: Select all

function getStats($start,$no,$file,$group,$reg,$type,$freq){
require_once('Connections/carhire.php');
for ($i=0; $i<$no; $i++){
$temps = explode("/",$start); //split end date
if ($freq == 'daily'){$end = $start;} //same day end
if ($freq == 'weekly'){
$end = date("Y\/m\/d",mktime(0,0,0,$temps[1],$temps[2]+6,$temps[0]));} //end date is 6 days from start
if ($freq == 'monthly'){
$end = date("Y\/m\/d",mktime(0,0,0,$temps[1]+1,0,$temps[0]));} //end date is last day of month
if ($freq == 'yearly'){
$end = date("Y\/m\/d",mktime(0,0,0,12,31,$temps[0]));} //end date is last day of year
if($type == 'income'){
mysql_select_db($database_carhire, $carhire);
$query_incomestats = "SELECT COUNT(total) AS no, SUM(total) as total FROM Rental WHERE sdate >= '$start' AND sdate <= '$end'";
$incomestats = mysql_query($query_incomestats, $carhire) or die(mysql_error());
$row_incomestats = mysql_fetch_assoc($incomestats);
$totalRows_incomestats = mysql_num_rows($incomestats);
}
 
if($type == 'vehicle'){
mysql_select_db($database_carhire, $carhire);
$query_vehiclestats = "SELECT COUNT(total) AS no, SUM(total) AS total FROM Rental WHERE reg = '$reg' AND sdate >= '$start' AND sdate <= '$end'";
$vehiclestats = mysql_query($query_vehiclestats, $carhire) or die(mysql_error());
$row_vehiclestats = mysql_fetch_assoc($vehiclestats);
$totalRows_vehiclestats = mysql_num_rows($vehiclestats);
}
if ($type == 'group'){
mysql_select_db($database_carhire, $carhire);
$query_groupstats = "SELECT COUNT(total) AS no, SUM(total) AS total FROM Rental R WHERE no = '$group' AND sdate >= '$start' AND sdate <= '$end'";
$groupstats = mysql_query($query_groupstats, $carhire) or die(mysql_error()); //error occurs here
$row_groupstats = mysql_fetch_assoc($groupstats);
$totalRows_groupstats = mysql_num_rows($groupstats);
}
if($type == 'income'){
fwrite($file,$start.",".$row_incomestats['no'].",".$row_incomestats['total']."\n");}
if($type == 'group'){
fwrite($file,$group.",".$start.",".$row_groupstats['no'].",".$row_groupstats['total']."\n");}
if($type == 'vehicle'){
fwrite($file,$reg.",".$start.",".$row_groupstats['no'].",".$row_groupstats['total']."\n");}
 
$tempe = explode("/",$end);
$start = date("Y\/m\/d",mktime(0,0,0,$tempe[1],$tempe[2]+1,$tempe[0]));
 
mysql_free_result($incomestats);
mysql_free_result($vehiclestats);
mysql_free_result($groupstats);
}
}
 
if ($type == "group" || $type == 'all'){
$filename = "Group_".$fstart."_".$fend.".csv";
$file = fopen("Stats/".$filename,w);
for ($i=1;$i<5;$i++){
fwrite($file,"\"Group No\",\"Start Date\",\"No of Bookings\",\"Income\"\n");
getStats($start,$no,$file,$i,0,"group",$freq);
fwrite($file,"\n");
}
fclose($file);
$message = $message."Group Statistics Output to<br>".$filename."<br>"; 

[ukgeoff]

Unless I'm missing something, each time you call the function, the queries are run a new so you will get the first row in the returned set of rows every time.

The same is true of your for...loop.

[Redempt1on]

You are missing something. The first row is all i need as it contains the values returned by the two functions. on each loop, the variables passed to the query are different and so different results are generated. the problem isn't the internal loop of the function. it seems to be if the function is called more than once then the query causes an error.

[ukgeoff]

Might be helpful if you told us what the error was!

[Redempt1on]

a blank page

[ukgeoff]

I was thinking more in terms of a mysql error as to why it's not returning any data, if in fact that is the case.

[grahame]

I suspect that the problem is with the require_once and the code you haven't shown us. This code only runs once - the first time you call the getStats function. Does it, by any chance, open the database connection and and set variables such as $carhire? If it does, then they're only set the first time you run the function, lost when the function exits, then not created again the second time. Solution - switch to require.

-- Graham