douxin1163
2016-03-28 16:04
浏览 51
已采纳

在MySQL中输出错误

I am writing an Android app from which I have to retrieve the data from MySQL. But I am getting the wrong output.

The table name is blood and the structure is as follows

structure of the table

The php code is as follows

<?php
   $con=mysqli_connect("server","username","password","databasename");

$city = "HYDERABAD";
$blood = "APOSITIVE";

$sql = "select name,blood,age,lastdonated,mobile,email,state,city,sex from blood where upper(blood) = '$blood' OR lower(blood) = '$blood' AND upper(city) = '$city' ORDER BY id DESC LIMIT 1000 ";

$res = mysqli_query($con,$sql);

$result = array();

while($row = mysqli_fetch_array($res)){
array_push($result,
array('name'=>$row[0],
'blood'=>$row[1],
'age'=>$row[2],
'donated'=>$row[3],
'number'=>$row[4],
'email'=>$row[5],
'state'=>$row[6],
'city'=>$row[7]
));
}

echo json_encode(array("feedAnegative"=>$result));

mysqli_close($con);

?>

The output is 

{
   "feedAnegative":[
      {
         "name":"saicharan",
         "blood":"APOSITIVE",
         "age":"19",
         "donated":"0000-00-00",
         "number":"8794511258",
         "email":"dsaicharan219@gmail.com",
         "state":"andhrapradesh",
         "city":"vijayawada"
      },
      {
         "name":"b",
         "blood":"Apositive",
         "age":"20",
         "donated":"0000-00-00",
         "number":"1234567892",
         "email":"b@gmail.com",
         "state":"Telangana",
         "city":"khammam"
      },
      {
         "name":"a",
         "blood":"APOSITIVE",
         "age":"20",
         "donated":"0000-00-00",
         "number":"1245863921",
         "email":"a@gmail.com",
         "state":"telangana",
         "city":"hyderabad"
      }
   ]
}

In the above json feed I should just get the data related to city = hyderabad. But I am getting all the records in the table where blood is APOSITIVE. Where is the error?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

1条回答 默认 最新

  • dpz3471 2016-03-28 16:30
    已采纳

    What determines rows in result-set is the SQL query sent to MySQL, not the PHP code around:

    select name,blood,age,lastdonated,mobile,email,state,city,sex
from blood
where upper(blood) = 'APOSITIVE' OR lower(blood) = 'APOSITIVE' AND upper(city) = 'HYDERABAD'
ORDER BY id DESC
LIMIT 1000

    If you don't use parenthesis to set operator priority, default ones apply:

    Operator precedences are shown in the following list, from highest precedence to the lowest. Operators that are shown together on a line have the same precedence.

    INTERVAL
BINARY, COLLATE
!
- (unary minus), ~ (unary bit inversion)
^
*, /, DIV, %, MOD
-, +
<<, >>
&
|
= (comparison), <=>, >=, >, <=, <, <>, !=, IS, LIKE, REGEXP, IN
BETWEEN, CASE, WHEN, THEN, ELSE
NOT
AND, &&
XOR
OR, ||
= (assignment), :=

    So your WHERE clause is equivalent to:

    where upper(blood) = 'APOSITIVE' OR
    (lower(blood) = 'APOSITIVE' AND upper(city) = 'HYDERABAD')

    Additionally, the blood column has latin1_general_ci collation so lower(blood) = 'APOSITIVE' can actually match (no idea why it's there to begin with).

    点赞 评论

报告相同问题？

提交

相关推荐 更多相似问题