How to prevent Duplicate entry from Multiple Insert from PHP to MYSQL

Question

I have for loop for insert multiple records at 1 click button. In side that I have IF statement for RATE = 0 or NULL then just Ignore the insert statement That's part working fine. It's ignore the the insert if rate = 0 or null. BUT HERE what is strange is it insert the records 2 times which is before then the rate = 0 or NULL.

I have PHPMYADMIN.

Here IS my CODE

for($i=0;$i<$a;$i++)
        {
            if(! get_magic_quotes_gpc() )
            {
                $po_number1[$i] = addslashes ($_POST['random']);
                $master_vendor1[$i] = addslashes ($_POST['vendor_name']);
                $market1[$i] = addslashes ($_POST['market'][$i]);
                $start_date1[$i] = addslashes ($_POST['start_date'][$i]);
                $end_date1[$i] = addslashes ($_POST['end_date'][$i]); 
                $qty1[$i] = addslashes ($_POST['qty'][$i]); 
                $rate1[$i] = addslashes ($_POST['rate'][$i]); 
                $comment1[$i] = addslashes ($_POST['comment'][$i]);
                $media_type1[$i] = addslashes ($_POST['media_type'][$i]);
                $sub_vendor1[$i] = addslashes ($_POST['sub_vendor'][$i]);
            }
            else
            {
                $po_number1[$i] = $_POST['random'];
                $master_vendor1[$i] = $_POST['vendor_name'];
                $market1[$i] = $_POST['market'][$i];
                $start_date1[$i] = $_POST['start_date'][$i];
                $end_date1[$i] = $_POST['end_date'][$i]; 
                $qty1[$i] = $_POST['qty'][$i]; 
                $rate1[$i] =  $_POST['rate'][$i]; 
                $comment1[$i] =  $_POST['comment'][$i];
                $media_type1[$i] =  $_POST['media_type'][$i];
                $sub_vendor1[$i] =  $_POST['sub_vendor'][$i];
            }


            if($rate1[$i] == 0 || $rate1[$i] == null)
                {
                // rate is 0 or null, add error
                //  $errors[] = 'Rate is invalid in line ';

                    $errors[] = "Rate is invalid in line $i";
                }
            else
            {
                $sql = "INSERT INTO `order`(`po_number`, `vendor_name`, `market`, `start_date`, `end_date`, `qty`, `rate`, `comment`, `media_type`, `sub_vendor`) VALUES ('$po_number1[$i]','$master_vendor1[$i]','$market1[$i]','$start_date1[$i]','$end_date1[$i]','$qty1[$i]','$rate1[$i]','$comment1[$i]','$media_type1[$i]','$sub_vendor1[$i]')";
            }

            mysql_select_db('mediaplan');
            $retval = mysql_query( $sql, $conn );

            if(! $retval )
            {
                die('Could not enter data: ' . mysql_error());
            }
        }

ANY helps will be appreciate Thank You..

crate table query

create table order ( index int(255) AUTO_INCREMENT, po_number int(255), vendor_name varchar(255), market varchar(255), start_date date, end_date date, qty int(255), rate varchar(10), comment varchar(255), media_type varchar(255), sub_vendor varchar(255))

try `var_dump $_POST` to see all the values are as you expect it to be, corresponding to their indexes. — Teena Thomas, Nov 05 '12 at 15:50
addslashes() is about as useful for preventing sql injection as a roll of wet toilet paper is for cleaning up New York. e.g. you're doing it totally wrong. — Marc B, Nov 05 '12 at 15:52
You are using [an **obsolete** database API](http://stackoverflow.com/q/12859942/19068) and should use a [modern replacement](http://php.net/manual/en/mysqlinfo.api.choosing.php). You are also **vulnerable to [SQL injection attacks](http://bobby-tables.com/)** that a modern API would make it easier to [defend](http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php) yourself from. — Quentin, Nov 05 '12 at 16:07

score 1 · Accepted Answer · answered Nov 05 '12 at 16:08

    if($rate1[$i] == 0 || $rate1[$i] == null)
    {
        // rate is 0 or null, add error
        //  $errors[] = 'Rate is invalid in line ';
        $line = $i + 1 ;
            $errors[] = "Rate is invalid $line in line $i"+"1";
        }
    else
    {
        $sql = "INSERT INTO `order`(`po_number`, `vendor_name`, `market`, `start_date`, `end_date`, `qty`, `rate`, `comment`, `media_type`, `sub_vendor`) VALUES ('$po_number1[$i]','$master_vendor1[$i]','$market1[$i]','$start_date1[$i]','$end_date1[$i]','$qty1[$i]','$rate1[$i]','$comment1[$i]','$media_type1[$i]','$sub_vendor1[$i]')";

        mysql_select_db('mediaplan');
        $retval = mysql_query( $sql, $conn );
        if(! $retval )
        {
            die('Could not enter data: ' . mysql_error());
        }
    }

try to place this inside else statement

mysql_select_db('mediaplan');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
            die('Could not enter data: ' . mysql_error());
}

the reason why you have duplicate entry is because in your loop
if($rate1[$i] == 0 || $rate1[$i] == null) does not satisfy it will go to else
and it will trigger the $sql = //your sql command.

and the next cycle will encounter a condition that will satisfy this condition
if($rate1[$i] == 0 || $rate1[$i] == null) and it will store into your erro array

here is the trick your $sql still holds the last query that it has even if it
does not go to the $sql it will still go to mysql_query() thats why you have duplicate entry.

How to prevent Duplicate entry from Multiple Insert from PHP to MYSQL

1 Answers1