vb.org Archive - Adding TABLE_PREFIX in a manual query

vb.org Archive (https://vborg.vbsupport.ru/index.php)

- vB3 General Discussions (https://vborg.vbsupport.ru/forumdisplay.php?f=111)

- - Adding TABLE_PREFIX in a manual query (https://vborg.vbsupport.ru/showthread.php?t=91919)

Adding TABLE_PREFIX in a manual query

Quote:

Query
ALTER TABLE " . TABLE_PREFIX . " user ADD COLUMN downloads TEXT NOT NULL

vBulletin Message
An error occurred while attempting to execute your query. The following information was returned.
error number: 1064
error desc: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '" . TABLE_PREFIX . " user ADD COLUMN downloads TEXT NOT NULL' a

Now please note his error shows a space between the quote mark and the word "user". My query does not have a space there, and since that user IS a coder here, I suspect he did not cut n paste my query incorrectly (but possible since I didn't ask him to confirm).

1) Is this a vbulletin bug and it's adding a space when it shouldn't?
2) Is the query I ask people to run incorrect?

Why am I now thinking that the " marks should be '
Hmmm
HELP?

For now I asked my installers to manually edit the query with their prefix if they use one but I'd rather not leave it like that if possible.

Crap, I'm sorry I asked a question in here.
I didn't mean that.

Can someone move this please?
(or answer it and it will then be a "how-to", LOL)
Again, I am truely sorry.

Quote:

Originally Posted by Mr Blunt

I don't know if it's a vbulletin bug or maybe me telling people to do something that's incorrect and I seek advise here as I don't wish to report it as a bug until someone with more knowledge tells me they think the same.

In my "whodownloaded this attachment" hack....
https://vborg.vbsupport.ru/showthread.php?t=91390
.... I require adding one new field to vb's existing "user" table.

Post#13 shows C.Birch with a 1064 error when running my query.

Now please note his error shows a space between the quote mark and the word "user". My query does not have a space there, and since that user IS a coder here, I suspect he did not cut n paste my query incorrectly (but possible since I didn't ask him to confirm).

1) Is this a vbulletin bug and it's adding a space when it shouldn't?
2) Is the query I ask people to run incorrect?

Why am I now thinking that the " marks should be '
Hmmm
HELP?

For now I asked my installers to manually edit the query with their prefix if they use one but I'd rather not leave it like that if possible.

Crap, I'm sorry I asked a question in here.
I didn't mean that.

Can someone move this please?
(or answer it and it will then be a "how-to", LOL)
Again, I am truely sorry.

if you are telling them to do a manual query and then tell them to cut and paste exactly the following query

PHP Code:


		
			
ALTER TABLE ".TABLE_PREFIX."user ADD COLUMN downloads TEXT NOT NULL

this will not work if they do a true cut and paste. The term 'TABLE_PREFIX' is a php definition from within vBulletin's resident filesystem. Unless they are using that query from within a php script that has called vBulletin's global.php, this term will not be defined, and MySQL does not know what that means.

I would tell the users to do the manual query

PHP Code:


		
			
ALTER TABLE *YourTablePrefix*user ADD COLUMN downloads TEXT NOT NULL

and to make sure that they replace the *YourTablePrefix* with what they have assigned the table prefix as, if any.

To explain better with your query, if the user who is running the query has a table prefix of "vb3_", then the query should be

PHP Code:


		
			
ALTER TABLE vb3_user ADD COLUMN downloads TEXT NOT NULL

for a manual run. Whereas a user that is not using a table prefix the query would look like this

PHP Code:


		
			
ALTER TABLE user ADD COLUMN downloads TEXT NOT NULL

This is most likely where your problem has occured. The user will be required to replace the TABLE_PREFIX with what their system is using, otherwise MySQL will be looking for a table that is nonexistant.

Your fix is basically what I told my other thread (meaning I concurr with you about solution).

But if they run the query from WITHIN the admincp, wouldn't globals be loaded?
I suppose it's possible he ran it from phpmyadmin, but why would he have?

It won't work from within the admincp either, the query is just passed straight to mysql (as is) - mysql will trip over the table prefix just the same.

Thanks guys!!

On a related note, I notice Cloudrunner has no semicolon at the end.

Should there be a semicolon at end of query?
Or not?
Or does it not matter either way?

Only if you are running multiple queries, which you can't do from the AdminCP.

Since when do you add columns by ADD COLUMN? When you add columns in phpmyadmin, it will show the column addition as just "ADD" without any "COLUMN" afterwards.

EDIT: This tutorial confirms what I just said. So you might wanna replace ADD COLUMN with ADD.

'ADD COLUMN' is the official syntax. You can however leave the 'COLUMN' out. Just the 'ADD' will be enough for MySQL to understand what you want.

Quote:

'ADD COLUMN' is the official syntax. You can however leave the 'COLUMN' out. Just the 'ADD' will be enough for MySQL to understand what you want.

I see, thanks. However, if there is a short way to write something, and that way is compatible with every system regardless of its version and/or configuration, then I suppose you should use the shorter way to preserve space.

If you want to be compatible with every system/configuration, you should stick to ANSI SQL, and use ADD COLUMN. There are no garantuees that i would work in future versions, and if vB would sometime support other database system (Oracle for example), you would be in more problems with only a ADD.

X vBulletin 3.8.12 by vBS Debug Information
Template Usage: (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (4)bbcode_php_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages:

Phrase Groups Available:

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.01052 seconds Memory Usage 1,750KB 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 (4)bbcode_php_printable (3)bbcode_quote_printable (1)footer (1)gobutton (1)header (1)headinclude (6)option (1)pagenav (1)pagenav_curpage (1)pagenav_pagelink (1)post_thanks_navbar_search (1)printthread (10)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 pagenav_page pagenav_complete bbcode_fetch_tags bbcode_create bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete printthread_post printthread_complete
Messages: