MySQL查询中有多个PHP变量

I'm trying to edit some PHP code I recieved which fetches data from a mysql database, it is essentially getting data and formatting it into a json object. The query use a php variable called "start". What I can't get to work is to make it consider another variable in the php file in the mysql query called klass, it just returns an empty json array when I add it, indicating there is something wrong with the query passed to the database.

我正在尝试编辑我收到的一些从mysql数据库中获取数据的PHP代码,它本质上是获取数据并将其格式化为json对象。该查询使用名为“start”的php变量。我无法工作的是让它考虑在mysql查询中称为klass的php文件中的另一个变量,当我添加它时它只返回一个空的json数组,表明传递给数据库的查询有问题。

Here is the original code:

这是原始代码:

<?php

include_once 'db.php';
//getting the varibles into PHP

$start = 0;
if(isset($_GET['start'])) {
     $start = $_GET['start'];
}

if(isset($_GET['klass'])) {
 $klass= $_GET['klass'];
}
//sql statement
$sql = "SELECT * FROM `table` WHERE `column2` > 0 ORDER BY `column1` ASC LIMIT :start ,30";

$stmt = $pdb->prepare($sql);
$stmt->bindValue(':start', (int) $start, PDO::PARAM_INT);
$stmt->execute();

//putting data into array
$data = array();
while($row = $stmt->fetch()) {
 $data[] = array(
      'id' => $row['id'],
      'column1' => $row['column1'],
      'column2' => $row['column2'], 
      'column3' => $row['column3'],
      'column4' => $row['column4'],
      'column5' => $row['column5']
      );
 $start++;
}

//send back data in json format
echo json_encode(array('nextAmt' => $start, 'laxor' => $data));
?>

This works perfectly fine and works great, and sends the correct data back to the javascript jquery.post function.

这非常好用,效果很好,并将正确的数据发送回javascript jquery.post函数。

But I've tried to just add a PHP varible into the query like this:

但我试图在这样的查询中添加一个PHP变量:

//sql statement

$sql = "SELECT * FROM `table` WHERE `column2` > 0 AND `klass` = " . $klass . "ORDER BY `column1` ASC LIMIT :start ,30";

And using another binValue like this

并使用另一个像这样的binValue

//sql statement

$sql = "SELECT * FROM `table` WHERE `column2` > 0 AND `klass` = :klass ORDER BY `column1` ASC LIMIT :start ,30";

$stmt = $pdb->prepare($sql);
$stmt->bindValue(':start', (int) $start, PDO::PARAM_INT);
$stmt->bindValue(':klass', (str) $klass, PDO::PARAM_INT);
$stmt->execute();

And none of them seems to be working, i don't think that the "klass" varible gets inserted into the query correctly.

并且它们似乎都没有工作,我不认为“klass”变量被正确地插入到查询中。

How should i do it in the right way?

我该如何以正确的方式做到这一点?

Thanks!

5 个解决方案

#1

String need to be in quote like for

字符串需要在引用中

`klass` = " . $klass . "

you should use

你应该使用

`klass` = '" . $klass . "'

for all string you need to add quote.

对于所有字符串,您需要添加引号。

#2

`klass` = " . $klass . "ORDER BY `column1`
                        ^ You have missed a whitespace after end of double quote

Replace your query with this one

用这个替换您的查询

$sql = "SELECT * FROM `table` 
  WHERE `column2` > 0 AND `klass` = " . $klass . " 
  ORDER BY `column1` ASC LIMIT :start ,30";

#3

Your last way is correct. Is $klass actually an numerical value? if not, try PDO::PARAM_STR instead of PDO::PARAM_INT and remove the (int) cast.

你的最后一条路是正确的。 $ klass实际上是一个数值吗?如果没有,请尝试PDO :: PARAM_STR而不是PDO :: PARAM_INT并删除(int)强制转换。

You are already using prepared statements, so stay with it and don't add PHP variables directly. You are opening your script for SQL injections again.

您已经在使用预准备语句,因此请继续使用它并且不要直接添加PHP变量。您正在再次打开SQL注入脚本。

#4

Okay, on this one you are missing a whitespace after $klass variable, I fixed it for you here

好的,在$ klass变量之后,你错过了一个空格,我在这里修复了它

//sql statement

$sql = "SELECT * FROM `table` WHERE `column2` > 0 AND `klass` = {$klass} ORDER BY `column1` ASC LIMIT {$start} ,30";

#5

Bad thing is, you're setting $klass only when is set $_GET['klass'], but use it in any situation no matter is defined or not.

不好的是,你只在设置$ _GET ['klass']时才设置$ klass,但无论是否定义,都可以在任何情况下使用它。

I'm sure your result query is something like:

我确定你的结果查询是这样的:

SELECT * 
FROM `table` 
WHERE 
    `column2` > 0 AND 
    `klass` = 0 
ORDER BY `column1` ASC 
LIMIT 0 ,30

It is little trick in php: (int) for undefined variable returns 0 (and you did this trick in bindValue — (int) $klass). For example:

这是php中的小技巧:(int)对于未定义的变量返回0(你在bindValue中做了这个技巧 - (int)$ klass)。例如:

$a = (int)$b;
echo $a;
//Outputs 0, even if $b was not defined. No errors/warnings

So, you trying to get all rows where klass is 0. I think you have no records in the table where klass is zero. As a result, you've received empty result.

所以,你试图得到klass为0的所有行。我认为你没有klass为零的表中的记录。结果,您收到了空的结果。

I would rewrite your code in this manner:

我会以这种方式重写你的代码:

$klass_piece = isset($klass) ? ' AND `klass` = :klass' : '';
$sql = "SELECT * FROM `table` WHERE `column2` > 0" . $klass_piece . " ORDER BY `column1` ASC LIMIT :start ,30";

$stmt = $pdb->prepare($sql);
$stmt->bindValue(':start', (int) $start, PDO::PARAM_INT);

if (isset($klass)) {
    $stmt->bindValue(':klass', (int) $klass, PDO::PARAM_INT);
}

$stmt->execute();

#1