PHP Create Procedure

Question

I try to create a stored procedure through php mysqli. I create another stored procedures and I didn´t have problems. This code:

$link = new mysqli("localhost", "root", "", "Question");
$link -> multi_query("DROP PROCEDURE IF EXISTS FacturaTerminarFactura;
    CREATE DEFINER=`root`@`%` PROCEDURE `FacturaTerminarFactura`(IN 
    id_factura INT(40), Total_factura DOUBLE, Total_IVA DOUBLE)
    BEGIN
    UPDATE `tb_factura`
        SET
        `tb_factura`.`intId_factura_resolucion_dian` = (SELECT
            MAX(`tb_resolucion_dian`.`intFactura_Actual`) FROM `tb_resolucion_dian`
            WHERE `tb_resolucion_dian`.`intEstado` = 1 LIMIT 1),
            `tb_factura`.`dblTotal_factura` = Total_factura,
            `tb_factura`.`dblIva` = Total_IVA
        WHERE `tb_factura`.`intid_factura` = id_factura;
   UPDATE `tb_resolucion_dian`
        SET `tb_resolucion_dian`.`intFactura_Actual` = 
            tb_resolucion_dian`.`intFactura_Actual` + 1
        WHERE `tb_resolucion_dian`.`intEstado` = 1;
   SELECT `tb_factura`.`intId_factura_resolucion_dian` AS ID
       FROM `tb_factura`
       WHERE `tb_factura`.`intid_factura` = id_factura;
END;");
$log  = "";
if ($mysqli -> warning_count) {
     if ($result = $link -> query("SHOW WARNINGS;")) {
         $row = $result -> fetch_row();
         $log .= $row[0]."-".$row[1]."-".$row[2]."\n";
         $result -> close();
     }
}

This is the error

"You have an error in your SQL syntax; check the manual that corresponds to your MySQL
server version for the right syntax to use near '`.`intFactura_Actual` 1 WHERE 
`tb_resolucion_dian`.`intEstado` = 1; ' at line 14

I see that the '+' is the problem, but I don't know how I can resolve.

Thanks and excuse me for my English.

What does "resolucion_dian" translate to? What is the data type? — J-Dizzle
– J-Dizzle, Commented Aug 27, 2014 at 15:49
tb_resolucion_dian is a table, but the problem is the '+' is cleared when I run the query — user3267098
– user3267098, Commented Aug 27, 2014 at 15:54

J-Dizzle · Accepted Answer · 2014-08-27 16:05:33Z

1

You have a typo Here:

tb_resolucion_dian`.`intFactura_Actual` + 1

You're missing the backtick before tb_resolucion_dian

Also, according to mysql docs for UPDATE you don't need to name the table name in the SET clause. The example given was UPDATE t1 SET col1 = col1 + 1; where you have UPDATE t1 SET t1.col1 = t1.col1 + 1, which might also cause issues.

An alternative to UPDATE is INSERT INTO ... SELECT.

edited Aug 27, 2014 at 16:05

answered Aug 27, 2014 at 15:57

J-Dizzle

3,1996 gold badges34 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

J-Dizzle Over a year ago

Are you sure that intFactura_Actual is an integer type in the datanase?

user3267098 Over a year ago

Yes, intFactura_Actual is an integer type

user3267098 Over a year ago

The problem was in PHP beacause, it was clearing the '+' in the sending to the control, I replaced '+' with '+' and problem resolved. Thanks

PHPmaker · Accepted Answer · 2014-08-27 16:04:26Z

0

simple fix try this

`intFactura_Actual` + 1

TO

`intFactura_Actual` = `intFactura_Actual` + 1

Thanks for comments I see i did not read the code right (Looks so weird here) there is a spelling mistake in the code just like the other guy said.

edited Aug 27, 2014 at 16:04

answered Aug 27, 2014 at 15:53

PHPmaker

16313 bronze badges

1 Comment

J-Dizzle Over a year ago

If you look at the full context, he does do this.UPDATE tb_resolucion_dian SET tb_resolucion_dian.intFactura_Actual = tb_resolucion_dian.intFactura_Actual` + 1

Collectives™ on Stack Overflow

PHP Create Procedure

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related