Geek asks and answers

PHP PDO error while trying to parameterize SQL LIMIT: offset ,: display?

This query returns 5 results in phpMyAdmin:

SELECT * FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT 0,5

And this returns count = 12 in phpMyAdmin (this is normal because there are 12 entries):

SELECT COUNT(*) AS count FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT 0,5

This function worked fine before I added two variables (offset, display), but now it will not work, and printing the variables leads to offset = 0, display = 5 (so what else LIMIT 0,5).

function getProducts($offset, $display) {
    $sql = "SELECT * FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT ?,?;";
    $data = array((int)$offset, (int)$display);
    $rows = dbRowsCount($sql, $data);
logErrors("getProducts(".$offset.",".$display.") returned ".$rows." rows.");
    if ($rows > 0) {
        dbQuery($sql, $data);
        return dbFetchAll();
    } else {
        return null;
    }
}

It does not work, because my method dbRowsCount(...)returned an empty string (stupidity PDOStatement::fetchColumn), so I changed it to return the counter with PDO::FETCH_ASSOCand it returns count = 0.

Here is a function that performs line counting:

function dbRowsCount($sql, $data) {
    global $db, $query;
    $regex = '/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/is';
    if (preg_match($regex, $sql, $output) > 0) {
        $query = $db->prepare("SELECT COUNT(*) AS count FROM {$output[1]}");
logErrors("Regex output: "."SELECT COUNT(*) AS count FROM {$output[1]}");
        $query->setFetchMode(PDO::FETCH_ASSOC);
        if ($data != null) $query->execute($data); else $query->execute();
        if (!$query) {
            echo "Oops! There was an error: PDOStatement returned false.";
            exit;
        }
        $result = $query->fetch();
        return (int)$result["count"];
    } else {
logErrors("Regex did not match: ".$sql);
    }
    return -1;
}

My error log gives me this result from the program:

: SELECT COUNT (*) AS count FROM tbl_product 1 ORDER BY last_update DESC LIMIT?,?;
getProducts (0,5) 0 .

, SQL , 0 5. .

- , ?

Update

, , :

function dbDebugTest() {
    global $db;
    $stmt = $db->query("SELECT COUNT(*) AS count FROM tbl_product WHERE 1 ORDER BY last_update LIMIT 0,5;");
    $result = $stmt->fetch();
    $rows = (int)$result["count"];
    logErrors("dbDebugTest() returned rows=".$rows);
}

:

> dbDebugTest() returned rows=12

, != null ! == null, $data:

logErrors("Data: ".implode(",",$data));
if ($data !== null) $query->execute($data); else $query->execute();

:

> Data: 0,5

dbRowsCount ($ sql, $data) - 0 !

2

PDOStatement, , , $query → ($ data), , .

:

function dbRowsCount($sql, $data) {
    global $db, $query;
    $regex = '/^SELECT\s+(?:ALL\s+|DISTINCT\s+)?(?:.*?)\s+FROM\s+(.*)$/is';
    if (preg_match($regex, $sql, $output) > 0) {
        $query = $db->prepare("SELECT COUNT(*) AS count FROM {$output[1]}");
logErrors("Regex output: "."SELECT COUNT(*) AS count FROM {$output[1]}");
        $query->setFetchMode(PDO::FETCH_ASSOC);
logErrors("Data: ".implode(",",$data));
        $query->execute($data);
logErrors("queryString:".$query->queryString);
logErrors("_debugQuery():".$query->_debugQuery());
        if (!$query) {
            echo "Oops! There was an error: PDOStatement returned false.";
            exit;
        }
        $result = $query->fetch();
        return (int)$result["count"];
    } else {
logErrors("Regex did not match: ".$sql);
    }
    return -1;
}

:

: SELECT COUNT() AS count FROM tbl_product_category WHERE id =?;
: 5
queryString: SELECT COUNT() AS count FROM tbl_product_category WHERE id =?;
_debugQuery(): SELECT COUNT (*) AS count FROM tbl_product_category WHERE id =?;

: SELECT COUNT (*) AS count FROM tbl_product 1 ORDER BY last_update DESC LIMIT?,?;
: 0,5
// ​​ _debugQuery

3

PDOStatement, , , getProducts(...), params .

function getProducts($offset, $display) {
    $sql = "SELECT * FROM tbl_product WHERE 1 ORDER BY last_update DESC LIMIT :offset, :display;";
    $data = array(':offset'=>$offset, ':display'=>$display);
    $rows = dbRowsCount($sql, $data);
logErrors("getProducts(".$offset.",".$display.") returned ".$rows." rows.");
    if ($rows > 0) {
        dbQuery($sql, $data);
        return dbFetchAll();
    } else {
        return null;
    }
}

:

: SELECT COUNT (*) AS count FROM tbl_product 1 ORDER BY last_update DESC LIMIT: offset,: display;
: 0,5
// $query- > execute ($ data) logErrors("getProducts(".$offset."...))

4

dbDebugTest 0,5 SQL. , :

function dbDebugTest($offset, $display) {
    logErrors("Beginning dbDebugTest()");
    global $db;
    $stmt = $db->prepare("SELECT COUNT(*) AS count FROM tbl_product WHERE 1 ORDER BY last_update LIMIT :offset,:display;");
    $stmt->bindParam(':offset', $offset, PDO::PARAM_INT);
    $stmt->bindParam(':display', $display, PDO::PARAM_INT);
    if ($stmt->execute()) {
      $result = $stmt->fetch();
      $rows = (int)$result["count"];
      logErrors("dbDebugTest() returned rows=".$rows);
    } else {
      logErrors("dbDebugTest() failed!");
    }
}

, :

dbDebugTest()

5

( ), :

$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

dbDebugTest() Update 4!

dbDebugTest() dbDebugTest() = 12

- :

[warn] mod_fcgid: stderr: PHP :
"PDOException" "SQLSTATE [42000]:
: 1064 SQL.
, MySQL,
'' 0 ',' 5 '' 1 '
/home/linweb09/b/example.com-1050548206/user/my_program/database/dal.php:36

36 dbRowsCount(...), $query->execute($data).

getProducts(...) - , , params '' 0 '' 5 '' ( ?). , dal.php, - bindParam.

, @Travesty3 @eggyal ! .

+3
2

2 , execute, , . PDO, , PDO:: ERRMODE_SILENT, , .

, , , LIMIT , .

, , bindValue. , , - .

, try-catch, MySQL.

bindValue:

if ($data !== null)
{
    for ($i=0; $i<count($data); $i++)
        $query->bindValue($i+1, $data[$i], PDO::PARAM_INT);
    $query->execute($data);
}
else
    $query->execute();
+1

, $data NULL , (. PHP , NULL ). ===/!==, is_null().

, Travesty3, , , empty().

+1

More articles:


All Articles