Inserting multiple rows with PHP

Question

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

Bad code. You do not use `@` operator esp. to mute warnings about inproper array keys — Marcin Orlowski, Sep 04 '17 at 06:32
Mysql, from row number 1 to row number 100, everything works well, but then after that the insert became empty. insert into po_label (carton, carton_number, po, size, pairs, colors, material, customer_style, EANCode) values (...),('10355000000099','99','0355CC0001','7',' 6','CAMEL','SOFT NUBUCK','CC-CRYSTALL','191644000270'),('10355000000100','100','0355CC0001','7',' 6','','SOFT NUBUCK','CC-CRYSTALL','191644000270'),('','','','','','','','',''),('','','','','','','','',''),('','','','','','','','',''),('','','','','','','','',''),(...) — correia, Sep 04 '17 at 06:34
Have you looked at the data being passed? Does `print_r($_POST);` give all of the data? — Nigel Ren, Sep 04 '17 at 07:16

Sahil Patel · Answer 1 · 2017-09-04T09:18:54.540

0

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

edited Sep 04 '17 at 09:18

answered Sep 04 '17 at 06:39

Sahil Patel

1,570
2
15
34

@correia What is the error? Also , remove '@' from code. so you can get the error, notice, warning – Sahil Patel Sep 04 '17 at 07:06
The error is that, I can insert 100 rows, but after that my insert starts to come like this: insert into po_label (carton, carton_number, po, size, pairs, colors, material, customer_style, EANCode) values ->row100('10355000000100','100‌','0355CC0001','7',' 6','','SOFT NUBUCK','CC-CRYSTALL','191644000270'),->row101.....('','','','','','','',‌'',''),('','','','',‌'','','','',''),('',‌'','','','','','',''‌,''),('','','','',''‌,'','','',''),(...) – correia Sep 04 '17 at 08:03
I am asking for the error which MySQL or PHP show you. – Sahil Patel Sep 04 '17 at 09:08
On my screen I have no error. But if i try to use the insert directly in the PHPMYADMIN sql task, I got the error : The requested URL's length exceeds the capacity limit for this server. But anyway, after get this error, I check my table and everything is inserted. – correia Sep 04 '17 at 09:11
This answer is very low quality and demonstrates insecure coding practices. – mickmackusa Aug 15 '20 at 13:28
@mickmackusa kindly provide updated answer on this. Thank you. – Sahil Patel Sep 09 '20 at 15:02
My point is that the snippet is recommending injection-vulnerable querying. I cannot use this page to close future duplicates because I would never advice anyone to use this advice. Additionally, all of those `!= ''` are unnecessary. – mickmackusa Sep 09 '20 at 15:26

score 0 · Answer 2 · answered Sep 04 '17 at 07:48

0

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

answered Sep 04 '17 at 07:48

KMS

566
4
16

Sorry, can you explain better your idea? I did not understand whats suppose to change in the code. – correia Sep 04 '17 at 08:05

Inserting multiple rows with PHP

2 Answers2