SitePoint Sponsor

User Tag List

Results 1 to 19 of 19
  1. #1
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    help debug with last_insert_id()

    Hi,

    After the query i put this to get the id of last insert row

    Code PHP:
    $listing_id = mysql_query("SELECT LAST_INSERT_ID()");			
    echo $listing_id;

    The output is
    Code:
    Resource id #5Resource id #6Resource id #7
    It doesn't look like row numbers. I check database, the last insert has id of 59

  2. #2
    Twitter: @AnthonySterling silver trophy AnthonySterling's Avatar
    Join Date
    Apr 2008
    Location
    North-East, UK.
    Posts
    6,111
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Sure, you still need to process the result set as normal, like so:
    PHP Code:
    <?php
    $sSQL 
    'SELECT LAST_INSERT_ID( ) AS lastInsert;';
    $rRes mysql_query($sSQL);
    $aRow mysql_fetch_assoc($rRes);
    echo 
    'The last inserted ID was ' . (Integer)$aRow['lastInsert'];
    ?>
    @AnthonySterling: I'm a PHP developer, a consultant for oopnorth.com and the organiser of @phpne, a PHP User Group covering the North-East of England.

  3. #3
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What you have there is a result resource, just like running any other query. You will get exactly the same result if you replaced the SQL with any other query. You need to get an associate array / numeric-indexed array out of the result resource before you can use the value returned from the query.

    Alternatively, what is wrong with using the php mysql_insert_id() or mysqli->insert_id functions/properties, to avoid having to query the database again?

  4. #4
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by SilverBulletUK View Post
    PHP Code:
    <?php
    $sSQL 
    'SELECT LAST_INSERT_ID( ) AS lastInsert;';
    $rRes mysql_query($sSQL);
    $aRow mysql_fetch_assoc($rRes);
    echo 
    'The last inserted ID was ' . (Integer)$aRow['lastInsert'];
    ?>
    That echo give me a huge number which contradicts my database complety,
    the biggest id in the table is 60, that echo gives me 356322

  5. #5
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Stormrider View Post
    Alternatively, what is wrong with using the php mysql_insert_id() or mysqli->insert_id functions/properties, to avoid having to query the database again?
    I used that, the result is extraordinary, it give me numbers like, 568884, 698998, 89933422, 325566566. While I don't have that many of id in table

    my code was

    Code PHP:
    $result = mysql_query($query, $connection);
    if ($result) {
        $rowId = mysql_insert_id($connection);
        echo $rowId;
    }

  6. #6
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Looks like there is a problem using both methods. You do have an auto-increment field in your table, yes? Getting the last ID only works for tables with auto-increment columns.

  7. #7
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Stormrider View Post
    Looks like there is a problem using both methods. You do have an auto-increment field in your table, yes? Getting the last ID only works for tables with auto-increment columns.
    Yes id column is auto-increment.

    More details, type int(11), attribute unsigned, Null No.

  8. #8
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Have you got a bit more of the code to post? Including the query itself, down to where you are trying to get the last insert id

  9. #9
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Stormrider View Post
    Have you got a bit more of the code to post? Including the query itself, down to where you are trying to get the last insert id
    Here is the code in full

    Code PHP:
    <?php
    $errors = array();
    $url = URL;
    $username = $_COOKIE['active']['username'];
     
    if(!isset($_COOKIE['active']))
    { //if not logged in redirect to login page
    	$url=URL;
    	$cur_url = basename($_SERVER["SCRIPT_FILENAME"]) . '?' . $_SERVER["QUERY_STRING"];
    	$cur_url = base64_encode($cur_url);
    	header('Location:'.$url.'login.php?ref='.$cur_url);
    	exit();
    }
     
    if(isset($_POST['submit']))
    {
    	$fields = array('jumpmenu', 'jumpmenu2','jumpmenu3' );
    	foreach($fields as $field)
    	{
    		${$field} = mysql_real_escape_string(trim($_POST[$field]));
     
    		$result = mysql_query("INSERT INTO listing (username, catlvl1, catlvl2, catlvl3) 
                                   VALUES('{$username}', '{$jumpmenu}', '{$jumpmenu2}', '{$jumpmenu3}' )");	
     
    		if($result)
    		{
    			$listing_id = mysql_insert_id();
    			echo $listing_id;
    		}
    	}
    }
    ?>

  10. #10
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Try echoing out the query before inserting it, and run it manually in phpMyAdmin, see what you get. I don't like those curly braces everywhere personally, I always break out of the string to put variables into it.

    Finally, you are running the query inside the foreach loop, which is fairly obviously wrong. The query is going to be run for each of the post variables, and for the first 3, not all the values are going to be set. You want to run the query after the foreach loop, not within it.

  11. #11
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Stormrider View Post
    Finally, you are running the query inside the foreach loop, which is fairly obviously wrong. The query is going to be run for each of the post variables, and for the first 3, not all the values are going to be set. You want to run the query after the foreach loop, not within it.
    Its the culprit.

    Thank you so much, Stormrider.

  12. #12
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Another issue here

    I want to setcookie lising_id with the value of $listing_id.

    It seems i can't archive that

    So far the code
    Code PHP:
    setcookie('listing_id', $listing_id, time()+3600, '/');

    It was not bale to registered.

  13. #13
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Are you setting it before any output?

  14. #14
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Not sure

    But I do get header output error, i used ob_start() to stop that. Still no cookie registered

    But when I tried something like this

    Code PHP:
    $a='kimberly';
    setcookie("listing_id", $a, time()+3600, "/");

    The cookie listing_id is register with no need of ob_start(), as no header error

    So I doubt I need to do something with $listing_id before using it in setcookie()

  15. #15
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Well, make sure there is something in it. Where are you checking for the cookie? Are you trying to read it on the same page, because that won't work - it will only become available on the next page.

  16. #16
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Stormrider View Post
    Well, make sure there is something in it. Where are you checking for the cookie? Are you trying to read it on the same page, because that won't work - it will only become available on the next page.
    As we know $listing_id has a value, which is row id, I've checked this by echoing it
    $a has a value of "kimberly".

    setcookie(listing_id, $a, time()+3600,"/"); works

    setcookie(listing_id, $listing_id, time()+3600,"/"); not works and cause header error.

  17. #17
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I managed to get it worked now.

  18. #18
    SitePoint Wizard silver trophybronze trophy Stormrider's Avatar
    Join Date
    Sep 2006
    Location
    Nottingham, UK
    Posts
    3,133
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    What was it?

  19. #19
    SitePoint Guru
    Join Date
    Sep 2008
    Location
    Dubai
    Posts
    971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    After many trying to debug as well as try out other values, the setcookie(listing_id, $lisingting_id....) now works without causing trouble .I think I luckily accidentally deleted the error code during the process.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •