How to query custom database table

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • cowbert
    New Member
    • Apr 2010
    • 7
    • 4.0.0

    [Forum] How to query custom database table

    I created a custom mysql table using a separate prefix from my vbulletin table prefix and I wrote a plugin to read from it in vB, but when I do:

    PHP Code:
    $result $vbulletin->db->query_first_slave("
       SELECT some_field
       FROM my_custom_table
       WHERE some_field = 'some value'"
    ); 
    It returns error:

    Code:
    Invalid SQL:
      
       SELECT some_field
       FROM my_custom_table
       WHERE some_field = 'some value';
      
    MySQL Error   : Table 'vb.my_custom_table' doesn't exist
    Even though when I am in mysql CLI as the vb database user and:
    Code:
    mysql> select * from my_custom_table;
    +------------+
    | some_field |
    +------------+
    | foo        |
    +------------+
    1 row in set (0.00 sec)
    The sql call fails via the "Execute a SQL Query" via admincp as well. How do I get my plugin to read my custom table? Do I have to do an end run around and create a 2nd new database connection outside of using the vB objects?
  • Lynne
    Former vBulletin Support
    • Oct 2004
    • 26255

    #2
    Is your new table in the same database as all the other vbulletin tables?

    Please don't PM or VM me for support - I only help out in the threads.
    vBulletin Manual & vBulletin 4.0 Code Documentation (API)
    Want help modifying your vbulletin forum? Head on over to vbulletin.org
    If I post CSS and you don't know where it goes, throw it into the additional.css template.

    W3Schools <- awesome site for html/css help

    Comment

    • renep
      Senior Member
      • Aug 2005
      • 596
      • 3.8.x

      #3
      What does
      Code:
      show tables
      report in the mysql cli and in AdminCP -> Maintenance -> Execute SQL Query?
      "The lurking suspicion that something could be simplified is the world's richest source of rewarding challenges"
      - Edsger Dijkstra

      Comment

      • cowbert
        New Member
        • Apr 2010
        • 7
        • 4.0.0

        #4
        Originally posted by renep
        What does
        Code:
        show tables
        report in the mysql cli and in AdminCP -> Maintenance -> Execute SQL Query?
        Code:
        show tables   	              
          	[B]Results: 232 (0.0042s), Page 1 of 1[/B]   	Tables_in_vb   	vbaccess   	vbaction   	vbad   	vbadcriteria   	vbadminhelp   	vbadministrator   	vbadminlog   	vbadminmessage   	vbadminutil   	vbalbum   	vbalbumupdate   	vbannouncement   	vbannouncementread   	vbattachment   	vbattachmentcategory   	vbattachmentcategoryuser   	vbattachmentpermission   	vbattachmenttype   	vbattachmentviews   	vbavatar   	vbbbcode   	vbbbcode_video   	vbblock   	vbblockconfig   	vbblocktype   	vbblog   	vbblog_attachment   	vbblog_attachmentlegacy   	vbblog_attachmentviews   	vbblog_category   	vbblog_categorypermission   	vbblog_categoryuser   	vbblog_custom_block   	vbblog_custom_block_parsed   	vbblog_deletionlog   	vbblog_editlog   	vbblog_featured   	vbblog_groupmembership   	vbblog_grouppermission   	vbblog_hash   	vbblog_moderation   	vbblog_moderator   	vbblog_pinghistory   	vbblog_rate   	vbblog_read   	vbblog_relationship   	vbblog_search   	vbblog_searchresult   	vbblog_subscribeentry   	vbblog_subscribeuser   	vbblog_summarystats   	vbblog_tachyentry   	vbblog_text   	vbblog_textparsed   	vbblog_trackback   	vbblog_trackbacklog   	vbblog_user   	vbblog_usercss   	vbblog_usercsscache   	vbblog_userread   	vbblog_userstats   	vbblog_views   	vbblog_visitor   	vbbookmarksite   	vbcache   	vbcacheevent   	vbcalendar   	vbcalendarcustomfield   	vbcalendarmoderator   	vbcalendarpermission   	vbcms_article   	vbcms_category   	vbcms_grid   	vbcms_layout   	vbcms_layoutwidget   	vbcms_navigation   	vbcms_node   	vbcms_nodecategory   	vbcms_nodeconfig   	vbcms_nodeinfo   	vbcms_permissions   	vbcms_rate   	vbcms_sectionorder   	vbcms_widget   	vbcms_widgetconfig   	vbcms_widgettype   	vbcontentpriority   	vbcontenttype   	vbcpsession   	vbcron   	vbcronlog   	vbcustomavatar   	vbcustomprofilepic   	vbdatastore   	vbdeletionlog   	vbdiscussion   	vbdiscussionread   	vbeditlog   	vbevent   	vbexternalcache   	vbfaq   	vbfiledata   	vbforum   	vbforumpermission   	vbforumprefixset   	vbforumread   	vbgroupmessage   	vbgroupmessage_hash   	vbgroupread   	vbholiday   	vbhumanverify   	vbhvanswer   	vbhvquestion   	vbicon   	vbimagecategory   	vbimagecategorypermission   	vbindexqueue   	vbinfraction   	vbinfractionban   	vbinfractiongroup   	vbinfractionlevel   	vblanguage   	vbmailqueue   	vbmoderation   	vbmoderator   	vbmoderatorlog   	vbnotice   	vbnoticecriteria   	vbnoticedismissed   	vbpackage   	vbpasswordhistory   	vbpaymentapi   	vbpaymentinfo   	vbpaymenttransaction   	vbphrase   	vbphrasetype   	vbpicturecomment   	vbpicturecomment_hash   	vbpicturelegacy   	vbplugin   	vbpm   	vbpmreceipt   	vbpmtext   	vbpmthrottle   	vbpodcast   	vbpodcastitem   	vbpoll   	vbpollvote   	vbpost   	vbpostedithistory   	vbposthash   	vbpostlog   	vbpostparsed   	vbprefix   	vbprefixpermission   	vbprefixset   	vbproduct   	vbproductcode   	vbproductdependency   	vbprofileblockprivacy   	vbprofilefield   	vbprofilefieldcategory   	vbprofilevisitor   	vbranks   	vbreminder   	vbreputation   	vbreputationlevel   	vbroute   	vbrssfeed   	vbrsslog   	vbsearchcore   	vbsearchcore_text   	vbsearchgroup   	vbsearchgroup_text   	vbsearchlog   	vbsession   	vbsetting   	vbsettinggroup   	vbsigparsed   	vbsigpic   	vbsmilie   	vbsocialgroup   	vbsocialgroupcategory   	vbsocialgroupicon   	vbsocialgroupmember   	vbspamlog   	vbstats   	vbstrikes   	vbstyle   	vbstylevar   	vbstylevardfn   	vbsubscribediscussion   	vbsubscribeevent   	vbsubscribeforum   	vbsubscribegroup   	vbsubscribethread   	vbsubscription   	vbsubscriptionlog   	vbsubscriptionpermission   	vbtachyforumcounter   	vbtachyforumpost   	vbtachythreadcounter   	vbtachythreadpost   	vbtag   	vbtagcontent   	vbtagsearch   	vbtemplate   	vbtemplatehistory   	vbtemplatemerge   	vbthread   	vbthreadrate   	vbthreadread   	vbthreadredirect   	vbthreadviews   	vbupgradelog   	vbuser   	vbuseractivation   	vbuserban   	vbuserchangelog   	vbusercss   	vbusercsscache   	vbuserfield   	vbusergroup   	vbusergroupleader   	vbusergrouprequest   	vbuserlist   	vbusernote   	vbuserpromotion   	vbusertextfield   	vbusertitle   	vbvisitormessage   	vbvisitormessage_hash

        Comment

        • renep
          Senior Member
          • Aug 2005
          • 596
          • 3.8.x

          #5
          There you have it: my_custom_table is not there, that explains it.
          "The lurking suspicion that something could be simplified is the world's richest source of rewarding challenges"
          - Edsger Dijkstra

          Comment

          • cowbert
            New Member
            • Apr 2010
            • 7
            • 4.0.0

            #6
            Originally posted by renep
            There you have it: my_custom_table is not there, that explains it.
            So how do I fix this, if I show tables from the vb database, it IS there. From the mysql command line. Why is vbulletin not seeing the same tables that mysql is?!

            Comment

            • Edwin Brown
              Former vBulletin Developer
              • Mar 2009
              • 1393
              • 5.5.x

              #7
              What database is the my_custom_table in, and is it the same as the vb tables? The two obvious reasons why it wouldn't show up are (1) incorrect permissions, which you've said isn't the issue, and (2) it's in a different database.
              Please- I'm not tech support. Don't send your problem reports to me unless I've asked you to.

              Comment

              • renep
                Senior Member
                • Aug 2005
                • 596
                • 3.8.x

                #8
                If you login to the MySQL server on the same host name, using the same database name and same user name, this can't happen. Are you sure you don't have your environments mixed up?
                "The lurking suspicion that something could be simplified is the world's richest source of rewarding challenges"
                - Edsger Dijkstra

                Comment

                • cowbert
                  New Member
                  • Apr 2010
                  • 7
                  • 4.0.0

                  #9
                  Originally posted by cowbert
                  So how do I fix this, if I show tables from the vb database, it IS there. From the mysql command line. Why is vbulletin not seeing the same tables that mysql is?!
                  OHHH DUH, I was in the wrong instance, I was modifying the live instance instead of our dev instance

                  Comment

                  • renep
                    Senior Member
                    • Aug 2005
                    • 596
                    • 3.8.x

                    #10
                    These things happen
                    "The lurking suspicion that something could be simplified is the world's richest source of rewarding challenges"
                    - Edsger Dijkstra

                    Comment

                    Related Topics

                    Collapse

                    Working...