Error with Primary Key(auto-increment int) Calling stored procedure in php

how to auto generate id in php mysql
mysql auto increment primary key
how to get last inserted data in mysql using php
php mysqli last insert id
mysql select last insert id from table
how to get last inserted id in mysql
last_insert_id php
select last_insert_id()

// STORED PROCEDURE NAME: insertName, _id is an auto-increment

try { 

        $sql = "CALL insertName(:_id, :_name)";
        $res = $pdo->prepare($sql);
        $res -> bindValue(":_id","NULL");
        $res -> bindValue(":_name",$name);
        $res->execute(); 
        $res->setFetchMode(PDO::FETCH_ASSOC);
} catch( PDOException $e ) {

        die( "ERROR ..." .$e->getMessage());
}

// if i try inserting directly a number all goes OK

try {  
        $sql = "CALL insertName(:_id, :_name)";

        $res = $pdo->prepare($sql);
        $res -> bindValue(":_id",1);
        $res -> bindValue(":_name",$name);
        $res->execute(); 
        $res->setFetchMode(PDO::FETCH_ASSOC);
} catch( PDOException $e ) {

        die( "ERROR ..." .$e->getMessage());
}

// Even if i try this without using the stored procedure goes OK

try {  
        $sql = 'INSERT INTO (id,name) VALUES (NULL,"'.$name.'" )';
        $res = $pdo->prepare($sql);
        $res->execute(); 
} catch( PDOException $e ) {

        die( "ERROR ..." .$e->getMessage());
}

insert the name value only, since the id is auto increment, it will take values by itself

mysql_insert_id - Manual, Edit Report a Bug The ID generated for an AUTO_INCREMENT column by the previous query on success, native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). Because mysql_insert_id() acts on the last performed query, be sure to call insert a datarow, primary key is auto_increment I get some strange results calling stored procedures from PHP. With following data: DROP TABLE IF EXISTS `countries`; CREATE TABLE `countries` ( `id` int(11) NOT NULL auto_increment, `country` varchar(50) default NULL, `capital` varchar(50) default NULL, PRIMARY KEY (`id`), UNIQUE KEY `land` (`country`)

You should set variable with NULL as string, then pass it to bindValue:

try { 
        $id = "NULL"
        $sql = "CALL insertName(:_id, :_name)";
        $res = $pdo->prepare($sql);
        $res -> bindValue(":_id", $id);

And you have to be sure, that you can save NULL as value for that column!

MySQL AUTO_INCREMENT with Examples, In order to avoid such complexity and to ensure that the primary key is always unique, we can use MySQL's Auto increment feature to generate� This may work well but as you can see the approach is complex and not foolproof. In order to avoid such complexity and to ensure that the primary key is always unique, we can use MySQL's Auto increment feature to generate primary keys. Auto increment is used with the INT data type. The INT data type supports both signed and unsigned values.

//I changed the stored procedure

start transaction;

DELIMITER |

CREATE PROCEDURE insertName(_name)

BEGIN

//operations

INSERT INTO TABLE VALUES (NULL,_name);

END;

|DELIMITER ;

commit;

//and then i call insertName(_name)

how to insert into a table that has an auto increment field , As far as I know the auto increment field can be ignored. So lets assume you have a table looking like this: ID(int) <- increment Calling Stored Procedure Return Error: TABLE Table1 ( Table1Id INT AUTO_INCREMENT NOT NULL, Name VARCHAR(100) NOT NULL, PRIMARY KEY(Table1Id) ); CREATE TABLE

MySQL LAST_INSERT_ID Function By Practical Examples, PHP � Python � Perl � Node.js � JDBC � TryIt CREATE TABLE messages( id INT AUTO_INCREMENT PRIMARY KEY, MySQL issued the following error: C) Using MySQL LAST_INSERT_ID() function in a stored procedure Third, call the stored procedure CreateAccount to create a new account with a phone number:. Description: From PHP and Perl I cannot call two stored procedure in the same connection (I guess it depends on the fact they are returning MULTI_RESULTS, it doesnt happen with normal SELECTs) When I run the second one I get the following error: "Commands out of sync; you can't run this command now".

15889: Stored procedure using insert into select on auto increment , Bug #15889, Stored procedure using insert into select on auto increment doesnt work FROM t1 Where t1 is a table with a primary key that is auto increment. ` p_id` int(10) unsigned NOT NULL auto_increment, `event_id` int(10) 0 rows affected (0.01 sec) mysql> call copy_ev_plan(11,13); Query OK,� Using Identity and Primary Key Constraints. The solution turns out to be using two constraint options provided by SQL Server. The first is PRIMARY KEY, which as the name suggests, forces the specified column to behave as a completely unique index for the table, allowing for rapid searching and queries.

5017: LAST_INSERT_ID called from within Stored Procedures, Bug #5017, LAST_INSERT_ID called from within Stored Procedures CREATE TABLE `temp` ( `ID` int(11) NOT NULL auto_increment, `Name` set @intID = LAST_INSERT_ID(); end \d ; call test('Badger', @A); select @A; PRIMARY KEY (`ID`) )// Query OK, 0 rows affected (0.08 sec) mysql> mysql>� Wondering if there is a shorthand version to insert a new record into a table that has the primary key enabled? (i.e. not having to include the key column in the query) Lets say the key column is called ID, and the other columns are Fname, Lname, and Website

Comments
  • "NULL" is a string and not mysql NULL
  • I tried with NULL without " " and don't give me errors. The problem is that doesn't insert the result into the database. When i use a number at NULL place, it goes to work