Page 5 of 10
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 7:10 am
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 7:26 am
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".
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 8:58 am
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 9:19 am
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?
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Thu Sep 05, 2013 9:23 am
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Wed Sep 11, 2013 5:32 am
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??
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Wed Sep 11, 2013 5:55 am
by Celauran
Seeing $row-> inside your bound parameters is setting alarm bells ringing. What's going on here?
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Wed Sep 11, 2013 5:58 am
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Wed Sep 11, 2013 6:02 am
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...
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Wed Sep 11, 2013 6:09 am
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_.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Wed Sep 11, 2013 6:18 am
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?
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Wed Sep 11, 2013 6:22 am
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?
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Wed Sep 11, 2013 6:23 am
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
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Wed Sep 11, 2013 6:26 am
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.
Re: New to PDO: basic script failing. Including ORDER BY...
Posted: Wed Sep 11, 2013 6:28 am
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.