TechQA.

Inserting multiple rows with PHP

1k views Asked by At

After importing a csv file, I'm trying to import the data with an insert statement, which is sending around 250 rows for one table.

The inserting, and code is working very well, but I had to print the insert query and the insert just worked till the row number 100.

PHP screen, which I import csv file data to the screen:

if(@$_FILES[file][type] == 'text/csv' || @$_FILES[file][type] == 'text/comma-separated-values' || @$_FILES[file][type] == 'application/vnd.ms-excel'){



if($_POST['FUNCAO'] == 'FORM_IMPORT')
{
    error_reporting(E_ALL);
    ##### UPLOAD FILE
    $uploaddir = 'gap_files/';
    @$filename="gap_files/".$_FILES[file][name];
    @$uploadfile = $filename;

    $count = 0;
    $id_linha = 0;
    $itens_po = '';
    $pos_to_import = '';

    if (!@move_uploaded_file($_FILES['file']['tmp_name'], $uploadfile))
    {
        print "<pre>";
        print_r($_FILES);
        print "$php_errormsg</pre>";
    }

    if(@$_FILES[file][name])
    {
        print '
        <html>
        <head>
            <title>PO</title>
            <link rel="stylesheet" type="text/css" href="../_includes/css/padrao.css" >
            <script language="JavaScript" type="text/javascript" src="../_includes/js/padrao.js"></script>
            <script language="JavaScript" type="text/javascript" src="../_includes/js/po.js"></script>
            <script language="JavaScript" type="text/javascript" src="../_includes/js/label.js"></script>
        </head>
        <body>
            <div id="div_report" style="background-color:#2B2B2B; position:fixed; top:0px; width:200%; height:200%; display:none; opacity:0.35; filter:alpha(opacity=35);">&nbsp;</div>
            <div id="div_link_color" style="display:none; position:fixed;  margin-left: -300px; margin-top: -240px; left:50%; top:50%; width:600px; height:280px; background-color:#525252; text-align:center; z-index: 1000; text-align:center; border-radius: 0.4em; ">
                <center>
                <table cellpadding="3" cellspacing="0">
                    <tr>
                        <td colspan="3" class="REPORT_EDIT_TITLE" colspan="3" align="center" id="title_box_comment"></td>
                    </tr>
                    <tr>
                        <td colspan="3">
                            <iframe style="width:587px; height:230px; background-color:#EAF0FA; text-align:center; text-align:center; border-radius: 0.4em; overflow:auto; border:none;" id="frame_link_color" name="frame_link_color"></iframe>
                        </td>
                    </tr>
                    <tr>
                        <td colspan="3" align="center"><input type="button" name="comments" id="pre_production_comm" onclick="close_link_color();" value="Close" class="button" style="width:60px; height:30px;"></td>              
                    </tr>
                </table>
                </center>
            </div>

            <div width="100%" id="FRAMESBOTTON">
                <table cellpadding="1" cellspacing="0">
                    <tr>
                        <td class="botton-apple"><img src="../img/user.png" />&nbsp;<b>Information</b>&nbsp;</td>
                        <td class="botton-apple" onclick="seleciona_div_po(3)"><img src="../img/help.png" />&nbsp;<b>Help</b>&nbsp;</td>
                        <td class="botton-apple" onclick="seleciona_div_po(9)"><img src="../img/application.png" />&nbsp;<b>Import Label</b>&nbsp;</td>
                        </tr>
                </table>
                <table width="100%" cellpadding="0" cellspacing="0">    
                    <tr>
                        <td class="titlestyle" colspan="6" align="center"><b>Labels to import</b></td>                  
                    </tr>
                </table>
            </div>
        ';

        $po = '';
        $line=1;
        $po_show = '';
        $itens_po = '';
        $factory_ids = '';
        $wrong_price = '';              
        $import_error = 0;
        $invoice_port = '';
        $invoice_number = '';               
        $number_of_invoices = 0;
        $invoice_gross_weight = '';
        $invoice_total_cartons = '';

        $total_pairs_invoice = 0;
        $total_amount_invoice = 0;
        $total_amount_discount_invoice = 0;

        $tr_check_po = '';
        $filename="gap_files/".@$_FILES[file][name];
        $handle = fopen("$filename", "r");

        $actual_po = '';

        while (($data = fgetcsv($handle, 1000, ",")) !== FALSE)
        {

            foreach($data as $varname => $value)
            {
                if($value){
                    $data[$varname]=mysql_real_escape_string($value);
                }
                else{
                    $data[$varname]="NULL";
                }
            }

            $check_if_inserted_invoice = mysql_query("select * from po_label where po_label.po = '".$data[3]."'");

            if(mysql_num_rows($check_if_inserted_invoice) == 0){

                if($line != 0){

                    $count++;

                    $itens_po .= '
                    <input type="hidden" name="iten_'.$count.'" value="'.$line.';'.$data[3].';'.$data[26].';'.$data[15].';'.$data[4].';'.$data[8].';'.$data[0].';'.$data[23].';'.$data[1].';"/>
                    <tr style="background-color:#ECEBEB;" onmouseover="ButtonRegSet(this,1)" onmouseout="ButtonRegSet(this)">
                        <td class="fontrecord" align="center"><input type="hidden" name="carton_number_'.$line.'" value="'.$line.'" readonly>'.$line.'</td>
                        <td class="fontrecord" align="center"><input type="hidden" name="po_'.$line.'" value="'.$data[3].'" readonly>'.$data[3].'</td>
                        <td class="fontrecord" align="center"><input type="hidden" name="carton_'.$line.'" value="'.$data[26].'" readonly>'.$data[26].'</td>
                        <td class="fontrecord" align="center"><input type="hidden" name="EANCode_'.$line.'" value="'.$data[15].'" readonly>'.$data[15].'</td>
                        <td class="fontrecord" align="center"><input type="hidden" name="size_'.$line.'" value="'.$data[4].'" readonly>'.$data[4].'</td>
                        <td class="fontrecord" align="center"><input type="hidden" name="pairs_'.$line.'" value="'.$data[8].'" readonly>'.$data[8].'</td>
                        <td class="fontrecord" align="center"><input type="hidden" name="customer_style_'.$line.'" value="'.$data[0].'" readonly>'.$data[0].'</td>
                        <td class="fontrecord" align="center"><input type="hidden" name="material_'.$line.'" value="'.$data[23].'" readonly>'.$data[23].'</td>
                        <td class="fontrecord" align="center"><input type="hidden" name="colors_'.$line.'" value="'.$data[1].'" readonly>'.$data[1].'</td>
                        </tr>';
                }

                $line++;


            }
        }



        fclose($handle);

        }

    $number_of_invoices++;

    print '         
    <form method="post" action="../_registers/po_import_label.php">
    <input type="hidden" maxlength="30" name="FUNCAO" value="insert_import_po" readonly>
    <input type="hidden" maxlength="30" name="itens_form" value="'.$line.'" readonly>
    <center>
    </br>
    <table class="round_sub_information_title_close" width="90%">
        <tr>
            <td colspan="8" class="round_sub_information_title_close" style="background-color:#FFFFFF;">
                <div id="id_itens_invoices_'.$number_of_invoices.'" style="display:block;">
                <center>
                <table cellpadding="0" cellspacing="1" style="min-width:99%; width:99.8%;">
                    <tr class="line_grid">                          
                        <td class="fontrecord" align="center">Carton Number</td>
                        <td class="fontrecord" align="center">PO</td>
                        <td class="fontrecord" align="center">Carton Code</td>
                        <td class="fontrecord" align="center">Case Code</td>
                        <td class="fontrecord" align="center">Size</td>
                        <td class="fontrecord" align="center">Quantity</td>                             
                        <td class="fontrecord" align="center">Style</td>
                        <td class="fontrecord" align="center">Material</td>
                        <td class="fontrecord" align="center">Color</td>

                    </tr>
                    '.$itens_po.'
                </table>
                </center>
                </div>
            </td>
        </tr>
        <tr>';

        print '
        <tr><td class="round_sub_information_title_close" style="background-color:#FFFFFF;"><input type="submit" value="Save"/></td></tr>';
        print '</tr>
    </table>
    </center>
    </form>';
}

}

