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
0
String need to be in quote like for
字符串需要在引用中
`klass` = " . $klass . "
you should use
你应该使用
`klass` = '" . $klass . "'
for all string you need to add quote.
对于所有字符串,您需要添加引号。
#2
0
`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
0
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
0
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
0
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
0
String need to be in quote like for
字符串需要在引用中
`klass` = " . $klass . "
you should use
你应该使用
`klass` = '" . $klass . "'
for all string you need to add quote.
对于所有字符串,您需要添加引号。
#2
0
`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
0
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
0
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
0
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();