MySQL sub query example – nice

$cp_output = “”;
if(!$get_cp_data = mysql_query(”
SELECT
q.*,
c.*,
vote_counts.Tally
FROM
questions q
LEFT JOIN
(SELECT
questionId,
count(1) as Tally
FROM
votes
GROUP BY
questionId
) vote_counts
ON
q.questionId = vote_counts.questionId
LEFT JOIN
customers c
ON
c.customerId = q.customerId
WHERE
q.courseId = 1
ORDER BY
vote_counts.Tally desc

“)){ echo mysql_error(); }

while($get_cp_data_array = mysql_fetch_array($get_cp_data)){
$q_id = $get_cp_data_array[‘questionId’];

$q_fname = $get_cp_data_array[‘customerFName’];
$q_lname = $get_cp_data_array[‘customerLName’];
$q_email = $get_cp_data_array[‘customerEmail’];

$q_topic = $get_cp_data_array[‘questionTopic’];
$q_question = $get_cp_data_array[‘question’];
$q_date = $get_cp_data_array[‘questionDate’];
//$q_count = $get_cp_data_array[‘votecount’];
$q_customer = $get_cp_data_array[‘customerId’];

if($q_topic == “”){ $q_topic = “–“; }
if($q_fname != “” || $q_lname != “”){
$from = ‘<strong>’ . $q_fname . ‘ ‘ . $q_lname . ‘</strong> – ‘;
}

# Parse the question text.
$old = array(“\n”);
$new   = array(“<br/>”);
$q_question = str_replace($old, $new, $q_question);

# Output the question.
$cp_output = $cp_output . ‘
<div>’ . $q_id . ‘</div>
<div>
<div><strong>’ . $q_topic . ‘</strong></div>
<div>’ . $q_question . ‘</div>
<div><strong>Submitted</strong> ‘ . $q_date . ‘ by ‘ . $from . ‘<a href=”mailto:’ . $q_email . ‘”>’ . $q_email . ‘</a></div>
</div>
<div></div>
‘;
}

Comments are closed.