Data From 2 Tables in one Query?

[derekivey]

Hi all,

For my Toplist hack, I am implementing a rating system for it, and need to show the average rating of each of the sites on the main page. I am not sure how to do this, because the rating data is in a separate table. I know how to get the average, but don't know how to get it on the main page, which uses a while loop to show all the sites in the Toplist.

The database structure is like this:

Table: toplist_sites
`id`
`username`
`name`
`url`
`description`
`image`
`in`
`out`
`status`

Table: toplist_ratings
`id`
`site_id`
`username`
`rating`

Any help is appreciated.

Thanks,
Derek

[calorie]

Test the following...

SELECT toplist_sites.*, AVG(toplist_ratings.rating)
FROM toplist_sites, toplist_ratings
WHERE toplist_sites.id = toplist_ratings.site_id
GROUP BY toplist_ratings.site_id;

[derekivey]

Thanks, I'll give it a try.

Derek

EDIT: It doesn't work :(. It only shows one site instead of all of them.
See here for what I mean - http://www.trafficsurfer.net/forums/toplist.php

[Marco van Herwaarden]

Then you are not processing all rows returned. Please post the php script you are using to display this.

[derekivey]

Hi Marco,

Here is my toplist.php file:


<?php

/*================================================= =====================*\
|| ################################################## ################## ||
|| # Toplist Hack - Version 1.0.3 # ||
|| # For vBulletin 3.5.1 # ||
|| # ---------------------------------------------------------------- # ||
|| # By Derek Ivey (derek@modbb.com) # ||
|| # This hack may not be redistributed without my written permission # ||
|| ################################################## ################## ||
\*================================================ ======================*/

// ####################### SET PHP ENVIRONMENT ###########################
error_reporting(E_ALL & ~E_NOTICE);

// #################### DEFINE IMPORTANT CONSTANTS #######################
define('NO_REGISTER_GLOBALS', 1);
define('THIS_SCRIPT', 'toplist');

// ################### PRE-CACHE TEMPLATES AND DATA ######################
// pre-cache templates used by all actions
$globaltemplates = array(
'Toplist',
'Toplist_1',
'Toplist_2',
'Toplist_3',
'Toplist_4',
'Toplist_5',
'Toplist_6',
'Toplist_Disabled',
'Toplist_No_Rows',
'Toplist_Rate',
'Toplist_Rate_Misc',
);

// ######################### REQUIRE BACK-END ############################
require_once('./global.php');

// ################################################## #####################
// ######################## START MAIN SCRIPT ############################
// ################################################## #####################
$navbits = array();
$navbits[$parent] = $vbphrase['toplist'];

$navbits = construct_navbits($navbits);
eval('$navbar = "' . fetch_template('navbar') . '";');

// #################### DETECT IF TOPLIST IS ENABLED #####################

if($vbulletin->options['toplist_enabled'] == 0) {
eval('$message = "' . fetch_template('Toplist_Disabled') . '";');
$html = $message;
eval('print_output("' . fetch_template('Toplist') . '");');
}

// ########################### DO RATE SITE ##############################

