Page 1 of 1

simple error? Couting rows in results

Posted: Mon Jul 31, 2006 11:23 am
by FE

Code: Select all

$query = "SELECT COUNT(*) FROM GRN_ITEM WHERE DATE BETWEEN '$startdate' AND '$enddate' AND STOCK_CODE LIKE 'A%'";
	$result = odbc_exec($connectionstring, $query); 
	echo 'testing $result ', $result ;
	$row = odbc_fetch_array ($result);
	$num_records = $row[0];
	echo 'testing $numrecords ', $numrecords ;
	// Calculate the number of pages
	if ($num_records > $display) { // More than 1 page.
		$num_pages = ceil ($num_records/$display);
	} else {
		$num_pages = 1;
	}
hello, can anyone tell me what is wrong with the above code?
i have tried modifiying it but i keep getting

"Resource id #3"
from the variable $result i need it to work so i can count the amount of rows from the returned results

Posted: Mon Jul 31, 2006 11:41 am
by volka
What does

Code: Select all

error_reporting(E_ALL);
ini_set('display_errors', true);

$query = "SELECT COUNT(*) FROM GRN_ITEM WHERE DATE BETWEEN '$startdate' AND '$enddate' AND STOCK_CODE LIKE 'A%'";
// $connectionstring? string? isn't it a connection resource?
$result = odbc_exec($connectionstring, $query);

echo "<fieldset><legend>Debug</legend>\n", 
		'query:', $query, "<br />\n",
		'result:', $result, "<br />\n",
		'errormsg: ', odbc_errormsg(), "<br />\n",
	"</fieldset>\n";
$row = odbc_fetch_array($result);

echo "<fieldset><legend>Debug: row</legend>\n";
var_dump($row);
echo "</fieldset>\n";

$num_records = $row[0];
echo 'testing $numrecords ', $numrecords ;
// Calculate the number of pages
if ($num_records > $display) { // More than 1 page.
	$num_pages = ceil ($num_records/$display);
} else {
	$num_pages = 1;
}
print?

Posted: Tue Aug 01, 2006 4:22 am
by FE
the error i get is:

Debug
query:SELECT COUNT(*) FROM GRN_ITEM WHERE DATE BETWEEN '2006-02-02' AND '2006-08-01' AND STOCK_CODE LIKE 'A%'
result:Resource id #3
errormsg:

Debug: rowarray(1) { ["Expr1000"]=> string(1) "2" }

Notice: Undefined offset: 0 in d:\Inetpub\wwwroot\batch2.php on line 47
testing $numrecords
Notice: Undefined variable: numrecords in d:\Inetpub\wwwroot\batch2.php on line 56
The code is:

Code: Select all

error_reporting(E_ALL); 
ini_set('display_errors', true); 

$query = "SELECT COUNT(*) FROM GRN_ITEM WHERE DATE BETWEEN '$startdate' AND '$enddate' AND STOCK_CODE LIKE 'A%'"; 
// $connectionstring? string? isn't it a connection resource? 
$result = odbc_exec($connectionstring, $query); 

echo "<fieldset><legend>Debug</legend>\n", 
                'query:', $query, "<br />\n", 
                'result:', $result, "<br />\n", 
                'errormsg: ', odbc_errormsg(), "<br />\n", 
        "</fieldset>\n"; 
$row = odbc_fetch_array($result); 

echo "<fieldset><legend>Debug: row</legend>\n"; 
var_dump($row); 
echo "</fieldset>\n"; 

$num_records = $row[0]; 

// Calculate the number of pages 
if ($num_records > $display) { // More than 1 page. 
        $num_pages = ceil ($num_records/$display); 
} else { 
        $num_pages = 1; 
}
}
echo 'testing $numrecords ', $numrecords ;
Line 47 is:
$num_records = $row[0];


and line 56 is: echo 'testing $numrecords ', $numrecords ;

from the error message i'm guessing the sql command is not working, but i have this sql command futher down the page:


Code: Select all

$query = "SELECT GRN_NUMBER, ITEM_NUMBER, ORDER_NUMBER, STOCK_CODE, DESCRIPTION, DATE, QTY_RECEIVED FROM GRN_ITEM WHERE DATE BETWEEN '$startdate' AND '$enddate' AND STOCK_CODE LIKE 'A%'" ; 
if(!$result = odbc_do($connectionstring, $query)) 
{ 
    die('There was a database error in the query: ' . odbc_errormsg()); 
}
and it works perfectly, maybe its something to do with the count(*)? do you think its anything to do with it being an ODBC database?

Posted: Tue Aug 01, 2006 4:51 am
by JayBird
add this and post the results

Code: Select all

echo "<pre>";
print_r($row);
echo "</pre>";

Posted: Tue Aug 01, 2006 5:03 am
by FE
i get

Code: Select all

Array
(
    [Expr1000] => 2
)

Posted: Tue Aug 01, 2006 5:15 am
by JayBird
Well, that should give you the answer then.

odbc_fetch_array() returns and associative array, NOT and enumerated one.

Which means you need to change this line

Code: Select all

$num_records = $row[0];
Should be obvious what you need to change it to now