Page 9 of 10

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

Posted: Thu Sep 19, 2013 9:56 am
by simonmlewis
How do I do a placeholder in "AGAINST" queries?

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

Posted: Thu Sep 19, 2013 11:01 am
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??

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

Posted: Thu Sep 19, 2013 2:42 pm
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.

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

Posted: Tue Sep 24, 2013 7:01 am
by simonmlewis
How do you do placeholders when the variables are like this: ('$myprod', .....) ? As you cannot do it like this: =:myprod.

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

Posted: Tue Sep 24, 2013 7:03 am
by simonmlewis

Code: Select all

$STH = $DBH->prepare("INSERT INTO foods ( name ) VALUES ( :name )");
$STH->bindParam(':name', $name);
$STH->execute();
Like this?

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

Posted: Tue Sep 24, 2013 8:20 am
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));

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

Posted: Tue Sep 24, 2013 8:29 am
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));

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

Posted: Tue Sep 24, 2013 8:54 am
by Celauran
That looks right. What isn't working?

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

Posted: Tue Sep 24, 2013 11:10 am
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.

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

Posted: Tue Sep 24, 2013 11:15 am
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";

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

Posted: Tue Sep 24, 2013 11:19 am
by simonmlewis
Good point about the naming.
$starrating = number_format($rowrate->AVG(stars));
This doesn't work either.

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

Posted: Tue Sep 24, 2013 11:21 am
by Celauran
$rowrate->average will once you've cleaned up the naming.

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

Posted: Tue Nov 19, 2013 8:50 am
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.

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

Posted: Tue Nov 19, 2013 3:46 pm
by Eric!
That type of update works fine for me in version 4.0.4.1.

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

Posted: Tue Nov 19, 2013 3:51 pm
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.