Problems with ODBC_exec

[Garudzo]

I am connecting to a Microsoft Access via ODBC and I am connecting successfully. When I try to query a table using the SQL statement illustrated in my code I get no results even for values that are in the table. If I echo my query statement $q and put it in MS office IDE it gives me some results can anyone help?
[php]
$conn=ODBC_connect('Msc', 'user', 'password');
if ($conn)
{
$q = "
SELECT * FROM tblUsers WHERE UserName='$user' AND Pasword='$pword'";
$r = odbc_exec($conn,$q);
if (odbc_num_rows($r) == 1) {
return TRUE;
} else {
return FALSE;
}[/php]

[ronverdonk]

Please enclose any code within the proper code tags. See the Posting Guidelines on how to do that.

MODERATOR

[ronverdonk]

When your quere results in an error, you do not catch it. So make your statement[php]$r = odbc_exec($conn,$q) or die("Error :".odbc_error_msg());[/php]Then you will see the error in the select statement.

Ronald

[Garudzo]

When your quere results in an error, you do not catch it. So make your statement[php]$r = odbc_exec($conn,$q) or die("Error :".odbc_error_msg());[/php]Then you will see the error in the select statement.

Ronald

It is not giving an error. Infact it was giving an error when I used double quotes to enclose my variables. I corrected that and put single quotes then its not giving any error and it does not give the correct result

[code green]

it does not give the correct result

It does give the correct result but you don't understand it
Try some debugging [PHP]$conn = ODBC_connect('Msc', 'user', 'password') or die(odbc_errormsg());
$q = "SELECT * FROM tblUsers WHERE UserName='$user'
AND Pasword='$pword'";
echo $sql;
if($r = odbc_exec($conn,$q))
echo odbc_num_rows($r);
else
return FALSE;
[/PHP] Is the field name correct?

 Pasword 

[ronverdonk]

I saw that also, but then the die() on the SELECT should have given the error. And according to OP it gives no error.

Ronald

[code green]

Hi ronverdonk
I am more worried about this [PHP]if (odbc_num_rows($r) == 1) {
return TRUE;
} else {
return FALSE;[/PHP] Too defintive. That's why I suggested [PHP]echo $sql;
if($r = odbc_exec($conn,$q))
echo odbc_num_rows($r);
else
return FALSE;[/PHP]

[Garudzo]

Hi ronverdonk
I am more worried about this [PHP]if (odbc_num_rows($r) == 1) {
return TRUE;
} else {
return FALSE;[/PHP] Too defintive. That's why I suggested [PHP]echo $sql;
if($r = odbc_exec($conn,$q))
echo odbc_num_rows($r);
else
return FALSE;[/PHP]

The echo [PHP]odbc_num_rows($r) [/PHP] gives -1

[ronverdonk]

Let's stick with the standard PHP documentation for this moment. It states explicitly:

odbc_num_rows() will return the number of rows in an ODBC result. This function will return -1 on error. For INSERT, UPDATE and DELETE statements odbc_num_rows() returns the number of rows affected. For a SELECT clause this can be the number of rows available.

Note: Using odbc_num_rows() to determine the number of rows available after a SELECT will return -1 with many drivers.

So your driver could be the cause. But there is a work-around, from the same doc:

This function (odbc_num_rows) returns -1 when ODBCing to MS SQL and making it hard to get the number of rows in the returned recordset.

Two workarounds:
1. When you just need to verify that any rows returned from your query you can use select count(*) as cnt from table and then just get $row['cnt']
2. When you need to actually loop through the records this function returns number of rows in the recordset if and only if you include ORDER BY clause in your query statement.

That sounds a bit annoying but thats the work around when dealing with MS SQL odbc driver I guess.

I must assume it is a driver error, since the code sounds fine and you do not get an error at the select. So try this work around and see if that works. I don't think it is a solution, but that way you can at least see that your odbc works on that table.

Ronald