Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions

Reply
 
Thread Tools Display Modes
  #1  
Old 01-02-2003, 07:38 PM
Darth Cow's Avatar
Darth Cow Darth Cow is offline
 
Join Date: Nov 2001
Posts: 61
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default MySQL query help please

Here is my current MySQL query...

SELECT SUM(hitscount) AS totalhits FROM hits WHERE time>123456789 AND siteid=10 GROUP BY siteid

This query works great most of the time, but sometimes there won't be any result rows that match the criteria. How would I make totalhits be equal to 0 if there are no results returned instead of a mysql resource result with no rows? Thanks for you help .
Reply With Quote
  #2  
Old 01-02-2003, 07:51 PM
Xenon's Avatar
Xenon Xenon is offline
 
Join Date: Oct 2001
Location: Bavaria
Posts: 12,878
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

this part here is useless if it's the full query:

AND siteid=10 GROUP BY siteid

just remove the group by and i think it should work...
Reply With Quote
  #3  
Old 01-02-2003, 11:45 PM
Darth Cow's Avatar
Darth Cow Darth Cow is offline
 
Join Date: Nov 2001
Posts: 61
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I realized that I was needlessly added in the SUM() part that was already taken care of by the grouping, so it was repetative. The solution I can up with on my own (using mysql_num_rows()) isn't nearly as elegant as merely removing the group by part and using intval() on the result. Thanks for you help .
Reply With Quote
  #4  
Old 01-03-2003, 12:24 AM
TECK's Avatar
TECK TECK is offline
 
Join Date: Nov 2001
Location: Canada
Posts: 4,182
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Quote:
Originally posted by Darth Cow
Here is my current MySQL query...

SELECT SUM(hitscount) AS totalhits FROM hits WHERE time>123456789 AND siteid=10 GROUP BY siteid

This query works great most of the time, but sometimes there won't be any result rows that match the criteria. How would I make totalhits be equal to 0 if there are no results returned instead of a mysql resource result with no rows? Thanks for you help .
PHP Code:
$count['hits'] = 0;
if ( 
$docount $DB_site->query_first("
  SELECT COUNT(*) AS totalhits
  FROM hits
  WHERE dateline>
$bbuserinfo[lastvisit] AND siteid=10
"
) and !empty( $docount['totalhits'] ) )
{
  
$count['hits'] = number_format$docount['totalhits'] );

Reply With Quote
  #5  
Old 01-03-2003, 12:32 AM
Logik's Avatar
Logik Logik is offline
 
Join Date: Nov 2002
Location: Tx
Posts: 432
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I think i see a typo in that..
Reply With Quote
  #6  
Old 01-03-2003, 12:33 AM
Logik's Avatar
Logik Logik is offline
 
Join Date: Nov 2002
Location: Tx
Posts: 432
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

PHP Code:
$count['hits'] = 0;
if ( 
$docount $DB_site->query_first)"
  SELECT COUNT(*) AS totalhits
  FROM hits
  WHERE dateline>
$bbuserinfo[lastvisit] AND siteid=10
"
) and !empty( $docount['totalhits'] ) )
{
  
$count['hits'] = number_format$docount['totalhits'] );

You had the ')' turned the wrong way.. I could be wrong.. aranoid:
Reply With Quote
  #7  
Old 01-03-2003, 12:35 AM
TECK's Avatar
TECK TECK is offline
 
Join Date: Nov 2001
Location: Canada
Posts: 4,182
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

The code above will give you a very nice error. My code is correct.
Reply With Quote
  #8  
Old 01-03-2003, 12:41 AM
Logik's Avatar
Logik Logik is offline
 
Join Date: Nov 2002
Location: Tx
Posts: 432
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Yes. Indeed. A VERY nice error.. Hey, cant hate me cause im trying
Reply With Quote
  #9  
Old 01-03-2003, 12:47 AM
TECK's Avatar
TECK TECK is offline
 
Join Date: Nov 2001
Location: Canada
Posts: 4,182
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

That's how we all learn, by doing mistakes.
Cheers.
Reply With Quote
  #10  
Old 01-03-2003, 01:45 AM
Logik's Avatar
Logik Logik is offline
 
Join Date: Nov 2002
Location: Tx
Posts: 432
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

hehe... :OI I just seen that '(' and was thinking.. 'whoa dont it have to be the other way?'.. But i was wrong
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT. The time now is 02:18 PM.


Powered by vBulletin® Version 3.8.12 by vBS
Copyright ©2000 - 2024, vBulletin Solutions Inc.
X vBulletin 3.8.12 by vBS Debug Information
  • Page Generation 0.04409 seconds
  • Memory Usage 2,245KB
  • Queries Executed 13 (?)
More Information
Template Usage:
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_header_end
  • (1)ad_header_logo
  • (1)ad_navbar_below
  • (1)ad_showthread_beforeqr
  • (1)ad_showthread_firstpost
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (2)bbcode_php
  • (1)bbcode_quote
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (1)pagenav
  • (1)pagenav_curpage
  • (1)pagenav_pagelink
  • (10)post_thanks_box
  • (10)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (10)post_thanks_postbit_info
  • (10)postbit
  • (10)postbit_onlinestatus
  • (10)postbit_wrapper
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper 

Phrase Groups Available:
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files:
  • ./showthread.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/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/functions_post_thanks.php 

Hooks Called:
  • init_startup
  • init_startup_session_setup_start
  • init_startup_session_setup_complete
  • cache_permissions
  • fetch_postinfo_query
  • fetch_postinfo
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • style_fetch
  • cache_templates
  • global_start
  • parse_templates
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • forumjump
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • post_thanks_function_post_thanks_off_start
  • post_thanks_function_post_thanks_off_end
  • post_thanks_function_fetch_thanks_start
  • post_thanks_function_fetch_thanks_end
  • post_thanks_function_thanked_already_start
  • post_thanks_function_thanked_already_end
  • fetch_musername
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • post_thanks_function_can_thank_this_post_start
  • pagenav_page
  • pagenav_complete
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete