[SOLVED] Executing .sql file

[AV]

Hi,

how may I execute .sql file using php?

[PHP_PhREEEk]

A .sql file is IMPORTED into MySQL. You can do that using phpMyAdmin or similar...

 

PhREEEk

[AV]

Exactly, but I want to do it using my own php script!

[only one]

Try something like:

<?php

$contents = file_get_contents("filename.sql");
mysql_query("$contents");

?>

[PHP_PhREEEk]

Then you would be re-writing a phpMyAdmin type application...

 

Can you maybe clue us in on what the use is here? Are you really intending on designing a phpMyAdmin app?

 

PhREEEk

[toplay]

Why would you want to? It's easier running it from the mysql command line, or GUI interface (like SQLyog, phpMyAdmin).

 

PHP only allows you to execute one SQL query at a time, and generally .sql files can and do contain more than one SQL command. If the queries are separated by semi-colon, then you can read the .sql file and extract each query separately and run each one at a time.

 

Please be more detailed when posting, like explaining what you're trying to do.

 

Is this a one time thing?

Is this part of setting up DB/tables as part of installing a script?

Do you know what will be in the sql ahead of time or others will provide .sql file (thinking security here)?

Are you allowing any SQL (alter, delete's, etc.) to be in .sql file?

[PHP_PhREEEk]

Try something like:

<?php

$contents = file_get_contents("filename.sql");
mysql_query("$contents");

?>

 

: chuckles :

 

If only it were that simple... that script would literally explode with errors...

 

You would have to parse the file first, and extract only valid sql commands. Then you would need to do some re-formatting, etc etc.... in the end, you would have phpMyAdmin.

 

PhREEEk

[AV]

I want to do it that way, becuase I'm creating CMS refreshing system - like on opensourcecms.com.

It count's down the time (already made that) and then it should drop existing database and create fresh one.

 

Here's my php code:

<?php

$table = 'tl';
$user = 'root';
$pass = '';

$connect = mysql_connect('localhost', $user, $pass);

if(!$connect) {
echo 'Can not connect!';

}else{

mysql_query('DROP DATABASE $table');
mysql_query('CREATE DATABASE $table');

$fh = file_get_contents('db_data.sql');

mysql_query($fh);
}

?>

I have no idea why it doesn't works - it even does not drop the existing database.

 

#up

I tried also with fopen, fread, no results either.

[toplay]

I have no idea why it doesn't works - it even does not drop the existing database.

 

You have $table in single quotes when it should be double quotes.

 

But you have to make sure that the user has drop/create permissions.

 

 

[PHP_PhREEEk]

A better approach might be to write a separate script that does all of the SQL commands. Test that script thoroughly, then when satisfied, use an INCLUDE instead of trying to read a .sql file.

 

Looks like you have other issues to sort out first, but reading a .sql assumes too much and is prone to giving you fits.

 

PhREEEk

[premiso]

<?php
mysql_query("DROP DATABASE $table");
mysql_query("CREATE DATABASE $table");

$fh = file('db_data.sql');

foreach ($fh as $line) {
    if (trim($line) != "") {
          mysql_query($line);
    }
}

 

That is the proper way to import. For the database issue, try using double quotes instead of single. Also make sure you have create/drop privileges.

 

Reason being is that mysql_query can only execute one line at a time. It cannot do multiple statements (unless I am incorrect which is possible) that is just my memory talking.

[AV]

#PHP_PhREEEK

When you export the whole database to a .sql file and then use it as mysql query it should work, huh?

 

#toplay

Still isn't working.

I think permissions are ok, I can drop/create tables by phpMyAdmin so it should work in .php, shouldn't it?

 

#premiso

Doesn't work either

I suppose root has right permissions. I am able to drop/create tables by phpMyAdmin.

[PHP_PhREEEk]

#PHP_PhREEEK

When you export the whole database to a .sql file and then use it as mysql query it should work, huh?

 

That depends on how it was exported. In fact, you can hand-create a .sql if you wanted to, if you get my point. By reading such a file in from a script, you are asking PHP to blindly send all those commands to MySQL, which would be asking for a lot of errors down the road. If you wrote a script that essentially did the same thing (rebuild all of the tables, fields and data), and just included it, it would make your life 100% easier in the end....

 

but what do I know...

 

PhREEEk

[premiso]

<?php
mysql_query("DROP DATABASE $table");
mysql_query("CREATE DATABASE $table");

// need to select database
mysql_select_db($table);

$fh = file('db_data.sql');

foreach ($fh as $line) {
    if (trim($line) != "") {
          mysql_query($line) or echo 'Error: ' . mysql_error() . '<br />';
    }
}

 

Give that a try.

[AV]

Okay, it has already dropped db and created another, but doesn't want to put tables and data in. Any ideas?

[toplay]

Okay, it has already dropped db and created another, but doesn't want to put tables and data in. Any ideas?

Always give details to forum members so they can help you better. Stating "doesn't want to" does not help.

 

What error message are you getting? are you even displaying the error message?

 

Post exact current code within forum code tags.

 

Listen to PHP_PhREEEk suggestions.

 

[AV]

I don't get any error, that's strange.

 

Suggestions PHP_PhREEEK may be good if I have a little db, but I have a large one. There's no sense to rewrite it manually.

 

Current code:

<?php
$table = "tl";
$user = 'root';
$pass = '';

$connect = mysql_connect('localhost', $user, $pass);

if(!$connect) {
echo 'Can not connect!';

}else{

mysql_query("DROP DATABASE $table");
mysql_query("CREATE DATABASE $table");

$fh = file('db_data.sql');

mysql_select_db($table);

foreach ($fh as $line) {
    if (trim($line) != "") {
          mysql_query($line);
    }
}

}
?>

[premiso]

Its not strange, you dont have the errors being handled with:

 

<?php
$table = "tl";
$user = 'root';
$pass = '';

$connect = mysql_connect('localhost', $user, $pass);

if(!$connect) {
echo 'Can not connect!';

}else{

mysql_query("DROP DATABASE $table");
mysql_query("CREATE DATABASE $table");

$fh = file('db_data.sql');

mysql_select_db($table);

foreach ($fh as $line) {
    if (trim($line) != "") {
          mysql_query($line) or echo 'Error:' . mysql_error() . '<br />';
    }
}

}
?>

 

As suggested earlier.

 

 

EDIT:

 

As a note, just thinking around. If you are creating tables and the export of sql was a dump or from phpmyadmin chances are the table declarations are spread out on separate lines, which will cause errors. You need to create a routine that checks for a table declaration and then does an internal loop inside checking for the end and combining that into one line.

 

I bet that is the main issue.

[AV]

Parse error: parse error, unexpected T_ECHO in C:\Program Files\WebServ\httpd\typolight\resetuj.php on line 22

 

I will try to correct the .sql file.

[PHP_PhREEEk]

EDIT:

 

As a note, just thinking around. If you are creating tables and the export of sql was a dump or from phpmyadmin chances are the table declarations are spread out on separate lines, which will cause errors. You need to create a routine that checks for a table declaration and then does an internal loop inside checking for the end and combining that into one line.

 

I bet that is the main issue.

 

No kidding... not to mention semi-colons and all kinds of things that PHP wouldn't handle well...

 

But...

 

What do I know...

 

PhREEEk

[premiso]

<?php
$table = "tl";
$user = 'root';
$pass = '';

$connect = mysql_connect('localhost', $user, $pass);

if(!$connect) {
echo 'Can not connect!';

}else{

mysql_query("DROP DATABASE $table");
mysql_query("CREATE DATABASE $table");

$fh = file('db_data.sql');

mysql_select_db($table);

$x=0;
foreach ($fh as $line) {
    if (trim($line) != "") {
          if (stristr($line, "CREATE TABLE")) {
                $cont = true;
                $lines = $fh[$x];
                $b=$x+1;
                while ($cont) {
                       $lines .= " " . $fh[$b];
                       if (stristr($fh[$b], ")")) {
                          $cont = false;
                       }else {
                          $b++;
                       }
                }
                mysql_query($lines) or die('Error:' . mysql_error() . '<br />');
          }else {
                mysql_query($line) or die('Error:' . mysql_error() . '<br />');
          }
    }
    $x++;
}

}
?>

 

That does not take into account for semicolons, which might also cause problems. Note the above is <b>UN-TESTED</b>.

[AV]

Note the above is <b>UN-TESTED</b>.

Here's result:

 

Error:Something is wrong in your syntax near '' in line 2

 

#BARAND

Something is wrong in your syntax near ''a:2:{i:0' in line 5

 

Oh, I forgot to mention there are some comments in .sql file.

[Barand]

No guarantees:

$file = file_get_contents('dbdata.sql');

$queries = explode (';', $file);

foreach ($queries as $sql)
{
    mysql_query (trim($sql)) or die (mysql_error());
}

[MadTechie]

try this

<?php

  function parse_mysql_dump($url, $ignoreerrors = false) {
   $file_content = file($url);
   $query = "";
   foreach($file_content as $sql_line) {
     $tsl = trim($sql_line);
     if (($tsl != "") && (substr($tsl, 0, 2) != "--") && (substr($tsl, 0, 1) != "#")) {
       $query .= $sql_line;
       if(preg_match("/;\s*$/", $sql_line)) {
         $result = mysql_query($query);
         if (!$result && !$ignoreerrors) die(mysql_error());
         $query = "";
       }
     }
   }
  }
?>

[trq]

I would still be more inclined to import the *.sql file via the mysql term. Something like...

 

<?php
  $db = 'temp';
  $table = "tl";
  $user = 'root';
  $pass = '';

  if (!mysql_connect('localhost', $user, $pass);
    die('Can not connect!');
  }

  if (!mysql_query("DROP DATABASE $table")) {
    die("Unablr to drop table");
  }

  if (!mysql_query("CREATE DATABASE $table")) {
    die("Unable to CREATE table");
  }

  exec("mysql -u $user -p $db < sqlfile.sql",$out,$return);
  if ($return == 1) {
    die("failed to import sql file");
  }

?>