4

I would like to add column to my MySQL-database with spaces.

In terms of SO-questions this is as close as I've come Insert data in mysql colum with spaces with php

In php MyAdmin I can write the code 

ALTER TABLE `msrk_krit` ADD `test 1` VARCHAR(255)

However in php I am trying to use the code below:

mysqli_query($db, "ALTER TABLE msrk_krit ADD 'test 1' VARCHAR( 255 )")

But I get this error code:

Error description: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1 VARCHAR( 255 )' at line 1

Any ideas?

Thanks

Ola Karlsson
  • 141
  • 1
  • 2
  • 10
  • 2
    So try backticks like in phpMyAdmin: `ADD \`test 1\``. And then __forget forever__ about such columns' names – u_mulder Aug 05 '17 at 20:43
  • Do not use blanks in table or column names – Jens Aug 05 '17 at 20:45
  • There's nothing wrong with using blank spaces in table names. Sometimes you have to handle table and column names with spaces and your code should never be compromised by it. Just use the proper syntax. – Altimus Prime Aug 05 '17 at 20:46
  • 1
    Know that in phpMyAdmin, below a successful query output, there is an option (to the right, just below the query, not the results) to `Create PHP code`. This will generate PHP code for the same query that can then be copy/pasted for use. – Paul T. Aug 05 '17 at 20:48

3 Answers3

7

Do this:

mysqli_query($db, "ALTER TABLE msrk_krit ADD `test 1` VARCHAR( 255 )")

Notice that the single quotes around test 1 are actually back ticks, not quote marks.

Honestly though, you should avoid using spaces in your column names, it will be easier to maintain in the long run.

(Documentation: mysqli_query)

ashleedawg
  • 20,365
  • 9
  • 72
  • 105
Difster
  • 3,264
  • 2
  • 22
  • 32
3
mysqli_query($db, "ALTER TABLE `msrk_krit` ADD `test 1` VARCHAR(255)")
prolific
  • 765
  • 1
  • 6
  • 23
1

mysqli_query($db, "ALTER TABLE msrk_krit ADD 'test 1' VARCHAR( 255 )") should be 

mysqli_query($db, "ALTER TABLE msrk_krit ADD `test 1` VARCHAR( 255 )")
Altimus Prime
  • 2,207
  • 2
  • 27
  • 46
  • Thanks! That did it, so easy. Darn it! – Ola Karlsson Aug 05 '17 at 20:46
  • No problem. Don't let anyone get you down about spaces in column names. Spaces are completely appropriate. – Altimus Prime Aug 05 '17 at 20:47
  • You say they're appropriate, but what's your reasoning? Being able to have spaces in table names is not a reason. – Jonnix Aug 05 '17 at 20:51
  • Because they aren't invalid syntax in MySQL and some libraries and some assignment don't allow you to choose your column or table names. Some lazy people don't properly mark up their queries and end up getting broken scripts and embarrassing results. – Altimus Prime Aug 05 '17 at 20:55
  • Not being invalid doesn't make them appropriate and certainly not appropriate when going by the rest of the query their standard is for underscores (not guaranteed but extrapolation from table name). – Jonnix Aug 05 '17 at 20:58
  • It should still be avoided if possible though. Just because it can be done, doesn't mean it should. There's no reason to use back ticks if there are no spaces. In fact, your code is easier to maintain if you avoid table names with spaces because it's less that can fail when multiple developers get their hands on it. It's not lazy to not add backticks if they're not necessary. – Difster Aug 05 '17 at 20:58
  • @Difster `There's no reason to use back ticks if there are no spaces`. This isn't actually true. There are a lot of reserved names special characters with backticks, besides simply spaces. If you have a static, well considered table names in latin characters, help yourself to skip the backticks. – Altimus Prime Aug 06 '17 at 00:23