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 »

How do I do a placeholder in "AGAINST" queries?
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 for INSERTs, is this correct?

Code: Select all

$updatesite = $pdo->exec("INSERT INTO rating (prodid, stars) VALUES ('$myprod', '5')");
I read that it should be, but does PDO not use placeholders??
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 for INSERTs, is this correct?

Code: Select all

$updatesite = $pdo->exec("INSERT INTO rating (prodid, stars) VALUES ('$myprod', '5')");
I read that it should be, but does PDO not use placeholders??
There's no escaping happening here. This is a bad idea. Use prepared statements with placeholders. The type of query doesn't change anything.
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 you do placeholders when the variables are like this: ('$myprod', .....) ? As you cannot do it like this: =:myprod.
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

$STH = $DBH->prepare("INSERT INTO foods ( name ) VALUES ( :name )");
$STH->bindParam(':name', $name);
$STH->execute();
Like this?
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 »

That works. Or just pass in the array to execute.

Code: Select all

$query = "INSERT INTO foods ( name ) VALUES ( :name )";
$STH = $DBH->prepare($query);
$STH->execute(array(':name' => $name));
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 »

Like this.....

Code: Select all

$queryhit = "INSERT INTO usercomments (prodid, dateentered, nickname, prodname, usercomments, ipaddress, email) VALUES (:prodid, :todaydate, :nickname, :prodname, :usercomments, :ipaddressnew, :usercommentsemail)";

$stmt = $pdo->prepare($queryhit);
$stmt->execute(array(':prodid' => $prodid, ':todaydate' => $todaydate, ':nickname' => $nickname, ':prodname' => $prodname, ':usercomments' => $usercomments, ':ipaddressnew' => $ipaddressnew, ':usercommentsemail' => $usercommentsemail));
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 »

That looks right. What isn't working?
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 »

It is working - just wondered if I had done it right.

Can you look at this "average" issue please?

Code: Select all

 $queryrating = ("SELECT prodid, AVG(stars) FROM rating WHERE prodid =:myprod GROUP BY prodid");
$rating = $pdo->prepare($queryrating);
$rating->execute(array(':myprod' => $myprod));
while ($rowrate = $rating->fetch(PDO::FETCH_OBJ)) 
      {
  $starrating = number_format($rowrate['AVG(stars)']);
}
[text]Fatal error: Cannot use object of type stdClass as array [/text]
It's on the final line about number_format.
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 »

As the error says, you're fetching an object (PDO::FETCH_OBJ) but trying to use it as though it were an array ($rowrate['AVG(stars)']). You'll need to either fetch an array (PDO::FETCH_ASSOC) or use object notation ($rowrate-> ). Speaking of which, why not name the result something more meaningful?

Code: Select all

$queryrating = "SELECT prodid, AVG(stars) AS average FROM rating WHERE prodid =:myprod GROUP BY prodid";
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 »

Good point about the naming.
$starrating = number_format($rowrate->AVG(stars));
This doesn't work 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 »

$rowrate->average will once you've cleaned up the naming.
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 »

Celauran wrote:Same as usual.

Code: Select all

$query = "UPDATE products SET hitcount = (hitcount + 1) WHERE id = :myprod";
$stmt = $sql->prepare($query);
$stmt->execute(array(':myprod' => $whatever));
Why would this not work in PHP MY ADMIN:

Code: Select all

UPDATE products SET hitcount = (hitcount + 1) WHERE id = '9'
9 is a row number, and the field is there.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Eric!
DevNet Resident
Posts: 1146
Joined: Sun Jun 14, 2009 3:13 pm

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

Post by Eric! »

That type of update works fine for me in version 4.0.4.1.
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 »

It actually works now.
I think this is the cause. The field type was set to NULL as default. Some had numbers in them already (possibly thru forced testing).
This one had NULL.
I set all thsoe where the field was NULL to be 0, but it didn't work. Once I manually set it to be 0, it then began to work.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply