Page 1 of 1

unable to run query from text file

Posted: Sat Dec 06, 2008 4:18 pm
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!!!

Re: unable to run query from text file

Posted: Sat Dec 06, 2008 4:32 pm
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);

Re: unable to run query from text file

Posted: Sat Dec 06, 2008 4:48 pm
by jon23d
Thank you, that worked flawlessly!