average results from mysql

[gandrap]

I have a problem to get result from mysql.

I want this average result when i have for example two reviews:

food: 5.0
prices: 5.0
lodging: 6.2

but i got this results separatly instead:

food: 8.0
prices: 6.0
lodging: 3.0
food: 2.0
prices: 4.0
lodging: 10.0

and i cant figure how to solve it, because i am still beginner in php/mysql

here is the code:

 `$db->query("SELECT ROUND( AVG(grade),1 ) as `grade` FROM grades WHERE id = '$grade_id'");`

thx.

[pritaeas]

You'll need to use GROUP BY id in your query.

[gandrap]

it is still the same result, so i am sure my code is bad :)

<?php
                    $article = $db->query("SELECT * FROM grades WHERE place_id = '$main_id'");
                    while ($data = $article->fetch_object()) {
                        $info_cat_id = $data->info_cat_id;
                        $grade_id = $data->id;

                    $article4 = $db->query("SELECT ROUND( AVG(grade),1 ) as `grade` FROM grades WHERE id = '$grade_id' GROUP BY id");
                    while ($data4 = $article4->fetch_object()) {
                    $average = $data4->grade;

                    $article2 = $db->query("SELECT * FROM info_cat WHERE id = '$info_cat_id'");
                    while ($data2 = $article2->fetch_object()) {
                        $info_cat_image = $data2->icon;

                    $article3 = $db->query("SELECT * FROM images WHERE id = '$info_cat_image'");
                    while ($data3 = $article3->fetch_object()) {
                        $info_cat_image = $data3->url;
                ?>
                <div class='place_top_left_reviews_inner'>
                    <img src='<?php echo $info_cat_image; ?>' />
                    <p><?php echo $average; ?></p>
                </div>
                <?php }}}} ?>

[diafol]

SELECT MIN(grade) as `grade` FROM grades WHERE id = '$grade_id' GROUP BY id

I'm assuming that 'id' is your Primary Key. SO in this case, you'll be returning everything. The MIN won't have any effect. You need to group on the field giving 'food', 'prices' and 'lodging'. Then the MIN, AVE, MAX etc functions will work.

BTW, if this field is like a 'category', that you do not allow users to expand, you'd be better off with a related table for categories and just have the foreign key field for category_id in this table.

[gandrap]

ok, thx. But i am not sure that i understand how to do that. Can you, please, give me example?

[diafol]

Do what? Create a related table?

OK, consider...

Grade

• grade_id (PK, int)
• category_id (FK, int) - this is the related field
• grade (tinyint)

Categories

• category_id (PK, int)

• label (varchar, 30)

  SELECT c.label, g.grade FROM Grade AS g INNER JOIN Categories AS c ON g.category_id = c.category_id

That will give you a list of all entries in the Grade table giving the name of the category (taken from the categories table) and the grade value itself. It may look complicated, but if we break it down it's not so bad.

c.label is short for Categories.label
g.grade is short for Grade.grade

This is declared in the AS clause which supplies an ALIAS. It's just convenient so that you don't have to specify the table with the full name every time, which can become tedious, especially if you're collecting data from many fields.

The link between the tables is specified by the INNER JOIN ... ON ... part. You just declare which table (categories) is linked to the FROM table (grade) and then which fields in each table are linked (i.e. category_id in each case - they don't have to have the same name though).

So, if you need an average with category names, you could do this...

SELECT c.label, AVE(g.grade) AS ave_grade FROM Grade AS g INNER JOIN Categories AS c ON g.category_id = c.category_id GROUP BY g.category_id

PHP can then pick up the average grade from something like $row['ave_grade']

The MINimum...

SELECT c.label, MIN(g.grade) AS minimum_grade FROM Grade AS g INNER JOIN Categories AS c ON g.category_id = c.category_id GROUP BY g.category_id

You can link (or create relashioships between) many tables, not just two.

[gandrap]

Thanks to you i solved my problem and i got result i wanted :)

thanks again!