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

Right, and MySQL returns that count as a single value. Run the query manually to see what I mean, maybe that will illustrate it better. In fact, run both queries (COUNT(id) vs. just id) so you can see the difference in result sets.
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've run the query in the other way, and it works.
If I do it the way that wasn't "working" for me, it was returning just "1".
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 »

Is there anything remotely wrong with this?

Code: Select all

$query = "SELECT DISTINCT manufacturer FROM products WHERE manufacturer = :manufacturer AND pause 'off'";
$result = $pdo->prepare($query);
$result->execute(array(':manufacturer' => $manufacturer));
while ($row = $result->fetch(PDO::FETCH_OBJ)) 
{ 
echo "<div class='head'>$row->manufacturer</div>";
}
When I do a var Dump, it shows :manufacturer in the query.
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 should work. Is it giving unexpected results? It also looks kinda pointless... you're selecting only one column and it's the same column as in your WHERE clause. Why are you querying what you already have?
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 »

Yeah the reason is, to stop someone putting in "idiot" in the URL, and making "idiot" appear on the page.
Kind of trying to be bomb proof!! But I do see your point.

No errors, just isn't producing anything at all.
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

(line 628)$queryrating = "SELECT prodid, AVG(stars) FROM rating WHERE prodid :myprod GROUP BY prodid";
629 $rating = $pdo->prepare($queryrating);
630 $rating->execute(array(':prodid' => $row->id));
631 $num_rows = $rating->rowCount();
[text]Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined in C:\xampp\phpMyAdmin\sitePDO\includes\product.inc on line 630[/text]

What have I done wrong here?
$row->id does exist as it's part of the overall page.
Do you do AVG differently in PDO??
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 »

Seeing $row-> inside your bound parameters is setting alarm bells ringing. What's going on here?
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 part of the main page query. Should that not be $row->id then, as the result of the page is running fine with that style.
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 you're running one query inside the results loop of another query. This is generally a bad idea and means you should rewrite the original query. Say the first query returns 20 rows. Now you're making 21 trips to the DB when 1 would suffice. Say the first query returns 100 rows, or 1,000...
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?
So if you run a query asking for all fields about that product.
Then you want to run queries inside that - find me the rating of "this product" in database (a).

Surely this is not wrong!!??!?!?! It works in mysql_.
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 possible using the MySQL driver (ie. mysql_ ) then it's possible using PDO. The point is that it's poor design. Why not get all the information you need up front and avoid multiple unnecessary trips to the database?
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 »

Because there are a ton of times when that is just not possible, and is easier to manage in this way.

I agree that at times using JOINs is easier, but when you do addons, it's just a simpler prospect to add an embedded query using the variables already available to query on.

So back to the original point today - what is wrong in there? I can see a row in the ratings table assigned to this product id, so how it is wrong?
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 »

Code: Select all

(line 628)$queryrating = "SELECT prodid, AVG(stars) FROM rating WHERE prodid :myprod GROUP BY prodid";
Missing equals in your WHERE clause
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

$queryrating = "SELECT prodid, AVG(stars) FROM rating WHERE prodid = :myprod GROUP BY prodid";
$rating = $pdo->prepare($queryrating);
$rating->execute(array(':prodid' => $row->id));
$num_rows = $rating->rowCount();
Same error code renders.
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 »

Don't know how I missed this before, but your placeholder is :myprod and you're passing :prodid to execute. They need to match.
Post Reply