How do I automatically assign the ID column of a row

Question

I need to automatically set the ID column of a row in the database and I would like some help in figuring out how to do that, because at the moment I have to assign an ID through an input field.

PHP

<?php
    //include db configuration file
    include 'connection.php';
    function user_joined($user_id, $user_name, $user_age, $user_end){
        $user_id = mysql_real_escape_string(htmlentities($user_id));
        $q = "INSERT INTO evenement (id, title, start, end) VALUES
              ('" . $user_id . "', '" . $user_name . "', '" . $user_age . "', '" . $user_end . "')";
        mysql_query($q);
        echo $user_id;
    }

    if(isset($_POST['user_id'], $_POST['user_name'], $_POST['user_age'], $_POST['user_end'], $_POST['action'])){
        $user_id = $_POST['user_id'];
        $user_name = $_POST['user_name'];
        $user_age = $_POST['user_age'];
        $user_end = $_POST['user_end'];
        $action = $_POST['action'];
        if ($action == 'joined'){
            user_joined($user_id, $user_name, $user_age, $user_end);
        }
    }
?>

HTML form

<form class="form-inline">
    <div id="d1" title="Maak afspraak aan" style="display: none">
        <div class="control-group">
            <label class="control-label">Gebruikers id:</label>
            <div class="controls">
                <input type="text" name="userid" placeholder="Gebruikers ID" id="id" />
            </div>
        </div>
        <div class="control-group">
            <label class="control-label">Title:</label>
                <div class="controls">
                    <input type="text" name="username" placeholder="Titel" id="name" />
                </div>
            </div>
            <div class="control-group">
                <label class="control-label">Starttijd:</label>
                <div class="controls">
                    <input type="text" name="userstart" placeholder="Starttijd" id="start" />
                </div>
            </div>
            <div class="control-group">
                <label class="control-label">Eindtijd:</label>
                <div class="controls">
                    <input type="text" name="userend" placeholder="Eindtijd" id="end" />
                </div>
            </div>
        </div>
    </div>
</form>

score 2 · Answer 1 · edited May 23 '17 at 10:26

Use the AUTO_INCREMENT attribute in combination with PRIMARY KEY to set the ID automatically. You can return this ID with SCOPE_IDENTITY(), or if you'd switch to a better PHP SQL option, you could use PDO::lastInsertID to get the latest ID.

I would also like to warn you that you have a pretty serious SQL injection problem, which you could fix by using prepared statements and perhaps an extra preg_replace to strip all unwanted characters from the query and table in general. You should totally go and learn a little bit more about preventing SQL injections. There are great topics here that are dedicated to the subject and I made a list of these articles to you:

I would also like to refer you to switch over to MySQLi or PHP PDO, because you are currently using the deprecated MySQL database extension.

How do I automatically assign the ID column of a row

1 Answers1