vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB3 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=15)
-   -   query problem (https://vborg.vbsupport.ru/showthread.php?t=75004)

miz 01-24-2005 02:15 PM
query problem
 
PHP Code:
$threads = $DB_site->query("
         SELECT $votequery $previewfield 
             thread.threadid, thread.title AS threadtitle, lastpost, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
             lastposter, thread.dateline, IF(views<=replycount, replycount+1, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach
             " . iif($vboptions['threadsubscribed'] AND $bbuserinfo['userid'], ", NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed") . "
             " . iif(!$deljoin, ", NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid,    deletionlog.username AS del_username, deletionlog.reason AS del_reason") . " 
             FROM " . TABLE_PREFIX . "thread AS thread
         " . iif(!$deljoin, " LEFT JOIN " . TABLE_PREFIX . "deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')") . "
         " . iif($vboptions['threadsubscribed'] AND $bbuserinfo['userid'], " LEFT JOIN " . TABLE_PREFIX . "subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = $bbuserinfo[userid])") . "
         $previewjoin
                WHERE thread.threadid IN (0$ids)
         ORDER BY sticky DESC, $sqlsortfield $sqlsortorder
     "); 
this is the orginal query from forumdisplay
now i tired to modify it abit

PHP Code:
$threads = $DB_site->query("
         SELECT user.userid AS user_uid , user.teamid AS userteamid, $votequery $previewfield 
             thread.threadid, thread.title AS threadtitle, lastpost, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
             lastposter, thread.dateline, IF(views<=replycount, replycount+1, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach
             " . iif($vboptions['threadsubscribed'] AND $bbuserinfo['userid'], ", NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed") . "
             " . iif(!$deljoin, ", NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid,    deletionlog.username AS del_username, deletionlog.reason AS del_reason") . " 
             FROM " . TABLE_PREFIX . "thread AS thread
         " . iif(!$deljoin, " LEFT JOIN " . TABLE_PREFIX . "deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')") . "
         " . iif($vboptions['threadsubscribed'] AND $bbuserinfo['userid'], " LEFT JOIN " . TABLE_PREFIX . "subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = $bbuserinfo[userid])") . "
         $previewjoin
         LEFT JOIN " . TABLE_PREFIX . "user ON(user.userid = thread.postuserid)
         WHERE thread.threadid IN (0$ids)
         ORDER BY sticky DESC, $sqlsortfield $sqlsortorder
     "); 
now i got this error :
PHP Code:
Invalid SQL: 
         SELECT user.userid AS user_uid , user.teamid AS userteamid, 
             IF(votenum >= 1, votenum, 0) AS votenum,
             IF(votenum >= 1 AND votenum > 0, votetotal / votenum, 0) AS voteavg,
          post.pagetext AS preview, 
             thread.threadid, thread.title AS threadtitle, lastpost, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
             lastposter, thread.dateline, IF(views<=replycount, replycount+1, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach
             , NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed
             , NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid,    deletionlog.username AS del_username, deletionlog.reason AS del_reason 
             FROM thread AS thread
          LEFT JOIN deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
          LEFT JOIN subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = 1)
         LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
         LEFT JOIN user ON(user.userid = thread.postuserid)
         WHERE thread.threadid IN (0,2,1)
         ORDER BY sticky DESC, lastpost DESC
     
 mysql error: Column: 'lastpost' in field list is ambiguous
 
 mysql error number: 1052 
what am i doing worng ?

Marco van Herwaarden 01-24-2005 02:31 PM
Quote:
mysql error: Column: 'lastpost' in field list is ambiguous
Means that the column 'lastpost' is found in to tables that are in the query. SQL don't know which instance of lastpost you mean.

You should use a syntax like:

[sql]SELECT a.ambiguousfield, b.ambiguousfield FROM tablea AS a, tableb AS b[/sql]

Using the "table AS xx" you can create a short name to refer to a table, then prefix the column with that shortname and a dot.

PS This is general info as i didn't even tried to read your query ;)

miz 01-24-2005 02:33 PM
hmm with out adding the user part its working fine i also tried with FROM user AS user
but still same error

Marco van Herwaarden 01-24-2005 02:42 PM
Well both the thread and the user table got a column called 'lastpost'.

So you would have to refer to that column by either user.lastpost or thread.lastpost

miz 01-24-2005 02:51 PM
Quote:
Originally Posted by MarcoH64
Well both the thread and the user table got a column called 'lastpost'.

So you would have to refer to that column by either user.lastpost or thread.lastpost
edited the query
and this is the error

PHP Code:
Invalid SQL: 
         SELECT user.userid AS user_uid , user.teamid AS userteamid, user.lastpost AS userlastpost, 
             IF(votenum >= 1, votenum, 0) AS votenum,
             IF(votenum >= 1 AND votenum > 0, votetotal / votenum, 0) AS voteavg,
          post.pagetext AS preview, 
             thread.threadid, thread.title AS threadtitle, lastpost, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
             lastposter, thread.dateline, IF(views<=replycount, replycount+1, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach
             , NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed
             , NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid,    deletionlog.username AS del_username, deletionlog.reason AS del_reason 
             FROM thread AS thread
          LEFT JOIN deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
          LEFT JOIN subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = 1)
         LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
         LEFT JOIN user AS user ON(user.userid = thread.postuserid)
         WHERE thread.threadid IN (0,2,1)
         ORDER BY sticky DESC, lastpost DESC
     
 mysql error: Column: 'lastpost' in field list is ambiguous 
same 1

sabret00the 01-24-2005 02:54 PM
PHP Code:
         SELECT user.userid AS user_uid , user.teamid AS userteamid, user.lastpost AS userlastpost,
            IF(votenum >= 1, votenum, 0) AS votenum,
            IF(votenum >= 1 AND votenum > 0, votetotal / votenum, 0) AS voteavg,
         post.pagetext AS preview,
            thread.threadid, thread.title AS threadtitle, thread.lastpost, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
            lastposter, thread.dateline, IF(views<=replycount, replycount+1, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach
            , NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed
            , NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid,    deletionlog.username AS del_username, deletionlog.reason AS del_reason
            FROM thread AS thread
         LEFT JOIN deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
         LEFT JOIN subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = 1)
        LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
        LEFT JOIN user AS user ON(user.userid = thread.postuserid)
        WHERE thread.threadid IN (0,2,1)
        ORDER BY sticky DESC, lastpost DESC 
should fix it.

Marco van Herwaarden 01-24-2005 03:13 PM
Quote:
Originally Posted by miz
edited the query
and this is the error

PHP Code:
Invalid SQL: 
         SELECT user.userid AS user_uid , user.teamid AS userteamid, user.lastpost AS userlastpost, 
             IF(votenum >= 1, votenum, 0) AS votenum,
             IF(votenum >= 1 AND votenum > 0, votetotal / votenum, 0) AS voteavg,
          post.pagetext AS preview, 
             thread.threadid, thread.title AS threadtitle, lastpost, thread.forumid, pollid, open, replycount, postusername, postuserid, thread.iconid AS threadiconid,
             lastposter, thread.dateline, IF(views<=replycount, replycount+1, views) AS views, notes, thread.visible, sticky, votetotal, thread.attach
             , NOT ISNULL(subscribethread.subscribethreadid) AS issubscribed
             , NOT ISNULL(deletionlog.primaryid) AS isdeleted, deletionlog.userid AS del_userid,    deletionlog.username AS del_username, deletionlog.reason AS del_reason 
             FROM thread AS thread
          LEFT JOIN deletionlog AS deletionlog ON(thread.threadid = deletionlog.primaryid AND type = 'thread')
          LEFT JOIN subscribethread AS subscribethread ON(subscribethread.threadid = thread.threadid AND subscribethread.userid = 1)
         LEFT JOIN post AS post ON(post.postid = thread.firstpostid)
         LEFT JOIN user AS user ON(user.userid = thread.postuserid)
         WHERE thread.threadid IN (0,2,1)
         ORDER BY sticky DESC, lastpost DESC
     
 mysql error: Column: 'lastpost' in field list is ambiguous 
same 1
You are using the column 'lastpost' more then once, so you should also change it more then once.


All times are GMT. The time now is 02:41 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.01772 seconds
  • Memory Usage 1,861KB
  • 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
  • (6)bbcode_php_printable
  • (3)bbcode_quote_printable
  • (1)footer
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (6)option
  • (1)post_thanks_navbar_search
  • (1)printthread
  • (7)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