CSV Upload to two different tables

[hewhocannotbenamed]

Hello everyone

 

I have the following code that uploads content from a CSV file to a database table. What I would like to do is extend it so that it updates two tables rather than just the one. For simplicity out of 8 columns I want 4 to go to table 1 and the other 4 to table two. My thinking here was that the array index can be utilised to achieve this but I'm not really getting it to work.

 

<?php
include("core/db.php");


//Upload File
if (isset($_POST['submit'])) {
if (is_uploaded_file($_FILES['filename']['tmp_name'])) {
echo "<h1>" . "File ". $_FILES['filename']['name'] ." uploaded successfully." . "</h1>";
echo "<h2>Displaying contents:</h2>";
readfile($_FILES['filename']['tmp_name']);
}

//Import uploaded file to Database
$handle = fopen($_FILES['filename']['tmp_name'], "r");

while (($data = fgetcsv($handle, 1000, ",")) !== FALSE) {

mysqli_query($con,"INSERT INTO site

(name, street, city, postcode) 

VALUES 

('$data[0]','$data[1]','$data[2]','$data[3]')");


mysqli_query($con,"INSERT INTO contacts 

(firstname, lastname, gender, title) 

VALUES 

('$data[4]','$data[5]','$data[6]','$data[7]')");
}

fclose($handle);


print "Import done";


//view upload form
}else {


print "Upload new csv by browsing to file and clicking on Upload<br />\n";


print "<form enctype='multipart/form-data' action='' method='post'>";


print "File name to import:<br />\n";


print "<input size='50' type='file' name='filename'><br />\n";


print "<input type='submit' name='submit' value='Upload'></form>";


}


?>

Can anyone help?

Thanks

 

[Frank_b]

You are in the right direction. i think you have to debug a bit. For example echo your queries to see if they are allright. 

Are you sure that you can read the temporarily file? Or do you need to move it first?

[ginerjm]

You don't say what leads you to believe that it is NOT doing the inserts? I assume that you looked at the table and didn't see anything.

 

Try adding php error checking (see my sign.) and also add error checking after each call such as your db connection, db select and query calls to be sure that they in fact were successful. Good practice.

[SmileyBlackSheep]

i've done something similar using tab delimited txt files.

My method is along the lines of:
Open file

Read each line.

Explode each line, than "list"

 

$s_line = explode('tab_character', $read_line)

list ($a, $b, $c, D) = $array

 

Than take each piece of the array, maniplate it if needed, and sick it into another array:
$table_1['something'] = $a

$table_2['something'] = $c

etc.

 

Take those arrays and use them for your inserts.
 

I'm a self-taught hack, though. There may be a better way.

[Psycho]

You should never run queries in loops (unless absolutely necessary). A process such as SmileyBlackSheep proposes is better, but you have to be sure to use the proper functions to escape content to prevent SQL injection. Or, use prepared statements. Right now, you are open to SQL Injections. Using prepared statements in a loop isn't "as much" of a drain on system resources. You can do an "all at once" prepared statement, but creating the placeholders can be goofy.

 

I'd also add that you need to add comments to your code. It will help you as well as others when you ask for help. Plus, format your code with indents to show structure (you may be doing this and the forum removed them).

 

As to your problem, I have a question. I would assume each line in the file is respective to a specific entity. If you are just dumping the data separately to two tables there is no association. If two tables make sense, then I would expect that you would insert the data into one table, get the primary key, then use that key when inserting the data into the other table. Looks like the second table is for the details about a person and the first one if for an address associated with that person. If that is correct, the current process (I think)  should insert the user first and, if it succeeds, then insert the address. But, I don't know your application, so I can't state that definitively.

 

So, there are a lot of changes to be made. But, try this to see if it tells you why the records are not being inserted

 

 

<?php
include("core/db.php");
 
 
//Upload File
if (isset($_POST['submit'])) {
    if (is_uploaded_file($_FILES['filename']['tmp_name']))
    {
        echo "<h1>File {$_FILES['filename']['name']} uploaded successfully.</h1>";
        echo "<h2>Displaying contents:</h2>";
        echo "<pre>" . htmlentities(readfile($_FILES['filename']['tmp_name'])) . "</pre>";
    }
 
    //Import uploaded file to Database
    $handle = fopen($_FILES['filename']['tmp_name'], "r");
 
    while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
    {
        $query = "INSERT INTO contacts (firstname, lastname, gender, title) 
                  VALUES ('$data[4]','$data[5]','$data[6]','$data[7]')"
        $result = mysqli_query($con, $query);
        if(!$result)
        {
            //For debugging only
            echo "Query: {$query}<br>Error: " . mysqli_error($con);
            //Skip the rest of this record
            continue;
        }
        $query = "INSERT INTO site (name, street, city, postcode) 
                  VALUES ('$data[0]','$data[1]','$data[2]','$data[3]')"
        $result = mysqli_query($con, $query);
        if(!$result)
        {
            //For debugging only
            echo "Query: {$query}<br>Error: " . mysqli_error($con);
        }
    }
 
    fclose($handle);
    print "Import done";
}
else
{
    //view upload form
    print "Upload new csv by browsing to file and clicking on Upload<br />\n";
    print "<form enctype='multipart/form-data' action='' method='post'>";
    print "File name to import:<br />\n";
    print "<input size='50' type='file' name='filename'><br />\n";
    print "<input type='submit' name='submit' value='Upload'></form>";
}
 
 
?>

[Barand]

 

Hello everyone

 

 For simplicity out of 8 columns I want 4 to go to table 1 and the other 4 to table two.

 

 

As soon you see the words "For simplicity..." then you know this isn't a copy of the actual code being executed. Spending any time on what is posted is, therefore, just a waste of effort. Just my 0.02 worth.