Create Multiple MySQL tables using PHP

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**](http://bit.ly/phpmsql). They are no longer maintained [and are officially deprecated](https://wiki.php.net/rfc/mysql_deprecation). See the [**red box**](http://j.mp/Te9zIL)? Learn about [*prepared statements*](http://j.mp/T9hLWi) instead, and use [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) - [this article](http://j.mp/QEx8IB) will help you decide which. If you choose PDO, [here is a good tutorial](http://j.mp/PoWehJ). — Naftali, Dec 24 '12 at 16:48
Technical differences aside, PDO is much easier to work with. — Brad Koch, Dec 24 '12 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, Dec 24 '12 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, Dec 24 '12 at 17:10
Even it comes late, this might be more helpful: possible duplicate of [PHP: multiple SQL queries in one mysql\_query statement](http://stackoverflow.com/questions/345637/php-multiple-sql-queries-in-one-mysql-query-statement) — hakre, Dec 24 '12 at 17:12
I did not knew that the error was due to multiple SQL queries, otherwise i would not have asked it. — S. A. Malik, Dec 24 '12 at 17:32
Sure it's possible if you skip mysql_query and instead use for example mysqli_query. Check the answers here that is for any multi query (not just create table) http://stackoverflow.com/questions/16591831/is-there-a-smart-way-to-run-multiple-queries-in-mysql-separated-by-a-semicolon/ . — K. Kilian Lindberg, May 16 '13 at 16:47

Salman A · Accepted Answer · 2012-12-24T16:58:17.880

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.

Separate out the two `CREATE TABLE` queries and execute them one by one using mysql_query. Do not attempt to do a `split(";", $query)`. — Salman A, Dec 24 '12 at 16:52

score 2 · Answer 2 · edited May 25 '19 at 14:49

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 '19 at 14:49

Zoe

27,060
21
118
148

answered Dec 24 '12 at 16:48

Naftali

144,921
39
244
303

The cms i am using, unfortunately only support mysql_* functions. – S. A. Malik Dec 24 '12 at 16:50
2

@backTangent ok.... then I would advise changing CMSs, but anyway. you can only do one query at a time with `mysql_*` – Naftali Dec 24 '12 at 16:52
I can't, I am freelancer :) – S. A. Malik Dec 24 '12 at 16:59
@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..? – Madara's Ghost Dec 24 '12 at 17:00
@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. – S. A. Malik Dec 24 '12 at 17:01
@Neal, What's the image all about? – Pacerier May 08 '15 at 07:54

Create Multiple MySQL tables using PHP

2 Answers2

Linked

Related