Page 1 of 1

Php instalation script--> sql

Posted: Mon Mar 24, 2008 2:23 pm
by morty
Hello there, I am new here and I have a (simple) question.
What I am trying to do is a instalation script (that executes an sql file) and that will create my database in MySql.
I did a search and I found anything... while it should be very requested, isn't it?
I found this function, that works great, but has a problem: all the sql request have to be in one single line.

Code: Select all

Create database etc;
will work ok
but

Code: Select all

create table test1{
 
..
 
}
Any ideas how to modify the function to execute all the request, no matter if they are not in one single line?

this is the function:

Code: Select all

 
parse_mysql_dump("database.sql","localhost","prueba","root","");
 
 
function parse_mysql_dump($url,$nowhost,$nowdatabase,$nowuser,$nowpass){
    $link = mysql_connect($nowhost, $nowuser, $nowpass);
        if (!$link) {
           die('Not connected : ' . mysql_error());
        }
        
        // make foo the current db
        $db_selected = mysql_select_db($nowdatabase, $link);
        if (!$db_selected) {
           die ('Error : ' . mysql_error());
        }
        
   $file_content = file($url);
   
   foreach($file_content as $sql_line){
   
   if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
    // echo $sql_line . '<br>';
 // $sql_line2 = str_replace(array("\r", "\n" ), '', $sql_line);
    echo $sql_line. '<br>';
    
    mysql_query($sql_line, $link) or die ("Error in the request: ".$sql_line."<br>");
 
    
    
     }
   }
  }
 
 
Thanks!!
Morty

Re: Php instalation script--> sql

Posted: Mon Mar 24, 2008 7:59 pm
by Christopher
morty wrote:I found this function, that works great, but has a problem: all the sql request have to be in one single line.
SQL considers newlines and tabs to be whitespace, so the SQL does not need to be on one line. That function is just converting to Unix line endings (sort of ;)).

Re: Php instalation script--> sql

Posted: Tue Mar 25, 2008 2:25 am
by morty
But it only works if the SQL commands are in one single line (till the ";"), otherwise it thows an error when for example it try to execute this line "create table{"
which is normal, because this is an incomplete command line...
What do yo suggest to amend the function?[I don't know why it does not work...]

Cheers!

Morty [a new new new newbie, lol]

Re: Php instalation script--> sql

Posted: Tue Mar 25, 2008 3:38 am
by Christopher
Well the trim() is all that is left that could be causing the problem in that code -- remove it too.

Re: Php instalation script--> sql

Posted: Tue Mar 25, 2008 9:44 am
by morty
arborint wrote:Well the trim() is all that is left that could be causing the problem in that code -- remove it too.
It's not working... Even if I remove it it does not work.
Not that in the above post there were some commented lines. Those lines were made by me, and it didn't work...

this is the real function:

Code: Select all

 
 
 
function parse_mysql_dump($url,$nowhost,$nowdatabase,$nowuser,$nowpass){
    $link = mysql_connect($nowhost, $nowuser, $nowpass);
        if (!$link) {
           die('Non Connecté : ' . mysql_error());
        }
        
        // make foo the current db
        $db_selected = mysql_select_db($nowdatabase, $link);
        if (!$db_selected) {
           die ('Error : ' . mysql_error());
        }
        
    mysql_query('SET NAMES "utf8"');
    mysql_query('SET CHARACTER SET utf8');
    mysql_query('SET COLLATION_CONNECTION = "utf8_general_ci"'); 
    
   $file_content = file($url);
   
   foreach($file_content as $sql_line){
   
   if(trim($sql_line) != "" && strpos($sql_line, "--") === false){
    echo $sql_line. '<br>';
    
    mysql_query($sql_line, $link) or die ("Erreur dans la requête: ".$sql_line."<br>");
 
    
    
     }
     
 
   }
  }
 
 
I don't know what are you talking about when you say that it converts the lines to Unix... it only takes line by line... which is annoying because I have to adapt all the sql scripts...

Any other clue? (Sorry to bother you ... maybe I am missing something...I don't know...)

Thanks!