PHP SQL error help rating average

jack55 · November 11, 2018, 2:02am

Hello, I am experimenting with a store and have a categories page that gets all the items with the same cat_id but i am now adding a star rating system onto it but now i am getting an issue where it only loads one record even tho there are many more but that one record is all correct. Can someone see what I am doing wrong?

Also could someone help with an sql if statement so if there are no reviews found in reviews table then = 0.

SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, c.name AS catname, c.icon AS icon FROM chewi_m_items i JOIN chewi_m_items_reviews r ON i.id = r.item_id INNER JOIN chewi_m_items_categories c ON i.cat_id = c.id WHERE i.status = 1 AND i.cat_id = :cat_id ORDER BY i.created DESC

Review/Rating Table:

& if there are no records available then the rating should be 0.

The single record that it gets shows all the correct data but the ratings are mixing with eachother

All help much appreciated. Thanks

r937 · November 11, 2018, 9:24am

SELECT i.*
     , r.rating
     , r.item_id
     , ROUND(AVG(r.rating),0) as averageRating
     , c.name AS catname
     , c.icon AS icon 
  FROM chewi_m_items i 
  JOIN chewi_m_items_reviews r 
    ON i.id = r.item_id 
INNER 
  JOIN chewi_m_items_categories c 
    ON i.cat_id = c.id 
 WHERE i.status = 1 
   AND i.cat_id = :cat_id 
ORDER 
    BY i.created DESC

you have two problems, possibly three

first, you need to use LEFT OUTER JOIN for the join to the reviews table

second, you’re using an aggregate function (AVG) without a GROUP BY clause

third, if it’s possible that an item can be in more than one category, your results will blow up

jack55 · November 11, 2018, 12:03pm

So this? And an item can only be in one category at a time.

SELECT i.*
     , r.rating
     , r.item_id
     , ROUND(AVG(r.rating), 0) as averageRating
     , c.name AS catname
     , c.icon AS icon 
FROM chewi_m_items i 
LEFT OUTER JOIN chewi_m_items_reviews r ON i.id = r.item_id 
INNER JOIN chewi_m_items_categories c ON i.cat_id = c.id 
WHERE i.status = 1 AND i.cat_id = :cat_id 
GROUP BY i.id 
ORDER BY i.created DESC

jack55 · November 11, 2018, 1:47pm

Also can i ask another sql question: Is it possible to make it so that you can go to a parent category of say 5 and then the sql looks for all the child categories with parent = 5 and then it gets all the items with the child category id’s.

An example: Parent Category 1 [ID = 1] then we have Child Category 1 [ID = 2, PARENT_ID = 1] & Child Category 2 [ID = 3, PARENT_ID = 1] then we Item 1 [CAT_ID = 2] & Item 2 [CAT_ID = 3]

So basically when u visit a child category, it loads all items with that cat id but when u visit parent how do you make that relationship so that it loads all the child category data & then child item data? Thanks in advance.

r937 · November 11, 2018, 3:51pm

what happened when you tested it? ™

r937 · November 11, 2018, 3:54pm

sure, it’s possible

how do you know a category is a parent category?

sounds like you’ll want two similar queries, and UNION them together, so that either one or the other returns results

of course, if a parent category can have items as well as child categories, then both parts of the UNION will return results

jack55 · November 11, 2018, 4:08pm

It worked great! Thanjs very much! Just needed the group by and left outer Thanks

Next bit…
There is a table called categories here is the structure:

If the parent_id is blank then it is a parent otherwise it is a child category. Yes I was experimenting with a union in another experiment but how would i structure the query, that is where i’m struggling.

The workings are relatively simple → name.com/category/1 find all child categories with 1 as parent and then get all data from items table where cat_id is the child category etc.

Best way to show you is this link:
https://www.codester.com/categories/43/scripts-code - this link gets all category items so php scripts & javascript etc.
https://www.codester.com/categories/10/php-scripts-php-code - this link gets just php scripts

Thanks

r937 · November 11, 2018, 4:17pm

SELECT ...
  FROM chewi_m_items_categories   parent_cat 
INNER
  JOIN chewi_m_items_categories   child_cat
    ON child_cat.parent_id = parent_cat.id
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE parent_cat.id = :cat_id 
UNION ALL
SELECT ...
  FROM chewi_m_items_categories   child_cat
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE child_cat.id = :cat_id

p.s. those codester links didn’t do a thing for me

jack55 · November 11, 2018, 4:18pm

oh I’ll have a test. Thanks

jack55 · November 11, 2018, 4:34pm

Ok so it gets one item, but i feel it needs a group by but what would i group it by?

$sql = 'SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon
  FROM chewi_m_items_categories   parent_cat 
INNER
  JOIN chewi_m_items_categories   child_cat
    ON child_cat.parent_id = parent_cat.id
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE parent_cat.id = :cat_id 
UNION ALL
SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon
  FROM chewi_m_items_categories   child_cat
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE child_cat.id = :cat_id';

jack55 · November 11, 2018, 4:39pm

Oh wait ignore me, i forgot to add a group by in the other union statement oops!

New:

       $sql = 'SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon
  FROM chewi_m_items_categories   parent_cat 
INNER
  JOIN chewi_m_items_categories   child_cat
    ON child_cat.parent_id = parent_cat.id
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE parent_cat.id = :cat_id GROUP BY i.id
UNION ALL
SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon
  FROM chewi_m_items_categories   child_cat
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE child_cat.id = :cat_id GROUP BY i.id';

