tired eyes -- help with $sql "Select" query

[RyanSF07]

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

[RyanSF07]

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";

[cooldude832]

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?

[cooldude832]

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

[RyanSF07]

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.

 

[RyanSF07]

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";

[cooldude832]

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

[RyanSF07]

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

 

 

 

 

[cooldude832]

what links ratings to videos?

[RyanSF07]

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.

 

 

[cooldude832]

take what I said under advisement work it in phpmyadmin till it does what you want

 

you need to use a JOIN query

[RyanSF07]

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