vb.org Archive

vb.org Archive (https://vborg.vbsupport.ru/index.php)
-   vB4 Programming Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=252)
-   -   How to query custom table (https://vborg.vbsupport.ru/showthread.php?t=249613)

cowbert 08-30-2010 02:35 PM
How to query custom table
 
I created a custom mysql table using a separate 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?

borbole 08-30-2010 02:46 PM
Quote:
Originally Posted by cowbert (Post 2091594)
I created a custom mysql table using a separate 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?

Can you try this?

PHP Code:
$result = $vbulletin->db->query_first_slave("
   SELECT some_field
   FROM " . TABLE_PREFIX . "my_custom_table
   WHERE some_field = 'some value'"); 
I think you forgot to include your custom table prefix in the query.
Code:
. TABLE_PREFIX .
covers all the prefixes. That should fix it.

cowbert 08-30-2010 02:53 PM
This does/will not work because I am pretty sure TABLE_PREFIX points to the $config['Database']['tableprefix'] value.

It looks like vbulletin is unable to query any tables except its own. Unless there is some sort of tablenamespace cache I have to flush. (I'm not using any caching just the normal mysqli to a single mysql server setup).

borbole 08-30-2010 03:13 PM
Quote:
Originally Posted by cowbert (Post 2091600)
This does/will not work because I am pretty sure TABLE_PREFIX points to the $config['Database']['tableprefix'] value.

It looks like vbulletin is unable to query any tables except its own. Unless there is some sort of tablenamespace cache I have to flush. (I'm not using any caching just the normal mysqli to a single mysql server setup).
That will work fine. I have coded several mods which use custom tables and that works ok for me.

The error you got is, as I mentioned above, that your custom table is using a prefix and when the query is run, it doesn''t find it.

Or enter the full table name with the prefix included in the query.

cowbert 08-30-2010 03:21 PM
Quote:
Originally Posted by borbole (Post 2091607)
That will work fine. I have coded several mods which use custom tables and that works ok for me.

The error you got is, as I mentioned above, that your custom table is using a prefix and when the query is run, it doesn''t find it.

Or enter the full table name with the prefix included in the query.
My Vbulletin instance uses a prefix ($config['Database']['tableprefix'] = 'vb'; ), my custom table does not....

But even if I created vbmy_custom_table, and called .TABLE_PREFIX. it does not find the table...


All times are GMT. The time now is 03:33 AM.

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.01134 seconds
  • Memory Usage 1,734KB
  • 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
  • (5)bbcode_code_printable
  • (3)bbcode_php_printable
  • (3)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