MySQL: Returning an error if insert value is not unique

Question

I would like an error to be outputted on the addStudent.php page if the combination of firstname and last name are already in the Student table. Currently, it simply doesn't add a non-unique entry into the table, but doesn't inform the user that the entry already exists.

Table structure:

<?php
include 'connect.php';

// sql to create table
$sql = "CREATE TABLE IF NOT EXISTS student (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY, 
fname VARCHAR(30),
lname VARCHAR(30),
mclass VARCHAR(30),
aclass VARCHAR(30),
UNIQUE (`fname`, `lname`)
)";

if ($conn->query($sql) === TRUE) {
} else {
    echo "Error creating table: " . $conn->error;
}

$conn->close();
?>

addStudent.php

    <fieldset>
        <legend><h2> Details </h2></legend>
        <form class="pure-form">
        <label>First Name&nbsp;&nbsp; </label><input type="text" id="firstname" name = "firstname" autofocus=""><br><br>
        <label>Last Name&nbsp;&nbsp; </label><input type="text" id="lastname" name = "lastname"><br><br>    
        </form>
    </fieldset>
    <br>
    <fieldset>
        <legend><h2>Classes</h2></legend>
        <form class="pure-form">
        <label>Morning Class&nbsp;&nbsp;  </label>
            <select id = "morningclass" name="morningclass">
              <option value=""> </option>
              <option value="G1F">G1-F</option>
              <option value="G1S">G1-S</option>
              <option value="G2J">G2-J</option>
              <option value="G2A">G2-A</option>
              <option value="G3">G3</option>
              <option value="G4">G4</option>
              <option value="G5">G5</option>
            </select>
        <br> <br>
        <label>Afternoon Class&nbsp;&nbsp; </label>
            <select id = "afternoonclass" name = "afternoonclass">
              <option value=""> </option>
              <option value="7P">7P</option>
              <option value="7H">7H</option>
              <option value="8P">8P</option>
              <option value="8H">8H</option>
              <option value="9">9</option>
              <option value="10">10</option>
              <option value="11">11</option>
            </select>
        <br> <br>
        </form>
    </fieldset>

    <br>
    <div class="buttonAlign">
    <input type="button" value="Cancel" onclick="cancel();" class="button-error pure-button"> &nbsp;
    <input type="button" value="Add" id="button"  class="button-secondary pure-button">
    </div>
    <br>

    </body>
</div > 

<script src="https://ajax.googleapis.com/ajax/libs/jquery/1.11.3/jquery.min.js"></script>

<script>
 $(document).ready(function(){

        $("#button").click(function(){

              var fname=$("#firstname").val();
              var lname=$("#lastname").val();
              var mclass=$("#morningclass").val();
              var aclass=$("#afternoonclass").val();
        if (fname !== "" && lname !== "" && mclass !== "" && aclass !== "") {
              $.ajax({
                  type:"post",
                  url:"add.php",
                  data:"firstname="+fname+"&lastname="+lname+"&morningclass="+mclass+"&afternoonclass="+aclass,
                  success:function(){
                     alert("Entry added");
                     window.location.href = "viewStudent.php";
                  }

              });
            document.getElementById("firstname").value = "";
            document.getElementById("lastname").value = "";
            document.getElementById("morningclass").value = "";
            document.getElementById("afternoonclass").value = "";
        } else {
            alert("You must fill out all the empty information!");
        }

        });     

});

function cancel() {
    window.location.href = "viewStudent.php";
}
</script>

add.php

<?php
include 'connect.php';

$fname=preg_replace('/[^a-z]/', "", strtolower($_POST["firstname"]));
$lname=preg_replace('/[^a-z]/', "", strtolower($_POST["lastname"]));
$mclass=$_POST["morningclass"];
$aclass=$_POST["afternoonclass"];

$sql=("INSERT INTO student(fname,lname,mclass,aclass) values('$fname','$lname','$mclass','$aclass')");

if ($conn->query($sql) === TRUE) {
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

Offtopic, but why do you need to show an error? Some people do have the same firstname and lastname. — Daan, Aug 05 '15 at 13:23
Agreed. Better to use some other identifier that is more likely to be unique , e.g. Student ID, and then add a unique constraint to that column in the table. — Bobby StJacques, Aug 05 '15 at 13:25
Just part of the project I'm doing. One of the requirements is only allowing students with unique first and last names. — SamG, Aug 05 '15 at 13:27
use a stored procedure that first checks for the existence of a user with aforementioned criteria, if none exist add the record and return a success message otherwise return an error of your choosing — Professor Abronsius, Aug 05 '15 at 13:28
just try to do a select on student's name and see if it exists... — Random, Aug 05 '15 at 13:28
@RamRaider It's a hassle to do multiple queries on one page with mySQL-PHP. Which would mean I would need to POST the data to the page with the SELECT, and then POST again (which is also a hassle) to the add.php page to INSERT the data. — SamG, Aug 05 '15 at 13:32

score 0 · Answer 1 · answered Aug 05 '15 at 13:52

Rather than separate calls via php to the select and update statements, a stored procedure will do the donkey work on the server.With this approach you do not need multiple posts to various pages...

basic sql stored procedure
--------------------------
create procedure `spAddUser`(IN `param_forename` VARCHAR(64), IN `param_surname` VARCHAR(64) )
    language sql
    not deterministic
    contains sql
    sql security definer
    comment ''

begin
    declare dbrecords integer default 0;
    declare strsql varchar(10000);
    declare forename varchar(64);
    declare surname varchar(64);
    set @forename=cast(param_forename as char character set utf8);
    set @surname=cast(param_surname as char character set utf8);

    /* Does the user already exist? */

    set @strsql=concat("select distinct count(*) into @dbrecords from `users` where `forename`=@forename and `surname`=@surname;");
        prepare stmt from @strsql;
        execute stmt;
        deallocate prepare stmt;

    if( @dbrecords > 0 ) then
        /* user exists, return statement that tells of problem */
        set @strsql=concat("select 'sorry, that combination of firstname and lastname already exists' as 'result'");
            prepare stmt from @strsql;
            execute stmt;
            deallocate prepare stmt;
    else
        /* all good, add the record */
        set @strsql=concat("insert into `users` set `forename`=@forename, `surname`=@surname;");
            prepare stmt from @strsql;
            execute stmt;
            deallocate prepare stmt;

        set @strsql=concat("select 'Thankyou, your details have been added to the database' as 'result'");
            prepare stmt from @strsql;
            execute stmt;
            deallocate prepare stmt;
    end if;     
end


php
---

$sql="call `spAddUser`('$forename','$surname')";
$db->query( $sql ); //etc

score 0 · Answer 2 · edited May 23 '17 at 11:51

Your code is mostly fine as is (well other than SQL injection vulnerability). All you need to do is to look at the mysql error information that is returned when trying to insert a duplicate to determine whether you encountered a unique constraint violation or whether something else went wrong.

See this other SO question around how to detect unique constraint violations - PHP - detect mysql update/insertion failure due to violated unique constraint

MySQL: Returning an error if insert value is not unique

2 Answers2