Helpex - Trao đổi & giúp đỡ Đăng nhập
8

$sql = "SELECT sql_calc_found_rows * FROM members".
       " ORDER BY username LIMIT :startRow, :numRows";

try {
    $st = $conn->prepare($sql);
    $st->bindParam(":startRow", $startRow, PDO::PARAM_INT);
    $st->bindParam(":numRows", $numRows, PDO::PARAM_INT);
    $st->execute();
} catch (PDOException $e) {
    die("Query failed: " . $e->getMessage());
}

Here I get error:

Query failed: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''5'' at line 1.

The LIMIT :startRow, :numRows has problem in :numRows.

I have tried both $st->bindParam and $st->bindValue, both didn't work.

8 hữu ích 5 bình luận 1.2k xem chia sẻ
0

I think the problem is with TBL_MEMBERS I suppose this is a view(subselect). So, if you have product table for example and you want to execute following statement:

select sql_calc_found_rows * from select id, code, name, slug, info from products order by code

you will receive following error:

SQL Error (1064): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'select id, code, name, slug, info from products order by code' at line 1

But if you change query to:

select sql_calc_found_rows * from (select id, code, name, slug, info from products) v order by code

this will work.

To summarize, TBL_MEMBERS is a view which should be put in parenthesis and given alias(i my example alias is 'v')

0 hữu ích 1 bình luận chia sẻ
0

I recommend to look at the SQL query text what PDO actually produces. You can do this with help of MySQL's general query log.

Most probably, the formal types of $startRow and/or $numRows are strings, not integers, and resulting query is therefore something like LIMIT '0', '5' (syntax error) instead of LIMIT 0, 5 (correct).

The thing is, even with PDO::PARAM_INT, when the parameter's formal type is not integer (is_int returns false), PDO wraps it in quotes. So, you have to cast parameters to integers before binding them (e.g. using intval):

$st->bindParam(":startRow", intval(trim($startRow)), PDO::PARAM_INT);
$st->bindParam(":numRows", intval(trim($numRows)), PDO::PARAM_INT);
0 hữu ích 0 bình luận chia sẻ
-2

I solved it.I Type casted the :numRows placeholder.

$numRows=(int)$numRows;
$sql = 'SELECT sql_calc_found_rows * FROM ' . 
    TBL_MEMBERS .'ORDER BY'.  $order .'LIMIT :startRow,:numRows';
    try {
        $st = $conn->prepare($sql);
        $st->bindValue(":startRow", $startRow, PDO::PARAM_INT);
        $st->bindValue(":numRows", $numRows, PDO::PARAM_INT);
        $st->execute();
    ...

And it worked. I also noticed the ' should be use instead of ".

-2 hữu ích 0 bình luận chia sẻ
loading
Không tìm thấy câu trả lời bạn tìm kiếm? Duyệt qua các câu hỏi được gắn thẻ php mysql pdo , hoặc hỏi câu hỏi của bạn.

Có thể bạn quan tâm

loading