Data upload from CSV file

Discussion in 'Programming/Scripts' started by Accura, Jan 26, 2006.

  1. Accura

    Accura New Member

    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
     
  2. falko

    falko Super Moderator

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

    Accura New Member

    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.
     
  4. falko

    falko Super Moderator

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

    Accura New Member

    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;
    ______________________________________________
     
  6. falko

    falko Super Moderator

    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...
     
  7. Accura

    Accura New Member

    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?
     
  8. falko

    falko Super Moderator

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

    Accura New Member

    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.
     
  10. 22hosting

    22hosting New Member

    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.
    ________
    LIVE SEX
     
    Last edited: Aug 22, 2011
  11. RogueDogg

    RogueDogg New Member

    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.
     
  12. falko

    falko Super Moderator

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

    PHP:
        $columns $app->db->queryAllRecords("SHOW COLUMNS FROM ".$_POST['table']);
        
    $columns array_slice($columns1);
        
    $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.
     
  13. EvanCarroll

    EvanCarroll HowtoForge Supporter

    it is vulnerable to a sql-injection attack.
     
  14. EvanCarroll

    EvanCarroll HowtoForge Supporter

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

    falko Super Moderator

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

Share This Page