Increase correlative with an insert

Question

Increase correlative with an insert

Navigation

#1 by (1 votes)

0

I have a table with this structure:

CREATE TABLE IF NOT EXISTS 'prueba_insert' (
  'id' int(11) NOT NULL,
  'asiento' int(11) NOT NULL,
  'correlativo' int(11) NOT NULL,
  'glosa' varchar(30) NOT NULL,
  PRIMARY KEY ('id')
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

I want to insert a record that increases the values of id, seat and correlative. I have this test code.

<?php
require ('includes/config.php');
$BD = new ConexionDB();    
$sql = "SET @id:= (SELECT max(id) from prueba_insert);
SET @asiento:= 8;
SET @corr:= (SELECT max(correlativo) from prueba_insert);
INSERT INTO prueba_insert (id, asiento, correlativo, glosa)
values (@id:=@id+1, @asiento:=@asiento+1, @corr:=@corr+1, 'GLOSA PARA ASIENTO')";
$sth = $BD->prepare($sql);
$sth->execute();
?>

FINAL NOTE: The SET works within the sentence. All I had to do was remove the "ace" of each increment. END.

mysql php

asked by Piropeator 22.02.2017 в 16:56

source

1 answer

How do I go from a DecimalFormat (String) to Double? How to apply the same action to two different elements by jquery? [.Click]

score 1 · Accepted Answer

In the second insert you are trying to read the fields id and corr of a table that is not between the FROM clauses. In fact, I do not even know that you can assign the value of the variable in this way even if tabla were inside the FROM.

What you can try is to declare the function using SET before insert:

$sql = "
    SET @id = SELECT max(id) from tabla;
    SET @corr = SELECT max(corr) from tabla;
    INSERT INTO tabla (id, libro, asiento, correlativo, dato) 
    SELECT @id:=@id+1 as id, $libro, $valorAsto, @corr:=@corr+1 as corr, dato
    FROM tabla2;
";

$sth = $BD->prepare($sql);
$sth->execute();