MySQL DB query compare to PHP variable

Question

I am very new to PHP and only have a class from a year ago where I touched MySQL.

I am trying to add a check in some existing code to query a db table for a value, and if that value is = to 1, change a variable in the code. Seems simple enough but it's not working out. I am getting 0 results from my query, even though the query works as expected in Sequel Pro.

I am modeling my syntax after the existing query even though I don't fully understand the prepare and execute functions, because I don't want to create a new db connection to make it easier on myself. I'll give the snippets that matter, I think.

My question: Why is this not returning results, when it works fine in the database directly? The query should return 2 results, in the form of Integers, which I want to compare to another integer, $friend_uid.

$dbObj = new sdb("mysql:host=".DB_HOST.";dbname=".DB_NAME, DB_USERNAME, DB_PASSWORD);
$dbObj->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$newStatus = 'REQUEST_PENDING';
$botquery = "SELECT `KAP_USER_MAIN.UID` FROM `KAP_USER_MAIN` WHERE `KAP_USER_MAIN.IS_BOT` = 1";

$botstatement = $dbObj->prepare($botquery, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$botstatement->execute();
$posts[]= "sql error " . mysql_error();
if(!$botstatement){
    $posts[] = "failed bot query: " . mysql_error();                        
}
$num_rows = mysql_num_rows($botstatement);
if ($num_rows == false) {
    $num_rows = 0;
}
$posts[] = "$num_rows rows";
while($row = mysql_fetch_array($botstatement)) {
 if($row[0]['UID'] == $friend_uid){
    $newStatus = 'FRIENDS';
 }  
}                           
$statement->execute(array(':uid'=>$uid,':friend_uid'=>$friend_uid,':status'=>$newStatus));

Here is an example of a query from the existing code that works just fine, which I am modeling after:

$query = "SELECT kits.TOTAL_UNIT,kum.ENERGY,kum.NAME,kum.LEVEL FROM KAP_USER_MAIN kum,KNP_INVENTORY_TRANSACTION_SUMMARY kits WHERE kits.UID = :uid AND kits.INV_ID = '10004' and kum.UID = :uid";
        $statement = $dbObj->prepare($query, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
        $statement->execute(array(':uid'=>$uid));
        $res = $statement->fetchAll(PDO::FETCH_ASSOC);
        $sender_name = $res[0]['NAME'];

How would this return two things if you only select one field? SELECT KAP_USER_MAIN.UID FROM [...]. Why are you executing an undefined statement $statement->execute([...]) — Félix Adriyel Gagnon-Grenier
– Félix Adriyel Gagnon-Grenier, Commented Sep 28, 2014 at 1:25
There are two results that match the requirements of the query, they should both be returned. The problem is nothing is being returned. Statement is identified in the prepare line above execute. If you know how to fix it I am all ears as I am very new to this, as I said. — user1814946
– user1814946, Commented Sep 28, 2014 at 1:39
Don't mix PDO and mysql interface calls. That is, the calls to functions mysql_num_rows and mysql_error should be removed, and replaced with the equivalent PDO functionality. — spencer7593
– spencer7593, Commented Sep 28, 2014 at 2:48
I've removed them. That doesn't actually fix the problem, however. Do you have an idea how I can fix the problem and get the information returned? Those calls were added to troubleshoot the issue, they didn't cause it. — user1814946
– user1814946, Commented Sep 28, 2014 at 3:00

spencer7593 · Accepted Answer · 2014-09-28 19:09:09Z

1

DON'T MIX PDO AND MYSQL FUNCTIONS

Looking more closely at the code, it looks like you are mixing PDO and mysql functions.

That's not valid. Don't mix calls to the two separate interface libraries.

The mysql_fetch_array function cannot be used to fetch from a PDO statement. Use the appropriate PDO fetch functions/methods.

There are three separate and distinct MySQL interface libraries in PHP.

There's the older (and now deprecated) mysql interface, all the functions from that interface start with mysql_.

There's the improved mysqli interface. The procedural style functions all begin with mysqli_.

And thirdly, there's the more database independent PDO interface.

Do not mix calls of these three separate interface libraries, because mixing calls won't work.

It looks like you're getting a connection with PDO, preparing a statement with PDO... but you are calling the msyql_error, mysql_num_rows and mysql_fetch_array functions. Replace those calls to the mysql_ functions with the appropriate PDO functions.

DOT CHARACTER IN COLUMN NAME?

It's very strange to include a dot character in a column name. (It's not invalid to do that, but something like that wouldn't fly in our shop.)

SELECT `KAP_USER_MAIN.UID` FROM `KAP_USER_MAIN` WHERE `KAP_USER_MAIN.IS_BOT` = 1
                     ^                                              ^

But I'm suspicious that the column names are actually UID and IS_BOT, and that what you intended was:

SELECT `KAP_USER_MAIN`.`UID` FROM `KAP_USER_MAIN` WHERE `KAP_USER_MAIN`.`IS_BOT` = 1
                     ^ ^                                              ^ ^

Each identifier (the column name and the table name) can be escaped separately. The dot character between the table name and the column name should not be escaped, because that's part of the SQL text, not part of the identifier.

We typically use a short table alias in our queries, so a typical query would look like this:

SELECT m.UID FROM `KAP_USER_MAIN` m WHERE m.IS_BOT` = 1

Or, for a query equivalent to the original query (with the dot character as part of the column name), like this:

SELECT m.`KAP_USER_MAIN.UID` FROM `KAP_USER_MAIN` m WHERE m.`KAP_USER_MAIN.IS_BOT` = 1

(That's not invalid, to include a dot character in a column name, but it is an unusual pattern, one that we don't see very often. I think that's because that pattern leads to more potential problems than whatever problem it was intended to solve.)

If the query works the way it is in your code, then that dot character must be part of the column name.

edited Sep 28, 2014 at 19:09

answered Sep 28, 2014 at 1:52

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

user1814946 Over a year ago

Thank you, you are correct that I am referring to columns with my dot notation and I don't mind removing it if it's uncommon. Changing to $botquery = "SELECT m.UID FROM KAP_USER_MAIN m WHERE m.IS_BOT = 1"; Now getting an undefined mysql_error() after the execute line.

spencer7593 Over a year ago

The real problem with the code is the mixing of calls to PDO interface functions and mysql_ interface functions. The call to the mysql_num_rows function should be removed, and replaced with equivalent PDO functionality.

user1814946 Over a year ago

I've removed the dot notation as well as the mysql_error() calls. Still not getting any results on the query.

user1814946 Over a year ago

I was checking number of rows to see if I had results, but I have removed that. statement executes fine from an earlier prepare, only botstatement and query are a problem.

spencer7593 Over a year ago

The mysql_fetch_array function cannot be used to fetch from a PDO statement. Use the PDO fetch functions/methods. There are three separate and distinct MySQL interface libraries in PHP. Theres the older and now deprecated mysql interface, all the functions from that interface start with mysql_, There's the improved mysqli interface, the procedural style functions all begin with mysqli_. And thirdly, there's the more database independent PDO interface. Do not mix calls of these three libraries, because it won't work.

|

Collectives™ on Stack Overflow

MySQL DB query compare to PHP variable

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related