How to Have the Table Name as "Option" in MySQL

Can we have the table name as option in MySQL?

Pick a different name (one that isn't a reserved word in your RDBMS) and save yourself and whoever else might work on it many headaches.

Get table names using SELECT statement in MySQL

To get the name of all tables use:

SELECT table_name FROM information_schema.tables;

To get the name of the tables from a specific database use:

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'your_database_name';

Now, to answer the original question, use this query:

INSERT INTO table_name
    SELECT table_name FROM information_schema.tables
        WHERE table_schema = 'your_database_name';

For more details see: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html

Syntax error due to using a reserved word as a table or column name in MySQL

The Problem

In MySQL, certain words like SELECT, INSERT, DELETE etc. are reserved words. Since they have a special meaning, MySQL treats it as a syntax error whenever you use them as a table name, column name, or other kind of identifier - unless you surround the identifier with backticks.

As noted in the official docs, in section 10.2 Schema Object Names (emphasis added):

Certain objects within MySQL, including database, table, index, column, alias, view, stored procedure, partition, tablespace, and other object names are known as identifiers.
...
If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.
...
The identifier quote character is the backtick ("`"):

A complete list of keywords and reserved words can be found in section 10.3 Keywords and Reserved Words. In that page, words followed by "(R)" are reserved words. Some reserved words are listed below, including many that tend to cause this issue.

ADD
AND
BEFORE
BY
CALL
CASE
CONDITION
DELETE
DESC
DESCRIBE
FROM
GROUP
IN
INDEX
INSERT
INTERVAL
IS
KEY
LIKE
LIMIT
LONG
MATCH
NOT
OPTION
OR
ORDER
PARTITION
RANK
REFERENCES
SELECT
TABLE
TO
UPDATE
WHERE

The Solution

You have two options.

1. Don't use reserved words as identifiers

The simplest solution is simply to avoid using reserved words as identifiers. You can probably find another reasonable name for your column that is not a reserved word.

Doing this has a couple of advantages:

It eliminates the possibility that you or another developer using your database will accidentally write a syntax error due to forgetting - or not knowing - that a particular identifier is a reserved word. There are many reserved words in MySQL and most developers are unlikely to know all of them. By not using these words in the first place, you avoid leaving traps for yourself or future developers.
The means of quoting identifiers differs between SQL dialects. While MySQL uses backticks for quoting identifiers by default, ANSI-compliant SQL (and indeed MySQL in ANSI SQL mode, as noted here) uses double quotes for quoting identifiers. As such, queries that quote identifiers with backticks are less easily portable to other SQL dialects.

Purely for the sake of reducing the risk of future mistakes, this is usually a wiser course of action than backtick-quoting the identifier.

2. Use backticks

If renaming the table or column isn't possible, wrap the offending identifier in backticks (`) as described in the earlier quote from 10.2 Schema Object Names.

An example to demonstrate the usage (taken from 10.3 Keywords and Reserved Words):

mysql> CREATE TABLE interval (begin INT, end INT);
ERROR 1064 (42000): You have an error in your SQL syntax.
near 'interval (begin INT, end INT)'mysql> CREATE TABLE `interval` (begin INT, end INT);
Query OK, 0 rows affected (0.01 sec)

Similarly, the query from the question can be fixed by wrapping the keyword key in backticks, as shown below:

INSERT INTO user_details (username, location, `key`)
VALUES ('Tim', 'Florida', 42)";               ^   ^

MySql select table option

In your PHP form why not have a simple select drop down like you have suggested with each drop-down option containing the table name that you want to insert into.

<select name='table-name'>
<option value='table1'>Table One</option>
<option value='table2'>Table Two</option>
</select>

Then simply get the value in PHP once your form has been posted using

$_POST['table-name'] or $_GET['table-name'] depending on your form method of course.

Bare in mind though that you know exacly what you are doing here as giving away vital information about your table structures can aid hackers in their dirty tricks.

You could always put the option values to identifier values instead of the actual table names and then using your PHP form handler map these to the correct table names. For example put your option values as simply 1 and 2 and then let your form handler convert these to the correct table names, only accepting specified values.

A couple of notes.

Always properly validate user input before connecting with your database using the user inputted information.

I hope you are using prepared statements for your database calls and binding the user inputted values to these, using PDO or similar for example

Can't use $_post value as table name for mysql in php

$_POST works like an array So you will need to get values from $_POST by his indexs i.e in your case subject,name etc. So Remove ' while assigning the values to variable.

$title = $_POST['subject'];
$by = $_POST['name'];
$short = $_POST['short'];
$long = $_POST['long'];
$portal = strtolower($_POST['portal']);

NOTE : The names 'by,long' are MySQL reserved keywords. So Change them.

Update your SQL from

 $sql = "INSERT INTO $portal (id, title, by, short, long) VALUES ('', '$title', '$by', '$short', '$long')";

 $sql = "INSERT INTO  $portal (`id`, `title`, `info_bys`, `info_shorts`, `info_longs`) VALUES ('', '$title', '$by', '$short', '$long')";

Your sql is vulnerable So use

// prepare and bind
$stmt = $conn->prepare("INSERT INTO  $portal (`title`, `info_bys`, `info_shorts`, `info_longs`) VALUES (?, ?, ?, ?)");
$stmt->bind_param($title, $by, $short, $long);
$stmt->execute();