mysqli problem

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

mysqli problem

Post 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;
User avatar
Zoxive
Forum Regular
Posts: 974
Joined: Fri Apr 01, 2005 4:37 pm
Location: Bay City, Michigan

Post by Zoxive »

Is the query failing?
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post by itsmani1 »

no
insert is ok, insert works fine and puts data in database but last insert id is returning 0.
User avatar
hawleyjr
BeerMod
Posts: 2170
Joined: Tue Jan 13, 2004 4:58 pm
Location: Jax FL & Spokane WA USA

Post by hawleyjr »

What does your create table look like?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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 ;
User avatar
itsmani1
Forum Regular
Posts: 791
Joined: Mon Sep 29, 2003 2:26 am
Location: Islamabad Pakistan
Contact:

Post 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
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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>
Post Reply