Code: Select all
$user = "call insert_user('".$_POST['userid']."', '".$password."','".$_POST['email']."')";
$mysqli->query($user);
echo $mysqli->insert_id;Moderator: General Moderators
Code: Select all
$user = "call insert_user('".$_POST['userid']."', '".$password."','".$_POST['email']."')";
$mysqli->query($user);
echo $mysqli->insert_id;Code: Select all
DELIMITER $$
DROP PROCEDURE IF EXISTS `intranet-dev`.`add_intranet_user`$$
CREATE PROCEDURE `add_intranet_user`(IN userName VARCHAR(20), IN firstName VARCHAR(40), IN lastName VARCHAR(60))
BEGIN
DECLARE current_insert_id INT;
INSERT INTO `intranet-dev`.`intranet_users`
(`user_name`, `user_first_name`, `user_last_name`)
VALUES
(userName, firstName, lastName);
IF ROW_COUNT() > 0 THEN
SELECT LAST_INSERT_ID() INTO current_insert_id;
ELSE
SELECT 0 INTO current_insert_id;
END IF;
SELECT current_insert_id AS new_user_id;
END$$
DELIMITER ;Can you please tell me how will get you last insert id from this ?Everah wrote:Insert ID will always return 0 in a stored proc in mysql. It is a known issue and has no fix. The best thing to do if you need to the last instert id is to add a select statement into your proc that returns the last_insert_id(). Something like (this is one of my test procs):
Code: Select all
DELIMITER $$ DROP PROCEDURE IF EXISTS `intranet-dev`.`add_intranet_user`$$ CREATE PROCEDURE `add_intranet_user`(IN userName VARCHAR(20), IN firstName VARCHAR(40), IN lastName VARCHAR(60)) BEGIN DECLARE current_insert_id INT; INSERT INTO `intranet-dev`.`intranet_users` (`user_name`, `user_first_name`, `user_last_name`) VALUES (userName, firstName, lastName); IF ROW_COUNT() > 0 THEN SELECT LAST_INSERT_ID() INTO current_insert_id; ELSE SELECT 0 INTO current_insert_id; END IF; SELECT current_insert_id AS new_user_id; END$$ DELIMITER ;
Code: Select all
BEGIN
INSERT INTO country (country.country_name) VALUES ('Heool');
IF ROW_COUNT() > 0 THEN
SELECT LAST_INSERT_ID() INTO current_insert_id;
ELSE
SELECT 0 INTO current_insert_id;
END IF;
SELECT current_insert_id AS new_user_id;
ENDCode: Select all
$mysqli = new mysqli("localhost", "root", "", "cmela");
if (mysqli_connect_errno())
{
printf("Connect failed: %s\n", mysqli_connect_error());
exit();
}
else
{
$myVar = "call new_proc0()";
$result = $mysqli->query($myVar);
$mysqli->commit();
$row = $result->fetch_array(MYSQLI_NUM);
printf ("%s (%s)\n", $row["current_insert_id"]);
}
The IF ROW_COUNT() part is how the procedure identifies if there were any rows affected by the insert. If there are affected rows, then it selects the last insert id into the declared variable current_insert_id. Otherwise it it inserts 0 into the current user id. At the end of the proc, it selects the current_user_id (either a 0 or the LAST_INSERT_ID() as the field named new_user_id. So essentially this is a select result eventhough you are running an insert query.itsmani1 wrote:Can you please tell me how will get you last insert id from this ?Everah wrote:Insert ID will always return 0 in a stored proc in mysql. It is a known issue and has no fix. The best thing to do if you need to the last instert id is to add a select statement into your proc that returns the last_insert_id(). Something like (this is one of my test procs):
Code: Select all
DELIMITER $$ DROP PROCEDURE IF EXISTS `intranet-dev`.`add_intranet_user`$$ CREATE PROCEDURE `add_intranet_user`(IN userName VARCHAR(20), IN firstName VARCHAR(40), IN lastName VARCHAR(60)) BEGIN DECLARE current_insert_id INT; INSERT INTO `intranet-dev`.`intranet_users` (`user_name`, `user_first_name`, `user_last_name`) VALUES (userName, firstName, lastName); IF ROW_COUNT() > 0 THEN SELECT LAST_INSERT_ID() INTO current_insert_id; ELSE SELECT 0 INTO current_insert_id; END IF; SELECT current_insert_id AS new_user_id; END$$ DELIMITER ;
You may want to declare the variable current_insert_id somewhere in there. Also, you cannot execute a stored procedure in mysql using query (or mysqli_query) you have to use one of the multi_query function.itsmani1 wrote:here is my proc:Code: Select all
BEGIN INSERT INTO country (country.country_name) VALUES ('Heool'); IF ROW_COUNT() > 0 THEN SELECT LAST_INSERT_ID() INTO current_insert_id; ELSE SELECT 0 INTO current_insert_id; END IF; SELECT current_insert_id AS new_user_id; END
Code: Select all
<html>
<head><title>Procedure Tester - MySQL</title></head>
<body>
<?php
$sql = '';
$show_results = false;
if (isset($_POST['form_submitted']))
{
// The form was submitted
$sql = $_POST['query'];
echo '<p>The query you entered entered was <strong>' . $sql . '</strong>.</p>';
$mysql = new mysqli('YOURMYSQLHOSTNAME', 'YOURUSERNAME', 'YOURUSERPASSWORD', 'YOURUSERDATABASE');
if (mysqli_connect_errno())
{
die(printf('MySQL Server connection failed: %s', mysqli_connect_error()));
}
echo '<pre>'; var_dump($mysql); echo '</pre>';
// Check our query results
if ($mysql->multi_query($sql))
{
$show_results = true;
$rs = array();
do {
// Lets work with the first result set
if ($result = $mysql->use_result())
{
// Loop the first result set, reading it into an array
while ($row = $result->fetch_array(MYSQLI_ASSOC))
{
$rs[] = $row;
}
// Close the result set
$result->close();
}
} while ($mysql->next_result());
}
else
{
echo '<p>There were problems with your query [' . $sql . ']:<br /><strong>Error Code ' . $mysql->errno . ' :: Error Message ' . $mysql->error . '</strong></p>';
}
$mysql->close();
}
echo '<form id="proc_tester" action="' . basename($_SERVER['SCRIPT_FILENAME']) . '" method="post">
<p>Enter your procedure:</p>
<p><input type="text" name="query" size="175" maxlength="255" value="' . $sql . '" /></p>
<p><input type="hidden" name="form_submitted" value="true" /><input type="submit" name="submit" value="Submit query" /></p>
</form>';
if ($show_results) {
echo '<pre>' . print_r($rs, true) . '</pre>';
}
?>
</body>
</html>