How to query custom table

[cowbert]

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:

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:

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]

Originally Posted by cowbert

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:

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:

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.

. TABLE_PREFIX .

covers all the prefixes. That should fix it.

[cowbert]

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]

Originally Posted by cowbert

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]

Originally Posted by borbole

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...