Prevent duplicate insertion in database

Question

I'm inserting some data into my database that are found in two tables. The insert is working fine, however when i try to insert again, if the cell has a value, the new value will be inserted next to it. I tried an if/else condition to prevent a new insertion if the cell has already a value in it, but failed on making it work. Maybe it's not the solution or i did it wrong. Any help please?

if(isset($_POST['submit'])) {   
    $degree = $_POST['Degree'];
    $major = $_POST['Major'];
    $univ = $_POST['Univ'];
    $brevet = $_POST['Brevet'];
    $baccbt = $_POST['Baccbt'];
    $selectaf = $_POST['SelectAF'];

    $sql1="SELECT certificatesgrading.brevet,
                  certificatesgrading.baccbt,
                  university.degree,
                  university.major,
                  university.univ
           FROM   university,
                  certificatesgrading
           WHERE  afnumber = '$selectaf'";

    if ($result=mysqli_query($con,$sql1)) {
        // Return the number of rows in result set
        $rowcount=mysqli_num_rows($result);
    }

    $sql="INSERT INTO university
            (afnumber,
             university.degree,
             university.major,
             university.univ)
          VALUES   ('$selectaf',
                    '$degree',
                    '$major',
                    '$univ')";

            $sql1="INSERT INTO certificatesgrading
            (afnumber,
             certificatesgrading.brevet,
             certificatesgrading.baccbt)
             VALUES     ('$selectaf',
                         '$brevet',
                         '$baccbt') ";
            $result = mysql_query($sql);
            $result = mysql_query($sql1);


}
echo $menu;

Answer 1

First off, the way you're building your queries is VERY dangerous. You're taking raw input from an outside source, not doing any data validation or escaping, and using it directly in a query string. This is how SQL injection attacks happen.

As you're using mysqli you really need to look at re-implementing your database access logic to use prepared statements, or at the very least start using mysqli_real_escape_string() to make your code more resistant to SQL injection abuse.

As for your particular problem, I'm going to assume that there are certain columns or combinations of columns that are required to be unique, and other columns that don't have to be unique. For the columns/combinations that do need to be unique, you can create indexes that indicate that their values must not be duplicated. For example, I'm going to assume that the AFnumber field is some kind of identification code for a university that must be unique for the universities table. If that's the case then you can create an index that indicates that this field must be unique.

CREATE UNIQUE INDEX keep_af_number_unique 
  ON university (AFnumber);

With this constraint in place, then you can only ever insert a particular AFnumber once. An attempt to insert the same value again will trigger an error (which you must check for and catch in your code!) until you either remove the existing AFnumber or change its value.

If you have a group of columns where the combination of values contained in them needs to be unique you can create a unique index across multiple columns

CREATE UNIQUE INDEX multi_col_unique
  ON table_name (column1, column2, column3);

For the three columns it will now be possible for one individual column to hold the same value more than once, but the combination of columns taken together must be unique. For example, the following data would be allowed:

col1 | col2 | col3
===================
   1 |    1 |    1
   2 |    2 |    2
   1 |    1 |    2

but if you tried to add another row with values 1, 1, 1 an error would occur.

In addition to this, it would also be a good idea to implement a system in your code to detect and prevent multiple form submissions, perhaps by using a token stored in the PHP session. This question has some possible solutions that may be of use.

Answer 2

if(isset($_POST['submit']))
{
    $degree = $_POST['degree'];
    $major = $_POST['major'];
    $univ = $_POST['univ'];
    $brevet = $_POST['brevet'];
    $baccbt = $_POST['baccbt'];
    $selectaf = $_POST['selectAF'];

    $sql="SELECT * FROM university,certificatesgrading WHERE AFNumber='$selectaf'";
    $result = mysql_query($sql);
    $count = count($result);

    if($count==0)
    {
        $sql1="INSERT INTO university (AFNumber,university.Degree,university.Major,university.Univ) VALUES('$selectaf','$degree','$major','$univ')";
        $sql2="INSERT INTO certificatesgrading (AFNumber,certificatesgrading.brevet,certificatesgrading.baccbt) VALUES('$selectaf','$brevet','$baccbt')";
        $result1 = mysql_query($sql1);
        $result2 = mysql_query($sql2);
        if(isset($result1) AND isset($result2))
        {
            echo '<script>swal("Success", "Changes have been saved", "success");</script>';
            redirect(base_url() . 'controller/method');//refresh header
        }
        else
        {
            echo 'Something went wrong';
        }
    }

}

Note: To use swal/sweetalert you need to Download and install that Check $_POST['degree'] variables start with caps or simple in your form namefield

Answer 3

This kind of behavior is "hard" to develop on your own if you want to cover all the possible cases, especially if you want to deal with relations between tables.

I think that you should consider using an ORM such as doctrine. That way, you'll be able to fix constraints on your data model that will be processed and checked by the ORM for you. Also consider using it together with a PHP framework like Symfony2. You don't have to implement the full stack, the use of some components can be enough depending on your needs.

Here is an exemple given by the documentation and how works annotations :

<?php
/**
 * @Entity
 * @Table(name="ecommerce_products",uniqueConstraints={@UniqueConstraint(name="search_idx", columns={"name", "email"})})
 */
class ECommerceProduct
{
}

The use of an ORM and a framework can also be very helpful in keeping your PHP data model consistent with your MySQL schema because you'll be able to generate and update it very simply. See an exemple on how symfony2 can help here.

Last but not least, you are not considering concurrent accesses to your data which can lead into data corruption if you only use MySQL statements without using transactions and an engine like innodb. It is very simple to activate and compatible with non-transactionnal queries, if you don't need or want to implement it for your whole application. This is another very important aspect covered by an ORM you should really think of.