counting rows

[RyanSF07]

Hello,

 

Users can create quizzes on this site, and sometimes (often) the quizzes are junk.

 

I want to learn how to run a query that will automatically disable quizzes with less than 3 questions.

 

Currently, this query below selects all quiz questions that relate to a particular video_id (the quizzes relate to videos):

SELECT * FROM $table WHERE $table.video_id = '$_SESSION[get]' ORDER BY id ASC",$db

 

And this query updates determines whether or not to include a quiz on the site:

"UPDATE video SET pass_text = '$_POST[pass_text]' WHERE video.id = '$_SESSION[get]'";

 

Is it possible to, in a query, count how many instances of video_id=session_get  (which would be the questions themselves) and then SET passtext=disable if there are less than 3 questions?

 

thank you for your help,

Ryan

[DarkKnight2011]

where are you counting/checking whether the quiz has less than 3 questions?

 

could you also please post your table structure so we can help you

 

 

[RyanSF07]

Hi Dark,

 

That's what I'm asking -- how do I do that?  What are the correct terms to google to learn about this?

 

I don't expect the query, I'm just looking for a push in the right direction.

 

I want to learn how to count rows and identify quizzes with less that 3 questions, and then, if they do have less than three questions, run a parallel query that UPDATEs that quiz's "pass text" to "disable"--  which will remove it from the site.

 

thanks!

[trq]

http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_count

[RyanSF07]

Thank you.  This works, but not perfectly.  It seems to ignore quizzes with zero questions.  Do I have to specify 0 or less than 4 questions?

 

SELECT video_id
FROM `quiz`
GROUP BY video_id
HAVING count( question ) <4
ORDER BY video_id ASC
LIMIT 0 , 3000

 

 

Also -- ideally I'd be able to scan all "new_quizzes".  This code below freezes the server.  How do I write this so as not to send my server into cardiac arrest?

 

SELECT *
FROM quiz, video
GROUP BY video_id
HAVING count( question ) <4
AND video.pass_text = 'new_quiz'
ORDER BY video_id ASC
LIMIT 0 , 3000

 

Thank you very much for your help!

 

Ryan