unable to run query from text file

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jon23d
Forum Newbie
Posts: 10
Joined: Wed Jun 07, 2006 3:47 am

unable to run query from text file

Post by jon23d »

I am writing an application that creates tables in a database. The sql is stored in a text file. The database is created by the script.

Code: Select all

// database_name is a unique client identifier
mysql_query("CREATE DATABASE `$database_name`;");
// get the sql
$sql = file_get_contents("base-sql.txt");
// create a new connection for this
$connection_2 = mysql_connect("localhost", 'myUsername "myPassword") or die("oh crap...");
mysql_select_db($database_name) or die("Time for tea");
// okay, lets do it --- So far I've
mysql_query($sql, $connection_2);
if (mysql_error()) {
  echo "Error #: ". mysql_errno() . "<br />" . mysql_error();
  echo "<br /><br /><h1>Base sql</h1>";
  echo "<textarea>$sql</textarea>";
  die();
  exit();
 }
I get an error every time:
Error #: 1064
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 `headers` ( `id` int(11) NOT NULL aut' at line 5
The sql seems fine...

Code: Select all

CREATE TABLE IF NOT EXISTS `headers` (
  `id` int(11) NOT NULL auto_increment,
  `url` text,
  `username` varchar(255) default NULL,
  `password` varchar(255) default NULL,
  `title` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
And if I paste it from the textbox into mysql from a shell, or into phpMyAdmin, it runs perfectly.

I should mention that the SQL is quite a bit longer than that, but I've only been able to get one query to run out of the 50 or so there (thought I haven't tried all of them.. I did try exploding it and running the query that way, but....:

Code: Select all

 
CREATE TABLE `customers` (
  `id` int(11) NOT NULL auto_increment,
  `name` varchar(255) default NULL,
  `home_phone` varchar(255) default NULL,
  `cell_phone` varchar(255) default NULL,
  `work_phone` varchar(255) default NULL,
  `email` varchar(255) default NULL,
  `website` varchar(255) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=43 DEFAULT CHARSET=latin1;
Could there be an encoding issue perhaps?

I'm stumped, help me please!!!
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: unable to run query from text file

Post by Eran »

mysql_query() can run only one query at a time, and hence not useful for your scenario. If you have the MySQL command-line tool installed, you can run it from the shell:

Code: Select all

shell_exec("mysql -u[username] -p[password] [dbname] < " . $pathToFile);
jon23d
Forum Newbie
Posts: 10
Joined: Wed Jun 07, 2006 3:47 am

Re: unable to run query from text file

Post by jon23d »

Thank you, that worked flawlessly!
Post Reply