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

Post Reply
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 »

That's got it!
Thanks.
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

$counter = "SELECT hitcount FROM products WHERE id = :myprod";
$resultc = $pdo->prepare($counter);
$resultc->execute(array(':myprod' => $myprod));
while ($rowcount = $resultc->fetch(PDO::FETCH_OBJ)) 
{
echo "$rowcount->hitcount<br/>";
$counttotal = ($rowcount->hitcount + 1);
$count = $pdo->exec ("UPDATE products SET hitcount = '$counttotal' WHERE id = '$myprod'");
echo "$counttotal  ";
}
This is causing an issue.
All it should do is add 1 to a count in the product's entry in the DB. But it's adding 6 each time it loads.
If I take out this code, nothing adds, so it's only this code doing it.

IS there a way to add, via the UPDATE, collecting what the number is and adding one to it, rather than running two queries.
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 »

The SELECT is unnecessary.

Code: Select all

UPDATE products SET hitcount = (hitcount + 1) WHERE id = :myprod
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 »

Well I'll be damned. That's genius. Thanks a mill. But how do I "tell it what :myprod is, since I am not doing a SELECT ?
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 »

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));
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 »

that (with pdo, not sql) is still adding 6 each time.
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 »

Are you running this inside a loop? What's the surrounding code?
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 »

Actually no I'm not this time. This is inside an admin only part of the code. It picks up the product ID from the URL, and then does as you see it. There are other queries above and below, but are not connected or embedded with this at all.
"Hitcount" is only mentioned one other time, and that is further on where it echoes what the hitcount is.
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 »

If it's adding 6, the query is being run 6 times.
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's now moved above ALL queries. And still runs (or adds) 5 times (sorry, not 6).
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 »

In fact I have now put it right slap-bang at the top of the page before anything. It appears only after it collects $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 »

It's actually more bizarre than I first thought.
What's rendering on screen is not what is in the database. The database is always 5 ahead of what's shown. Even tho the UPDATE query runs before it shows what's in the DB.
So it's as though it IS adding one, and then adding a load more back end.

I''m even trying it like this:

Code: Select all

$queryhit = "UPDATE products SET hitcount = (hitcount + 1) WHERE id = :myprod";
$stmt = $pdo->prepare($queryhit);
$stmt->execute(array(':myprod' => $myprod));
So it's a unique $queryhit value. If I take this all out, it adds nothing. but if it's in, it adds it, and does it again "without you noticing".
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 »

The query itself isn't the problem. It's plainly only adding 1. If the hitcount field is being incremented by more than that, the query must be executing multiple times, or there is another query doing the same thing elsewhere.
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 »

I agree with your analysis. However, this query (and the page) is being loaded just one. I don't know how to make the query load more times than that, other than with a "count", which is doesn't do. And the page doesn't load more times; if it did, the area that shows how many "hitcounter" values there are would still match.
I'm totally stumped. Remove the code, it adds nothing. Put it back, it adds one on screen, and then another 4 "behind the scenes".
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 »

Ok... so I put the basic update query into a test.inc file, and it does add just one.
So somehow (god knows how) I have to find where it is adding it 5 times on the page.
"hitcount" is definitely only mentioned twice in the page. Once for that query, and once when $row->hitcount is mentioned to show the contents of that db table.
So what else do I flippin search on??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply