Execute MySQL script file thru PHP

[MikkyThomeon]

HI All

I am usually a VB programmer but now need to know some php stuff

I am working remotely and would like to be able to upload my sql database script the the server via FTP and then then open the script file on the server and finally create the database by executing the commands in the file.

I would like to do this as a batch and not have to write a PhP script line by line to create the database structure.

Any pointers would be a real help.

[MikkyThomeon]

What about using the file-get-contents function to read the contents of the whole file into a variable and then calling mysql_query ???

[kows]

This is what I used to install an old site of mine where I only had a 10 MB SQL file.. I created the database first, though, but I just added something to allow you to either use a database or not.. I'm not sure if it actually works, as this is untested code after I've changed it.

Since you are doing this remotely, I'm not sure if this will work for you.. but it might.. all you have to do is execute the script and click the "Install file" link. This also might not be the best way to do it for anyone, but it works for me.

PS: I suggest doing this in 700KB file increments.. meaning, don't try to insert a 10 MB SQL file at one time, I split mine into 5 or 6 different files to make it smoother.. since this is untested, I also suggest using premade databases.. if you don't, you should make sure you select the database you want to use within the SQL file, with "USE db_name;", because otherwise, I'm pretty sure it'll give you an error for not having a database selected.

<?
  //set your default mysql host/user/pass
    $mysql['host'] = "localhost";
    $mysql['user'] = "user";
    $mysql['pass'] = "pass";
  //use database or create your own? 1 = use db, 0 = create your own
    $usedb = 1;
  //if $usedb is 1, define this, otherwise leave it blank
    $mysql['maindb'] = "your_database";
  //file to grab mysql statements from
    $file['file'] = "mysql_db_file.sql";
  if(!isset($_GET['act'])){
?>
<a href="?act=install">Install <b><?=$file['file'];?></b></a>
<?
  }elseif($_GET['act'] == "install"){
    //connect to host
    mysql_connect($mysql['host'], $mysql['user'], $mysql['pass']) or die("MySQL: Error while connecting to <i>" . $mysql['user'] . ":" . $mysql['pass'] . "@" . $mysql['host'] . "</i>");
      //on error will return: "MySQL: Error while connecting to user:pass@host"
    //check if database is being used or not
    if($usedb){
      //select main database
      mysql_select_db($mysql['maindb']) or die("MySQL: Error while selecting database <i>" . $mysql['maindb'] . "</i>");
        //on error will return: "MySQL: Error while selecting database db_name"
    }
    //open the file with the sql statements for reading
    $file['open'] = fopen($file['file'], "r");
    $file['read'] = fread($file['open'], filesize($file['file']));
    fclose($file['open']);

    //create the mysql query from the file
    $mysql['query'] = $file['read'];

    //query the database and see if it queried correctly
    if(mysql_query($mysql['query'])){
      echo "Success.<br>\n";
      echo $file['file'] . " has been installed.";
    }else{
      echo "Error.<br>\n";
      echo $file['file'] . " has not been installed.\n";
    }
/*
  //use this to check to see if the mysql query was read correctly from the file
  echo "<xmp>\n";
  echo $mysql['query'];
  echo "\n</xmp>\n";
*/
  }
?>

[MikkyThomeon]

Thanks Kows. I didn't think that php allowed a whole batch to be executed like

$mysql['query'] = $file['read']; 

Well now my VB program is interacting with the website perfectly!!!

Another question if I may...

What do I have to do if I want to connect to the server using https: ? Basically this part of the program interaacting with the website is dealing with authentication so I do not want somebody sniffing around to see what I am sending between my VB app and the web server

[kows]

You're going to need an SSL certificate, although I don't really know much about them. Whoever is hosting your server, if it isn't you, should be able to sell you one. It also has a yearly cost. You should look around the 'net for SSL information to be able to use it to your advantage..

You could also use HTACCESS to require a username/password to be able to view anything.. or just block usage altogether. IIS servers don't support HTACCESS, or at least the one I'm using doesn't, so only some web servers will support it, Apache being one of them.

[MikkyThomeon]

TKS kows

I will speak to my client's ISP. They are running a linux apache server.