1

I want to create tables in mySQL with names that include hyphons, full stops, the % symbol and others. The name will be in a variable called image_name. How do I go about this? Of course I could replace all with underscores, but that is suboptimal. Is the default collation wrong?

My Code:

$image_name = 'abc_def.ghi%';

$sql = "CREATE TABLE $image_name(
id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
film_name VARCHAR(70) NOT NULL,
...
)";
if(mysqli_query($link, $sql)){
    echo "Table of image $image_name created successfully.";
} else{
    echo "ERROR: Could not execute $sql. " . mysqli_error($link);
}
Kevin Qiu
  • 37
  • 1
  • 4
  • 1
    This sounds like a really bad idea. Why do you want to do this? If there aren't actual words to describe the names of your tables then it sounds like you may have a design problem somewhere. – David Jan 28 '19 at 14:30
  • use backticks for that – Cid Jan 28 '19 at 14:30
  • 3
    Why? Why? **WHY?** – Salman A Jan 28 '19 at 14:30
  • @SalmanA why not, eh – Cid Jan 28 '19 at 14:30
  • Possible duplicate of [Using backticks around field names](https://stackoverflow.com/questions/261455/using-backticks-around-field-names) – Cid Jan 28 '19 at 14:31
  • 1
    Let the OP answer it. He does not have a clue how to do this and will run into more problems down the road. – Salman A Jan 28 '19 at 14:31
  • 1
    `The dot is the reference operator in SQL` and using special characters not a good practice, use `_` in your table name, this is the best practice. – devpro Jan 28 '19 at 14:35
  • @David The names of the tables are given to me like that. There is nothing I can do about that. There are actual words of course, but some do contain these characters. – Kevin Qiu Jan 28 '19 at 14:36
  • then you can clear this charcaters by using php @KevinQiu before creating tables – devpro Jan 28 '19 at 14:37
  • @KevinQiu: It still seems *very likely* that there's an overall better approach to whatever you're trying to do. Why are you building a system where people are directly managing database schemas? There are already products where people can login to a database and manage schemas. – David Jan 28 '19 at 14:38
  • if you found `_` in image name, then ignore, if you found `.` then you can replace it with `_`, if you found `%` in your image name replace with empty, make it easy for other people, who will work after you :) – devpro Jan 28 '19 at 14:39
  • Guys, there is nothing wrong badly naming tables, to grant you a lifetime job because no one could maintain your DB/Code (No, seriously, never do that). Reminds me [this answer](https://stackoverflow.com/a/52621998/8398549) I posted few monthes ago – Cid Jan 28 '19 at 14:40
  • @Cid: *"because no one could maintain your DB/Code"* - *Everything* is wrong with that. – David Jan 28 '19 at 14:41
  • @David this is sarcasm – Cid Jan 28 '19 at 14:41
  • @Cid: Sarcasm which may be missed by the OP because, well, this very question. – David Jan 28 '19 at 14:42
  • Yep got the point I edited the comment – Cid Jan 28 '19 at 14:43

2 Answers2

1

You should surround the table names with backticks - ``

IVO GELOV
  • 13,496
  • 1
  • 17
  • 26
1

The table name has to surrounded by backticks.

SQL DEMO 

CREATE TABLE `abc_def.ghi%` (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    film_name VARCHAR(70) NOT NULL
)
Juan Carlos Oropeza
  • 47,252
  • 12
  • 78
  • 118