New to PDO: basic script failing. Including ORDER BY...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post 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)) {  
}
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post by Celauran »

What's the value of $result?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post 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;
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post 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?
Last edited by Celauran on Tue Sep 03, 2013 11:17 am, edited 1 time in total.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

Code: Select all

$result->execute(array(':c' => $_GET['c']));
Like this? (fails)

APOLOGIES - that works!!
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post 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............
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post by Celauran »

Depends. Do you need the IDs? Otherwise, you could just SELECT COUNT(id) AS rowCount
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post by simonmlewis »

I'm not echoing anything, just counting the rows.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post by Celauran »

I'd just SELECT COUNT(id) then. No need to return anything you don't need.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post 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();
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post 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  = '&nbsp;'; // we're on page one, don't print previous link
   $first = '&nbsp;'; // 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 = '&nbsp;'; // we're on the last page, don't print next link
   $last = '&nbsp;'; // nor the last page link
}
 
// print the navigation link
echo "<div class='navpages'>" . $first . $prev . $nav . $next . $last . "</div>";
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: New to PDO: basic script failing. Including ORDER BY...

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post 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'.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: New to PDO: basic script failing. Including ORDER BY...

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply