Php Sql

[kat35601]

I have a little over one million rows of data that I want to insert into a mysql database and I need some help with the correct code for sql in PHP to do this Please.

I can get my data with this but how would I convert the echo to an SQL Insert statment

 

 

 

$file = fopen("data.txt", "r") or exit("Fail!");

while(!feof($file)){

$string = fgets($file);

$array = explode('|',$string);

echo $array[0]." - ".$array[1]." - ".$array[4]." - ".$array[5]." - ".$array[6]." - ".$array[7]." - ".$array[8]." - ".$array[9]." - ".$array[10]." - ".$array[15]." - ".$array[16]." - ".$array[17]." - ".$array[18]." - ".$array[21]." - ".$array[22]." - ".$array[23]."
";

}

fclose($file);

?>

 

iNSERT INTO publicEN( unique_system_identifiern,
uls_file_number,
ebf_number,
call_sign,
entity_type,
licensee_id,
entity_name,
first_name,
mi,
last_name,
suffix,
phone,
fax,
email,
street_address,
city,
state,
zip_code,
po_box,
attention_line,
sgin,
frn,
applicant_type_code,
applicant_type_other,
status_code,
status_date) VALUES ($array[0],$array[1],$array[4],$array[5],$array[6],$array[7],$array[8],$array[9],$array[10],$array[15],$array[16],$array[17],$array[18],$array[21],$array[22],$array[23])

[Muddy_Funster]

You are almost there, but you need to start thinking in multi-dimensions (for your array that is).

$topArray = explode("\n", $string);
foreach($topArray as $topKey => $topValue){
 $topValue = explode("|", $topValue);
}
foreach($topArray as $subArrays){
 foreach($subArrays as $subKey => $subValue){
   $subValue = trim($subValue);
   if(get_megic_quotes_gpc){
     strip_slashes($subValue);
   }
 }
}

$qry = <<<QRY
INSERT INTO publicEN( unique_system_identifiern,
uls_file_number,
ebf_number,
call_sign,
entity_type,
licensee_id,
entity_name,
first_name,
mi,
last_name,
suffix,
phone,
fax,
email,
street_address,
city,
state,
zip_code,
po_box,
attention_line,
sgin,
frn,
applicant_type_code,
applicant_type_other,
status_code,
status_date) VALUES 
QRY;
$valueList = "";
foreach($topArray as $valueArray){
$valueList .= "('";
$valuelist .= implode("', '", $valueArray);
$valueList .= "')";
}
$qry .= $valueList;
//Perform SQL commands using $qry

 

Don't expect this to work as is, it's just ment to show you an example of the methodology. You'll need to work with it to make it happen.

[DavidAM]

$sql = "INSERT ... VALUES (" . $array[0] . ", " . $array[1] . ", " ...;

 

You have to make sure you put quotes around string data, and escape string data to protect against breaking the sql.

 

Be advised that running one million rows this way will take a long time. You will likely need to remove or adjust the time limit (set_time_limit).

 

You can make it faster, if you are using mySql, by inserting multiple rows at one time. I don't know what other database engines support this, but in mySql, you can do:

 

INSERT INTO tablename (column1, column2, ...) VALUES
(row1Column1, row1Column2, ...),
(row2Column1, row2Column2, ...),
(row3Column1, row3Column2, ...);

 

Here is an example (I'm not going to type out your whole INSERT):

$file = fopen("data.txt", "r") or exit("Fail!");

$insert = "INSERT INTO mytable (ID, FirstName, LastName, Age) VALUES ";
$counter = 0;
$values = array();

while(!feof($file)){
   $string = fgets($file);
   $array = explode('|',$string);

   $values[] = sprintf("VALUES(%d, '%s', '%s', %d)",
       $array[0], 
       mysql_real_escape_string($array[1]), 
       mysql_real_escape_string($array[2]), 
       $array[3]);

   $counter++;
   if ($counter >= 100) {
       $sql = $insert . implode(',', $values);
       mysql_query($sql);
       $values = array();
       $counter = 0;
   }
}

## Oops, almost forgot. If the file does not have a multiple of our check count (100)
##   There will be values left in the array that need to be written:
if ($counter > 0) {
   $sql = $insert . implode(',', $values);
   mysql_query($sql);
}

fclose($file);

 

Of course, it looks like you have a delimited file there. If the string values are already quoted, you can import it directly into mySql just like you would a CSV file, by specifying the delimiter and quote characters.

 

Also, if the string data is already quoted in the file, you are going to have to be careful. The example above added single quotes around the string data, so the quotes in the string data you read will be inserted into the database (probably not what you want). And depending on where the file came from, the quoted-string data my have been escaped for the quotes that were used, which may then be escaped by mysql_real_escape_string(), resulting in the escape character becoming part of the data in the database.

 

As you write this, you should stop after the first 100 rows and thoroughly check the database to be sure you get what you expect. I would do the same check after the first 1000 records.

[DavidAM]

$topArray = explode("\n", $string);
foreach($topArray as $topKey => $topValue){
 $topValue = explode("|", $topValue);
}

@Myddy_Funster: This code will not modify the contents of $topArray. In later versions of PHP (5.2+, I think), you can use a reference on $topValue to allow the modification of the array contents. i.e. foreach($topArray as $topKey => &$topValue){

 

   if(get_megic_quotes_gpc){
     strip_slashes($subValue);

 

Actually, it is get_magic_quotes_runtime that affects data read from a file. The _gpc version affects GET, POST, COOKIES, and FILES. I missed this in my earlier post; but I would put this check earlier, immediately after reading from the file, rather than after manipulating the values.

[mikosiko]

As DavidAM mentioned, if your file has the fields well delimited, like a CSV I will personally prefer to use LOAD DATA INFILE statement instead of manipulate the file with PHP, you should get better performance.