vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   I suck at MySQL JOINS, can someone gimme a hand? (https://vborg.vbsupport.ru/showthread.php?t=91592)

Cloudrunner 07-04-2005 04:10 AM
I suck at MySQL JOINS, can someone gimme a hand?
 
How would I compress these four queries into one?
PHP Code:
$nav_new_users_today = $vbulletin->db->query_first("SELECT
    COUNT(*) AS count
    FROM ".TABLE_PREFIX."user
    WHERE joindate >= '".$nav_starttime."'");
$nav_users = $vbulletin->db->query_first("SELECT
    COUNT(*) AS count
    FROM ".TABLE_PREFIX."user
    WHERE lastactivity >= '".$nav_starttime."'");
$nav_new_threads = $vbulletin->db->query_first("SELECT
    COUNT(*) AS count
    FROM ".TABLE_PREFIX."thread
    WHERE dateline >= '".$vbulletin->userinfo['lastvisit']."'");
$nav_new_posts = $vbulletin->db->query_first("SELECT
    COUNT(*) AS count
    FROM ".TABLE_PREFIX."post
    WHERE dateline >= '".$vbulletin->userinfo['lastvisit']."'"); 
I came up with a LEFT JOIN scenario, but alas, due to my lack of knowledge on this, it failed...
PHP Code:
$nav_query = $vbulletin->db->query_first("SELECT 
    COUNT(new_user.*) AS new_users_today, 
    COUNT(thread.*) AS new_threads, 
    COUNT(post.*) AS new_posts, 
    COUNT(user.*) AS users 
    FROM ".TABLE_PREFIX."user AS new_user 
    LEFT JOIN ".TABLE_PREFIX."thread AS thread ON(thread.dateline >= '".$vbulletin->userinfo['lastvisit']."') 
    LEFT JOIN ".TABLE_PREFIX."post AS post ON(post.dateline >= '".$vbulletin->userinfo['lastvisit']."') 
    LEFT JOIN ".TABLE_PREFIX."user AS user ON(user.lastactivity >= '".$nav_starttime."') 
    WHERE new_user.joindate >= '".$nav_starttime."'"); 
Any and all help on this would be greatly appreciated...I'm just straight up stuck, I've been through all of my reference materials, but just cannot figure out how to do this...

Thank you in advance for the assistance!

)O( Cloudrunner )O(

filburt1 07-04-2005 04:32 AM
Can you explain in English what specifically you are you trying to do (i.e., what data you are trying to retrieve)?

Cloudrunner 07-04-2005 04:37 AM
Quote:
Originally Posted by filburt1
Can you explain in English what specifically you are you trying to do (i.e., what data you are trying to retrieve)?
Oh that part's easy LOL

I'm looking for the total count of new users registered today, the total count of users visited, the total count of new threads since a users last visit, and the total count of new posts since a user's last visit all in one shot.

I can do that easily with the first four queries, but I want to squish those four queries into one to cus everyone and their dog here gripes about multiple queries in a hack, so I figured if I could turn those four queries into one, no one could gripe :D

Marco van Herwaarden 07-04-2005 07:17 AM
You can not combine counts from different types of data into 1 query i think.

deathemperor 07-04-2005 07:41 AM
I dont think this is right:

PHP Code:
ON(thread.dateline >= '".$vbulletin->userinfo['lastvisit']."') 
ON() is used for the connection between 2 tables, not for condition IIRC.

I learnt SQL half a year ago and almost forgot complex parts +_+


All times are GMT. The time now is 09:32 PM.

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.01132 seconds
  • Memory Usage 1,737KB
  • 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
  • (3)bbcode_php_printable
  • (1)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (5)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