Go Back   vb.org Archive > vBulletin 3 Discussion > vB3 Programming Discussions
FAQ Community Calendar Today's Posts Search

Reply
 
Thread Tools Display Modes
  #1  
Old 01-20-2007, 08:42 AM
thincom2000 thincom2000 is offline
 
Join Date: May 2006
Location: Bronx, NY
Posts: 1,205
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default Can't get MySQL JOINs to work right

I am trying to write a single query that does the following:

- reads all threads where forumid = $forumid
- reads all posts where threads.firstpostid = postid
- reads all polls where threads.pollid = pollid, but only if a poll exists

I tried:

PHP Code:
SELECT thread.*, post.*, IF(thread.pollid poll.pollidpoll.*, '')
FROM " . TABLE_PREFIX . "thread" . TABLE_PREFIX . "post" . TABLE_PREFIX . "poll"
WHERE thread.forumid = '" 
$forumid "'
    AND post.postid = thread.firstpostid
    AND IF(thread.pollid = poll.pollid, poll.pollid = thread.pollid, '1 > 0')
ORDER BY dateline DESC 
but it just gives T_STRING errors and returns NULL...
Reply With Quote
  #2  
Old 01-20-2007, 08:58 AM
Marco van Herwaarden Marco van Herwaarden is offline
 
Join Date: Jul 2004
Posts: 25,415
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Use LEFT JOIN's.
Reply With Quote
  #3  
Old 01-20-2007, 04:13 PM
thincom2000 thincom2000 is offline
 
Join Date: May 2006
Location: Bronx, NY
Posts: 1,205
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

I don't think this has the same meaning as what I suggested in my first post, but here it is with LEFT JOINs and still not working (database error):

Code:
SELECT thread.*, post.*, IF(thread.pollid = poll.pollid, 'poll.*', '')
FROM " . TABLE_PREFIX . "thread AS thread
LEFT JOIN " . TABLE_PREFIX . "post AS post ON (post.postid = thread.firstpostid)
IF(thread.pollid = poll.pollid, 'LEFT JOIN " . TABLE_PREFIX . "poll AS poll ON (poll.pollid = thread.pollid)', '')
WHERE thread.forumid = " . $forumid . "
Reply With Quote
  #4  
Old 01-20-2007, 04:31 PM
ConqSoft's Avatar
ConqSoft ConqSoft is offline
 
Join Date: Jul 2003
Location: Raleigh, NC
Posts: 686
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

What's the error given?
Reply With Quote
  #5  
Old 01-20-2007, 04:35 PM
thincom2000 thincom2000 is offline
 
Join Date: May 2006
Location: Bronx, NY
Posts: 1,205
Благодарил(а): 0 раз(а)
Поблагодарили: 0 раз(а) в 0 сообщениях
Default

Code:
SQL query:

SELECT thread. * , post. * , IF( thread.pollid = poll.pollid, 'poll.*', '' )
FROM forum_thread AS thread
LEFT JOIN forum_post AS post ON ( post.postid = thread.firstpostid ) IF( thread.pollid = poll.pollid, 'LEFT JOIN forum_poll AS poll ON (poll.pollid = thread.pollid)', '' )
WHERE thread.forumid =2
LIMIT 0 , 30

MySQL said:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF( thread . pollid = poll . pollid , 'LEFT JOIN forum_poll AS poll ON (poll.pol' at line 1
Removed the entire poll table check from the query. All I need is to check if a poll exists anyway, which can be done with thread.pollid

Was returning NULL because I somehow deleted every record in my post table. Good thing I made a backup yesterday.

Now the query works as expected:

Code:
SELECT thread.*, post.*
FROM " . TABLE_PREFIX . "thread AS thread
LEFT JOIN " . TABLE_PREFIX . "post AS post ON (post.postid = thread.firstpostid)
WHERE thread.forumid = " . $forumid . "
Reply With Quote
Reply


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 12:56 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.08518 seconds
  • Memory Usage 2,202KB
  • 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
  • (3)bbcode_code
  • (1)bbcode_php
  • (1)footer
  • (1)forumjump
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)navbar
  • (3)navbar_link
  • (120)option
  • (5)post_thanks_box
  • (5)post_thanks_button
  • (1)post_thanks_javascript
  • (1)post_thanks_navbar_search
  • (5)post_thanks_postbit_info
  • (5)postbit
  • (5)postbit_onlinestatus
  • (5)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
  • tag_fetchbit_complete
  • forumrules
  • navbits
  • navbits_complete
  • showthread_complete