File: "../_registers/po_import_label.php"

<?php
session_start();
if(!$_SESSION["uid"]){
    die(header("location: ../login.php"));
}

include('../_library/config.php');
include('../_library/opendb.php');
include("../_library/functions.php");

$s = "insert into po_label (carton,
            carton_number,
            po,
            size,
            pairs,
            colors,
            material,
            customer_style,
            EANCode) values";

for($i=1;$i<$_POST['itens_form'];$i++)
{
    $s .="('".$_POST['carton_'.$i]."',
    '".$_POST['carton_number_'.$i]."',
    '".$_POST['po_'.$i]."',
    '".$_POST['size_'.$i]."',
    '".$_POST['pairs_'.$i]."',
    '".$_POST['colors_'.$i]."',
    '".$_POST['material_'.$i]."',
    '".$_POST['customer_style_'.$i]."',
    '".$_POST['EANCode_'.$i]."'),";
}
$s = rtrim($s,",");

print $s;

return mysql_query($s) ? 'INSERTED.' : 'ERROR: '.mysql_error();

include '../library/closedb.php'; 
?>

After import to the screen, I call the insert function to send the data to the database, it is when I got the error.

insert into po_label (carton, carton_number, po, size, pairs, colors, material, customer_style, EANCode) values (...),('10355000000098','98','0355CC0001','7',' 6','CAMEL','SOFT NUBUCK','CC-CRYSTALL','191644000270'),('10355000000099','99','0355CC0001','7',' 6','CAMEL','SOFT NUBUCK','CC-CRYSTALL','191644000270'),('10355000000100','100','0355CC0001','7',' 6','','SOFT NUBUCK','CC-CRYSTALL','191644000270'),('','','','','','','','',''),('','','','','','','','',''),('','','','','','','','',''),('','','','','','','','',''),(...)

