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 »

Oh that's new. Thanks. :). Back to the other issue, have I done something silly? It actually does work - but throws the error with errors on.
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 don't see anything. $result is an object, we've confirmed that, then two lines later it apparently isn't. I'm not seeing a typo or anything silly like that.
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 »

Mmmmm. And odd that it's working too yet erroring.

So what about this:

Code: Select all

$query = "SELECT id, photoprimary, title, uk_title, price, subname, uk_subname, catname, uk_catname, subid, catid FROM products WHERE (title LIKE :q OR description LIKE :q OR uk_title LIKE :q OR uk_description LIKE :q OR romancode = :q) AND pause = 'off'";
$result = $pdo->prepare($query);
$result->execute(array(':q' => "%{$q}%"));
while ($row = $result->fetch(PDO::FETCH_OBJ)) {
This works and produces results.

This differs only in the name of the variable, but produces nothing:

Code: Select all

  $query = "SELECT id, photoprimary, title, uk_title, price, subname, uk_subname, catname, uk_catname, subid, catid FROM products WHERE (title LIKE :search OR description LIKE :search OR uk_title LIKE :search OR uk_description LIKE :search OR romancode = :search) AND pause = 'off' ORDER BY title  LIMIT $offset, $rowsPerPage";
  $result = $pdo->prepare($query);
$result->execute(array(':search' => "%{$search}%"));
$num_rows = $result->rowCount();
  while ($row = $result->fetch(PDO::FETCH_OBJ)) {
Var Dump says this:
[text]object(PDOStatement)#4 (1) { ["queryString"]=> string(297) "SELECT id, photoprimary, title, uk_title, price, subname, uk_subname, catname, uk_catname, subid, catid FROM products WHERE (title LIKE :search OR description LIKE :search OR uk_title LIKE :search OR uk_description LIKE :search OR romancode = :search) AND pause = 'off' ORDER BY title LIMIT 0, 16" }
[/text]

What does the "string(297) mean, is that a kind of error code?
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 »

I've found it. It was stopping at a point where it's asked if num_rows == 0. Not $num_rows.

DURRRR
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 »

Ok - the results from each query differ - both should be 20 when I query on "white".

Code: Select all

  $rowsPerPage = 20;
  $pageNum = 1;
  if(isset($_GET['pagenum']))
  {
      $pageNum = $_GET['pagenum'];
  }
  
  $offset = ($pageNum - 1) * $rowsPerPage;

  $query = "SELECT id, photoprimary, title, uk_title, price, subname, uk_subname, catname, uk_catname, subid, catid FROM products WHERE (title LIKE :search OR description LIKE :search OR uk_title LIKE :search OR uk_description LIKE :search OR romancode = :search) AND pause = 'off' ORDER BY title  LIMIT $offset, $rowsPerPage";
I'm doing it fully like this. The $offset and $rowsperpage and for page numbering. If I set the $rowsperpage to 16, it shows only 16 as a fully count. Yet on our other main web site, that has no reflection on the count displayed.
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 »

If I set the $rowsperpage to 16, it shows only 16 as a fully count. Yet on our other main web site, that has no reflection on the count displayed.
Have you ruled out cached results?
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 »

Cleared cache.
Changed it to 4 per page, and still it produces a "count" of four.
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 do you mean produces a count of four?
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

  $rowsPerPage = 4;
  $pageNum = 1;
  if(isset($_GET['pagenum']))
  {
      $pageNum = $_GET['pagenum'];
  }
  
  $offset = ($pageNum - 1) * $rowsPerPage;

  $query = "SELECT id, photoprimary, title, uk_title, price, subname, uk_subname, catname, uk_catname, subid, catid FROM products WHERE (title LIKE :search OR description LIKE :search OR uk_title LIKE :search OR uk_description LIKE :search OR romancode = :search) AND pause = 'off' ORDER BY title  LIMIT $offset, $rowsPerPage";
  $result = $pdo->prepare($query);
$result->execute(array(':search' => "%{$search}%"));
$num_rows = $result->rowCount();
$num_rows only shows 4.
With the same code on our other sites, it shows whatever is the actual count - not the count on the page.
It's something to do with

Code: Select all

.... LIMIT $offset, $rowsPerPage...
And how PDO uses it I think. As other sites don't do this problem. That $rowsperpage part in the code, does what it is named.
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're asking it to return four rows and it's returning four rows. I don't understand what the problem is.
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 »

Well that same code, but not using 'PDO' with the the LIMIT, works the way all our sites do.
This is why I am confused. It should put up a count of ALL products, but limit those shown per page.
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 »

No, it shouldn't. You're not requesting the count of all the items in the database table, you're specifically asking for the number of rows returned by this particular query, which you have specifically limited to four results. If you'd like to post the two queries side by side, we can compare them and see where the differences lie.
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 »

Oh goodness yes, that other version has two queries. The first is for the count, the second is for the rows per page.

Sorry. It can take a different view point to really see these things at times.
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 »

How do I do a MATCH?

Code: Select all

$query = "SELECT * FROM faq WHERE MATCH (answer) AGAINST ('%$faq%')";
  $result = $pdo->prepare($query);
$result->execute(array(':faq' => "%{$faq}%"));
$num_rows = $result->rowCount();
Is this correct ??
It seems wrong in the "AGAINST" part of the query....even tho it works..
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're passing the variable directly into the query rather than using a placeholder.
Post Reply