I wrote some code to show a site poll on my website and it works fine, but I feel it is very inefficient, can someone help me simplify this into 1 query or not have it use RAND()?
I have 3 tables, one for "Ballots" or groups of poll questions. Then the "Questions" that are in the ballots. Then the "Answers" for that belong to each question. The following code displays 1 random question from an open ballot, then it randomly orders all the child answers from the parent question.
<?php
$POLL_SQL = "SELECT ";
$POLL_SQL .= "B.id, B.StartTime, B.EndTime, B.BallotName, B.BallotDesc, B.GameID, B.CompetitionID, ";
$POLL_SQL .= "Q.id AS question_id, Q.parent_ballot_id, Q.question ";
$POLL_SQL .= "FROM votingpolls_ballots B, votingpolls_questions Q ";
$POLL_SQL .= "WHERE ";
$POLL_SQL .= "(B.EndTime = 0 OR B.EndTime > ".time().") ";//Hasn't Ended
$POLL_SQL .= "AND B.StartTime > 0 ";//Ballot Started
$POLL_SQL .= "AND Q.parent_ballot_id = B.id ";
$POLL_SQL .= "ORDER BY RAND() LIMIT 0,1";
if($POLL_RESULT = $db->query($POLL_SQL))
{
while($POLL_DATA = $POLL_RESULT->fetch_array())
{
$template->assign_block_vars('side_table.site_poll',array(
'QUESTION_ID' => intval($POLL_DATA['question_id']),
'QUESTION' => DBStringExtract($POLL_DATA['question'])
));
$ANSWERS_SQL = "SELECT ";
$ANSWERS_SQL .= "id, parent_question_id, answer ";
$ANSWERS_SQL .= "FROM votingpolls_answers ";
$ANSWERS_SQL .= "WHERE parent_question_id = ".intval($POLL_DATA['question_id'])." ";
$ANSWERS_SQL .= "ORDER BY RAND()";
if($ANSWERS_RESULT = $db->query($ANSWERS_SQL))
{
while($ANSWERS_DATA = $ANSWERS_RESULT->fetch_array())
{
$template->assign_block_vars('side_table.site_poll.answer',array(
'ANSWER_ID' => intval($ANSWERS_DATA['id']),
'ANSWER' => DBStringExtract($ANSWERS_DATA['answer'])
));
}
}
break;
}
}
?>
- <?php
- $POLL_SQL = "SELECT ";
- $POLL_SQL .= "B.id, B.StartTime, B.EndTime, B.BallotName, B.BallotDesc, B.GameID, B.CompetitionID, ";
- $POLL_SQL .= "Q.id AS question_id, Q.parent_ballot_id, Q.question ";
- $POLL_SQL .= "FROM votingpolls_ballots B, votingpolls_questions Q ";
- $POLL_SQL .= "WHERE ";
- $POLL_SQL .= "(B.EndTime = 0 OR B.EndTime > ".time().") ";//Hasn't Ended
- $POLL_SQL .= "AND B.StartTime > 0 ";//Ballot Started
- $POLL_SQL .= "AND Q.parent_ballot_id = B.id ";
- $POLL_SQL .= "ORDER BY RAND() LIMIT 0,1";
-
- if($POLL_RESULT = $db->query($POLL_SQL))
- {
- while($POLL_DATA = $POLL_RESULT->fetch_array())
- {
- $template->assign_block_vars('side_table.site_poll',array(
- 'QUESTION_ID' => intval($POLL_DATA['question_id']),
- 'QUESTION' => DBStringExtract($POLL_DATA['question'])
- ));
- $ANSWERS_SQL = "SELECT ";
- $ANSWERS_SQL .= "id, parent_question_id, answer ";
- $ANSWERS_SQL .= "FROM votingpolls_answers ";
- $ANSWERS_SQL .= "WHERE parent_question_id = ".intval($POLL_DATA['question_id'])." ";
- $ANSWERS_SQL .= "ORDER BY RAND()";
-
- if($ANSWERS_RESULT = $db->query($ANSWERS_SQL))
- {
- while($ANSWERS_DATA = $ANSWERS_RESULT->fetch_array())
- {
- $template->assign_block_vars('side_table.site_poll.answer',array(
- 'ANSWER_ID' => intval($ANSWERS_DATA['id']),
- 'ANSWER' => DBStringExtract($ANSWERS_DATA['answer'])
- ));
- }
- }
-
- break;
- }
- }
- ?>
I read on this guys blog about how you can use mt_rand() instead of RAND() [
http://akinas.com/pages/en/blog/mysql_random_row/ ], but I have 2 sets of things I need randomized and 2 queries, so I was wondering if that was even realistic, considering it would create 4 queries and he claims only 79% of RAND() execution time. My main problem is I've never used RAND() and I suck when it comes to reducing MySQL queries in loops.
There's no place like 127.0.0.1, badass part is now it's ::1
July 11th, 2010, 7:14 am
