CODEWITHSUNDEEP
phpmysql
user1814946
user1814946

Reputation: 181

MySQL DB query compare to PHP variable

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'];

Upvotes: 0

Views: 350

Answers (1)

spencer7593
spencer7593

Reputation: 108370

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.

Upvotes: 1

Related Questions