Page 4 of 10
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Tue Sep 03, 2013 10:58 am
by simonmlewis
If I use the old code below, it works.
Code: Select all
$query = "SELECT id, catid, catname, subid, subname FROM products WHERE catid = '$c' AND catid IS NOT NULL AND pause = 'off' GROUP BY subname ASC";
$result = $pdo->query($query);
while ($cat = $result->fetch(PDO::FETCH_OBJ)) {
}
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Tue Sep 03, 2013 11:02 am
by Celauran
What's the value of $result?
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Tue Sep 03, 2013 11:06 am
by simonmlewis
Using that old code:
[text]object(PDOStatement)#3 (1) { ["queryString"]=> string(136) "SELECT id, catid, catname, subid, subname FROM products WHERE catid = '535' AND catid IS NOT NULL AND pause = 'off' GROUP BY subname ASC" } [/text]
Using new code:
[text]object(PDOStatement)#3 (1) { ["queryString"]=> string(133) "SELECT id, catid, catname, subid, subname FROM products WHERE catid = :c AND catid IS NOT NULL AND pause = 'off' GROUP BY subname ASC" } [/text]
Here's an oddity, at the top where I declare the variables, $c is in red, while $s is all black.
Code: Select all
$c = isset($_GET['c']) ? $_GET['c'] : null;
$s = isset($_GET['s']) ? $_GET['s'] : null;
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Tue Sep 03, 2013 11:14 am
by Celauran
simonmlewis wrote:Here's an oddity, at the top where I declare the variables, $c is in red, while $s is all black.
That's editor-specific and doesn't mean anything to me. Why not simply pass $_GET['c'] to your prepared statement?
What is $result->execute() returning?
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Tue Sep 03, 2013 11:16 am
by simonmlewis
Code: Select all
$result->execute(array(':c' => $_GET['c']));
Like this? (fails)
APOLOGIES - that works!!
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Tue Sep 03, 2013 11:18 am
by simonmlewis
Code: Select all
$rowcount = $pdo->query("SELECT id FROM products WHERE subid = '$s' AND pause = 'off'");
$num_rows = $rowcount->rowCount();
Is this the right way to count, or is there a more efficient method?
It isn't using :s either............
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Tue Sep 03, 2013 11:20 am
by Celauran
Depends. Do you need the IDs? Otherwise, you could just SELECT COUNT(id) AS rowCount
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Tue Sep 03, 2013 11:22 am
by simonmlewis
I'm not echoing anything, just counting the rows.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Tue Sep 03, 2013 12:11 pm
by Celauran
I'd just SELECT COUNT(id) then. No need to return anything you don't need.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 4:59 am
by simonmlewis
Code: Select all
$query = "SELECT COUNT(id) AS numrows FROM products WHERE catid = :c AND pause = 'off'";
$result = $pdo->prepare($query);
$num_rows = $result->rowCount();
$row = mysql_fetch_array($result, MYSQL_ASSOC);
$numrows = $row['numrows'];
// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);
I'm trying to adjust this section now.
It's what I use for page numbering.
The big in question is:
Code: Select all
$row = mysql_fetch_array($result, MYSQL_ASSOC);
.. as I think the rest is ok.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 5:46 am
by Celauran
You've created the prepared statement but have not executed the query. You might consider bookmarking
the PDO book while you're still familiarizing yourself with it. Any function call that starts with mysql_ is definitely wrong.
If you select the IDs, then you can get the number of rows returned by using PDOStatement::rowCount like so
Code: Select all
$query = "SELECT id FROM products WHERE catid = :c AND pause = 'off'";
$stmt = $pdo->prepare($query);
$stmt->execute(array(':c' => $whatever_c_is_bound_to));
$numrows = $stmt->rowCount();
If you're specifically querying the count, though, you know that will only return a single value, so rowCount would always be 1. In this case, you want to access the result set directly
Code: Select all
$query = "SELECT COUNT(id) AS numrows FROM products WHERE catid = :c AND pause = 'off'";
$stmt = $pdo->prepare($query);
$stmt->execute(array(':c' => $whatever_c_is_bound_to));
$numrows = $stmt->fetchColumn();
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 6:59 am
by simonmlewis
This is only counting one for $num_rows.
There are at least 20!
Code: Select all
$query = "SELECT COUNT(id) AS numrows FROM products WHERE catid = :c AND pause = 'off'";
$result = $pdo->prepare($query);
$result->execute(array(':c' => $_GET['c']));
$numrows = $result->rowCount();
echo "$numrows";
$maxPage = ceil($numrows/$rowsPerPage);
$categ = "$cname";
$findcateg ="/ /";
$replacecateg ="-";
$categreplace = preg_replace ($findcateg, $replacecateg, $categ);
// print the link to access each page
$self = $_SERVER['PHP_SELF'];
$nav = '';
for($page = 1; $page <= $maxPage; $page++)
{
if ($page == $pageNum)
{
$nav .= " $page "; // no need to create a link to current page
}
else
{
$nav .= " <a href=\"/categ/page/$catid/$categreplace/$page\" class='bodylink'>$page</a>";
}
}
// creating previous and next link
// plus the link to go straight to
// the first and last page
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = " <a href=\"/categ/page/$catid/$categreplace/$page\" class='bodylink'>[Anterior]</a> ";
$first = " <a href=\"/categ/page/$catid/$categreplace/pagenum=1\" class='bodylink'>[Primera Página]</a>";
}
else
{
$prev = ' '; // we're on page one, don't print previous link
$first = ' '; // nor the first page link
}
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"/categ/page/$catid/$categreplace/$page\" class='bodylink'>[Siguiente]</a>";
$last = " <a href=\"/categ/page/$catid/$categreplace/$maxPage\" class='bodylink'>[Última]</a>";
}
else
{
$next = ' '; // we're on the last page, don't print next link
$last = ' '; // nor the last page link
}
// print the navigation link
echo "<div class='navpages'>" . $first . $prev . $nav . $next . $last . "</div>";
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 7:05 am
by Celauran
See above. I already explained this. You're querying the count directly, so the result set will always be one row with one column. You need to fetch that column. rowCount() is only useful if you're returning multiple rows.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 7:08 am
by simonmlewis
I am though... aren't I?
I'm asking how many rows there are (by counting the IDs) where catid = *number* and pause = 'off'.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 7:09 am
by simonmlewis
Apologies, I just did the other method and it works, but I really don't understand how.
The way you wrote it, it sounded like if you want the numrows to be an expected "1", then use that.