Creating An Sql Table With Email Address As Title

I'm struggling to create SQL tables using email addresses as the table name. The code I am using produces the following error: You have an error in your SQL syntax; check the manu

Solution 1:

You should be able to get around this by escaping the table name with backticks (`). But really for most circumstances this is a bad way to design your database.

You should use one table with an emailaddress field instead of creating a new table for each user/email address, unless you have a specific need to do so.

Solution 2:

The problem here is that you're not using backticks. Using backticks allows you to choose identifiers more freely:

CREATETABLE `$emailaddress` ( id INTNOTNULL AUTO_INCREMENT, PRIMARY KEY(id), name VARCHAR(30), age INT)

See here for more information on this.

Solution 3:

http://dev.mysql.com/doc/refman/5.5/en/identifiers.html

Permitted characters in quoted identifiers include the full Unicode Basic Multilingual Plane (BMP), except U+0000:

ASCII: U+0001 .. U+007F

Solution 4:

It would probably be to your advantage to have one table designed as follows

CREATETABLE emailaddr
(
    id INTNOTNULL AUTO_INCREMENT,
    email varchar(50),
    PRIMARY KEY(id),
    name VARCHAR(30),
    age INT,
    unique key (email)
);

The reason I suggest this is in the event you have to add more fields to the table, you only have to alter one table. With the design you are using, you would have to query a table that may not exist yet (something mysql would do for you upfront). Should you add fields, you have to cycle through hundreds, probably thousands, of table to make needed changes.

You could query for an email address like this:

SELECT*FROM db_name.emailaddr where email ='$emailaddress';

With your design, you must check if the table exists first

SELECTCOUNT(1) FROM information_schema.tables
WHERE table_schema ='db_name'AND table_name ='$emailaddress';

Then, if the table count is 1, then you could

SELECT * FROM db_name.`$emailaddress`;

Solution 5:

Change your code to this:

// create new table for users tosses// Make a MySQL Connectionmysql_connect("localhost", "$sqlusername", "$sqlpassword") or die(mysql_error());
        mysql_select_db("$db_name") or die(mysql_error());

// Create a MySQL table in the selected databasemysql_query("CREATE TABLE `$emailaddress`(
        id INT NOT NULL AUTO_INCREMENT, 
        PRIMARY KEY(id),
        name VARCHAR(30), 
        age INT)")
        or die(mysql_error()); 

You need back ticks "`" around the variable.