Adding TABLE_PREFIX in a manual query

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

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.

[Cloudrunner]

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.

[Mr Blunt]

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?

[Paul M]

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.

[Mr Blunt]

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?

[Christine]

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

[akanevsky]

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.

[Marco van Herwaarden]

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

[akanevsky]

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

[Marco van Herwaarden]

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.