Insert Multiple Row into Mysql using PHP Array
Results 1 to 10 of 10

Thread: Insert Multiple Row into Mysql using PHP Array

  1. #1
    Junior Member
    Join Date
    Feb 2010
    Posts
    21

    Insert Multiple Row into Mysql using PHP Array

    Hi

    Ok so I did a Form with array such as this

    Code:
    <tr>
    <td width="75"><b>PERSON 1</b></td>
    </tr>
    
    <tr>
    <td width="75">Name</td>
    <td width="301"><input name="name[]" type="text" id="name[]" class="required">
    </td>
    </tr>
    <tr>
    <td>IC No</td>
    <td><input name="icno[]" type="text" id="icno[]" class="required"></td>
    </tr>
    <tr>
    <td width="75"><b>PERSON 2</b></td>
    </tr>
    <tr>
    <td width="75">Name</td>
    <td width="301"><input name="name[]" type="text" id="name[]" class="required">
    </td>
    </tr>
    <tr>
    <td>IC No</td>
    <td><input name="icno[]" type="text" id="icno[]" class="required"></td>
    </tr>
    <tr>
    <td width="75"><b>PERSON 3</b></td>
    </tr>
    <tr>
    <td width="75">Name</td>
    <td width="301"><input name="name[]" type="text" id="name[]" class="required">
    </td>
    </tr>
    <tr>
    <td>IC No</td>
    <td><input name="icno[]" type="text" id="icno[]" class="required"></td>
    </tr>
    And my form post to this:

    PHP Code:

    $con 
    mysql_connect("localhost","user","pass");
    if (!
    $con)
    {
    die(
    'Could not connect: ' mysql_error());
    }

    mysql_select_db("test"$con); 

    foreach(
    $_POST['name'] as $row=>$Name)
    {
        
    $name=mysql_real_escape_string($Name);
        
    $id=mysql_real_escape_string($_POST['id'][$row]);
        
    $icno=mysql_real_escape_string($_POST['icno'][$row]);
        
    $route=mysql_real_escape_string($_POST['route'][$row]);
        
    $price=mysql_real_escape_string($_POST['price'][$row]);
        
    $date=mysql_real_escape_string($_POST['date'][$row]);
        
    $time=mysql_real_escape_string($_POST['time'][$row]);
        
    $username=mysql_real_escape_string($_POST['username'][$row]);
        
    $pax=mysql_real_escape_string($_POST['pax'][$row]);
    //    $paxcur = $pax - $row;

    $sql="INSERT INTO issue(id, name, icno, route, price, date, time, username, issuedate, issuetime)VALUES('', '$name', '$icno', '$route', '$price', '$date', '$time', '$username', CURDATE(), CURTIME())" or die(mysql_error());
    $result mysql_query($sql$con) or die(mysql_error());

    }
    // if successfully updated.
    if($result){

    echo 
    "$row successfully inserted.<br/>";
    echo 
    "<BR>";
    echo 
    "Update Successful";
    echo 
    "<BR>";
    }
    else {
    echo 
    "ERROR";
    }

    ?> 
    So I inserted 2 Username & ICNO instead of 3 with this data:

    Name: Test 3, Test 4
    ICNO: 841123155002, 841123155003

    the Result came out as:

    Code:
    2 successfully inserted.
    Update Successful
    But when I go to PHPMYADMIN it showed:

    ID Name ICNO..................Route Price Date Time Username
    83 (empty) (empty).......... LBN-MBK 14 2010-02-16 01:30:00 neodize
    82 Test 4 841123155003 LBN-MBK 14 2010-02-16 01:30:00 neodize
    81 Test 3 841123155002 LBN-MBK 14 2010-02-16 01:30:00 neodize

    The thing I dont know is, how do I make it not insert ID No. 83 when i only key in ID No. 81 & No. 82??

  2. #2
    Senior Member dagon's Avatar
    Join Date
    Nov 2001
    Posts
    6,178
    wrap your sql query with a check for what field(s) can't be empty, like so:

    PHP Code:
    if(!empty($icno)){
    $sql="INSERT INTO issue(id, name, icno, route, price, date, time, username, issuedate, issuetime)VALUES('', '$name', '$icno', '$route', '$price', '$date', '$time', '$username', CURDATE(), CURTIME())" or die(mysql_error());
    $result mysql_query($sql$con) or die(mysql_error());
    }else{
    //icno was empty, you may want something here


  3. #3
    Junior Member
    Join Date
    Feb 2010
    Posts
    21
    Hi dagon!

    I figured out something very simple

    I added this before the SQL:

    PHP Code:
    if (empty($name) || empty($icno)) {continue;} 
    And it somehow work!

    Ok 1 thing that I cant figure out now is how to echo the $icno of the two data I inserted

  4. #4
    Junior Member
    Join Date
    Feb 2010
    Posts
    21
    Can someone help show me how to echo / print the 2 successfully inserted data?

    Ive been trying and am without a clue :/

  5. #5
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,645
    Change your query to execute once, not once per row. It will be more efficient, and you can also have MySQL deal with counting number of inserts rather than doing it yourself.
    PHP Code:
    $row_data = array();
    foreach(...) {
        
    // first part as before (setting up the variables)

        
    $row_data[] = "('$name', $icno, '$route', ...)";
    }
    if (!empty(
    $row_data)) {
        
    // drop id from the fields to insert into. then you don't have to pass an empty string or null
        // for it among your values
        
    $query 'INSERT INTO issue(name, icno, route, ...) VALUES '.implode(','$row_data);

        if (
    mysql_query($qry))
            echo 
    'Successful inserts: ' mysql_affected_rows();
        else
            echo 
    'query failed';

    Last edited by johanafm; 02-16-2010 at 06:34 AM.

  6. #6
    Junior Member
    Join Date
    Feb 2010
    Posts
    21
    Hi johanafm thanks!

    This is what I did:

    PHP Code:
    $row_data = array();
    foreach(
    $_POST['name'] as $row=>$Name) {
    $issuedate=('Y-m-d');
    $issuetime=('h:i:s');
    $name=mysql_real_escape_string($Name);
        
    $id=mysql_real_escape_string($_POST['id'][$row]);
        
    $icno=mysql_real_escape_string($_POST['icno'][$row]);
        
    $route=mysql_real_escape_string($_POST['route'][$row]);
        
    $price=mysql_real_escape_string($_POST['price'][$row]);
        
    $date=mysql_real_escape_string($_POST['date'][$row]);
        
    $time=mysql_real_escape_string($_POST['time'][$row]);
        
    $username=mysql_real_escape_string($_POST['username'][$row]);
        
    $pax=mysql_real_escape_string($_POST['pax'][$row]);
    $paxcur $pax $row;

        
    $row_data[] = "(',', '$name', '$icno', '$route', '$price', '$date', '$time', '$username', '$issuedate', '$issuetime')";
    }
    if (!empty(
    $row_data)) {
        
    $query 'INSERT INTO issue(id, name, icno, route, price, date, time, username, issuedate, issuetime) VALUES '.implode(','$row_data);

        if (
    mysql_query($query))
            echo 
    'Successful inserts: ' mysql_affected_rows();
        else
            echo 
    'query failed';

    I inserted with only 2 data instead of 3 and it came out with this:

    Successful inserts: 3

    Somehow it doesnt work. And how do I echo icno of those datas inserted?

  7. #7
    Junior Member
    Join Date
    Dec 2008
    Posts
    29
    $row_data[] = "(',', ...
    What is this supposed to do?

    Shouldn't that read:
    PHP Code:
     $row_data[] = "('$id', '$name', '$icno', '$route', '$price', '$date', '$time', '$username', '$issuedate', '$issuetime')";
    }
    if (!empty(
    $row_data)) {
        
    $query 'INSERT INTO issue(id, name, icno, route, price, date, time, username, issuedate, issuetime) VALUES '.implode(','$row_data); 

  8. #8
    Junior Member
    Join Date
    Feb 2010
    Posts
    21
    works fine for me though. my id is using auto increment

    i finally figured out fixing the 2 inserted instead of 3 but i still cant figure out how to echo the icno inserted

  9. #9
    Senior Member
    Join Date
    Jul 2007
    Posts
    3,645
    The only way to know what icnos that were inserted is to save them all and then select from the db to see which went ok. However, if the number of rows to be inserted matches the number of rows inserted, you know that they were all inserted.
    PHP Code:
    $all_icno = array();
    foreach () {
        
    // as before
        
    $all_icno[] = $icno;
    }
    mysql_query();
    $rows_inserted mysql_affected_rows();
    if (
    count($all_icno) == $rows_inserted) {
        echo 
    'All (' $rows_inserted.  ') rows inserted. icnos: <pre>'.print_r($all_icno,1).'</pre>';
    }
    else if (
    $rows_inserted 0) {
        
    $query 'SELECT icno FROM issue WHERE icno IN (' implode(',' $all_icno) . ')';
        
    $result mysql_query($qry);
        if (
    $result) {
            echo 
    $rows_inserted ' of ' count($all_icno) . ' rows inserted. icno:<br/>';
            while (
    $row mysql_fetch_array($query)) {
                echo 
    $row[0] . '<br/>';
            }
        }
        else {
            echo 
    mysql_errno() . ': ' mysql_error();
        }
    }
    else
        echo 
    'No rows inserted'

  10. #10
    Junior Member
    Join Date
    Feb 2010
    Posts
    21
    I understand that before this it was:

    PHP Code:
    $row_data[] = "(',', '$name', '$icno', '$route', '$price', '$date', '$time', '$username', '$issuedate', '$issuetime')";

    $query 'INSERT INTO issue(id, name, icno, route, price, date, time, username, issuedate, issuetime) VALUES '.implode(','$row_data);

    mysql_query($query); 
    so i changed to the one u gave me but i got confused. the one u showed me at:

    PHP Code:
    foreach () {
        
    // as before
        
    $all_icno[] = $icno;
    }
    mysql_query(); 
    if i put it empty - () it doesnt query anything and it gives me this error:

    Warning: Wrong parameter count for mysql_query()
    No rows inserted

    I cant do this below:

    PHP Code:
    $all_icno[] = "(',', '$name', '$icno...
    $query = 'INSERT INTO issue(id, name, icno, route, price, date, time, username, issuedate, issuetime) VALUES '.implode(',', $all_icno); 
    because its already INSERTING in the new coding u gave me if i put that query it will only double up my INSERT

    Can you show me whats wrong with my code?

    PHP Code:
    $all_icno = array();
    foreach(
    $_POST['name'] as $row=>$Name) {
        
    $issuedate=('Y-m-d');
        
    $issuetime=('h:i:s');
        
    $name=mysql_real_escape_string($Name);
        
    $id=mysql_real_escape_string($_POST['id'][$row]);
        
    $icno=mysql_real_escape_string($_POST['icno'][$row]);
        
    $route=mysql_real_escape_string($_POST['route'][$row]);
        
    $price=mysql_real_escape_string($_POST['price'][$row]);
        
    $date=mysql_real_escape_string($_POST['date'][$row]);
        
    $time=mysql_real_escape_string($_POST['time'][$row]);
        
    $username=mysql_real_escape_string($_POST['username'][$row]);
        
    $pax=mysql_real_escape_string($_POST['pax'][$row]);
        
    $paxcur $pax $row;
        
    $all_icno[] = $icno;

    //$row_data[] = "(',', '$name', '$icno', '$route', '$price', '$date', '$time', '$username', '$issuedate', '$issuetime')";
    // $query = 'INSERT INTO issue(id, name, icno, route, price, date, time, username, issuedate, issuetime) VALUES '.implode(',', $row_data);

    mysql_query();
    }
    $rows_inserted mysql_affected_rows();
    if (
    count($all_icno) == $rows_inserted) {
        echo 
    'All (' $rows_inserted.  ') rows inserted. icnos: <pre>'.print_r($all_icno,1).'</pre>';
    }
    else if (
    $rows_inserted 0) {
        
    $query "SELECT icno FROM issue WHERE icno IN (' . implode(',' $all_icno) . ')";
        
    $result mysql_query($query);
        if (
    $result) {
            echo 
    $rows_inserted ' of ' count($all_icno) . ' rows inserted. icno:<br/>';
            while (
    $row mysql_fetch_array($query)) {
                echo 
    $row[0] . '<br/>';
            }
        }
        else {
            echo 
    mysql_errno() . ': ' mysql_error();
        }
    }
    else
        echo 
    'No rows inserted'

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •