Csv file insert problem

[billy_111]

Hi,

 

I am trying to insert the contents of a csv file into a table, this is my code:

 

public function InsertCSVFileToDB(){

$has_title_row = true;
$not_done = array();

        if(is_uploaded_file($_FILES['csvfile']['tmp_name'])){
            $filename = basename($_FILES['csvfile']['name']);

            if(substr($filename, -3) == 'csv'){
                $tmpfile = $_FILES['csvfile']['tmp_name'];
                if (($fh = fopen($tmpfile, "r")) !== FALSE) {
                    $i = 0;
                    while (($items = fgetcsv($fh, 10000, ",")) !== FALSE) {

                        if($has_title_row === true && $i == 0){ // skip the first row if there is a tile row in CSV file
                            $i++;
                            continue;
                        }

                        $sql = "INSERT INTO ConfPaper SET
                                CPRid = ".$items[0].",
                                Pid = ".$items[1].",
                                CPtitle = '".mysql_real_escape_string($items[2])."',
                                CPabstract = '".mysql_real_escape_string($items[3])."',
                                CPspage = ".mysql_real_escape_string($items[4]).",
                                CPepage = ".mysql_real_escape_string($items[5]).",
                                CPlastesited = now()";
                        if(!mysql_query($sql)){
                            $not_done[] = $items;
                        }
                        $i++;
                    }
                }
                // if there are any not done records found:
                if(!empty($not_done)){
                    echo "<strong>There are some records could not be inserted</strong><br />";
                    print_r($not_done);
                }
            }
            else{
                die('Invalid file format uploaded. Please upload CSV.');
            }
        }
        else{
            die('Please upload a CSV file.');
        }
    }

 

This is the csv file:

 

http://www.prima.cse.salford.ac.uk:8080/~ibrarhussain/ConfPaper.csv

 

But i keep getting this:

 

Array ( [0] => Array ( [0] => 9 [1] => 1 [2] => CSV1 [3] => 4 [4] => 4 [5] => 01625 584412 ) [1] => Array ( [0] => 9 [1] => 1 [2] => CSV2 [3] => 14 [4] => 24 [5] => 01625 584412 ) )

 

Any ideas what the problem might be?

 

Hope someone can help..

 

Thanks

[MadTechie]

probably a SQL error, have you checked ?

[billy_111]

haha  you were right it was an sql error.

 

I have now got the following sql:

 

                        $sql = "INSERT INTO ConfPaper SET
                                CPRid = ".$items[0].",
                                Pid = ".$items[1].",
                                CPtitle = '".mysql_real_escape_string($items[2])."',
                                CPabstract = '".mysql_real_escape_string($items[3])."',
                                CPspage = ".mysql_real_escape_string($items[4]).",
                                CPepage = ".mysql_real_escape_string($items[5]).",
                                CPfile = '".mysql_real_escape_string($items[6])."',
                                CPlastedited = now(),
                                CPUid = ".$_SESSION['Uid']."";

 

And it works!

 

Ok now that this works perfectly, i need to add to it, i have a method already where i do this but i'm not quite sure how i will add it to the csv method. If you take a look at my csv file again:

 

http://www.prima.cse.salford.ac.uk:8080/~ibrarhussain/ConfPaper.csv

 

You will notice i have Author at the end of each column, what i need to do it add these into another table. Now the way i currently do this is like so:

 

    public function insertAuthor($authArray, $PCorder=0)
    {
        $query = sprintf('SELECT Pid, Pname FROM People WHERE Pname IN(\'%s\') ORDER BY Pid ASC', implode('\',\'', $authArray));
        $result = mysql_query($query);

        $maxquery = "SELECT MAX(CPid) as max FROM ConfPaper WHERE CPRid = ".$_GET['CPRid'];
        $maxresult = mysql_query($maxquery);
        $max = mysql_fetch_array($maxresult);
        $CPid = $max['max'];

        if($result && mysql_num_rows($result) > 0)
        {
            $sqlValues = array();
            while(list($PId, $PName) = mysql_fetch_row($result))
            {
                if(in_array($PName, $authArray))
                {
                    $sqlValues[] = sprintf("(%d, 1, ".$CPid.", %d, now(), 0)", $PId
                                                               , $PCorder++ );
                    // Author already exists within the Pname table
                    // remove user from $authArray
                    $key = array_search($PName, $authArray);
                    unset($authArray[$key]);
                }
            }
        $sql  = "INSERT INTO PeopleCon(Person_id, PCHid, Pid, PCorder, PCdateadded, PCdeleted) VALUES \n";
        $sql .= implode(",\n", $sqlValues);
        $result = mysql_query($sql);
        }
        // If there are Authors left within the $authArray
        // Add them to the Pname table
        if(count($authArray) > 0)
        {
            People::insertPersons($authArray); // call insertPersons method for remaining authors
            $this->insertAuthor($authArray, $PCorder); // insert the remaining auhtors into PeopleCon
        }
    }

 

Insert persons is this:

 

    public function insertPersons($PName)
    {
        $query = "INSERT INTO People (Pname) VALUES ('" . implode("'), ('", $PName) . "')";
        $result = mysql_query($query);
    }

 

These methods together insert an array of authors into a table from a series of input textboxes, something a little like this:

 

http://www.prima.cse.salford.ac.uk:8080/~ibrarhussain/admin/testing.php

 

If you click on "add author" you can add however many textboxes and this method inserts all of them..

 

So now i need to combine this with my CSV file upload to do the same..

 

Is this possible?