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:

$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]

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:

$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?

$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]

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]

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