Page 1 of 1

mysqli problem

Posted: Wed Sep 05, 2007 11:02 am
by itsmani1
this is returning me 0 i don't know what's the problem with it.

Code: Select all

$user = "call insert_user('".$_POST['userid']."', '".$password."','".$_POST['email']."')";		
		$mysqli->query($user);
		echo $mysqli->insert_id;

Posted: Wed Sep 05, 2007 11:16 am
by Zoxive
Is the query failing?

Posted: Wed Sep 05, 2007 11:43 am
by itsmani1
no
insert is ok, insert works fine and puts data in database but last insert id is returning 0.

Posted: Wed Sep 05, 2007 11:44 am
by hawleyjr
What does your create table look like?

Posted: Wed Sep 05, 2007 12:16 pm
by RobertGonzalez
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 ;

Posted: Wed Sep 12, 2007 11:09 am
by itsmani1
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 ;
Can you please tell me how will get you last insert id from this ?

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
It works fine and inserts id but i don't know how to use it with php

here is my php code:

Code: 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"]);	
	}
and i get :

Fatal error: Call to a member function fetch_array() on a non-object in C:\wamp\www\blf\test1.php on line 14

Posted: Wed Sep 12, 2007 11:27 am
by RobertGonzalez
itsmani1 wrote:
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 ;
Can you please tell me how will get you last insert id from this ?
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: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
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.

The following is something that I use as a tester of mysql stored procs on my local system. Please note that this is not something that should be used in any production system as it is because of the insecure nature of the code. It is quie literally meant to be used by you as a developer on your local development machine in an environment that no other user has access to:

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>