0

I am trying to insert data into my table such the id field is one more than my previous max id. For example, if I had 21 users registered and another one was added, then that users' id would be 21 (note my ids start at 0). I tried this: 

mysqli_query($con,"INSERT INTO logins (ID,UserName)
                VALUES ((SELECT COUNT(ID) FROM logins),'$username')");

This is the error message: #1093 - You can't specify target table 'logins' for update in FROM clause

I also tried this which works:

$result=mysqli_query($con,"SELECT ID FROM logins WHERE ID=(SELECT MAX(ID) FROM logins)");
    while ($db_field = mysqli_fetch_assoc($result)) 
    {
        $id=$db_field['ID']+1;
        mysqli_query($con,"INSERT INTO logins (ID, UserName)
              VALUES ('$id','$username'");
        break;
    }

But I am looking for a way to do this with one command. Thanks in advance.

TAAPSogeking
  • 350
  • 4
  • 14
  • **By building SQL statements with outside variables, you are leaving yourself wide open to SQL injection attacks.** Also, any input data with single quotes in it, like a name of "O'Malley", will blow up your SQL query. Please learn about using parametrized queries, preferably with the PDO module, to protect your web app. http://bobby-tables.com/php has examples to get you started, and [this question](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) has many examples in detail. – Andy Lester Oct 06 '13 at 17:38
  • @AndyLester The variables are do not contain any characters that would allow an SQL injection. The username is checked before it is uploaded and the id is made into an integer. I appreciate the concern, but that is not related to my problem – TAAPSogeking Oct 06 '13 at 17:44
  • "checked before it is uploaded"? You do some JavaScript validation? That won't stop an attacker from making direct GET/POSTs without a browser. – Andy Lester Oct 07 '13 at 02:27

1 Answers1

3

I suggest you make id the primary key of the table and enable AUTO_INCREMENT. You can then remove the id column from your query, and increasing id's will be added automatically.

Patrick Kostjens
  • 5,065
  • 6
  • 29
  • 46