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 »

Maybe to test it, I could use a session. Set it to zero. Then each time it's loaded, it adds one. How do I do that within a session? mmmm
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

$query = "SELECT hitcount FROM products WHERE id = :myprod";
$result = $pdo->prepare($query);
$result->execute(array(':myprod' => $myprod));
while ($row = $result->fetch(PDO::FETCH_OBJ)) 
{
  echo "$row->hitcount before<br/><br/>";
}

$queryhit = "UPDATE products SET hitcount = (hitcount + 1) WHERE id = :myprod";
$stmt = $pdo->prepare($queryhit);
$stmt->execute(array(':myprod' => $myprod));

$query = "SELECT hitcount FROM products WHERE id = :myprod";
$result = $pdo->prepare($query);
$result->execute(array(':myprod' => $myprod));
while ($row = $result->fetch(PDO::FETCH_OBJ)) 
{
  echo "$row->hitcount after";
}
Am trying this. the result is 391 before, 392 after.
And down the page, is shows 392 as well, but in the DB it shows 396.

I've run a search on the whole folder/structure of this web site, and "hitcount" is mentioned in just 3 other files, none of which are "included" in this page.

Is there an access/query log in the latest PHP ???
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:Am trying this. the result is 391 before, 392 after.
And down the page, is shows 392 as well, but in the DB it shows 396.
This doesn't make any sense. It's pulling the value from the DB. They have to match.
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 »

Agreed.
If I then remove that code, and leave only the bit on the page that "should" be there, it then DOES show what is in the database.
It seems to add 1, then show that from the database, then add a further 4 AFTER into the database (after it has shown the previous amount).
But I promise you, "hitcount" is mentioned only in the files I said, and in fact none of those ALTER the hitcount field anyway.

I've also put the count code right at the very end of the include, to see if something happens after it, as there is no where in the template file that it queries this product in particular (tho it does in the menu, but that comes first and doesn't touch the hitcount), and that too show the current page amount and NOT what is finally left in the database.
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 »

Stepping back from this particular issue (as I just don't see ANYTHING causing this), what is wrong with this?

Code: Select all

$query = ("SELECT * FROM products WHERE pause = 'off' and fps > 50 ORDER BY fps DESC LIMIT 0,32;");
$result = $pdo->prepare($query);
while ($row = $result->fetch(PDO::FETCH_OBJ)) 
      {

}
I'm getting no errors on screen, yet errors are displayed.

IGNORE: it's because it was "query" not "prepare" in the $pdo-> part.
I assume it's 'prepare' if you are quering against a variable?
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 »

Prepare creates a prepared statement, which will handle escaping for you on execution, while query sends the query as is, without escaping, and without the need to call execute() separately. That said, yes, use query() when you aren't passing in variables and prepared statements when you are.
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 catid, catname FROM products WHERE catid = :itemtypeid GROUP BY catid");
$result = $pdo->prepare($query);
$result->execute(array(':itemtypeid' => $itemtypeid));
while ($rowcat = $result->fetch(PDO::FETCH_OBJ)) 
      {
It's not liking this.
$itemtypeid is populated.
[text]Fatal error: Call to a member function fetch() on a non-object in C:\xampp\phpMyAdmin\sitePDO\includes\a_products.inc on line 124[/text]

Line 124 is while ($rowcat = $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 »

It's complaining that $result isn't an object. What is it?
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 »

Sorry I'm not with you - it's the same as most other queries. It's all there..... isn't it ?!?!
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 »

It looks like it, but something is clearly amiss. After calling $pdo->prepare(), var_dump($result). What is it?
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 catid, catname FROM products WHERE catid = :itemtypeid GROUP BY catid");
$result = $pdo->prepare($query);
var_dump($result)

$result->execute(array(':itemtypeid' => $itemtypeid));
Parse error: syntax error, unexpected '$result' (T_VARIABLE) in C:\xampp\phpMyAdmin\sitePDO\includes\a_products.inc on line 125
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 forgot a semicolon
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 »

[text]object(PDOStatement)#4 (1) { ["queryString"]=> string(76) "SELECT catid, catname FROM products WHERE catid = :itemtypeid GROUP BY catid" } [/text]
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 »

Also how do you do you do LIKEs in PDO?

Code: Select all

....title LIKE '%".$search."%' OR description .....
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:Also how do you do you do LIKEs in PDO?

Code: Select all

$query = "SELECT foo FROM bar WHERE title LIKE :search";
$stmt = $sql->prepare($query);
$stmt->execute(array(':search' => "%{$variable}%"));
Post Reply