Adding an observation: Before I updated the files in my official system, I tried and test it in the Wamp server, where everything works well.

If i have only 100 rows, the insert works well

If i have more than 100 rows, you can check in the bottom that after the row 100 the insert becomes empty

php mysql csv multiple-records
Original Q&A
2

There are 2 answers

1
KMS On

1) Please confirm whether all values coming without spaces by following function

print_r()

2) Use the trim() function to every single data passed in insert query

INSERT INTO TBL_NAME VALUES(
'".trim($value[0])."',
'".trim($value[1]))";

May it will help

7
Sahil Patel On

I think you should improve your below code with given

// Your code
for($i=1;$i<$_POST['itens_form'];$i++)
{
    $s .="('".$_POST['carton_'.$i]."',
    '".$_POST['carton_number_'.$i]."',
    '".$_POST['po_'.$i]."',
    '".$_POST['size_'.$i]."',
    '".$_POST['pairs_'.$i]."',
    '".$_POST['colors_'.$i]."',
    '".$_POST['material_'.$i]."',
    '".$_POST['customer_style_'.$i]."',
    '".$_POST['EANCode_'.$i]."'),";
}

for($i=1;$i<$_POST['itens_form'];$i++)
{
    // Remove POST for columns in which you have allowed null in data table
    if($_POST['carton_'.$i] != '' && $_POST['carton_number_'.$i] != '' && $_POST['po_'.$i] != '' && $_POST['size_'.$i] != '' && $_POST['pairs_'.$i] != '' && $_POST['colors_'.$i] != '' && $_POST['material_'.$i] != '' && $_POST['customer_style_'.$i] != '' && $_POST['EANCode_'.$i] != '')
    {
        $s .="('".$_POST['carton_'.$i]."',
        '".$_POST['carton_number_'.$i]."',
        '".$_POST['po_'.$i]."',
        '".$_POST['size_'.$i]."',
        '".$_POST['pairs_'.$i]."',
        '".$_POST['colors_'.$i]."',
        '".$_POST['material_'.$i]."',
        '".$_POST['customer_style_'.$i]."',
        '".$_POST['EANCode_'.$i]."'),";
    }
}

UPDATE

As per the given comment, you should increase the length of URI. Please check below Increase your URI limit in your apache server.

but the best way to insert bulk data is mysqldump please see below answer for more information regarding mysqldump using PHP Answer

Related Questions in PHP

Related Questions in MYSQL

Related Questions in CSV

Related Questions in MULTIPLE-RECORDS

Popular Questions

Popular Tags

javascript python java c# php android html jquery c++ css ios sql mysql r reactjs node.js arrays c asp.net json python-3.x ruby-on-rails .net sql-server swift django angular objective-c pandas excel

Trending Questions