kexxcream kexxcream - 6 months ago 25
PHP Question

Get each array into an SQL query

Problem:

Getting each array generated from a text file into an SQL query. I have a text file containing this type of lines of airport information: https://abc-supervisor.c9users.io/airports.txt

If you do not wish to click the link, here's a sample:

1,"Goroka","Goroka","Papua New Guinea","GKA","AYGA",-6.081689,145.391881,5282,10,"U","Pacific/Port_Moresby"
2,"Madang","Madang","Papua New Guinea","MAG","AYMD",-5.207083,145.7887,20,10,"U","Pacific/Port_Moresby"
3,"Mount Hagen","Mount Hagen","Papua New Guinea","HGU","AYMH",-5.826789,144.295861,5388,10,"U","Pacific/Port_Moresby"


I'm trying to get each value into a SQL table.

PHP:

<?php
$lines = file('airports.txt');

echo '<xmp>';

$airport = array();

foreach($lines as $row)
{
$airport = str_replace('"', '', explode(",", $row));
print_r($airport);
}

echo '</xmp>';
?>


Outputs:

Array
(
[0] => 1
[1] => Goroka
[2] => Goroka
[3] => Papua New Guinea
[4] => GKA
[5] => AYGA
[6] => -6.081689
[7] => 145.391881
[8] => 5282
[9] => 10
[10] => U
[11] => Pacific/Port_Moresby

)
Array
(
[0] => 2
[1] => Madang
[2] => Madang
[3] => Papua New Guinea
[4] => MAG
[5] => AYMD
[6] => -5.207083
[7] => 145.7887
[8] => 20
[9] => 10
[10] => U
[11] => Pacific/Port_Moresby

)


Desired output:

Insert each value into a row in SQL. Thankful if someone could point in the right direction.

Answer

I presume you want to read the CSV file in and load each line to a row of a database table.

The easiest way you be to use LOAD DATA INFILE, if the file is accessible to the MySQL server. Something like this (not tested and not something I use often)

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

$mysqli->query("LOAD DATA INFILE 'airports.txt' 
                INTO TABLE expenses  
                    FIELDS TERMINATED BY ',' 
                           OPTIONALLY ENCLOSED BY '\"'
                    LINES  TERMINATED BY '\\n' 
                (col0, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11)");

but assuming you might want to do some other processing on some of the values before hand, or the file is not accessible from MySQL then you might want to just do an insert of each line manually (note that you could insert multiple rows at once which would be far more efficient, but that is a separate question).

<?php

$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

if (($handle = fopen('airports.txt', "r")) !== FALSE) 
{
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) 
    {
        if ($stmt = $mysqli->prepare("INSERT INTO some_table(col0, col1, col2, col3, col4, col5, col6, col7, col8, col9, col10, col11) VALUES(?,?,?,?,?,?,?,?,?,?,?,?)")) {
            $stmt->bind_param("isssssddiiss", $data[0], $data[1], $data[2], $data[3], $data[4], $data[5], $data[6], $data[7], $data[8], $data[9], $data[10], $data[11]);
            $stmt->execute();
        }   
    }
    fclose($handle);
}
Comments