ID goes wrong when info double MYSQL

Question

I've made a database, so people can join a competition (it's a schoolproject), and I've added something so you can't have twice the same emailadress, but when there is a duplicate input, the ID will still count. So for example someone wants to join with the email "helloworld@gmail.com" and he gets the ID 2 and then he tries again, he will get an error that that emailadress is already taken, but the next person who wants to sign in, will get the ID 4 instead of 3. If you are able to make it so the database saves an IP from the one who signs is, I'm fine with it too, cause thats what I actually am trying to make. But as long as you solve the problem, I'm happy. Thank you! (I dunno why but the code always messes up...

<?php
// Only process the form if $_POST isn't empty
if ( ! empty( $_POST ) ) {

// Connect to MySQL
$mysqli = new mysqli( '***', '***', '***', '***' );

// Check our connection
if ( $mysqli->connect_error ) {
die( 'Kan niet verbinden met database. Probeer het later opnieuw. ' . 
$mysqli->connect_errno . ': ' . $mysqli->connect_error );
}

// Insert our data

$sql = "INSERT INTO inschrijven ( naam, email, aantal ) VALUES ( '{$mysqli->real_escape_string($_POST['naam'])}', '{$mysqli->real_escape_string($_POST['email'])}', '{$mysqli->real_escape_string($_POST['aantal'])}' )";
     // validate agree unless you want to add 'checked' to one of the values

     $insert = $mysqli->query($sql);


// Print response from MySQL
if ( $insert ) {
echo "U bent succesvol ingeschreven! U heeft bootnummer: {$mysqli->insert_id}. Op de naam: . U krijgt hiervan nog een bevesteging op uw mail, uw bootnummer kan nog veranderen. Hopelijk zien we u op de BotenBouwDag 2016!";
} else {
die("Error: {$mysqli->errno} : {$mysqli->error}");
}


$mysqli->close();
}
?>


<HTML>
<head>
<title>
 Welkom op de officiëlea site van de BotenBouwDag 2016
</title>
</head>
<body>

<form method="post" action="">
<input name="naam" id="naam" type="text">
<input name="email" id="email" type="email">
<input name="aantal" id="aantal" type="number">
<input type="radio" name="eten"> Ja 
<input type="radio" name="eten"> Nee
<input type="submit" id="submit" value="Verstuur Informatie">
</form>

Hey, you might want to hide the `db connection credentials` ;) — Peter, Feb 12 '16 at 16:07
Why does it matter if there are gaps within the id sequence? Users do not really have to know their numeric id anyway. — Shadow, Feb 12 '16 at 16:10
@Shadow Every contester gets their own number and we want to use the ID as the number for them, plus we need to know howmany groups of ppl are coming. We need to reserve food and stuff — E S, Feb 12 '16 at 16:12
Don't worry about the ids. Just count the number of people in the database. — Strawberry, Feb 12 '16 at 16:23
@EsliStavenga I'm sorry, but this is bad design! The value of the user id should not represent any more, than to identify the particular user. If you want to know how many users there are, just count them, as Strawberry has suggested. Just ask yourself: what happens, if somebody decides to delete himself from your system? Are you going to delete that user record? If yes, then are you going to change every user id greater than the user id of the deleted person? — Shadow, Feb 12 '16 at 16:32
And while it's great that you're using a modern API, like mysqli, now see about prepared statements. — Strawberry, Feb 12 '16 at 16:54

score -1 · Answer 1 · edited May 23 '17 at 12:31

-1

As taken from this Q&A, InnoDB does increment due to their rollback strategy. So they allow gaps between Id's. I see two options now:

Give everyone the generated id, don't care for the gaps and make a query asking how many entries there are in the db SELECT COUNT(*) FROM table_name;
Ask the db if the mail adress is already taken before inserting. Kind of like in this Q&A.

Edit: Take a look at this to see how the get IP adress. Since most ppl will sign up at school for this, you might find your schools ip adress multiple times ;)
Hope it helps!

edited May 23 '17 at 12:31

Community

1
1

answered Feb 12 '16 at 16:19

Peter

1,844
2
31
55

We want people from all over the country to join. And we want that adults sign up for this so not the children at school – E S Feb 12 '16 at 16:30
No. The OP should just make sure that the address is unique, and not worry about the ids. – Strawberry Feb 12 '16 at 16:55

score -1 · Answer 2 · edited May 23 '17 at 11:45

-1

It's not considered good practice to pass an internal, auto-generated number to the user.

"Auto-increment surrogate keys are useful for internal application use. Never pass those on to the user." See here:- ID Best Practices for Databases

If you really want to have full control of the user numbers, there are a few options.

Add a new field to the table. Before each insertion of a new user, query the database, sorted, descending to find the current, highest number. Insert the new user with that number +1.

Or you could use a separate table to maintain "next id to assign" and save that in your "inschrijven" table. You could even start it at, say 10,000, so all subscribers get a number with the same number of digits. All in the same transaction, read the new table, increment the number and UPDATE and use that number for the new subscriber. That would be the number you give them. The ISN (internal sequence number) would still be the key.

In both cases there will be gaps if you ever delete a user.

edited May 23 '17 at 11:45

Community

1
1

answered Feb 12 '16 at 16:36

BryanT

412
3
12

This sounds like an accident waiting to happen - while trying to solve a problem that doesn't exist. – Strawberry Feb 12 '16 at 16:55
I've seen it done. And how else would you have control over the assigned numbers? In a real commercial application, you may want carefully formatted numbers, with different ranges. – BryanT Feb 12 '16 at 17:14
Also - I wasn't suggesting that this be used for counting the subscribers. – BryanT Feb 12 '16 at 17:17
I wasn't suggesting that. This is from an accepted StackOVerflow answer. "Auto-increment surrogate keys are useful for internal application use. Never pass those on to the user." http://stackoverflow.com/questions/4350369/id-best-practices-for-databases – BryanT Feb 12 '16 at 17:28
@Strawberry - what to do? I replied to a comment of yours that seems to have been deleted? – BryanT Feb 12 '16 at 19:56

ID goes wrong when info double MYSQL

2 Answers2