Where would i do an order by i.created desc? and a limit 8 ? Thanks They are the two things that break the statement

r937 · November 11, 2018, 4:58pm

oh yeah, i’m sorry i forgot the GROUP BY in both halves of the UNION

a UNION query can have only one ORDER BY, and it goes at the end

jack55 · November 11, 2018, 5:02pm

Oh don’t be sorry at all! You have made my day with your help.

The order by is the last thing not working for some reason.

Should return 3 items.

        $sql = 'SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon
  FROM chewi_m_items_categories   parent_cat 
INNER
  JOIN chewi_m_items_categories   child_cat
    ON child_cat.parent_id = parent_cat.id
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE parent_cat.id = :cat_id GROUP BY i.id
UNION ALL
SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon
  FROM chewi_m_items_categories   child_cat
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE child_cat.id = :cat_id GROUP BY i.id ORDER BY i.created DESC';

This is how i used to do my old statement:
$sql = 'SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, c.name AS catname, c.icon AS icon FROM chewi_m_items i LEFT OUTER JOIN chewi_m_items_reviews r ON i.id = r.item_id INNER JOIN chewi_m_items_categories c ON i.cat_id = c.id WHERE i.status = 1 AND i.cat_id = :cat_id GROUP BY i.id ORDER BY i.created DESC '. $limit;

Thanks again so much!

r937 · November 11, 2018, 5:17pm

if it says 0 items found, then it’s not the ORDER BY that’s the problem

jack55 · November 11, 2018, 5:18pm

It works fine but then as soon as i add the order by it breaks. Very odd

r937 · November 11, 2018, 5:25pm

wait a sec, it ~is~ the ORDER BY

run your query directly in mysql, not via php

you will then discover the syntax error

jack55 · November 11, 2018, 5:29pm

This is the error response:

4 errors were found during analysis.

1. Unrecognized keyword. (near "INNER" at position 173)
2. Unrecognized keyword. (near "INNER" at position 269)
3. This type of clause was previously parsed. (near "JOIN" at position 278)
4. Unrecognized statement type. (near "JOIN" at position 278)

**SQL query:** [![Documentation](http://localhost/phpmyadmin/themes/dot.gif)](http://localhost/phpmyadmin/url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Fselect.html)

SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon FROM chewi_m_items_categories parent_cat INNER JOIN chewi_m_items_categories child_cat ON child_cat.parent_id = parent_cat.id INNER JOIN chewi_m_items i ON i.cat_id = child_cat.id AND i.status = 1 LEFT OUTER JOIN chewi_m_items_reviews r ON r.item_id = i.id WHERE parent_cat.id = 1 GROUP BY i.id UNION ALL SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon FROM chewi_m_items_categories child_cat INNER JOIN chewi_m_items i ON i.cat_id = child_cat.id AND i.status = 1 LEFT OUTER JOIN chewi_m_items_reviews r ON r.item_id = i.id WHERE child_cat.id = 1 GROUP BY i.id ORDER BY i.created DESC

**MySQL said:** [![Documentation](http://localhost/phpmyadmin/themes/dot.gif)](http://localhost/phpmyadmin/url.php?url=https%3A%2F%2Fdev.mysql.com%2Fdoc%2Frefman%2F5.7%2Fen%2Ferror-messages-server.html)

`#1250 - Table 'i' from one of the SELECTs cannot be used in field list`

I will do som

jack55 · November 12, 2018, 1:14am

wow really odd i cannot figure it out.

Works fine with the limit just order by causes the issue.

        $sql = '
        
SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon
  FROM chewi_m_items_categories   parent_cat 
INNER
  JOIN chewi_m_items_categories   child_cat
    ON child_cat.parent_id = parent_cat.id
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE parent_cat.id = :cat_id GROUP BY i.id 
UNION ALL
SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon
  FROM chewi_m_items_categories   child_cat
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE child_cat.id = :cat_id GROUP BY i.id      
        ' . $limit;

I also tested one statement on its own and removed from the union and it works fine but as soon as i readd it and do an order by it breaks! Strange.

By adding the order by, it causes an bool(false)

update: when i add order by averageRating it works but as soon as i do i.something it breaks so i guess i should do an i.id as itemid or something… nope that breaks it, how odd

UPDATE: FIXED IT, It did not want i.id just simply id:

SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon
  FROM chewi_m_items_categories   parent_cat 
INNER
  JOIN chewi_m_items_categories   child_cat
    ON child_cat.parent_id = parent_cat.id
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE parent_cat.id = :cat_id GROUP BY i.id 
UNION ALL
SELECT i.*, r.rating, r.item_id, ROUND(AVG(r.rating), 0) as averageRating, child_cat.name AS catname, child_cat.icon AS icon
  FROM chewi_m_items_categories   child_cat
INNER
  JOIN chewi_m_items i 
    ON i.cat_id = child_cat.id 
   AND i.status = 1
LEFT OUTER 
  JOIN chewi_m_items_reviews r 
    ON r.item_id = i.id 
WHERE child_cat.id = :cat_id GROUP BY i.id ORDER BY id DESC

Thanks again for all your help

r937 · November 12, 2018, 9:26am

i was hoping that by now you would’ve looked up the syntax in da manual

“This kind of ORDER BY cannot use column references that include a table name (that is, names in tbl_name . col_name format). Instead, provide a column alias in the first SELECT statement and refer to the alias in the ORDER BY.”
–.https://dev.mysql.com/doc/refman/8.0/en/union.html

you don’t actually have to assign an alias, just drop the table reference

system · February 11, 2019, 4:26pm

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.