Rating Script - SELECT item, (totalrate / nrrates)

[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.'';
} 

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

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.

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 />'; 

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?

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.

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"