New to PDO: basic script failing. Including ORDER BY...
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...
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.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
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...
Mmmmm. And odd that it's working too yet erroring.
So what about this:
This works and produces results.
This differs only in the name of the variable, but produces nothing:
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?
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 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)) {[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.
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...
I've found it. It was stopping at a point where it's asked if num_rows == 0. Not $num_rows.
DURRRR
DURRRR
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
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...
Ok - the results from each query differ - both should be 20 when I query on "white".
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.
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";Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
Have you ruled out cached results?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.
-
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...
Cleared cache.
Changed it to 4 per page, and still it produces a "count" of four.
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.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
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...
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();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...Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
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...
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.
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.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
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...
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.
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.
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...
How do I do a MATCH?
Is this correct ??
It seems wrong in the "AGAINST" part of the query....even tho it works..
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();
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.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
You're passing the variable directly into the query rather than using a placeholder.