how to prevent duplicate entry in mysql while data are entered from array?

Question

I am new in android development I am sending a json array through android application and insert that data into MySQL database. The problem is that whenever I insert userJSON it entered every time with duplicate entries. So, I want to prevent duplicate entry in mysql how can this possible with php. Please help me to solve this problem.

json string from android side

'[{"type":"Outgoing","duration":"0","number":"XXXXXXXX","date":"Tue Dec 13 15:26:29 GMT+05:30 2016"},
{"type":"Outgoing","duration":"0","number":"XXXXXXXX","date":"Tue Dec 13 13:49:50 GMT+05:30 2016"}]';

Here is my php file for post json:

<?php  
require_once('conn.php');
if($_SERVER['REQUEST_METHOD']=='POST')
{
 $json = $_POST["usersJSON"]; 
 echo $json;
 if (get_magic_quotes_gpc())
 {
    $json = stripslashes($json);
 }
 $data = json_decode($json,true);

 $query=mysqli_query($con,"SELECT * 
                          FROM users
                          where number = '$number' 
                            and type = '$type' 
                            and date = '$date' 
                            and duration= '$duration'");

if(mysqli_num_rows($query)>0) {
    echo "already exist";
}
elseif(is_array($data))
 {
       $sql = "INSERT IGNORE INTO users (type, duration, number,date) values ";

       $valuesArr = array();

 foreach($data as $row)
 {
    $type = mysqli_real_escape_string( $con,$row['type'] );
    $duration = mysqli_real_escape_string($con, $row['duration'] );
    $number = mysqli_real_escape_string( $con,$row['number'] );
$date = mysqli_real_escape_string( $con,$row['date'] );

    $valuesArr[] = "('$type', '$duration', '$number', '$date')";


}

$sql .= implode(',', $valuesArr);
if(mysqli_query($con,$sql))
{
     echo 'Entry Added Successfully';
}
else
{
     echo 'Could Not Add Entry';
}
}
//Closing the database 
 mysqli_close($con);
}
?>

definitely not with PHP, you do this by creating a UNIQUE index on the relevent column in the database — e4c5
– e4c5, Commented Dec 13, 2016 at 11:56
@e4c5 will you plz explain how can i give UNIQUE index or provide me a link — Hiren Gujarati
– Hiren Gujarati, Commented Dec 13, 2016 at 11:58
@Blueblazer172 i just want to stop repeatation of database entry how can i achieve that? — Hiren Gujarati
– Hiren Gujarati, Commented Dec 13, 2016 at 12:14
You can't reliably do it on the PHP side. If two instances of your script happen to insert the same data at the same time then you will have a duplicate no matter what you do. It's called a race condition. The only way to do what you want with any degree of reliability is to enforce a unique constraint on the database table. — GordonM
– GordonM, Commented Dec 13, 2016 at 13:30

e4c5 · Accepted Answer · 2016-12-13 13:24:02Z

Create a UNIQUE INDEX

Regardless of whatever programming language that you use, all the constraints on the data have to be enforced with in the database and not in your application layer. And the easiest way to do that is to add a UNIQUE KEY on the columns in question.

ALTER TABLE users ADD UNIQUE KEY all_columns(number,type,date,duration)

I am adding all the four columns to the unique index because you seem to want any column to have duplicate values taken in isolation. Please confirm if this is correct or choose the columns appropriately when creating the index.

Simply your code

With a unique key in place, your don't need that SELECT

$data = json_decode($json,true);
if(is_array($data))
{
       $sql = "INSERT IGNORE INTO users (type, duration, number,date) values ";
       ....
}

Use Prepared Statements

Instead of a huge string concatenation as is being currently done and multple calls to mysqli_real_escape, you would be better of using prepared statements. You might even get a tiny increase in performance. However more importantly there is a maximum size of a string that can be passed through to the server, if you get a large array you might go beyond that.

Collectives™ on Stack Overflow

how to prevent duplicate entry in mysql while data are entered from array?

1 Answer 1

Create a UNIQUE INDEX

Simply your code

Use Prepared Statements

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Create a UNIQUE INDEX

Simply your code

Use Prepared Statements

Comments

Your Answer

Sign up or log in

Post as a guest

Related