1
I am making a pagination, but it is generating a memory error.
Is there any way to optimize or fix the fault? Or is it on the same server?
Note: the php.ini
this set the memory in 512.
if (!(isset($_GET['pagenum']))) {
$pagenum = 1;
} else {
$pagenum = $_GET['pagenum'];
}
$page_limit = ($_GET["show"] <> "" && is_numeric($_GET["show"]) ) ? $_GET["show"] : 30;
try {
$keyword = trim($_GET["keyword"]);
if (!empty($keyword)) {
$sql = "SELECT * FROM noticias WHERE conteudo LIKE :keyword OR titulo LIKE :keyword ORDER BY Nid DESC";
$stmt = $DB->prepare($sql);
$likekeyword = "%".$keyword."%";
$stmt->bindParam(':keyword', $likekeyword, PDO::PARAM_STR);
} else {
$sql = "SELECT * FROM noticias WHERE 1 ORDER BY Nid DESC";
$stmt = $DB->prepare($sql);
}
$stmt->execute();
$total_count = count($stmt->fetchAll());
$last = ceil($total_count / $page_limit);
if ($pagenum < 1) {
$pagenum = 1;
} elseif ($pagenum > $last) {
$pagenum = $last;
}
$lower_limit = ($pagenum - 1) * $page_limit;
$lower_limit = ($lower_limit < 0) ? 0 : $lower_limit;
$sql2 = $sql . " limit " . ($lower_limit) . " , " . ($page_limit) . " ";
$stmt = $DB->prepare($sql2);
if ($keyword <> "" ) {
$stmt->bindParam(':keyword', $likekeyword, PDO::PARAM_STR);
}
$stmt->execute();
$results = $stmt->fetchAll();
} catch (Exception $ex) {
echo $ex->getMessage();
}
code displays counters
<div class="col-lg-12 center">
<ul class="pagination pagination-sm">
<?php
for ($i = 1; $i <= $last; $i++) {
if ($i == $pagenum) {
?>
<li class="active"><a href="javascript:void(0);" ><?php echo $i ?></a></li>
<?php
} else {
?>
<li><a href="noticias_listar.php?pagenum=<?php echo $i; ?>&keyword=<?php echo $_GET["keyword"]; ?>" class="links" onclick="displayRecords('<?php echo $page_limit; ?>', '<?php echo $i; ?>');" ><?php echo $i ?></a></li>
<?php
}
}
?>
</ul>
</div>
You need to add the
limit
in that select with twolikes
.– rray
either limit and difinifo by the $page_limit variable, or it’s the way I did it wrong?
– Arsom Nolasco
At your appointment, after
ORDER BY Nid DESC
addLIMIT 0, 70
just for testing see if the error will happen. The way the query is returned is the largest number of records, with thislimit
only the first 70 will be returned, of course you need to change this mechanism to work dynamically. See that answer– rray
putting in the select with 2 Likes the error continued the same already putting in the other was the following -> 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 'limit 0 , 30' at line 1
– Arsom Nolasco
Put there the query that was wrong.
– rray
And another you are running the same query twice ... one without limit and the other with.
– rray
rray $sql = "SELECT * FROM noticias WHERE 1 ORDER BY Nid DESC LIMIT 0,30"; in case I am running 2 pq if the user does not type anything it lists everything if type in the search field it searches with the other
– Arsom Nolasco
I slapped the question :)
– Wallace Maxters
The way the system is is more likely to receive errors than the expected result, an obvious example is this -
$total_count = count($stmt->fetchAll());
- that can cause overload, and still maintains the values, and then another query is executed, with the methodfetchAll()
. There are own functions to count the number of lines found, and there are also clauses own to count the number of lines, examples are the functionCOUNT
of SQL and thefetchColumns
or simplyrowCount
to thePDO
.– Edilson
$total_count = count($stmt->fetchAll());
more here is not using exactly Count? there is difference between rowCount($stmt->fetchAll()) and Count($stmt->fetchAll())?– Arsom Nolasco
There is, and much, being that the
count
iterates each array position, object properties, and method rowCount is basically an additive to consultation, that is, it already exists at the time a query is executed, and besides, it is not used with thefetchAll
, is used directly in the consultation$stmt->rowCount
, as the function columnCount. Another option is a native SQL function - COUNT.– Edilson