Page 1 of 1

trouble passing variable to SQL

Posted: Wed Aug 03, 2005 10:06 pm
by nephish
Hey there,
i have a tricky one here
i am having trouble passing a variable to be either a field name or a value.

when i run this

Code: Select all

$Day = $_GET["Day"];
$Month = $_GET["Month"];
$date = "2005-".$Month."-";
$date .= $Day;

function Hist($Unit, $date){	  
	  $result = mysql_query("SELECT Daily FROM RainGaugeHistory WHERE Sensor_ID = {$Unit} AND `Date`> {$date}");
		$num=mysql_numrows($result);
		if ($num == "FALSE"){
			$Unit = 'No History';
			return $Unit;				 
		}		
		else{	
			$i=0;
			while ($i < $num){
				echo $i;
				echo nl2br("\n");				
				$DayTotal = mysql_result($result,$i,"Daily");
				$Daily = $Daily + $DayTotal;
				$i++;				
				$Today = OneDay($Unit);
				$Unit = $Daily + $Today;
				return $Unit;
			}			
		}
}

$Lab = Hist('515',$date);
i am trying to add up the daily totals from a daily updated history file
trouble is, when i run it, it selects every record for the unit except for only those after the $date.
i have tried changing the field type from date to VARCHAR(10) but that wasn't it helping either. maybe (probably) there is a much better way to do this, i am really open for any suggestion that may prevent more hair loss.

Posted: Wed Aug 03, 2005 10:15 pm
by feyd
  1. mysql_numrows() returns FALSE, not 'FALSE' on failure.
  2. Your while loop returns after the first iteration.
  3. At quick glance, your query looks fine

Posted: Wed Aug 03, 2005 10:45 pm
by nephish
ok, i changed it to this (if the records do not go back far enough, i am supposed to print out 'No History"
thats why i moved the loop

Code: Select all

function Hist($Unit, $date){
	   print'date inside function';
	   echo nl2br("\n");
	   print $date;
	   echo nl2br("\n");
	   $BackCheck = mysql_query("SELECT Daily FROM RainGaugeHistory WHERE Sensor_ID = {$Unit} AND Date = {$date}");
	   $num=mysql_numrows($BackCheck);
	   if ($num == FALSE ){
			$Unit = 'No History';
			return $Unit;				 
		}		
	   echo nl2br("\n");
	   echo nl2br("\n");
		$result = mysql_query("SELECT Daily FROM RainGaugeHistory WHERE Sensor_ID = {$Unit} AND Date > {$date}");
		$num=mysql_numrows($result);
		echo nl2br("\n");
		print 'numrows = ';
		print $num;
		$i=0;
			while ($i < $num){
				echo $i;
				echo nl2br("\n");				
				$DayTotal = mysql_result($result,$i,"Daily");
				$Daily = $Daily + $DayTotal;
				$i++;				
				$Today = OneDay($Unit);
				$Unit = $Daily + $Today;
			}	
			return $Unit;		
}
thanks, by the way

Posted: Wed Aug 03, 2005 11:02 pm
by feyd
a date must be passed as a string, you are currently passing it as a math constant 2005 minus your month minus your day

Place quotes around the value.

Posted: Wed Aug 03, 2005 11:16 pm
by nephish
ok, here is what i have now.

Code: Select all

function Hist($Unit, $date){
	   print'date inside function';
	   echo nl2br("\n");
	   print $date;
	   echo nl2br("\n");
	   $BackCheck = mysql_query("SELECT Daily FROM RainGaugeHistory WHERE Sensor_ID = {'$Unit'} AND Date = '{$date}'");
	   $num=mysql_numrows($BackCheck);
	   if ($num == FALSE ){
			$Unit = 'No History';
			return $Unit;				 
		}		
	   echo nl2br("\n");
	   echo nl2br("\n");
		$result = mysql_query("SELECT Daily FROM RainGaugeHistory WHERE Sensor_ID = {$Unit} AND Date > {$date}");
		$num=mysql_numrows($result);
		echo nl2br("\n");
		print 'numrows = ';
		print $num;
		$i=0;
			while ($i < $num){
				echo $i;
				echo nl2br("\n");				
				$DayTotal = mysql_result($result,$i,"Daily");
				$Daily = $Daily + $DayTotal;
				$i++;				
				$Today = OneDay($Unit);
				$Unit = $Daily + $Today;
			}	
			return $Unit;		
}
i get this when i run it

Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in /var/www/pivotrac/web_root/phptest.php

line 55 is this one

Code: Select all

$num=mysql_numrows($BackCheck);
where do i put the quotes, outside of the { } braces ?
this is what i get now.
(had to run two checks, one to see if records are there, and one to count and add values)

thanks for all your help on this, i really appreciate it

Posted: Thu Aug 04, 2005 12:03 am
by feyd
that means the query failed.

Posted: Thu Aug 04, 2005 1:17 am
by nephish
i fixed it,
man its late
thank you for all of your help.
i finally fixed it.
put my $Unit and $date in quotes

thanks again