-3

I want to make a code where if the data already exists in the database and the user insert the same input again and send to the database, the sql command will detect it and will not allow the duplicate data enter the database. Addtional information, I don`t have primary key for my table. Here is my code.

$sql="INSERT IGNORE INTO tempahan(Nama,Aktiviti,No_HP,Unit,Tempat,Tarikh_Penggunaan,Masa_Mula,Masa_Akhir,Email) VALUES('$_POST[name]','$_POST[Aktiviti]','$_POST[number]','$_POST[unit]','$_POST[tempat]','$_POST[tarikh]','$_POST[masa1]','$_POST[masa2]','$_POST[email]')";

       $_POST['tempat'] = $data['Tempat'] ;
       $_POST['masa1'] = $data['Masa_Mula']; 
       $_POST['masa2'] = $data['Masa_Akhir'];  if($_POST['tempat'] != $data['Tempat'] && $_POST['masa1'] != $data['Masa_Mula'] && $_POST['masa2'] != $data['Masa_Akhir']) {
         echo 'the booking was successful.';
         }
         else
         { echo 'the place already occupied.';}

I'm new to sql and also php. Therefore, I really need help from all of you guys. I already see the other same question. But, every solution provided I've failed.

pala_
  • 8,901
  • 1
  • 15
  • 32
najiy
  • 3
  • 5

3 Answers3

1
$query = $db->query( // query your table );

 $array = array('name'=>$_POST['name'],
                 'address'=>$_POST['address']);

while ($row = mysqli_fetch_all($query)) {

    $diff = in_array($array, $row);
    {
        if(empty($diff))
        {
                // insert data into table
        }
        else{
            //data already exist
        }
    }
}
pala_
  • 8,901
  • 1
  • 15
  • 32
shadab
  • 347
  • 1
  • 10
  • thank you Sam Bown...but im sorry...i dont really understand the structure of your coding..can you explain it... – najiy Apr 27 '15 at 04:40
1

The correct way to do this is to enforce a unique constraint on your table, across the fields that you consider to be unique. You can do that as such.

alter table tempahan
  add unique (Tempat, Masa_Mula, Masa_Akhir)

Your database will then reject out of hand any attempts to insert duplicate data. No need to do a prior check before inserting.

Here is a very basic demo of what happens when you set your table up with this unique constraint, and then try and insert duplicate data. In short: it errors.

pala_
  • 8,901
  • 1
  • 15
  • 32
  • thank you...i want to know...after i put this command, there will be no effect to my insert into mysql command right??..it only detect the duplicate data.... – najiy Apr 27 '15 at 04:47
  • That's correct. Your insert command will fail if there is duplicate data, but otherwise it's exactly the same. You will need to test for failure in your php application. – pala_ Apr 27 '15 at 04:49
  • thank you pala...i finally succeed when using your command alter...thank you very much....next i will ask again... – najiy Apr 27 '15 at 06:38
  • no problem - since it seems to have worked for you, could you please mark the question as accepted – pala_ Apr 27 '15 at 06:43
  • may i know pala...what is the different between set the field to primary key and set the field to UNIQUE....what i mean is the different in function to avoid data redundancy like im facing hear.... – najiy Apr 27 '15 at 08:20
  • try reading here: http://stackoverflow.com/questions/9565996/difference-between-primary-key-and-unique-key – pala_ Apr 27 '15 at 08:50
0
// first check existing recors on the database
    $select = "SELECT `Tempat`, `Masa_Mula`, `Masa_Akhir` 
        FROM  `tempahan`
        WHERE `Tempat` = {$_POST['tempat']} 
        AND `Masa_Mula` = {$_POST['masa1']} 
        AND `Masa_Akhir` = {$_POST['masa2']}";

    $result = mysql_query($select, $dbconnection);
    // check if the have existing records
    // the query fetching depends on your work
    // but this is a simple way only
    // but have more examples on the internet
    // to make query more better and ellegant
    if (mysql_num_rows($select) > 0) {
        echo 'the place already occupied.';
    } else {
        // insert new record
        $sql="INSERT IGNORE INTO tempahan(Nama,Aktiviti,No_HP,Unit,Tempat,Tarikh_Penggunaan,Masa_Mula,Masa_Akhir,Email) 
        VALUES(
            '$_POST[name]',
            '$_POST[Aktiviti]',
            '$_POST[number]',
            '$_POST[unit]',
            '$_POST[tempat]',
            '$_POST[tarikh]',
            '$_POST[masa1]',
            '$_POST[masa2]',
            '$_POST[email]')";
        echo 'the booking was successful.';
    }
Oli Soproni B.
  • 2,774
  • 3
  • 22
  • 47
  • doing it at application level requires all possible sources of data to be done this way. far better to do it with a `before insert` trigger. far better again to enforce a unique constraint across some table fields. – pala_ Apr 27 '15 at 03:19
  • Note that performing a select and conditional insert at the application layer is vulnerable to a race condition. It might be good enough for the task at hand, though. – Dan Guzman Apr 27 '15 at 03:28
  • @pala_, yeah.. ok.. to fulfill that then a database design should be a good shape to start,, since the database is not well designed.. please share your thoughts about the question and how to design the database well for us to learn thanks mate – Oli Soproni B. Apr 27 '15 at 03:34
  • `insert ignore` means don't crap out when it encounters an error. it is of no benefit here. – pala_ Apr 27 '15 at 04:47
  • @Oli...i've tried the code but why it produce an error which is mysql_num_rows is no a valid sql command..... – najiy Apr 27 '15 at 06:15