PHP Array insert into MySQL table as individual rows.

Question

I want to insert mutiple rows in mysql table using php. Data is in arrays and I am trying to employ following codes but its not working. Please help me.

Query String is:

new_document.php?doctype=Inv&paytype=u&docno=3&bookno=31&prname=40329&pename=1001&paydate=07%2F22%2F2011&indate=7%2F30%2F2011&accname1=Man+Site&DrAmount1=20&CrAmount1=1&accname2=Man+Site&DrAmount2=0&CrAmount2=5&ref=Jul%2F2011

    $rows = explode("&", $_SERVER['QUERY_STRING']);
    $rowcount =((count($rows)-9)/3); // Count set of rows received , 9 is the costant no of inputs 

    $serialno = array();
    $acc_name = array();
    $debit = array();
    $credit = array();

    $serialno [] = $i;
    array_unshift($serialno,"");
    unset($serialno[0]); 

    $acc_name[] = ($_GET['accname'.$i]);
    array_unshift($acc_name,"");
    unset($acc_name[0]);

    $debit[] = ($_GET['DrAmount'.$i]);
    array_unshift($debit,"");
    unset($debit[0]);

    $credit[] = ($_GET['CrAmount'.$i]);
    array_unshift($credit,"");
    unset($credit[0]);



    echo 'Serial Numbers : '.print_r($serialno);
    echo '<br>A/C Names : '.print_r($acc_name);
    echo '<br>Debit Amount : '.print_r($debit);
    echo '<br>Credit Amount : '.print_r($credit);

    This gives me following result :
    ARRAY
    (
    [1] => 1
    [2] => 2
    )
    SERIAL NUMBERS : 1ARRAY
    (
    [1] => MAN SITE
    [2] => MAN SITE
    )

    A/C NAMES : 1ARRAY
    (
    [1] => 20
    [2] => 0
    )

    DEBIT AMOUNT : 1ARRAY
    (
    [1] => 1
    [2] => 5
    )

    CREDIT AMOUNT : 1


    for ($i=1;$i=$rowcount;$i++)
    {
    $amount = ($debit[$i]-$credit[$i]);
    $accname=$acc_name[$i];

    $insert2 = "INSERT INTO `khata2`.`docitems` 
    (
    `dateinput`,
    `docno`, 
    `itemno`, 
    `accountname`, 
    `amount`, 
    `picrefno`, 
    `updatestamp`)
    VALUES (
    NOW(''), 
    '$docno', 
    '$i', 
    '$accname', 
    '$amount', 
    '$docno', 
    Null)";      
    }
    if (!mysql_query($insert2)){
    echo '<br><a style="color:RED">Zero</a> items saved! Try Again..<br>';

    die('Error: ' . mysql_error());
    break;
    }
    echo '<br><a style="color:GREEN">'.$rowcount.' </a>record inserted.'; 
    exit();


    RESULTS AS :
    Fatal error:  Maximum execution time of 30 seconds exceeded in F:\server\htdocs\xampp\Khata2\processor\new_document.php on line 184 ( Line 184 is  '$amount',)

evan · Accepted Answer · 2011-07-30 23:52:41Z

1

ignoring the fact that this is wildly insecure, here's your problem:

for ($i=1;$i=$rowcount;$i++)

this should probably be:

for ($i=1;$i<$rowcount;$i++)

right now you're assigning $rowcount to $i which is always true, so the for loop never terminates.

answered Jul 30, 2011 at 23:52

evan

4,3081 gold badge20 silver badges18 bronze badges

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

2 Comments

user837168 Over a year ago

Thank you very much for the reply. Its working now but only inserting last set of values in the array(s). Any Idea what am I doing wrong ? Cheers (SORRY FOR BEING A PAIN) Also what else approch I can use to get the desired results ?

evan Over a year ago

You're probably skipping the first one because PHP indexes arrays starting at 0, not 1. So change the for loop to start at 0.

RiaD · Accepted Answer · 2011-07-31 11:21:57Z

1) You do:

for ($i=1;$i<=$rowcount;$i++){
     //generate query
}
//do query

but yoou need:

for ($i=1;$i<=$rowcount;$i++){
     //generate query
     //do query
}

2) Maybe some faster will be to do 1 query than 1 query for every row:
You can use following syntax:

INSERTY INTO table(field1,field2) VALUES(v11,v12),(v21,v22),(v31,v32);

$insert2=array();
for ($i=1;$i=$rowcount;$i++)
{
    $amount = ($debit[$i]-$credit[$i]);
    $accname=$acc_name[$i];

    $insert2[] = "(
    `dateinput`,
    `docno`, 
    `itemno`, 
    `accountname`, 
    `amount`, 
    `picrefno`, 
    `updatestamp`)
    VALUES (
    NOW(''), 
    '$docno', 
    '$i', 
    '$accname', 
    '$amount', 
    '$docno', 
    Null)";      
}
if (!mysql_query('INSERT INTO tblname VALUES'.implode(',',$insert2))){
    echo '<br><a style="color:RED">Zero</a> items saved! Try Again..<br>';

    die('Error: ' . mysql_error());
    break;
}

3) PDO or mysqli with prepared statements is great;)

Collectives™ on Stack Overflow

PHP Array insert into MySQL table as individual rows.

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related