split Import CSV to mysql database restricted

Question

I have a php script that ask the user to upload a csv, then it display the result before importing and ask the user to map the fields corresponding to the database tables. It work fine.

My problem is that it seems that the server restrict the number of post or insert that can be made. So I can insert like 75 lines. But I have something like 600 lines. How could I split those like maybe in different files or maybe INFILE function.

I'm a little bit loss on that one. Because also, the user can select wich record he wants to be inserted with checkboxes.

<?php 
setlocale(LC_ALL,"en_US.UTF-8");


ob_start();
if(!isset($_SESSION)) 
{
    session_start();
}
//variables
$upload_file='';
$err='';
$msg='';
include "config.php";

if(isset($_POST['frm_type']))   // Insert Records
{   
    if(isset($_POST['frm_type']) && $_POST['frm_type']=='custom')
    {
        if(count($_POST["chk"])>0)
        {   
            $count_cols=$_POST["h_cols"];
            foreach($_POST["chk"] as $index)
            {
                $query="";
                $dyn_query="";
                $select_where="";

                $date_reg=date('Y-m-d');

                //STATIC COLUMN
                $query = "cat_id='".$_POST["cat_id".$index]."'";
                $query .= ",date_reg='".$date_reg."'";
                $query .= ",user_id='".$_SESSION['user_id']."'";
                $query .= ",type_client='".$_SESSION['cltype']."'";
                $query .= ",birthday_switch='1',";
                //DYNAMIC COLUMN
                for($s=0; $s<$count_cols;$s++)
                {
                    if(isset($_POST["colum_".$s]) && $_POST["colum_".$s]!='')
                    {
                        $value=Add_Slashes($_POST["row_".$s."_".$index]);
                        $dyn_query[]="".$_POST["colum_".$s]."='".$value."'";                        
                    }
                    if($_POST["colum_".$s]=="full_name")
                    {
                        $value=trim($_POST["row_".$s."_".$index]);
                        if($value!="")
                        {
                            $allow=1;
                        }
                        else
                        {
                            $allow=0;
                        }
                    }
                }
                if($dyn_query)
                {
                    if($allow==1)
                    {
                        $query .=implode(',',$dyn_query);
                        $select_where[]="cat_id='".$_POST["cat_id".$index]."'";
                        $select_where[]="user_id='".$_SESSION['user_id']."'";
                        $select_where[]="type_client='".$_SESSION['cltype']."'";

                        for($t=0; $t<$count_cols;$t++)
                        {
                            if(isset($_POST["colum_".$t]) && $_POST["colum_".$t]!='')
                            {
                                $value=Add_Slashes($_POST["row_".$t."_".$index]);
                                $select_where[]="".$_POST["colum_".$t]."='".$value."'";
                            }
                        }
                        $select_where_query=implode(' AND ', $select_where);
                        $re = mysql_query("Select cat_id From clients Where $select_where_query");

                        if(mysql_num_rows($re)==0)
                        {
                            mysql_query("Insert Into clients Set $query");
                            $msg = "<span class='error' style=\"color:green;\"><b>Data Successfully Imported Into The Database</b></span>";
                        }
                        else
                        {
                            $msg = "<span class='error' style=\"color:red;\"><b>Data Already Existed in Database</b></span>";
                        }
                    }
                }
                else
                {
                    $msg = "<span class='error' style=\"color:red;\"><b>Please select fields!</b></span>";
                }

            }

            if(file_exists($_SESSION["FILENAME"]))
                unlink($_SESSION["FILENAME"]);
        }
    }
}

