mysql_num_rows problem

Anon

Hello all,

Mysql is reporting the wrong number of rows and I can't figure it out. I've looked over the code many times with no luck. I feel like the solution is easy, like the wrong syntax or something, but I am overlooking it. I need another pair of eyes to be sure.

I know that it is better to use COUNT() instead of mysql_num_rows, but count reports the wrong number of rows too so once this is fixed I can change it.

Here is the function:

function isBuilderOrphan($builder_id) {

connectToDatabase();
$orphan_sql = "SELECT builder_id
FROM new_construction
builder_id = $builder_id";
$result = mysql_query($orphan_sql) or die("Can't get builder id from orphan function!");
$numrows = mysql_num_rows($result);

// for debugging
echo $numrows . "<br>";
echo $orphan_sql . "<br>";

// if just that one entry
if ($numrows == 1) {
return true;
}
else { return false; }
}

The variable inside the function, reports the number of rows as 0. I took the SQL echoed out from the function and pasted it into the MySQL prompt and the query returned 1 row, which is correct.

So if the query doesn't die, and the SQL is correct, why is num_rows returning 0?

I tried changing variable names and freeing my mysql results, but nothing works. The funny thing is I have this exact code elsewhere and it works fine. This is such a simple function that I've done numerous times before, I just don't get it. Please help.
Thanks
-Samantha

Anon

BTW, I accidentally erased the WHERE in my sql, but it is really there in the code. One more time for the masses:

"SELECT builder_id FROM new_construction WHERE builder_id = $builder_id";

-Samantha

Anon

Are you sure you are querying the same database in the MySQL prompt as you connected to in php? It sure looks like a data issue to me, the code seems fine, although the old perl programmer in me would write the return like this instead of the if-then-else

return ($numrows == 1);

Anon

Yes I'm sure I'm querying the same db. I'm glad you see no problem in the code, I'll look into the data side more. If I find the solution I will post it.

-Samantha

Anon

Ok, so I've been trying to debug this num rows problem and I cam across something so strange I don't even know where to begin.

I'm referring to the function mentioned earlier in this post.

In the function, I was having problems with the line:

$orphan_sql = "SELECT builder_id FROM new_construction WHERE builder_id = " . $builder_id;

When I echoed it out to the screen, builder_id parsed correctly. In other words, the line read:

$orphan_sql = "SELECT builder_id FROM new_construction WHERE builder_id = 10;

But mysql_num_rows returned 0 instead of 1. I have checked all my data and code and found nothing until...

I replaced the variable builder_id in the sql with the id number 10, so that the id was entered manually. And it worked.

This makes no sense, the variable builder_id contains 10, but it only works when I manually enter the sql?!

I checked builder_id's variable type and php declared it to be a string, which should work fine. Just to check I converted the varaible to type INT, but it still didn't work. How can this be?

Please help
-Samantha

mmilano

hi samantha,

please try this:

"SELECT builder_id FROM new_construction WHERE builder_id = '$builder_id'";

if you don't put single quotes around $builder_id, then your sql is looking for a value in a field that has the name of whatever $builder_id is.

if this doesn't work, let me know.