Insert Multiple Row into Mysql using PHP Array

[neodize]

Hi

Ok so I did a Form with array such as this

<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:

$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:

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??

[dagon]

wrap your sql query with a check for what field(s) can't be empty, like so:

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
}

[neodize]

Hi dagon!

I figured out something very simple

I added this before the SQL:

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

[neodize]

Can someone help show me how to echo / print the 2 successfully inserted data?

Ive been trying and am without a clue :/

[johanafm]

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.

$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';
}

[neodize]

Hi johanafm thanks!

This is what I did:

$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?

[johnpeace]

$row_data[] = "(',', ...

What is this supposed to do?

Shouldn't that read:

 $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);

[neodize]

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

[johanafm]

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.

$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';

[neodize]

I understand that before this it was:

$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:

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:

$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?

$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';