if(isset($_POST['submt']))
{   
    if($_FILES['filename']["name"]=="")
        $err = "<span class='error'>".IMPORT_PLEASE."</span>";
    elseif(substr(strrev($_FILES['filename']["name"]),0,3)!="vsc")
        $err = "<span class='error'>".IMPORT_INVALID."</span>";
    else
    {

        $_SESSION['cltype']=$_POST['cltype'];
        set_time_limit(0);
        if(file_exists($Upload_Path.$_FILES['filename']["name"]))
            $filename = GetRandomString(6).$_FILES['filename']["name"];
        else
            $filename = $_FILES['filename']["name"];
        if(move_uploaded_file($_FILES['filename']["tmp_name"],$Upload_Path.$filename))
        {
            $upload_file = $Upload_Path.$filename;
            $_SESSION["FILENAME"] = $upload_file;
        }
        else
            $err = "<span class='error'>".IMPORT_FAIL."</span>";
        set_time_limit(30);
    }   
}
?>
<script>
function Check_All2(obj,arr)
{
    if(obj.checked)
    {
        var i=document.cfrm.elements.length;
        var j;
        for(j=0;j<i;j++)
        {
            if(document.cfrm.elements[j].name==arr)
                document.cfrm.elements[j].checked=true;
        }
    }
    else
    {
        var k=document.cfrm.elements.length;
        var l;
        for(l=0;l<k;l++)
        {
            if(document.cfrm.elements[l].name==arr)
                document.cfrm.elements[l].checked=false;
        }
    }
}
</script>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.6.3/jquery.min.js"></script>
<script> 
    jQuery(document).ready(function(){
        jQuery('#cfrm').submit(function(event) {
            colums = jQuery('select.colum');
            var arr = new Array();
            var h_status=false;
            colums.each(function() 
            { 

                var el=jQuery(this).val();
                if(el!='')
                {
                    h_status=true;
                    if(jQuery.inArray(el,arr)>-1)
                    {
                        event.preventDefault();
                        jQuery(this).css("border","2px solid red");
                        alert("Duplicated Fields!");
                        jQuery(this).focus();
                        return false;
                    }
                    else
                    {
                        arr.push(el);
                        jQuery(this).css("border","");
                    }
                }
            });
            chks = jQuery('.chk');
            chks.each(function() 
            {
                if (jQuery(this).is(':checked')) 
                {
                    h_cols=jQuery('#h_cols').val();
                    var mand=1;
                    for(var k=0; k<=h_cols; k++)
                    {
                        colum_id='#colum_'+k;
                        colum_value=jQuery(colum_id).val();
                        flel_object=jQuery('#row_'+k+'_'+jQuery(this).val());
                        flel_value=jQuery('#row_'+k+'_'+jQuery(this).val()).val();
                        if(colum_value=='full_name')
                        {
                            if(flel_value=='')
                            {
                                event.preventDefault();
                                alert('Enter Full Name');
                                flel_object.focus();
                                return false;
                            }
                        }
                        else if(colum_value=='aaa')
                        {
                            if(flel_value=='')
                            {
                                event.preventDefault();
                                alert('Enter Email');
                                flel_object.focus();
                                return false;
                            }
                        }
                    }
                }
            });
            if(!h_status)
            {
                event.preventDefault();
                alert('Select Any Field To Insert!');
                return false;
            }
        });
    });
</script>

<script type="text/javascript" src="ajaxsublistgenerator.js"></script>

<table class="sub" height="20px" width="100%" border="0" cellpadding="0" cellspacing="0" align="center"></table>
<table  width="99%" border="0" cellpadding="0" cellspacing="0" align="center">
 <tr>
  <td>
   <fieldset>
   <?
   if($upload_file=="")
   {
   ?>
    <legend style="font-size:12px; font-weight:bold"><?=IMPORT_CSV;?></legend>
    <form name="frm" action="" method="post" enctype="multipart/form-data"> 
     <table width="45%" border="0" cellpadding="4" cellspacing="4" align="center">
        <?
        if($err!="" || $msg!="")
        {
        ?>
         <tr><td colspan="2"><?=($err!="") ? $err : $msg?></td></tr>
        <?
        }
        ?>
        <tr>
          <td width="30%" align="left" valign="middle"><b><?=IMPORT_FILE;?></b></td>
          <td width="70%" align="left" valign="middle">
           <input name="filename" id="filename" type="file" size="25" />
          </td>
        </tr>
        <tr>
          <td width="30%" align="left" valign="middle"><b><?=IMPORT_SECTION;?></b></td>
          <td width="70%" align="left" valign="middle">
           <select name="cltype" id="cltype">
           <option value="Client">Clients</option>
           <option value="prospect">Leads</option>
           <option value="Contacts">Contacts</option>
           </select>
          </td>
        </tr>
        <tr>
          <td>&nbsp;</td>
          <td align="left" valign="middle">
           <input name="submt" type="submit" value="<?=IMPORT_UPLOAD;?>">
          </td>
        </tr>
      </table>
     </form>
     <script>document.getElementById("filename").focus();</script>
   <?
   }
   else
   {

       ?>
        <legend style="font-size:12px; font-weight:bold"><?=IMPORT_CONTENT;?></legend>
         <form name="cfrm" id="cfrm" action="" method="post" enctype="multipart/form-data"> 
          <input type="hidden" name="frm_type" id="frm_type" value="custom">
          <div style="overflow:auto;width:100%;">
          <table border="0" cellspacing="4" cellpadding="4" align="center" width="100%">
            <tr>
                  <td width="5%" align="center">
               <input onClick="javascript:Check_All2(this,'chk[]')" type="checkbox" checked="checked">
              </td>
                  <td width="10%" align="center"><b><?=IMPORT_CAT;?></b></td>
                  <?php 

              $fd_header = fopen ($upload_file, "r");
               $buffer_header = fgetcsv($fd_header, 100000, ',');
               //print_r(count($buffer_header));
              for($r=0; $r<count($buffer_header);$r++)
              {
              ?>
              <td width="10%" align="center">
              <select class="colum" name="colum_<?php echo $r;?>" id="colum_<?php echo $r;?>">
                <option value="">--Select Field--</option>
                <option value="full_name">Name</option>
                <option value="address">Address</option>
                <option value="city">City</option>
                <option value="state">Province</option>
                <option value="zip">Code Postal</option>
                <option value="country">Country</option>
                <option value="home_phone">Tel(Home)</option>
                <option value="work_phone">Tel(Work)</option>
                <option value="cell_phone">Tel(Mobile)</option>
                <option value="email">Email</option>
                <option value="conjoint">Joint</option>
                <option value="notes">Notes</option>
                <option value="birthday">Birthday</option>
              </select><input type="hidden" id="h_cols" name="h_cols" value="<?php echo count($buffer_header); ?>" />
              </td>
              <?php } ?>
              </tr>
             <?
             $fd = fopen ($upload_file, "r");
             $i=1;
             while (!feof ($fd)) 
             {
             $buffer_header = fgetcsv($fd, 100000, ',');
             ?>
             <tr>

              <td align="center"><input class="chk" type="checkbox" value="<?=$i?>" name="chk[]" checked="checked" /></td>
              <td align="left" ><?php $sql_cat_names = mysql_query("select * from clients_cat where user_id='".$_SESSION['user_id']."' order by cat_name") ;?>
                  <select class="ann_select" name="cat_id<?=$i?>" id="cat_id<?=$i?>">
                  <?php while($row_cat_names = mysql_fetch_array($sql_cat_names)){?>
                  <option value="<?=$row_cat_names['id']?>"><?=$row_cat_names['cat_name']?></option>                
                  <?php } ?>
                  </select>
              </td>
              <?php
                  for($v=0; $v<count($buffer_header);$v++)
                  {
                  ?>
                  <td  align="center">
                  <input type="text" value="<?=$buffer_header[$v]?>" name="row_<?=$v?>_<?=$i?>" id="row_<?=$v?>_<?=$i?>" />
                  </td>
              <?php } ?>
             </tr>
                <?php
                $i++;
                unset($buffer_header);
            }
            fclose ($fd);
             ?>
             <tr>
              <td colspan="7" align="center">
               <input name="Submit" id="Submit" type="submit" value="Submit" />
              </td>
             </tr>
          </table>
          </div>
         </form>    
       <?php

    }
   ?>    
   </fieldset>
  </td>
 </tr>
