How to check for double entries in my sql table?

Question

i'm a newbie in programming and still learning. I've created a database in mySQL to store a fictional restaurant's reservations. I got my site's form up and running, when you enter your info (email/seats needed/date/hour) it registers and is entered into the database successfully. But now i'm trying to check and see if i have double entries in my database to then send a message to my customer saying "sorry, that table is already booked, please choose another time or date"

just to make it simple, all i want to check for now is if the "seating table" in question is already in the database more than 2 times, if a third attempt is made i want to error message to appear, but i can't seem to figure out how to check my sql for that, here is the code i have for now... the column i'm checking is the seating table "5-8" people, my selections are 1-2, 3-4 and 5-8. 5-8 is what is written in the sql table column as "text"

require_once("config.php.inc");

$idConnexion = new mysqli($serveur, $utilisateur, $motDePasse, $bd, $port);

if ($idConnexion->connect_error) {
    die('Erreur de connexion (' . $idConnexion->connect_errno . ') ' .$idConnexion->connect_error);
}

$requete = $idConnexion->query("SELECT * FROM tables") or die("La requete a echouee!");
$tableau = $requete->fetch_array();
$heure_res = $tableau["heure"];
$date_res = $tableau["date_res"];
$nombre_res = $tableau["nombre_personnes"];

if (($tableau = $requete->fetch_array()) !=FALSE) {
    $nombre_table = $nombre_res == "5-8";
    if (count($nombre_table) >= 2) {
        echo "Il n'y a plus de place, choisissez une autre heure ou date.";
    } else {
        $resultats = $idConnexion->query("INSERT INTO `tables` SET email = '$_GET[email]', nombre_personnes ='$_GET[nombre_personne]', date_res = '$_GET[date_res]', heure = '$_GET[heure_preference]'") or die("La requête a échouée! : (" . $idConnexion->errno . ") " . $idConnexion->error);
        $message = "L'ajout de la réservation a été fait avec succès!";
        include("succes_interface.php");
    }
}

once i fetch the array, i'm trying to see if the "5-8" is in the database more than twice, to return an error, and if it is less than 2, then PHP can continue and add the reservation into the database.

I really hope i'm asking the right questions, I have searched StackOverflow for an answer, but didn't have any luck.

Thank you in advance.

Your code is vulnerable to [**SQL injection**](https://en.wikipedia.org/wiki/SQL_injection) attacks. You should use prepared statements with bound parameters, via either [**mysqli**](https://secure.php.net/manual/en/mysqli.prepare.php) or [**PDO**](https://secure.php.net/manual/en/pdo.prepared-statements.php). [**This post**](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has some good examples. — Alex Howansky, May 30 '19 at 15:32
**Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, May 30 '19 at 15:33
Looking for duplicates should be done in the SQL itself. There are lots of examples of that on SO — dyz, May 30 '19 at 15:53

score 0 · Answer 1 · answered May 30 '19 at 16:48

First, your code only seems to be looking at the first row returned by the SELECT statement. Second, the following statement:

$nombre_table = $nombre_res == "5-8";

assigns to $nombre_table a value of True or False. You cannot apply the PHP count function to such a value, so the next statement will throw a warning. In any case, you will not be counting the number of times "5-8" appears in column nombre_personnes. To do that, a query such as the following could be used:

$requete = $idConnexion->query("SELECT count(*) AS cnt from tables WHERE nombre_personnes = '5-8'");

Of course, between the time you read the count and insert a new row, is it possible that another process has modified the database such that the count you read is no longer current and you still end up with too many occurrences of '5-8'? You may need to first lock the table against writes before proceeding.

score -2 · Answer 2 · answered May 30 '19 at 15:40

-2

you must put an ID (ID int NOT NULL AUTO_INCREMENT,) in your table so your tables have an id which will guarantee that every table are unique. When somebody try to make a reservation you will have the id of the table and compare it with the reservation so you can't have two same table.

answered May 30 '19 at 15:40

Andrei Tornea

108
10

While having an ID column is almost always a good idea, especially if set `PRIMARY KEY`, this is not something that prevents duplicate reservations. Only a `UNIQUE` constraint can do that. – tadman May 30 '19 at 16:37
@tadman, BTW, a UNIQUE constraint will not help here either. As I read the problem, it is O.K. to have two reservations with "5-8" but not a third. – Booboo May 30 '19 at 16:53
@RonaldAaronson Good point, but you'd need some kind of index to check counts efficiently. You could do a UNIQUE index if it became `5-8-1` and `5-8-2` though, for seat 1/2 respectively. – tadman May 30 '19 at 18:20
@tadman You can still index the column, it just doesn't shouldn't be a UNIQUE index. And nothing in the specification called for adding '-1' and '-2' suffices' for forcing uniqueness. Doing that would also make the query unnecessarily complicated and less efficient because now you are comparing '5-8' with a prefix of the column value. It really doesn't make much sense to do that, does it? – Booboo May 30 '19 at 18:55

How to check for double entries in my sql table?

2 Answers2