Truncate and insert csv data in php

[candice]

Hi all, I'm currently creating a php application where the user can actually insert data from the csv file into the database table. The truncating function works, however, I found out that the insertion of the data has a little bug. For example, i have 5 rows of data in the csv file, but when it is inserted into the database, it only inserts one row out of the five rows of data. Can anybody help? Thanks!

 

Below is a code snippet that i used to truncate and insert data in the table in my database.

 

$query = "INSERT INTO timetable values('$linemysql')";

mysql_select_db($database, $test);

mysql_query("TRUNCATE TABLE timetable") or die("MySQL Error: " . mysql_error()); //Delete the existing rows

mysql_query($query, $test) or die('SQL ERROR:'.mysql_error()); // INsert in the new values into the database

[Fadion]

Your query is running only once, so no surprise you have just one row. Something like this should do it:

 

<?php
//connect to the db and truncate
$csv = 'data1,data2,data3,data4,data5';
$data = explode(',', $csv);
foreach($data as $val){
     $results = mysql_query("INSERT INTO timetable (column) VALUES ('$val')");
}
?>

[candice]

Hi GuiltyGear,

 

thanks for your prompt reply. I tried using your method, but i got this error instead:

 

Warning: Invalid argument supplied for foreach() in C:\wamp\www\Test upload\test.php on line 71

 

I was wondering whether it has to do with the foreach statement?

 

 

Thanks for your help again!

[BlueSkyIS]

can we see your latest code?

[candice]

I've attached my latest code below. I dont know what is wrong.. maybe you can help? Thanks!

 

 

<?php require_once('connection.php'); ?>

 

<?php

 

//UPLOADING FILE

if(isset($_POST['upload']))

{

 

$uploaddir = 'uploads/';

$uploadfile = $uploaddir . basename($_FILES['fileupload']['name']);

 

// function escape_string($file) {

// $str=str_replace(",","",$file_string);

// echo ($file_string);

//

// //return $str;

//}

//print($str);

 

echo '<pre>';

if (move_uploaded_file($_FILES['fileupload']['tmp_name'], $uploadfile)) {

echo "File is valid, and was successfully uploaded.\n";

} else {

echo "Possible file upload attack!\n";

}

 

echo 'Here is some more debugging info:';

print_r($_FILES);

 

print "</pre>";

}

 

 

  $fcontents = $_SESSION[csvfile];

 

  for($i=0; $i<sizeof($fcontents); $i++) {

 

      $line = trim($fcontents[$i]);

      $arr = explode("\t", $line);

 

echo $arr."<br>\n";

 

 

}

 

//ATTRIBUTES

$fieldseparator = ',';

$lineseparator = '\n';

 

//READING OF FILE

$csvfile = $uploaddir . basename($_FILES['fileupload']['name']);

 

//IMPORT CSV INTO DATABASE

$lines = 0;

$queries = "";

$linearray = array();

$fcontents = file($csvfile);

 

for($i=0; $i<sizeof($fcontents); $i++) {

 

$lines++;

 

$line = trim($fcontents[$i]);

$linearray = explode(",",$line); //break the string into an array

$linemysql = implode("','",$linearray);//returns a string from the elements of an array<br />

//$linemysql = "\"$linemysql\"";

echo "$linemysql"."\n";

 

 

mysql_select_db($database, $test);

mysql_query("TRUNCATE TABLE timetable") or die("MySQL Error: " . mysql_error()); //Delete the existing rows

 

foreach($linemysql as $val){

$query = "INSERT INTO timetable values('$val')";

mysql_query($query, $test) or die('SQL ERROR:'.mysql_error()); // Insert in the new values into the database

}

 

}

 

 

?>

 

[BlueSkyIS]

$linemysql is not an array. do you mean to use $linearray?

 

foreach($linemysql as $val){ // $linemysql is not an array

[candice]

I tried that previously, but it simple prints out the word array and then comes out with an error like this:

 

Array SQL ERROR:Column count doesn't match value count at row 1

[BlueSkyIS]

so that fixed the Invalid argument supplied for foreach() error. the new problem seems to be this:

 

SQL ERROR:Column count doesn't match value count at row 1

 

Your SQL implies there is only one column in your table. is that true? if not, then your SQL is wrong.

[candice]

Not really.. previously when i imported in the csv file without the foreach function, it worked. The only problem now is such that it inserts only one row out of the five rows of data in the csv file ever since i added in the truncating function in the codes.

[BlueSkyIS]

Not really...what??

 

the new problem seems to be this:

 

SQL ERROR:Column count doesn't match value count at row 1

 

Your SQL implies there is only one column in your table. is that true? if not, then your SQL is wrong.

[candice]

As in.. what i meant was.. I'm pretty sure my database has the correct amount of columns, since the importing worked previously when i tried to import the file from the php application itself. Also, when i try to import the csv file directly into the database in wamp, it worked without an error as well.

[BlueSkyIS]

this is your SQL:

 

$query = "INSERT INTO timetable values('$val')";

 

does your table contain ONLY ONE COLUMN? Your SQL implies there is only one column in your table. is that true? if not, then your SQL is wrong.

[candice]

I already tried specifying all the columns names.. which is something I had tried a few times before this.. and all of them returned the same error:

 

SQL ERROR:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'mod, grp, room, roomPart, sem, weeks, lecturer) values('A01072')' at line 1

 

I found out that the error would only be gone after i have left the columns space blank.

[BlueSkyIS]

if you list fields in a SQL insert, you have to provide a value for each field listed. if you only want to update one column, then only list one column, something like this:

 

$query = "INSERT INTO timetable (some_field) values ('$val')"; // to insert with only this field (all others default)

$query = "INSERT INTO timetable (some_field, another_field) VALUES ('$val1', '$val2'); // inserting setting 2 values.
// the number of columns must match the number of values.

 

 

[candice]

Hi BlueSkyIS,

 

sorry for the late reply. I'll give your solution a try. Thanks.... !!

 

Candice