Increment field using set/mysql in php

Hello everyone. I using Xampp (php/mysql).
I want to insert a record by incrementing by one the value of the ID field, this field is not autoincremental.
But it does not show or do anything.

$sql = "SET @id = (SELECT max(id) from prueba_insert);
INSERT INTO prueba_insert (id, asiento, correlativo, glosa)
values (@id:=@id+1 as id, 8, 10, 'GLOSA PARA ASIENTO')";
$sth = $BD->prepare($sql);
$sth->execute();

PHP:

What is my mistake?

 

What error are you getting?

 

Try this (maybe):

$sql = "SET @id = (SELECT max(id) from prueba_insert);
INSERT INTO prueba_insert (id, asiento, correlativo, glosa)
values (@id+1, 8, 10, 'GLOSA PARA ASIENTO')";
$sth = $BD->prepare($sql);
$sth->execute();
[code]

But whatever what happens, if you execute this query twice at the exactly same time, you will have two rows with the same ID. You shoud use an autoincrement

Code (markup):

 

Are you using PDO or mysqli?

PDO doesn't allow for multiple queries per querystring.... so you can't SET and INSERT in the same string.

Also not sure why you're prepare/executing when you are passing/binding no variables to it.

Though really, if it's an id, WHY isn't it set up as the master key with auto increment? Is there some other field already set to that?!? This looks like brute forcing something you should be letting SQL do for you.

 

I use PDO. Other way to solve my issue is use:

$sql = "SELECT max(id) as valorId FROM table";
$sth = $BD->prepare($sql);
$sth->execute();

$valor   = $sth->fetch(PDO::FETCH_ASSOC);
$valorId = $valor['valorId'];   // is not a number (why?)

if (is_null($valorId)){  // Do this if there is not records
    $valorId = 0;
}

if (is_string($valorId)){  // Do this if there is records.
    $valorId = intval($valorId) + 1;
}

Code (markup):

There are any short way to do this?

 

1) lose the $sql variable for NOTHING

2) If you aren't passing any variables, you don't need prepare/execute, just used PDO->query

3) PHP is loosely cast.. you shouldn't have to typecast for integer.

4) fetch returns null when there are no more records, or even no records...

5) you shouldn't be pulling MAX since race conditions could make that not me the most recently added. Again, let SQL do it's bloody job with an auto-increment field on creating a new one. IF you need the number AFTER doing an INSERT, use lastInsertId

http://php.net/manual/en/pdo.lastinsertid.php

Take your original query:

$stmt = $db->query('
	INSERT INTO prueba_insert (
		asiento, correlativo, glosa
	) values (
		9, 10, 'GLOSA PARA ASIENTO'
	)
');
echo $db->lastInsertId();

Code (markup):

If 'id' is set to "auto-increment primary key", PDO->lastInsertId will pull the newly created one for you. You should NOT be screwing around trying to calculate that in the PHP in the first place.

 

I don't know why you are not letting the database do this for you, but here's another way of doing this:
This I think will be more reliable than your max(id) query

$sql = "SELECT id FROM table order by id desc limit 1 ";
$sth = $BD->prepare($sql);
$sth->execute();
$valor = $sth->fetch(PDO::FETCH_ASSOC);
if(!is_numeric( $valor )){ $valor=1; }else{ $valor= $valor+1; }

And then insert using the $valor value as id

 

ok. I got it.
One question about query(), is not necesary to use execute()??

$stmt = $db->query( "statements");
$stmt->execute();

Code (markup):

 

PDO->query -- should be used when NO values from variables are being plugged in, but results are expected. Automatically executes so you do NOT PDOStatement->execute

PDO->exec -- should be used when NO values from variables are being plugged in, and NO results are returned by the query. Automatically executes so you do NOT PDOStatement->execute

PDO->prepare -- the one you use when plugging in values from variables, this is the only one you do a separate PDOStatement->execute on. Also be aware that you can PDOStatement->execute the same prepared statement more than once with different values, either passed by array, or using PDOStatement->bindParam.

... and do NOT confuse PDO->exec with PDOStatement->execute -- they are two entirely separate things.