CODEWITHSUNDEEP
phpmysqli
iNex
iNex

Reputation: 60

MAX ID count only works every other time

Problem: I want to get the MAX "SID" from my Database and add one. I handle the input via an Form that i submit through the HTTP Post Method. I get the current MAX "SID" from my database, then i put the value into an HTML input field and add one. For some reason this just works every other time. So the output i get is:

  1. Try = 1
  2. Try = 1
  3. Try = 2
  4. Try = 2

and so on. Would be nice if someone could point me in the right direction.

PHP get MAX(ID):

    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "soccer";

    $conn = mysqli_connect($servername, $username, $password, $dbname);

    if (!$conn) {
        die("Connection failed: " . mysqli_connect_error());
    }
    echo "Connected successfully";

    $sql = "SELECT MAX(SID) FROM spieler";
    $result = mysqli_query($conn, $sql);

    if(mysqli_num_rows($result) > 0)
    {
        while($row = mysqli_fetch_assoc($result))
        {
            $lastID = $row["MAX(SID)"];
        }
    }

    mysqli_close($conn);

PHP insert in database:

        $servername = "localhost";
        $username = "root";
        $password = "";
        $dbname = "soccer";

        $conn = mysqli_connect($servername, $username, $password, $dbname);

        if (!$conn) {
            die("Connection failed: " . mysqli_connect_error());
        }
        echo "Connected successfully";

        ?><br><?php

        $sql = "INSERT INTO spieler VALUES ('$sid', '$name', '$verein', 
        '$position', '$einsaetze', '$startelf', '$tore',
        '$torschuesse', '$eigentore', '$vorlagen', '$elfmeter', 
        '$verwandelt', '$gegentore', '$gelb',
        '$rot', '$fouls', '$zweikampf', '$pass', '$note')";

        if(mysqli_query($conn, $sql)){
            echo "Success";
        }else{
            echo "Failed" . mysqli_error($conn);
        }

        mysqli_close($conn);

HTML & PHP Input Field:

 <tr>
     <td><input id="SID" name="SID" readonly value="<?php echo $lastID += 1; 
     ?>"></td>
 </tr>

Screenshot of the page:

Screenshot The paragraph "Spieler ID:" is where I put the "SID" so that everytime the page loads the next free ID gets automatically loaded into the input field.

Upvotes: 0

Views: 59

Answers (1)

symcbean
symcbean

Reputation: 48357

I want to get the MAX "SID" from my Database and add one

No. You don't. You really, really don't.

This is the XY Problem.

You can do it by running a system wide lock and a autonomous transaction. It would be a bit safer and a lot more efficient to maintain the last assigned value (or the next) as a state variable in a table rather than polling the assigned values. But this still ignores the fact that you going to great efforts to assign rules to what is a surrogate identifier and hence contains no meaningful data. It also massively limits the capacity and poses significant risks of both accidental and deliberate denial of service.

To further compound the error here, MySQL provides a mechanism to avoid all this pain out of the box using auto-increment ids.

While someone might argue that these are not portable, hence there may be merit in pursuing another solution, that clearly does not apply here, where your code has no other abstraction from the underlying DBMS.

Upvotes: 1

Related Questions