Page 8 of 10
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 9:37 am
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 9:40 am
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 9:50 am
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?
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 10:45 am
by simonmlewis
I've found it. It was stopping at a point where it's asked if num_rows == 0. Not $num_rows.
DURRRR
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 10:54 am
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 10:57 am
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?
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 11:00 am
by simonmlewis
Cleared cache.
Changed it to 4 per page, and still it produces a "count" of four.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 11:09 am
by Celauran
What do you mean produces a count of four?
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 11:23 am
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 11:26 am
by Celauran
You're asking it to return four rows and it's returning four rows. I don't understand what the problem is.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 12:12 pm
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 12:15 pm
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 12, 2013 12:24 pm
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 19, 2013 9:27 am
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..
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 19, 2013 9:53 am
by Celauran
You're passing the variable directly into the query rather than using a placeholder.