RyanSF07 Posted August 25, 2008 Share Posted August 25, 2008 Hi All, This is working: $sql = "SELECT video.id, title, description_text, category_text, level_text, pass_text, user_name, DATE_FORMAT(date, '%M %D, %Y') as date FROM video, registered_users WHERE video.user_id = registered_users.id AND video.category_text = 'English' AND video.level_text = 'beginning' AND video.pass_text = 'featured' ORDER BY id DESC"; Now I'm trying to add this: SELECT `item_name`, AVG(`rating`) AS `totalRating` FROM `rating` GROUP BY `item_name` ORDER BY `totalRating` DESC This is what I have, and it isn't working: $sql = "SELECT video.id, title, description_text, category_text, level_text, pass_text, user_name, item_name DATE_FORMAT(date, '%M %D, %Y') as date FROM video, registered_users, rating WHERE video.user_id = registered_users.id AND video.category_text = 'English' AND video.level_text = 'beginning' AND video.pass_text = 'new_quiz' AVG('rating') AS totalRating GROUP BY item_name ORDER BY totalRating DESC"; Please let me know if you see anything mis-puctuated, etc. All suggestions welcome. Thank you! Ryan Quote Link to comment Share on other sites More sharing options...
RyanSF07 Posted August 25, 2008 Author Share Posted August 25, 2008 This one isn't working either: $sql = "SELECT video.id, title, description_text, category_text, level_text, pass_text, user_name, rating.item_name, DATE_FORMAT(date, '%M %D, %Y') as date FROM video, registered_users, rating, WHERE video.user_id = registered_users.id AND video.category_text = 'English' AND video.level_text = 'beginning' AND video.pass_text = 'new_quiz' AVG(rating.rating) AS 'totalRating' GROUP BY rating.item_name ORDER BY 'totalRating' DESC"; Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 25, 2008 Share Posted August 25, 2008 quick notice you are missing a comma after item_name and why aren't u joining? and this? video.pass_text = 'new_quiz' AVG('rating') AS totalRating Makes ltittle sense can we see table structure? Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 25, 2008 Share Posted August 25, 2008 take this into phpmyadmin and report back errors SELECT video.id, title, description_text, category_text, level_text, pass_text, user_name, item_name, DATE_FORMAT(date, '%M %D, %Y') as date FROM video, registered_users, rating WHERE video.user_id = registered_users.id AND video.category_text = 'English' AND video.level_text = 'beginning' AND video.pass_text = 'new_quiz' AVG('rating') AS totalRating GROUP BY item_name ORDER BY totalRating DESC Quote Link to comment Share on other sites More sharing options...
RyanSF07 Posted August 25, 2008 Author Share Posted August 25, 2008 Thanks, Here is the Error message. Error SQL query: Documentation SELECT video.id, title, description_text, category_text, level_text, pass_text, user_name, item_name, DATE_FORMAT( date, '%M %D, %Y' ) AS date FROM video, registered_users, rating WHERE video.user_id = registered_users.id AND video.category_text = 'English' AND video.level_text = 'beginning' AND video.pass_text = 'new_quiz'AVG( 'rating' ) AS totalRating GROUP BY item_name ORDER BY totalRating DESC LIMIT 0 , 30 MySQL said: Documentation #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AVG( 'rating' ) AS totalRating GROUP BY item_name ORDER BY totalRating DESC' at line 1 Note: this part -- AVG( 'rating' ) AS totalRating -- should relate to "item_name" in the "Rating" table. Thanks for you help. Quote Link to comment Share on other sites More sharing options...
RyanSF07 Posted August 25, 2008 Author Share Posted August 25, 2008 tried this -- removed DATE. Still the syntax is wacked. Everything works until I try to AVERAGE. This didn't work either: $sql = "SELECT video.id, title, description_text, category_text, level_text, pass_text, user_name, rating.item_name, FROM video, registered_users, rating, WHERE video.user_id = registered_users.id AND video.category_text = 'English' AND video.level_text = 'beginning' AND video.pass_text = 'new_quiz' AVG(rating.rating) AS 'totalRating' GROUP BY item_name ORDER BY totalRating DESC"; Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 25, 2008 Share Posted August 25, 2008 can I see the table structures I'm assuming registered_users and rating are child to video in this case? also every field in you select should have their table name in front of them for good practice because if a name duplicates you will get a too ambiguous error. this is more what I think you want but like I said I need more explanation SELECT video.id, title, description_text, category_text, level_text, pass_text, user_name, item_name, DATE_FORMAT(date, '%M %D, %Y') as date FROM video LEFT JOIN `rating` ON(rating.VideoID = video.id) LEFT JOIN `registered_users` ON(registered_user.id = video.userid) WHERE video.user_id = registered_users.id AND video.category_text = 'English' AND video.level_text = 'beginning' GROUP BY item_name ORDER BY totalRating DESC Quote Link to comment Share on other sites More sharing options...
RyanSF07 Posted August 25, 2008 Author Share Posted August 25, 2008 Thank you for your help. Here are the table structures: Table: notes Rows: id, user_id, video_id, notes_text Table: quiz Rows: id, user_id, video_id, q, question, opt1, opt2, opt3, answer Table: rating Rows: item_name, ip_address, rating, date_rated Table: registered_users Rows: id, first_name, last_name, email, password, link, date_added Table: transcript Rows: id, user_id, video_id, transcript_text Table: video Rows: id, user_id, title, video, discription_text, category_text, level_text, pass_text, date Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 25, 2008 Share Posted August 25, 2008 what links ratings to videos? Quote Link to comment Share on other sites More sharing options...
RyanSF07 Posted August 25, 2008 Author Share Posted August 25, 2008 I recently added a star rating script that grabs the video.id and passes it to an include file that writes the number to the table. The stars show up with: $videoID = $row["id"]; $ratingData = Rating::OutputRating($videoID); I also recently added pagination -- counts rows and displays 15 per page. I just reconfirmed -- this works/displays perfectly with: $max = 'limit ' .($pagenum - 1) * $page_rows .',' .$page_rows; $sql_p = "SELECT video.id, title, description_text, category_text, level_text, pass_text, user_name, DATE_FORMAT(date, '%M %D, %Y') as date FROM video, registered_users WHERE video.user_id = registered_users.id AND video.category_text = 'English' AND video.level_text = 'beginning' AND video.pass_text = 'new_quiz' ORDER BY id DESC $max"; Now I'm trying to have list the quizes/video.ids by average rating from highest to lowest. So I'm trying to add this to my Select query: SELECT `item_name`, AVG(`rating`) AS `totalRating` FROM `rating` GROUP BY `item_name` ORDER BY `totalRating` DESC Perhaps a Date/Limit/Order by Desc conflict? Thank you again for your help. Quote Link to comment Share on other sites More sharing options...
cooldude832 Posted August 25, 2008 Share Posted August 25, 2008 take what I said under advisement work it in phpmyadmin till it does what you want you need to use a JOIN query Quote Link to comment Share on other sites More sharing options...
RyanSF07 Posted August 25, 2008 Author Share Posted August 25, 2008 Thanks. I will. Thanks again for your help. I'll google JOIN and see what comes up -- never used that before. All other suggestions welcome. Thanks much as always. Ryan Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.