</table>

welcome to stackoverflow.com ! can you provide the exact reponse/ error what you are getting (copy, paste the error details). — Gnanz
– Gnanz, Commented Mar 22, 2014 at 10:37
Hi, I have no error at all, it just import a certain number of insertion, like if I have 600 lines to be imported, I get only 80 records inserted in database. It's like the server has reach the linits of post or something like that. This is why I wanted to split it or use the INFILE function maybe.. — Seby
– Seby, Commented Mar 22, 2014 at 10:39
I have to check with my programmer, but not sure if the max_post restriction on server will make it work. — Seby
– Seby, Commented Mar 22, 2014 at 11:07

Federico J. · Accepted Answer · 2014-03-23 08:13:42Z

1

You may insert multiple values in an only insert, and reduce the DDBB load.

In your PHP code, you receive the values, and make a foreach to insert each row one by one. What you should have to do is change your one by one insert to a multiple to one insert.

You're doing:

INSERT INTO table (field1, field2) VALUES ( 'value1', value2');
INSERT INTO table (field1, field2) VALUES ( 'value3', value4');
INSERT INTO table (field1, field2) VALUES ( 'value5', value6');

That gives you 3 connection to database, 3 inserts. Your query should be:

INSERT INTO table (field1, field2) VALUES ( 'value1', value2'), ('value3, 'value4'), ('value5', 'value6');

That gives you 1 connection to database, 3 inserts. Same result, less load.

UPDATE

To accomplish it in your code, you must create a string inside of your foreach with the data you want to insert: Validate the fields, create the string, and then insert them into the DDBB with an insert sentence that is out of your foreach. It's going to be a mess if I try to do it without running the script to check everything goes well, so I can only point you in the way to do the job.

edited Mar 23, 2014 at 8:13

answered Mar 22, 2014 at 10:39

Federico J.

16k7 gold badges35 silver badges54 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Seby Over a year ago

How could I use INFILE from what I have so far?

Federico J. Over a year ago

what is a 999 max_post? according to your js and markup, you're sending just a post, not posting each row individually. And if the problem is the maximum size of the post (max_post_size, in your php.ini) you have nothing to do but increase the value or change the server

Seby Over a year ago

maybe max_input_vars then wich is set to 999. Does this restrict then? Cause there's something restricting on my server cause I have no error on limit time or memory, but it seems to stop at some point because of the server. Do you feel that INFILE is the solution?

Seby Over a year ago

On your suggestion on inserting on one connexion only, how could I accpomplish this in my code? Because i'm in a Foreach.. Tks

Collectives™ on Stack Overflow

split Import CSV to mysql database restricted

1 Answer 1

UPDATE

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

UPDATE

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related