phpmyadmin exported sql errors with mysql_query

[cjohnweb]

Hello all,

I've a seemingly simple question, but it's kind of got me thinkin there might be a "proper" way to accomplish my goal.

My goal is a simple MySQL database schema install.

In PHPMyAdmin I export a database, any database.

I then save the file to the root directory of my project as database.sql.

In my PHP script I open the file and pass it to mysql_query();

I receive errors telling my that my SQL is wrong.

Is there a specific PHP standard function for preparing database from PHPMyAdmin to be used with mysql_query()?

Here's a code sample:

<?php
include("config.php"); // connect to mysql

$sql = file_get_contents("database.sql"); // file_get_contents, or use fopen...

if(mysql_query($sql)){
echo "Database was installed!";
}else{
echo "There was an error installing the database: ".mysql_error();
}
?>

MySQL error is something like:

There was an error installing the database: 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 `snippets` ( `id` int(255) NOT NULL AUTO_INCREMENT' at line 22.

I guess a work around will do the job, but I was hoping PHP had something specifically for handling this data. I assumed that PHPMyAdmin export data was raw MySQL code, if so, why would I be getting errors?

Thanks guys (and girls)

[ddymacek]

Do you have command line access?
try:
mysql -u username -ppassword database_name < database.sql
The database needs to already be created in mysql.

[cjohnweb]

Thanks for your reply ddymacek.

I have searched for the answer to my problem, and every post out there gives ideas like using the command line, and using phpmyadmin, and other tools - all the obvious stuff that newbs might have trouble with. I am not searching for these ideas. I specifically and trying to import an sql file produced from PHPMyAdmin using mysql_query.

This is an install script for PHP / MySQL scripts. You know, you make a cool little tool in PHP/MySQL and you want to share it with people, and you want to make an installer for.

User enters their MySQL information into a config file, including the name of the database.

If the install.php file exists, user is redirected to install.php, install.php runs some sql to get the database setup and some initial data inserted.

User is redirected back to index.php where they can now use the script for whatever purpose they wish.

I want to be clear, I'm not having trouble loading information into MySQL - I can import the file with phpmyadmin, and I can do it with the commandline, I can do it with other tools. I am simply trying to export PHPMyAdmin data, and run it in a mysql_query();

When developing a script, I build the database with PHPMyAdmin, I finish up the script, and then I want a quick and easy way to allow someone else to load up that same database without having to import files, or use phpmyadmin, etc.

Just curious, how does wordpress do their database installation?

[ddymacek]

Ahh, I see.
I could not do it with a straight file from phpmyadmin.
I know this is your goal. I did however do it with the table schema and the data split into two files. I created my phpmyadmin .sql dump file with database and full inserts. I opened that file and pulled the db creation code into one file db-maker.sql and put all of the insert code into another file called db-data.sql.
So the data is still straight from phpmyadmin, but was broken out into two calls.
// I dont have a config.php. so db data on page.
my data file was 3925 records inserted.

<?php
    //include("config.php"); // connect to mysql
	ini_set('max_execution_time', 0);
$dbhost = "localhost";
$dbuser = "root";
$dbpass = "";
$dbname = "test";
	//Connect to MySQL Server
	mysql_connect($dbhost, $dbuser, $dbpass);
	//Select Database
	mysql_select_db($dbname) or die(mysql_error());     
    
	$sql = file_get_contents("db-maker.sql"); // file_get_contents, or use fopen...
     echo "sql = " . $sql . "<BR>";
    if(mysql_query($sql)){
		echo "Database was installed!";		

		$handle = fopen("db-data.sql","r");
		while(!feof($handle)) {
			$buffer = fgets($handle);
			if(mysql_query($buffer)){
				echo "Data was inserted! for " . $buffer . "<br />";
			} else {
				echo "could not load data for: " . $buffer . mysql_error() . "<br />";		
			}			
		}
		fclose($handle);		
    }else{
    echo "There was an error installing the database: ".mysql_error();
    }
    ?>

if you just need to create the databases with no data, get rid of the extra
-- 'commented' lines in the .sql dump file as well.
I hope some of this may help you, I could not get a straight .sql dump to load through mysql_query($sql-from-file)
That file was 'made' to either be mysql < filename or phpadmin imported, not necessarily to be parsed or read by php.
So I split out the actual db creation, and if that works, it tries to insert data.
wish it were easier but I don't think it is.
Option 2. You need to build a parser and parse out the .sql file. Filter out all of the -- 'commented' lines find the 'create table' statement execute it, then find all of the insert statements.
Option 3. Flatten out your .sql dump file. And by this I mean, reduce your table creation statement to one line (first line in the file). and then each line after have an insert statement. And then use the 'handle' code to read each line of the file and execute only that statement. Unfortunately all of these options are forcing you to do something to that .sql dump file that you have, but I don't really see any other way around it.

[cjohnweb]

That's some good stuff. Thanks for the info. For my immediate project, since it is only one table, I found something that works, though not quite what I wanted, I think I can make it work. Give me some time and I'll post the finished work for a simple mysql exporter and importer specifically for creating installers for PHP projects. It's such a common thing, you'd think there would already be a ton of these out there....(Maybe I didn't search hard enough).

In the mean time, here is my finished little project: PHP Tester. (I know, the design of the download site was totally jacked from ThePirateBay. What can I say, TI hate design and TPB looks good to me :) )

Please download the zip file and let me know what you think?

[ddymacek]

Just some notes about php-tester

undefined index : action in install.php line 3
undefined index : action in install.php line 10
undefined variable l line 68.

simple fix for the 3 & 10 errors, you are checking for $_GET['action']... check for a value at all like or !empty

if ($_GET['action'] != '') {
    // do action stuff
    //wrap lines 3-15 in this section
}

just set var $l = ''; before using it on line 68.

Everything worked pretty well and 'snippets' was created and overall it is a pretty cool little program to run some test code in. I could see it being a useful tool to have. Thanks.

[cjohnweb]

Ah, yes, I have turned off many of the irritating, non critical warnings such as declaring variables (I've never liked having to declare vars), I'll see what I can do. Thanks for your input! As you can see in the install.php, I found a way to work around this phpmyadmin / mysql thing. I exported just the "create table" sql and removed all the funny ` tilde-apostrophes from the PHPMyAdmin export. Then I used some php/mysql code to pull the content for the tables out and save them in files. See code:

So first step - Go into PHPMyAdmin and export the Schema only - do not export the content inside of your tables. You can either manually remove the ` chars from the code, or run $sql = str_replace("`","",$sql); in in the installer.

Then run this code to save the content, it will generate .sql files named as tablename.sql. On my machine, this will not work if the file name already exists, might check on yuors:

<?php
/*
This code is used to create backups of the content inside your mysql table
*/

include 'config.php'; // connect to mysql

$q = mysql_query("SHOW TABLES FROM ".$settings['mysql']['dbname']);
echo "<ul>";
while($aq = mysql_fetch_array($q)){
$tablename = $aq[0]; // table name
 
//Save file in the same directory as php script (otherwise saves to /mysql/data/)
$loc = $_SERVER['SCRIPT_FILENAME'];
$locs = explode("/",$loc);
unset($locs[count($locs)-1]);
foreach($locs as $k => $v){
$l .= "$v/";
}
$backup = $l.$tablename.".sql"; // save as 'tablename.sql'
echo "<li>Saving $backup...";
//save sql to file
if(@mysql_query("SELECT * INTO OUTFILE '$backup' FROM $tablename")){
echo "<span style=\"color:#006600;\">Success</span>";
}else{
echo "<span style=\"color:#ff0000;\">ERROR: </span>".mysql_error();
}   
echo "</li>";
}
echo "</ul>";

?>

The next step is to put this into your installer and make it work. First create the tables as needed:

<?php
$sql = <<<SQL
CREATE TABLE IF NOT EXISTS snippets (
  id int(255) NOT NULL AUTO_INCREMENT,
  name varchar(255) NOT NULL,
  code text NOT NULL,
  comment text NOT NULL,
  PRIMARY KEY (id)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19;
SQL;

if(@mysql_query($sql)){
echo "Table created!";
}else{
echo "Table creation failed: ".mysql_error();
}

And finally load all the sql files into their respective database table names:

echo "<b>Adding data</b>: ";

$dir = scandir("./"); // scandir returns array with all files and folders from CWD
foreach ($dir as $k => $v){

// get the file extension
$fn = explode(".",$v);
$cfn = count($fn)-1;

// check if file has an sql extension
if(strtolower($fn[$cfn]) == "sql"){
echo "Found <b>$v</b>. ";

$tablename = $fn[$cfn-1];

if(@mysql_query("LOAD DATA INFILE '$v' INTO TABLE $tablename")){
echo "<span style=\"color:#00ff00;\">Successfully loaded $v</span>";
}else{
echo "<span style=\"color:#ff0000;\">Error loading $v: </span>".mysql_error();
}


}

}

It's not what I wanted, but it gets the job done. I like to make small, short, usful projects. I was thinking about making a MySQL to XML exporter, and an XML > MySQL importer, that work together for backups, installers or whatever. I'll be sure to post if I do it.

Thanks again for the help!