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 Code:

<?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]

Originally Posted by 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.

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!