vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)
-   -   Data From 2 Tables in one Query? (https://vborg.vbsupport.ru/showthread.php?t=101747)

derekivey 11-28-2005 02:23 AM
Data From 2 Tables in one Query?
 
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 11-28-2005 02:36 AM
Test the following...
Code:
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 11-28-2005 02:51 AM
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 11-28-2005 07:14 AM
Then you are not processing all rows returned. Please post the php script you are using to display this.

derekivey 11-28-2005 02:13 PM
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 11-28-2005 02:34 PM
I find referencing 2 tables at once can be made much easier to understand by using aliases in your query, eg:
Code:
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 11-28-2005 04:36 PM
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 11-28-2005 09:17 PM
Quote:
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 11-28-2005 10:03 PM
Thanks! :)


All times are GMT. The time now is 07:55 AM.

Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2025, vBulletin Solutions Inc.

X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.01278 seconds
  • Memory Usage 1,855KB
  • Queries Executed 10 (?)
More Information
Template Usage:
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (2)bbcode_code_printable
  • (1)bbcode_php_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (9)printthreadbit
  • (1)spacer_close
  • (1)spacer_open 
Phrase Groups Available:
  • global
  • postbit
  • showthread
Included Files:
  • ./printthread.php
  • ./global.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/config.php
  • ./includes/functions.php
  • ./includes/class_hook.php
  • ./includes/modsystem_functions.php
  • ./includes/class_bbcode_alt.php
  • ./includes/class_bbcode.php
  • ./includes/functions_bigthree.php 
Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • printthread_start
  • bbcode_fetch_tags
  • bbcode_create
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • printthread_post
  • printthread_complete