New to PDO: basic script failing. Including ORDER BY...
Moderator: General Moderators
Re: New to PDO: basic script failing. Including ORDER BY...
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...
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".
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.
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...
Is there anything remotely wrong with this?
When I do a var Dump, it shows :manufacturer in the query.
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>";
}Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
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...
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.
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.
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...
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();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.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
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...
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.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
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...
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_.
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.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
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...
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?
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.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
Code: Select all
(line 628)$queryrating = "SELECT prodid, AVG(stars) 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...
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();Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
All the best from the United Kingdom.
Re: New to PDO: basic script failing. Including ORDER BY...
Don't know how I missed this before, but your placeholder is :myprod and you're passing :prodid to execute. They need to match.