douye9822 2014-09-28 01:23
浏览 23
已采纳

MySQL DB查询与PHP变量进行比较

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'];
  • 写回答

1条回答 默认 最新

  • douzhankui0758 2014-09-28 01:52
    关注

    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.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 spring后端vue前端
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题
  • ¥15 Visual Studio问题
  • ¥20 求一个html代码,有偿