Data upload from CSV file

[Accura]

Hi,

I'm pretty new at this so excuse ignorance.
2 quick questions.

1)I have a script used to upload data from a CSV file to database.

No matter how many rows are populated in the CSV file it only uploads the first 15 rows of data.

Any Ideas?

2)Also can anyone tell me what the line of code from PHP.ini below does

;session.entropy_length = 16

[falko]

Hi,

I'm pretty new at this so excuse ignorance.
2 quick questions.

1)I have a script used to upload data from a CSV file to database.

No matter how many rows are populated in the CSV file it only uploads the first 15 rows of data.

Any Ideas?


To what database? MySQL? Which program do you use for uploading? phpMyAdmin?
Any error messages during the upload process?

[Accura]

I use a php script to upload the data to a mysql database.

No error messages.

I know the file size is less than the 2mb limit and I know the first 15 rows are uploaded instantly.

Is it possible to limit the number of rows being uploaded into the DB in the settings.

[falko]

Please post the content of your PHP script here, if it isn't too long...

[Accura]

It has too many image tags so it the BB script is not allowing me to post all of it.

These are the sections I feel might be relevant.

I should point out I didn't code this myself.(like I said i'm a beginner)
__________________________________________________ _________
include_once("../../connection.php");
while ($data = fgetcsv($handle, 4096, $separator))
{
if (count($data) >= 15 && $data[0] != "" && $data[1] != "" && $data[2] != "" && in_array($data[3], array("B", "L")) && in_array($data[4], array("R", "C")) && is_numeric($data[5]) && $data[5] >= 0 && is_numeric($data[6]) && $data[6] >= 0 && in_array($data[7], array("T", "F")) && is_numeric($data[9]) && $data[9] >= 0 && in_array($data[10], array("P", "S", "T", "C")) && format_date($data[12]) && format_date($data[13]))
{
mysql_query("insert into properties(property_id, property_description, property_location, property_rooms, property_bathrooms, property_other_details, property_price, property_date_posted, property_last_date_available, agent_id, property_postcode, property_letorbuy, property_contract, property_accepted, property_contract_terms, property_of_week, property_available_from, property_furniture, property_type) values('', '" . str_replace("'", "`", $data[0]) . "', '" . str_replace("'", "`", $data[1]) . "', {$data[5]}, {$data[6]}, '" . str_replace("'", "`", $data[8]) . "', {$data[9]}, now(), str_to_date('" . format_date($data[13]) . "', '%Y-%m-%d %T'), {$_SESSION['user_id']}, '" . str_replace("'", "`", $data[2]) . "', '{$data[3]}', '{$data[10]}', 'T', '" . str_replace("'", "`", $data[11]) . "', 'F', str_to_date('" . format_date($data[12]) . "', '%Y-%m-%d %T'), '{$data[7]}', '{$data[4]}')");
$result = mysql_query("select property_id from properties order by property_id desc limit 1");
$property_id = 0;
if ($array = mysql_fetch_assoc($result))
{
$property_id = $array["property_id"];
}
mysql_free_result($result);
for ($i = 14; $i < count($data); $i++)
{
for ($j = 0; $j < $_POST["image_files_no"]; $j++)
{
if ($data[$i] != "" && str_replace("'", "`", $data[$i]) == str_replace("'", "`", $_FILES["image_files"]["name"][$j]))
{
$result = mysql_query("select image_id from property_images where image_name = '" . str_replace("'", "`", $data[$i]) . "'");
if (mysql_fetch_assoc($result))
{
mysql_free_result($result);
continue 2;
______________________________________________

[falko]

__________________________________________________ _________
include_once("../../connection.php");
while ($data = fgetcsv($handle, 4096, $separator))
{
if (count($data) >= 15 && $data[0] != "" && $data[1] != "" && $data[2] != "" && in_array($data[3], array("B", "L")) && in_array($data[4], array("R", "C")) && is_numeric($data[5]) && $data[5] >= 0 && is_numeric($data[6]) && $data[6] >= 0 && in_array($data[7], array("T", "F")) && is_numeric($data[9]) && $data[9] >= 0 && in_array($data[10], array("P", "S", "T", "C")) && format_date($data[12]) && format_date($data[13]))
{
mysql_query("insert into properties(property_id, property_description, property_location, property_rooms, property_bathrooms, property_other_details, property_price, property_date_posted, property_last_date_available, agent_id, property_postcode, property_letorbuy, property_contract, property_accepted, property_contract_terms, property_of_week, property_available_from, property_furniture, property_type) values('', '" . str_replace("'", "`", $data[0]) . "', '" . str_replace("'", "`", $data[1]) . "', {$data[5]}, {$data[6]}, '" . str_replace("'", "`", $data[8]) . "', {$data[9]}, now(), str_to_date('" . format_date($data[13]) . "', '%Y-%m-%d %T'), {$_SESSION['user_id']}, '" . str_replace("'", "`", $data[2]) . "', '{$data[3]}', '{$data[10]}', 'T', '" . str_replace("'", "`", $data[11]) . "', 'F', str_to_date('" . format_date($data[12]) . "', '%Y-%m-%d %T'), '{$data[7]}', '{$data[4]}')");
$result = mysql_query("select property_id from properties order by property_id desc limit 1");
$property_id = 0;
if ($array = mysql_fetch_assoc($result))
{
$property_id = $array["property_id"];
}
mysql_free_result($result);
for ($i = 14; $i < count($data); $i++)
{
for ($j = 0; $j < $_POST["image_files_no"]; $j++)
{
if ($data[$i] != "" && str_replace("'", "`", $data[$i]) == str_replace("'", "`", $_FILES["image_files"]["name"][$j]))
{
$result = mysql_query("select image_id from property_images where image_name = '" . str_replace("'", "`", $data[$i]) . "'");
if (mysql_fetch_assoc($result))
{
mysql_free_result($result);
continue 2;
______________________________________________
It seems as if the highlighted code has to do with your problem. Have you read that software's documentation? I think the authors intended it to be like that...

[Accura]

I paid someone to help me create this section of the script there wasn't suppose to be a limit on the number to rows I could upload.

I would usually have to upload between 50 and 200 rows.

Any ideas on how to fix it?

[falko]

I strongly recommend to ask this someone. He knows best how the script works and why it works like that.

[Accura]

It was a contract job from last year.

I've had a few problems but I've been able to fix them myself.

I'm new to php so I needed to be sure the solution wasn't an obvious one.

[22hosting]

If you are interested I have created a set of classes in PHP to perform almost the same sort of thing. However it should be noted that he is using individual SQL Inserts whereas instead he should using the MySQL load inline function which doesn't do individual queries per data item. Alternatively instead of loading into a live database they should create a temporary table and then insert directly into that table and do a load inline from one table to another. That would almost certainly be the most efficient clean way of doing it.

My code is less messy and better commented so hopefully it will be easier to learn from that the person who wrote that script.

If you are contact me using private messages on this board.


Update : It's probably worth noting that the "count($data) >= 15" section is counting the number of items in the array $data. Therefore this shouldn't have anything to do with the number of rows it's inserting into the database. What it does do is check that the number of columns is greater than or equal to 15.

What counts I think is " $_POST["image_files_no"] " as this should return the number of times it performs the select query and draws one result from the inserted data. Therefore I suspect that if you change this to a static value i.e. the number of rows in your CSV file it should print out all the results.The image_files_no is passed from the previous page as it's a $_POST value, or it's a static value set in one of the other core php files.

[RogueDogg]

if someone could help me find or give me the code to do such a task I'd greatly appreciate it. All the ones I've found so far just don't do it.

[falko]

This is a small snippet of a PHP script that I wrote:

$columns = $app->db->queryAllRecords("SHOW COLUMNS FROM ".$_POST['table']);
$columns = array_slice($columns, 1);
$app->db->query("TRUNCATE ".$_POST['table']);
$app->uses('file');
$content = file_get_contents($_FILES['file']['tmp_name']);
$lines = explode("\n", $content);
foreach($lines as $line){
$fields = explode("\"", $line);
$sql = "INSERT INTO ".$_POST['table']."(";
$sql1 = $sql2 = '';
$l = 0;
for($i=0;$i<sizeof($fields);$i++){
$k = 0;
if(substr($fields[$i],0,1) == '"' && substr($fields[$i],-1) != '"'){
$j = $i + 1;
do {
$fields[$i] .= $fields[$j];
$j++;
$k++;
} while(substr($fields[$j-1],-1) != '"');
}
if(substr($fields[$i],0,1) == '"' && substr($fields[$i],-1) == '"') $fields[$i] = substr($fields[$i],1,-1);

$fields[$i] = str_replace('""', '"', $fields[$i]);
$sql1 .= $columns[$l]['Field'];
$l++;
$sql2 .= "'".addslashes($fields[$i])."'";
if($i < (sizeof($fields)-1)){
$sql1 .= ',';
$sql2 .= ',';
}
$i += $k;
}
$sql .= $sql1.") VALUES (".$sql2.");";
//echo $sql."<br>";
$app->db->query($sql);
}It should give you the idea.

[EvanCarroll]

This is a small snippet of a PHP script that I wrote:

$columns = $app->db->queryAllRecords("SHOW COLUMNS FROM ".$_POST['table']);
$columns = array_slice($columns, 1);
$app->db->query("TRUNCATE ".$_POST['table']);
$app->uses('file');
$content = file_get_contents($_FILES['file']['tmp_name']);
$lines = explode("\n", $content);
foreach($lines as $line){
$fields = explode("\"", $line);
$sql = "INSERT INTO ".$_POST['table']."(";
$sql1 = $sql2 = '';
$l = 0;
for($i=0;$i<sizeof($fields);$i++){
$k = 0;
if(substr($fields[$i],0,1) == '"' && substr($fields[$i],-1) != '"'){
$j = $i + 1;
do {
$fields[$i] .= $fields[$j];
$j++;
$k++;
} while(substr($fields[$j-1],-1) != '"');
}
if(substr($fields[$i],0,1) == '"' && substr($fields[$i],-1) == '"') $fields[$i] = substr($fields[$i],1,-1);

$fields[$i] = str_replace('""', '"', $fields[$i]);
$sql1 .= $columns[$l]['Field'];
$l++;
$sql2 .= "'".addslashes($fields[$i])."'";
if($i < (sizeof($fields)-1)){
$sql1 .= ',';
$sql2 .= ',';
}
$i += $k;
}
$sql .= $sql1.") VALUES (".$sql2.");";
//echo $sql."<br>";
$app->db->query($sql);
}It should give you the idea.

it is vulnerable to a sql-injection attack.

[EvanCarroll]

All of these solutions are poor. I feel obliged to buzz in and suggest DBD::CSV.

[falko]

I'm using this script in a password-protected area that only I can access...