explode does not work

Question

This Code is for inserting Units for certain specifications. for example, if specification is length, then units are centimeter, meter and millimeter.

When I try to read all units in single text field with commas and in PHP tried to explode the units with comma. But when I submit the form only the first unit is saved to DB.

This is my database structure:

CREATE TABLE IF NOT EXISTS `tbl_unit` (
  `unit_id` varchar(5) NOT NULL,
  `unit_name` varchar(50) NOT NULL,
  `specification_id` int(11) NOT NULL,
  PRIMARY KEY (`unit_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

This is my code:

<?php
include("../config.php");
$uid=$_POST['unitid'];
$unit=$_POST['unitname'];
$spec=$_POST['specification'];

$arr1 = explode(',',$unit);
$size=count($arr1);

for($i=0;$i<$size;$i++)
{
  mysql_query("insert into tbl_unit values('".$uid."','$arr1[$i]','".$specification."')");

}
 header('Location:addunit.php');
 ?>

What is confusing me is that when I try to insert $arr1[0], $arr1[1] or $arr1[2] separately the values are being saved. I think the for loop is executing only once. what is the problem with for loop?

Your code is vulnerable to SQL injections. You should read on [how to prevent them in PHP](http://stackoverflow.com/q/60174/53114). — Gumbo, Mar 26 '14 at 15:31
Welcome to Stack Overflow! Also [the mysql extension is deprecated](http://www.php.net/manual/en/function.mysql-query.php). You should switch to [MySQLi](http://www.php.net/manual/en/book.mysqli.php) or [PDO](http://www.php.net/manual/en/ref.pdo-mysql.php) and use prepared statements. — TimWolla, Mar 26 '14 at 15:32
`var_dump($arr1);` to see that explode works, i wouldn't explode blind. I'd consider a strpos check, like `if (strpos($unit, ',' ) !== FALSE) { echo "INPUT IS READY TO EXPLODE"; }`. You shouldn't query in a loop and why don't you check the return value of the Query (see http://php.net/mysqli_query)... — pce, Mar 26 '14 at 15:35

score 1 · Answer 1 · answered Mar 26 '14 at 15:36

1

The problem is that unit_id is a primary key in your table, but you are trying to insert multiple records with the same value for this column. Each time through the loop, you insert using the same unit_id value, but a different unit_name value. You can't do that as long as unit_id is a primary (or unique) key.

answered Mar 26 '14 at 15:36

Patrick Q

6,373
2
25
34

thnx alot.. :) i got it – Shiji Mar 26 '14 at 16:41

score 0 · Accepted Answer · answered Mar 26 '14 at 15:51

You have a 'primary key' constraint on unit_id, hence the first insert succeeds, but all the subsequent ones fail.

There are many other things I could point out here, but without having the full picture, I am just listing some major ones:

1) In mysql, it is preferable for a int primary key, so it is best to have such a column explicitly even if you don't use it. The 'unit_id' column which is a varchar can still have 'unique key' constraints if you want to enforce it.

2) If you have a unique constraint on 'unit_id', you will have to re-think your table spec. on how you want to capture multiple units. Maybe it should just be indexed without a unique constraint?

3) You haven't escaped or sanitized the input from $_POST before inserting them in a table - makes it vulnerable to SQL injection attacks

4) Depending upon the size of the array, you could do a 'batch insert' rather than looping for each insert - i.e. a singly mysql inset call with multiple row values supplied.

5) Minor thing on 'explode()' - it has a very subtle behavior on empty strings, so it is best to check for that (maybe as part of data sanitization checks mentioned in point 3) - e.g.:

php -r '$a = explode(",", ""); var_dump($a);'

array(1) {
  [0]=>
  string(0) ""
}

explode does not work

2 Answers2