How do I use php to create a database with table and info using a backup file?

Question

How do I use php to create a database with table and info using a backup file?

so far my code is

<?
header("content-type:text/plain");

$serv= mysql_connect('localhost', 'root', 'xxxx');

if($serv){
     wText("Server connection created");
}
else{
    wText("Server connection failed");

}

$sql ="CREATE DATABASE tf2faq";

if(@mysql_query($sql,$serv)){

    wText("Database tf2faq created");
}
else{wText("Database tf2faq create failed");}




$sql = 'CREATE TABLE `tf2faq`.`loadoutitems` (`item` TEXT NULL, `class` TEXT NULL, `type` TEXT NULL, `replaces` TEXT NULL, `itempos` TEXT NULL, `modpos` TEXT NULL, `modneg` TEXT NULL, `imgurl` TEXT NULL, `notes` TEXT NULL) ENGINE = MyISAM';
mysql_query($sql,$serv);
if(@mysql_query($sql,$serv)){
wText("table loadoutitems created");
}
else{
wText("table loadoutitems create failed");

}

$sql="LOAD DATA INFILE 'loadoutitmsfx.csv' INTO TABLE  `tf2faq`.'loadoutitems'";

if(@mysql_query($sql,$serv)){
wText("Data Load Passed");
}
else{
wText("Data Load Failed");
}



function wText($txt){
    echo $txt . "\n";
}
?>

it fails on the LOAD DATA line, not sure why.

edit in my code i removed the "@" in there and it still says the file load failed

Answer 1

Where is loadoutitmsfx.csv? The file needs to be on the computer where the database is running. You might try specifying the whole path to it. Something like "/home/jim/loadoutitmsfx.csv".

Do the number of columns from your csv file and the table match up?

You probably need to specify some more options to get csv working.

LOAD DATA INFILE '/home/jim/loadoutitmsfx.csv' INTO TABLE tf2faq.loadoutitems FIELDS TERMINATED BY ',' ENCLOSED BY '"' ESCAPED BY '\' LINES TERMINATED BY '\r\n'

Additionally don't use @.

Answer 2

LOAD DATA INFILE is disabled on most MySQL servers, as it doesn't work with remote database servers and requires the data file to be readable by the database server. Parse the file yourself and generate SQL statements.