-1

I want to show last inserted data from MySQL row. But if I use mysql_insert_id() it not worked. But if I use without id='$id that means $sql="SELECT * FROM $table WHERE creator='$creator'"; it worked. But not select in last row. How can I show Last inserted data?

<?php @session_start(); ?>
<?php include "header.php"?>
<?php include "db.php"?>
<?php
                        $table="signup";
                        $creator=$_SESSION["id"];
                        $id = mysql_insert_id();
                        $sql="SELECT * FROM $table WHERE id='$id' AND creator='$creator'";                      
                        $result=mysql_query($sql);
                        $row = mysql_fetch_array($result);

?>


   <p>Name: <?php echo $row['name']; ?></p>
   <p>Email: <?php echo $row['email']; ?></p>
   <p>ID: <h2><span class="star "><?php echo $row['id']; ?></span></h2></p>
S M Nahidur Rahman
  • 39
  • 7

2 Answers2

3

mysql_insert_id is calculated on a per-connection basis.

The PHP manual doesn't bother to state this, but you're indirectly using the MySQL API and the MySQL documentation for mysql_insert_id is very clear:

The value of mysql_insert_id() is affected only by statements issued within the current client connection. It is not affected by statements issued by other clients.

This feature prevents race conditions where you INSERT in one script instance while someone else INSERTs at the same time in another, and you wouldn't know what you were getting from last_insert_id. (reference)

However, that also means that you cannot just run it in a script without having executed an INSERT at all. It is actually not possible to use this technique to get the ID of the last inserted row when it wasn't you who inserted that row. From PHP and MySQL's point of view, you are a different person to the person who ran the script last time!

The best you can do in this instance, so far as I can tell, is to perform a SELECT to find the highest ID in the extant data, and hope that this is what you wanted. Alternatively, in the code that performs the INSERT, store the result of mysql_insert_id() in a session variable and use that here.

Without having been told what you're actually trying to do, I can't suggest anything further.

Lightness Races in Orbit
  • 378,754
  • 76
  • 643
  • 1,055
-2

correct usage of mysql_insert_id (using details from the question) is:

      $sql="INSERT INTO ".$table." (creator)  VALUES ('$creator')"; 
    mysql_query($sql);
    $id = mysql_insert_id();

$id returns the value of the last insert statement.

Martin
  • 22,212
  • 11
  • 70
  • 132
  • 1
    He doesn't want to `INSERT` anything. – Lightness Races in Orbit Jan 24 '15 at 17:52
  • 1
    I was showing the correct usage of the `mysql_insert_id` , as that is the original question. weather he wants to insert or not is fairly unimportant – Martin Jan 24 '15 at 17:55
  • 1
    It may not be important to _you_, but I'm guessing it's fairly important to _him_. "Here, use some code that does something completely different than what you want to do" is not helpful. The question is _not_ "how to do correct use of `mysql_insert_id`?"; it is "how do I do want I want to do?" Thanks. – Lightness Races in Orbit Jan 24 '15 at 17:56
  • 1
    with all due respect, that's for him to decide. The question was about this specific function, it was not about how his SELECT statement was not returning a mysql_insert_id value. He was using the function completely out of its intended context so I wrote an example to should what context the function _would_ work in. He can learn from that and adapt his code accordingly. – Martin Jan 24 '15 at 17:59
  • ​​​​​​​​: That's absurd. What do you suggest he inserts into the database, then? Magic fairy dust? _His application does not do that!_ I don't know how to make it any clearer. Oh well. – Lightness Races in Orbit Jan 24 '15 at 18:01
  • 2
    Welcome to the 90's. I hope you enjoy your SQL injections. – milleniumbug Jan 24 '15 at 18:03
  • wow, easy there Lightness - I'm not suggesting he inserts anything into the database, I am showing in a few lines example - using details he's already posted - what `mysql_insert_id` is _intended_ to do, he can then take that knowledge and apply it to his script however he wants to. – Martin Jan 24 '15 at 18:08
  • @Martin: ... which is completely useless, because he doesn't want to insert anything within this particular script. So showing him how to use `mysql_insert_id` with an `INSERT` is not helpful to even the slightest degree. It's literally the opposite of the correct answer. What he needs is an _alternative_ to `mysql_insert_id`! And an explanation as to why his code currently does not work. – Lightness Races in Orbit Jan 24 '15 at 18:09
  • his question was **mysql_insert_id() is not working** and so I showed how this function is _intended_ to work, using code that can be directly related to from the question, I did not go into his specific situation, because if anyone else reads this question with similar issues as to the question title they will not be in this specific situation, but searching Google or SO for details on `mysql_insert_id`. There are two subjects, of answering a question and fixing an OPs code within, and these two subjects do not always totally correlate. – Martin Jan 24 '15 at 18:15
  • You didn't fix the OP's code, _and_ you did not answer the question! Other than that, yeah great. If your intent was to just point out that an `INSERT` must be previously performed within the same script, and not to go any further than that, then that is fine but you didn't do that: just posting an unrelated code example without _explaining_ the problem is insufficient. But it doesn't matter because I did it in my answer. – Lightness Races in Orbit Jan 24 '15 at 18:22
  • BTW if you're holding this code up as an example of "how this function is _intended_ to work", then you failed also because there is no error checking in your example. – Lightness Races in Orbit Jan 24 '15 at 18:23