if($_REQUEST['do'] == 'dorate') {
if($vbulletin->userinfo['usergroupid'] == 1) {
print_no_permission();
} else {
if($_POST['id']) {
$sql = "SELECT * FROM " . TABLE_PREFIX . "toplist_ratings WHERE site_id='".addslashes($_GET['id'])."' && username='".addslasheS($vbulletin->userinfo['username'])."'";
$sqlr = $db->query_read($sql);
$num_rows = $db->num_rows($sqlr);
if($num_rows == 0) {
$sql = "INSERT INTO " . TABLE_PREFIX . "toplist_ratings SET
site_id='".addslashes($_POST['id'])."',
username='".addslashes($vbulletin->userinfo['username'])."',
rating='".addslashes($_POST['rating'])."'";
$db->query_write($sql);
$html = $vbphrase['toplist_rating_successful'];
eval('print_output("' . fetch_template('Toplist_Rate_Misc') . '");');
} else {
$html = $vbphrase['toplist_rating_error'];
eval('print_output("' . fetch_template('Toplist_Rate_Misc') . '");');
}
} else {
$html = $vbphrase['toplist_rating_no_id'];
eval('print_output("' . fetch_template('Toplist_Rate_Misc') . '");');
}
}

// ########################## RATE SITE FORM #############################

} elseif($_REQUEST['do'] == 'rate') {
if($vbulletin->userinfo['usergroupid'] == 1) {
print_no_permission();
} else {
eval('print_output("' . fetch_template('Toplist_Rate') . '");');
}
} else {

// ############################ PAGINATION ###############################
//------------------------------------------------------------
// Find the num of recs
//------------------------------------------------------------
$sql = "SELECT COUNT(*) FROM " . TABLE_PREFIX . "toplist_sites WHERE status='active'";
$total_sites = $db->query_first($sql, DBARRAY_NUM);
$total_sites = $total_sites[0];


//------------------------------------------------------------
// Get some needed values
//------------------------------------------------------------
// Set some defaults
$per_page = $vbulletin->options['sites_per_page'];
$cur_page = 1;


// Get per_page from user if it specified and valid
if(isset($_REQUEST['per_page']) && is_numeric($_REQUEST['per_page']) && $_REQUEST['per_page'] <= 200 && $_REQUEST['per_page'] > 5) {
$per_page = $_REQUEST['per_page'];
}


// How many pages should we have total?
$num_pages = ceil($total_sites / $per_page);


// Ge the current page from the user if it is specified and valid
if(isset($_REQUEST['page']) && is_numeric($_REQUEST['page']) && $_REQUEST['page'] > 0 && $_REQUEST['page'] <= $num_pages) {
$cur_page = $_REQUEST['page'];
}


// The SQL limit offset
$limit_offset = ($cur_page * $per_page) - $per_page;

//------------------------------------------------------------
// Build up the HTML nav
//------------------------------------------------------------
$pagenav = construct_page_nav($cur_page, $per_page, $total_sites, 'toplist.php?' . $vbulletin->session->vars['sessionurl'], "");

// ######################## GET SITES FROM DB ############################

if(empty($page)) {
$page = 1;
}

if(empty($pp)) {
$pp = $vbulletin->options['sites_per_page'];
} else {
$pp = $_REQUEST['pp'];
}

if($vbulletin->options['rank_by_in'] == 1) {
$order = 'in';
} else {
$order = 'out';
}

//$sql = "SELECT * FROM " . TABLE_PREFIX . "toplist_sites WHERE status='active' ORDER BY '".$order."' DESC LIMIT $limit_offset, $per_page";
$sql = "SELECT toplist_sites.*, AVG(toplist_ratings.rating)
FROM toplist_sites, toplist_ratings
WHERE toplist_sites.id = toplist_ratings.site_id
GROUP BY toplist_ratings.site_id";
$sqlr = $db->query_read($sql);

// ######################## SHOW SITES ###################################

$num_rows = $db->num_rows($sqlr);

if($num_rows == 0) {
eval('$message = "' . fetch_template('Toplist_No_Rows') . '";');
$html = $message;
} else {
$rank = $limit_offset+1;
$rrank = 1;
$randsite1 = "SELECT * FROM " . TABLE_PREFIX . "toplist_sites WHERE status='active' ORDER BY RAND() LIMIT 1";
$randsite2 = $db->query_read($randsite1);
$randsite = $db->fetch_array($randsite2);

$rand1 = "SELECT * FROM " . TABLE_PREFIX . "toplist_sites WHERE status='active' ORDER BY '".$order."' DESC";
$rand2 = $db->query_read($rand1);
while($rand = $db->fetch_array($rand2)) {
if($randsite['name'] == $rand['name']) {
$rand_rank = $rrank;
}
$rrank++;
}
//$rate = "SELECT round(avg( rating ), 2) AS average, round(avg( rating )) AS average_image FROM " . TABLE_PREFIX . "toplist_ratings GROUP BY site_id";
//$rate1 = $db->query_read($rate);
//while($row = $db->fetch_array($rate1)) {
//$rating = $row['average'];
//$rating_image = $row['average_image'];
//}

//$query = "SELECT * FROM " . TABLE_PREFIX . "toplist_ratings WHERE site_id='".addslashes($_GET['id'])."'";
//$result = $db->query_read($query);
//$num_votes = $db->num_rows($result);

//$rating = $row['average'];
//$rating_image = $row['average_image'];

while($sr = $db->fetch_array($sqlr))
{
if($sr['image'] != '') {
$image = "<a href='{$vbulletin->options['bburl']}/out.php?id={$sr['id']}' target='_BLANK'><img src='{$sr['image']}' border='0'></a>";
} else {
$image = $vbphrase['toplist_none'];
}
if($vbulletin->options['toplist_disable_html'] == 1) {
$sr['sitename'] = strip_tags($sr['sitename']);
$sr['description'] = strip_tags($sr['description']);
}
$sr['url'] = strip_tags($sr['url']);

if($vbulletin->options['toplist_open_in_new_window'] == 1) {
if($vbulletin->userinfo['username'] == $sr['username'] & $vbulletin->options['toplist_allow_member_edit'] == 1 OR is_member_of($vbulletin->userinfo, explode(',', $vbulletin->options['toplist_allowed_to_moderate']))) {
eval('$content = "' . fetch_template('Toplist_1') . '";');
$html .= $content;
} elseif($vbulletin->userinfo['username'] == $sr['username']) {
eval('$content = "' . fetch_template('Toplist_2') . '";');
$html .= $content;
} else {
eval('$content = "' . fetch_template('Toplist_3') . '";');
$html .= $content;
}
} else {
if($vbulletin->userinfo['username'] == $sr['username'] & $vbulletin->options['toplist_allow_member_edit'] == 1 OR is_member_of($vbulletin->userinfo, explode(',', $vbulletin->options['toplist_allowed_to_moderate']))) {
eval('$content = "' . fetch_template('Toplist_4') . '";');
$html .= $content;
} elseif($vbulletin->userinfo['username'] == $sr['username']) {
eval('$content = "' . fetch_template('Toplist_5') . '";');
$html .= $content;
} else {
eval('$content = "' . fetch_template('Toplist_6') . '";');
$html .= $content;
}
}
if($randsite['image'] != '') {
$image = "<a href='{$vbulletin->options['bburl']}/out.php?id={$randsite['id']}' target='_BLANK'><img src='{$randsite['image']}' border='0'></a>";
} else {
$image = $vbphrase['toplist_none'];
}
$rank++;
}
}

// ######################## Fetch Templates ##############################

eval('print_output("' . fetch_template('Toplist') . '");');
}

// ################################# END #################################
?>


I need to show all the sites in the toplist on that toplist.php page, which is what I had before I tried having it show the sites' ratings also. Any ideas?

Thanks,
Derek

[Kristoph]

I find referencing 2 tables at once can be made much easier to understand by using aliases in your query, eg:
SELECT t1.field AS field1, t2.anotherfield AS field2 FROM user AS t1, post AS t2 WHERE t1.userid = t2.userid AND t2.postid = '11111'

[derekivey]

Will that show all of the sites though? I need to show all the sites in the toplist_sites table, and their average rating, in the toplist_ratings table.

[Kristoph]

Will that show all of the sites though? I need to show all the sites in the toplist_sites table, and their average rating, in the toplist_ratings table.

it looks like you are not processing all the returned rows, as MarcoH64 suggested... I'll have a look at your code more thoroughly in the morning and see if I can come up with a fix... far too late for me at the moment!

[derekivey]

Thanks! :)