Creating multiple MySQL tables using PHP

Question

I am trying to create more than one MySQL table using php, but it gives errors, and I can not figure 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 an error, in addition, do not worry about connecting mysql. I connected to the database correctly and I tested it, this is definitely wrong with the syntax.

You have an error in the SQL syntax; check the manual that matches your version of MySQL server for the correct syntax to use near 'CREATE TABLE, if not EXISTS second_table ( user_id INT

Answer 1

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

You must follow your instructions 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 - this means you have to switch to mysqli .

Answer 2

Just do what the error message says.

You can only execute one query at a time with mysql_* but please do not use the mysql_* functions in the new code . They are no longer supported and are officially outdated . See the red box ? Instead, learn about ready-made statements and use PDO or MySQLi - this article will help you decide which ones. If you choose PDO, here is a good lesson .