mysql query in a function

Redempt1on · May 17th, 2006, 07:02 AM

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.

Code:


			
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 · May 18th, 2006, 06:33 AM

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 · May 18th, 2006, 08:44 AM

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 · May 18th, 2006, 10:43 AM

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

Redempt1on · May 18th, 2006, 11:27 AM

a blank page

ukgeoff · May 18th, 2006, 11:43 AM

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 · May 18th, 2006, 09:25 PM

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