Create Multiple MySQL tables using PHP [closed]

Question

I am trying to create more then one MySQL tables using php but it gives errors and i can not sort out what is wrong with it.

PHP code :

$sql = ' CREATE TABLE IF NOT EXISTS `mod_reminder_entries` 
  ( 
   `id`          INT(10) NOT NULL auto_increment, 
   `user_id`     INT(10) NOT NULL, 
   `entry_name`  VARCHAR(255) NOT NULL, 
   `entry_value` INT(10) NOT NULL, 
   PRIMARY KEY (`id`), 
   FOREIGN KEY (`user_id`) REFERENCES tblclients (`id`) 
 ); 

 CREATE TABLE IF NOT EXISTS `second_table` 
  ( 
   `user_id`     INT(10) NOT NULL, 
   `fieldstotal` INT(10) NOT NULL, 
   FOREIGN KEY (`user_id`) REFERENCES tblclients (`id`) 
  ); ';

  mysql_query($sql);

It gives the error, besides do not worry about the mysql connection. I properly connect with the DB and i have tested it, it is definitely some thing wrong with syntax.

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 'CREATE TABLE IF NOT EXISTS second_table ( user_id INT

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — Naftali
– Naftali, Commented Dec 24, 2012 at 16:48
Technical differences aside, PDO is much easier to work with. — Brad Koch
– Brad Koch, Commented Dec 24, 2012 at 16:49
Thanks for a vote down, but the CMS i am working supports mysql_* functions. I do not understand why people here are so fond of down votes when they do not even have some thing to use in the heads. — S. A. Malik
– S. A. Malik, Commented Dec 24, 2012 at 16:55
Are you all sure this has not been asked before? This sounds a bit like a questions that has been asked. Also @backTangent what is the Mysql error number? Please provide it. — hakre
– hakre, Commented Dec 24, 2012 at 17:10
Even it comes late, this might be more helpful: possible duplicate of PHP: multiple SQL queries in one mysql_query statement — hakre
– hakre, Commented Dec 24, 2012 at 17:12

Salman Arshad · Accepted Answer · 2012-12-24 16:58:17Z

4

You cannot use semicolon separated queries with mysql_query, this function only allows one query at a time!

You must execute your statements separately:

mysql_query("
    CREATE TABLE IF NOT EXISTS `mod_reminder_entries` (
        `id` INT(10) NOT NULL AUTO_INCREMENT, 
        `user_id` INT(10) NOT NULL, 
        `entry_name` VARCHAR(255) NOT NULL, 
        `entry_value` INT(10) NOT NULL, 
        PRIMARY KEY (`id`), 
        FOREIGN KEY (`user_id`) REFERENCES tblclients (`id`) 
    )
") or die(mysql_error());

mysql_query("
CREATE TABLE IF NOT EXISTS `second_table` (
    `user_id` INT(10) NOT NULL, 
    `fieldstotal` INT(10) NOT NULL, 
    FOREIGN KEY (`user_id`) REFERENCES tblclients (`id`) 
)
") or die(mysql_error());

Or better, use mysqli_multi_query -- which means you must switch to mysqli.

edited Dec 24, 2012 at 16:58

answered Dec 24, 2012 at 16:48

Salman Arshad

274k85 gold badges450 silver badges540 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

S. A. Malik Over a year ago

how would it work with mysql functions?

Salman Arshad Over a year ago

Separate out the two CREATE TABLE queries and execute them one by one using mysql_query. Do not attempt to do a split(";", $query).

Zoe - Save the data dump · Accepted Answer · 2019-05-25 14:49:34Z

2

Just do what the error message says.

You can only execute one query at a time with mysql_* but please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

edited May 25, 2019 at 14:49

Zoe - Save the data dump

28.4k22 gold badges130 silver badges163 bronze badges

answered Dec 24, 2012 at 16:48

Naftali

147k41 gold badges247 silver badges304 bronze badges

6 Comments

S. A. Malik Over a year ago

The cms i am using, unfortunately only support mysql_* functions.

Naftali Over a year ago

@backTangent ok.... then I would advise changing CMSs, but anyway. you can only do one query at a time with mysql_*

S. A. Malik Over a year ago

I can't, I am freelancer :)

Madara's Ghost Over a year ago

@backTangent: How does being a freelancer == bound to a specific CMS? Isn't the point of freelancer is that you can code in whatever way you want..?

S. A. Malik Over a year ago

@MadaraUchiha: I hope one day a freelancer gets such a leisure. But at this time we have to work according to a client. The only leisure i have, is that i do not have a boss.

|

Collectives™ on Stack Overflow

Create Multiple MySQL tables using PHP [closed]

2 Answers 2

2 Comments

6 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

6 Comments

Linked

Related