how to avoid duplicate records insertion in php mysqli?

Question

How do I avoid duplicate records insertion in PHP MYSQLi? this my script :

$nama=$_POST['nama'];
$member=$_POST['member'];
$peserta=$_POST['peserta'];
$tour=$_POST['tour'];

mysqli_query($conn,"insert into gathering (nama, member, peserta, tour) values ('$nama', '$member', '$peserta', '$tour')");

header('location:index.php');

You are wide open to [SQL Injections](http://php.net/manual/en/security.database.sql-injection.php) and should really use [Prepared Statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) instead of concatenating your queries. Specially since you're not escaping the user inputs at all! — M. Eriksson, Nov 23 '17 at 06:26
Does this answer your question? [How do I specify unique constraint for multiple columns in MySQL?](https://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql) — Dharman, Jul 25 '20 at 00:09

score 1 · Accepted Answer · answered Nov 23 '17 at 06:43

1

Below code will help you inserting unique data:

$result = mysqli_query($conn, "SELECT nama, member, peserta, tour FROM gathering where nama = '$nama' and member = '$member' and peserta='$peserta' and tour='$tour'")
$total = mysqli_num_rows($result);

if($total==0)
{
    mysqli_query($conn,"insert into gathering (nama, member, peserta, tour) 
values ('$nama', '$member', '$peserta', '$tour')");
}

answered Nov 23 '17 at 06:43

Amit Gupta

2,771
2
17
31

2

If you're just counting the number of rows, why do you need to select all those columns? Just use `SELECT 1 FROM ...` – Barmar Nov 23 '17 at 06:55

score 1 · Answer 2 · answered Nov 23 '17 at 06:49

1.You can alter table with Unique constraint :

ALTER TABLE mytbl ADD UNIQUE (columnName);

get record from table before insert record and check whether record is already exist or not.

$dupesql = "SELECT * FROM gathering where (nama = '$nama' )";
$duperaw = mysql_query($dupesql);
if (mysql_num_rows($duberaw) > 0) {
   show error msg;
}else{
   mysqli_query($conn,"insert into gathering (nama, member, peserta, tour) values ('$nama', '$member', '$peserta', '$tour')");
}

Anandhu Nadesh · Answer 3 · 2021-12-07T09:54:48.443

-1

first check if the row exists and then insert

$res= mysqli_query($conn,"SELECT 1 from gathering where nama='$nama' and member='$member' and peserta='$peserta' and tour='$tour'");

if(mysqli_num_rows($res)==0)
{
mysqli_query($conn,"insert into gathering (nama, member, peserta, tour) values ('$nama', '$member', '$peserta', '$tour')");
}
else
{
echo "record already exists";
}

edited Dec 07 '21 at 09:54

answered Nov 23 '17 at 06:40

Anandhu Nadesh

672
2
11
20

score -1 · Answer 4 · edited Jul 25 '20 at 00:08

-1

Add a column with primary key

ALTER TABLE gathering ADD COLUMN `id` INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST

edited Jul 25 '20 at 00:08

M--

25,431
8
61
93

answered Nov 23 '17 at 06:45

Donald Wengki

29
1
6

how to avoid duplicate records insertion in php mysqli?

4 Answers4