Page 1 of 1
Rating Script - SELECT item, (totalrate / nrrates)
Posted: 24 Jan 2015, 20:40
by David
Hi people,
I'm reasonably new to PDO and have just completed a GNU project for myself that uses your rating script, from:
https://coursesweb.net/php-mysql/rating- ... jax-php_s2
And I'm stuck with this either returning multiple arrays or nonsensical data?
This is the db that I'm testing:
Code: Select all
item totalrate nrrates
rt_35 3 1
rt_11 3 1
rt_25 4 1
rt_34 2 1
rt_33 4 1
rt_32 6 1
rt_17 5 1
rt_36 6 1
rt_12 4 1
rt_22 5 1
rt_24 4 1
rt_20 6 1
rt_11 3 1
rt_25 6 1
rt_33 6 1
rt_17 6 1
rt_32 6 1
rt_36 6 1
rt_35 7 1
And I've kicked this around for hours and got nowhere?
Can you help me to sort it out so that is returns some sensible data that I can process as a list?
Thanks.
Code: Select all
$stmt = $database->prepare("SELECT `item`, (`totalrate` / `nrrates`) AS `rank` FROM `rtgitems` ORDER BY (`totalrate` / `nrrates`) DESC LIMIT 10");
$stmt->execute();
$row = $stmt->fetchAll();
{
foreach ($row as $row2)
{
$name2 = htmlEsc($row2['item']);
}
$name2 = str_replace("rt_", "", $name2);
echo ''.$name2.'';
}
Rating Script - SELECT item, (totalrate / nrrates)
Posted: 25 Jan 2015, 09:22
by Admin
Hi David
I didn't understand what is the problem, related to the SQL query or PHP? What results returns your code, and what results you want to get?
- Trying to guess, maybe you want a SQL query like this:
Code: Select all
SELECT `item`, (SUM(`totalrate`) / SUM(`nrrates`)) AS `rank` FROM `rtgitems` GROUP BY item ORDER BY rank DESC LIMIT 10
This query applied to this table:
Code: Select all
item | totalrate | nrrates
rt_35 3 1
rt_11 3 1
rt_32 6 1
rt_35 7 1
Results:
Code: Select all
item rank
rt_32 6.0000
rt_35 5.0000
rt_11 3.0000
Rating Script - SELECT item, (totalrate / nrrates)
Posted: 25 Jan 2015, 10:41
by David
And now it's working fine!
One it was the wrong select and on top of that the wrong query.
This fixed it: But I still have one question.
Can we drop the 6.0000 for example to 6.00 - or are the extra digits required for rounding percentages?
Code: Select all
$stmt = $database->query("SELECT `item`, (SUM(`totalrate`) / SUM(`nrrates`)) AS `rank` FROM `rtgitems` GROUP BY item ORDER BY rank DESC LIMIT 10");
while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo ''.$row['item'].' '.$row['rank'].'<br />'; //etc...
}
Output:
Code: Select all
rt_36 6.0000
rt_32 6.0000
rt_2 6.0000
rt_17 5.5000
rt_20 5.0000
rt_22 5.0000
rt_31 5.0000
rt_12 5.0000
rt_33 5.0000
rt_25 5.0000
Thanks for the new SELECT btw - You may want to add it to your instructions for noobs like me.
Rating Script - SELECT item, (totalrate / nrrates)
Posted: 25 Jan 2015, 11:03
by Admin
To format a number in php, use the number_format() function.
Example:
Code: Select all
$nr = '8.0680';
$nr = number_format($nr, 2, '.', '');
echo $nr; // 8.07
In your code:
Code: Select all
echo $row['item']. ' '. number_format($row['rank'], 2, '.', '') .'<br />';
Rating Script - SELECT item, (totalrate / nrrates)
Posted: 25 Jan 2015, 11:42
by David
Brilliant! And your help has been fantastic.
And I even made a mini-banner for you for my Credit's List"
Unless you have something better?
Rating Script - SELECT item, (totalrate / nrrates)
Posted: 25 Jan 2015, 14:44
by Admin
About the errors you included in your previous post (and I deleted them), it was of a bug in phpbb, i try to fix it; but I viewed the attachment on server.
Thank you for the mini-banner yor made, I not have something better.
I'm glad the rating script works and is useful for you.
Rating Script - SELECT item, (totalrate / nrrates)
Posted: 26 Jan 2015, 01:30
by David
The script does everything that one could ask for and with a little imagination one can construct it to do just about anything that anyone would want. And I'm glad you did it and made it available.
In the next few weeks I should have my work on-line and you can see it in action. I'll also upgrade my profile with a few extras.
Thanks again for your help - And I'll spend a bit of time here seeing what else I can "Steal"
