CSV to DB

[Rory]

Hey folks,

I have a large table on a webpage and all the data on this page can be modified (this is accomplished with some AJAX)...

Now I would like the user to be able to save the information on a server DB...Currently I have a button the exports the table contents to a html textarea in CSV format, I need (I think) to split this data by row and throw it into an array, saving one row at a time (through a while loop)...any suggestions.

the CSV in the textarea looks like this:
;1;2;3;4;5
;1;2;3;4;5
;1;2;3;4;5
;1;2;3;4;5

each col is seperated by ";" and each row is seperated by "\r\n"....

any suggestions?

Thanks

Rory

[Twey]

<?php
$data = explode($_POST['data'], "\r\n");

for($i = 0; $i < count($data); $i++) {
  $sql = 'INSERT INTO '. $table . ' VALUES (';
  $thisrow = explode($data[$i], ";");
  for($j = 0; $j < count($thisrow); $j++)
    $sql .= mysql_real_escape_string($thisrow[$j], $conn) . ($j != count($thisrow) - 1 ? ", " : "");
  $sql .= ');';
  mysql_query($conn, $sql);
}
?>

Code relies on three variables: $conn, the connection handler for the database, $_POST['data'], a POST variable containing the data, and $table, a table name to store the data in.

[Rory]

- Twey

Thanks for the quick reply...

That looks like exaclty what I was after, however it does not seem to generate the sql statement correcly. (well, its correct, but not giving me anything useful at the moment).

The code:

<?php
include 'db_connect_item.php';
$table = "test_1";

$data = explode($_POST['csv'], "\r\n");

for($i = 0; $i < count($data); $i++) {
  $sql = 'INSERT INTO '. $table . ' VALUES (';
  $thisrow = explode($data[$i], ";");
  for($j = 0; $j < count($thisrow); $j++)
    $sql .= mysql_real_escape_string($thisrow[$j]) . ($j != count($thisrow) - 1 ? ", " : "");
  $sql .= ');';
  mysql_query($sql) or die ("nice try but: ".mysql_error());
}

//echo $sql
?>

if I echo the value of $sql I get: INSERT INTO test_1 VALUES (;);

the exact csv data I used when this happened was:

;;1111;111;11;11;11;01/01;01/01;01/01;01/01;01/01;5555555
;;2222;222;22;22;22;02/02;02/02;02/02;02/02;02/02;9999999

Any suggestions?

(possibly the "for loop" is not getting past the first *empty* field. This field will always be empty when submitted as it is set to AUTO INCREMENT)

That's a beautiful piece of code, a lot cleaner than what I was attempting, I never have luck with putting "for" loops inside a query.

I'll spend some more time playing with it.

Thanks

Rory

[Rory]

Getting closer...

The SQL statemnet looks a little better now, I just have to figure out how how to put quotes around the values...

here is the code:

include 'db_connect_item.php';
$table = "pr_item";

$data = explode("\r\n", $_POST['csv']); //the arguments are reversed 
for($i = 0; $i < count($data); $i++) {
  $sql = 'INSERT INTO '. $table . '(v1, v2, v3, v4, v5, v6, v7, v8, v10, v11, v12 ) VALUES (';
  $thisrow = explode(";", $data[$i]);
  for($j = 0; $j < count($thisrow); $j++)
    $sql .= mysql_real_escape_string($thisrow[$j]) . ($j != count($thisrow) - 1 ? ", " : ""); //I'll need to include the quotation marks in here 
  $sql .= ');';
  mysql_query($sql) or die ("nice try but: ".mysql_error());
}


//echo "$sql"; 

that code will give you this:

INSERT INTO pr_item(v1, v2, v3, v4, v5, v6, v7, v8, v10, v11, v12 ) VALUES (, 5555555, 55555, 555, 555, 555, 05/05, 05/02, 05/05, 05/05, 05/05, 55667799);

I think it would be a better idea to have my data already surrounded by quotes (using JS; when the HTML is being converted to CSV), then I think the above code will suffice...

any suggestions?

Rory

[Twey]

$sql .= '"' . mysql_real_escape_string($thisrow[$j]) . '"' . ($j != count($thisrow) - 1 ? ", " : "");

I think it would be a better idea to have my data already surrounded by quotes (using JS; when the HTML is being converted to CSV), then I think the above code will suffice...

No, it wouldn't. This would leave your site open to SQL injection, a method of attack by which malicious users could execute arbitrary commands against your database.

[Rory]

Thanks for that security tip Twey...

Alright, so what I was initially trying to achieve has been accomplished (thanks to you), however I overlooked something...

each row is givin a unique ID, as a result if I modify an existing row it tries to create (INSERT) a new row with the same ID (throwing a Duplicate Entry Error).

Is there a way I can catch this error so everytime it occurs it will preform an UPDATE on the row WHERE ID=X?

Thanks,

Rory

[djr33]

Sure. You just need to have an extra step.

Check if it that ID exists.
(SELECT FROM ... WHERE ID=X)

Then if a result is found, use the update query. Else, new entry.

[Twey]

Yup. You could catch the error, using mysql_error() and friends, but another query is probably more efficient. Especially since you've probably got these results somewhere already, so you just have to loop through them looking for the ID, rather than executing a new query.