SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 37 of 37
  1. #26
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,182
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    You seem to have a few things going on here, but one is you seem to be stuck on using MySQL's LAST_INSERT_ID in the query sent to PDO

    PDO is designed to work with many database types, not all have LAST_INSERT_ID
    I don't know how and where PDO decides which database is being used and which syntax to use, but my guess is it doesn't work with LAST_INSERT_ID in the query.

    But PDO does have lastInsertId() which I'm guessing will convert it to LAST_INSERT_ID for you (or an equivalent if not a MySQL database).

    I think if you change your use of LAST_INSERT_ID to lastInsertId at least this problem will be resolved

  2. #27
    SitePoint Member
    Join Date
    Dec 2013
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Mittineague

    I altered the select query to
    Code:
    try
    {
      $sql =  'SELECT name, jobtitle, organisation, country, landline, cellphone, email, password FROM tablename WHERE id = lastInsertId()';
    
      $results = $pdo->query($sql);
    }
    catch (PDOException $e)
    but it just threw an exception:
    Error fetching registration information:SQLSTATE[42000]: Syntax error or access violation: 1305 FUNCTION tablename.lastInsertId does not exist
    There are release notes online that suggest that LAST_INSERT_ID() is recognised in the version on MySQL that I have within MAMP, which is v5.5.29:
    It must be the way I am using LAST_INSERT_ID() that is the problem.

  3. #28
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,182
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    You forgot to associate lastInsertId() with your instance of the PDO object.

  4. #29
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,410
    Mentioned
    81 Post(s)
    Tagged
    3 Thread(s)
    I don't know how and where PDO decides which database is being used
    You specify it in the settings.

    Note:
    This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences.
    As you thought Mittineague

    I do not know if you can use lastinsetid() in the code that way; I am always cautiouse and tend to keep things simple and would put it into a variable and use th variable in the sql code.

    In my case I used:
    Code:
    	//check this is the last album_ID
    	$last_album = $PDO->lastInsertId();

  5. #30
    SitePoint Member
    Join Date
    Dec 2013
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You forgot to associate lastInsertId() with your instance of the PDO object.
    I don't know how to do this, Mittineague.

    I do not know if you can use lastinsetid() in the code that way; I am always cautiouse and tend to keep things simple and would put it into a variable and use th variable in the sql code.
    Is there some disagreement between you both about the correct way to use this lastInsertID() function, Rubble? It's difficult for a beginner to fathom the correct method if even the experts seem to disagree.

    Please would either/both of you show me what you consider the correct code, and in context with my MySQL SELECT query code, please? I've tried a few variations of my own but no luck so far.

  6. #31
    SitePoint Mentor silver trophy
    Rubble's Avatar
    Join Date
    Dec 2005
    Location
    Cambridge, England
    Posts
    2,410
    Mentioned
    81 Post(s)
    Tagged
    3 Thread(s)
    There are always one or more ways to do most things and I always try and keep things simple!

    I would try ( although I have only done it once and there was a lot more database action before the lastinsert() ):

    PHP Code:
    try
    {

    $last_inserted $PDO->lastInsertId();

      
    $sql =  'SELECT name, jobtitle, organisation, country, landline, cellphone, email, password FROM tablename WHERE id = $last_inserted';

      
    $results $pdo->query($sql);
    }
    catch (
    PDOException $e
    The database type should be set here:
    PHP Code:
    // Database settings
    $driver 'mysql';
    $host 'localhost';
    $database 'database';
    $user 'username';
    $pass 'password';

    try {
    // Connect to database
    $PDO = new PDO("$driver:host=$host; dbname=$database; charset=utf8"$user$pass); 

  7. #32
    SitePoint Member
    Join Date
    Dec 2013
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks for that, Rubble.
    However it's not working for me at the moment, unless I'm doing something wrong.
    Here's a recap:

    My database connection is an include file called connect.inc.db and is as follows:
    Code:
    <?php
    try
    {
      $pdo = new PDO('mysql:host=localhost;dbname=databasename', 'username','password');
      $pdo ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
      $pdo ->exec('SET NAMES "utf8"');
    }
    catch (PDOException $e)
    {
       $output = 'Unable to connect to the database server: '. $e->getMessage();
       include 'output.html.php';
      exit();
    }
    and I have written up your suggested code like so:
    Code:
     include 'connectdb.inc.php';
    
    try
    {
      $last_inserted = $pdo->lastInsertID();
    
      $sql =  'SELECT name, jobtitle, organisation, country, landline, cellphone, email, password FROM tablename WHERE id = $last_inserted';
    
      $results = $pdo->query($sql);
    }
    catch (PDOException $e)
    {
      $output = 'Error fetching registration information:'.$e->getMessage();
      include 'output.html.php';
      exit();
    The result is as follows:
    Error fetching registration information:SQLSTATE[42S22]: Column not found: 1054 Unknown column '$last_inserted' in 'where clause'

  8. #33
    Programming Team silver trophybronze trophy
    Mittineague's Avatar
    Join Date
    Jul 2005
    Location
    West Springfield, Massachusetts
    Posts
    17,182
    Mentioned
    191 Post(s)
    Tagged
    2 Thread(s)
    Case typo
    $last_inserted = $pdo->lastInsertID();
    should be
    $last_inserted = $pdo->lastInsertId();

  9. #34
    SitePoint Member
    Join Date
    Dec 2013
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    No, it wasn't that. I just tried again with the correct case but it made no difference - same error message.

  10. #35
    SitePoint Guru bronze trophy
    Join Date
    Feb 2013
    Posts
    738
    Mentioned
    7 Post(s)
    Tagged
    0 Thread(s)
    Full quote query so variable can be phrased.
    PHP Code:
    include 'connectdb.inc.php';

    try
    {
      
    $last_inserted $pdo->lastInsertID();

      
    $sql =  "SELECT name, jobtitle, organisation, country, landline, cellphone, email, password FROM tablename WHERE id = $last_inserted";

      
    $results $pdo->query($sql);
    }
    catch (
    PDOException $e)
    {
      
    $output 'Error fetching registration information:'.$e->getMessage();
      include 
    'output.html.php';
      exit(); 

  11. #36
    SitePoint Member
    Join Date
    Dec 2013
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, Drummin

    that's exactly what I have, followed by the while loop that is supposed to create the $regdetails variable as below:
    Code:
      include 'connectdb.inc.php';
    
    try
    {
      $last_inserted = $pdo->lastInsertId();
    
      $sql =  "SELECT name, jobtitle, organisation, country, landline, cellphone, email, password FROM tablename WHERE id = $last_inserted";
    
      $results = $pdo->query($sql);
    }
    catch (PDOException $e)
    {
      $output = 'Error fetching registration information:'.$e->getMessage();
      include 'output.html.php';
      exit();
    }
    while ($row = $results->fetch())
    {
      $regdetails [] = array(
       'name' => $row['name'],
       'jobtitle' => $row['jobtitle'],
       'organisation' => $row['organisation'],
       'country' => $row['country'],
       'landline' => $row['landline'],
       'cellphone'=> $row['cellphone'],
       'email' => $row['email'],
       'password' => $row['password'] );
    }
    
      include   //  the output file using nested foreach loops
    However it just leads to 'undefined variable' message.
    However if I substitute lastInsertId() with the id number of an existent db entry, the $regdetails variable is created and the output happens successfully.

    I know that the db entries are being written to the db, although I have to click refresh on phpmyadmin to see the latest one appear.

    Is it because lastInsertId() can't be used in this ...where id = $last_inserted"; context?

  12. #37
    SitePoint Member
    Join Date
    Dec 2013
    Location
    United Kingdom
    Posts
    17
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay, I've discovered what the problem was.
    lastInsertId() works on a per connection basis, and I was making a second connection for the select query. Doh!

    I found the answer after performing a search on Stackoverflow, which drew attention to
    http://dev.mysql.com/doc/refman/5.0/...last-insert-id
    which states:
    The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that each client can retrieve its own ID without concern for the activity of other clients, and without the need for locks or transactions.
    although admittedly this is referring to LAST_INSERT_ID() and not lastInsertId(). Can anybody tell me the difference?

    I'd also be interested to know if anybody thinks there is still a danger of retrieving the wrong persons details on a busy website, and if so more details about avoiding this possibility.

    In the meantime I can now retrieve the last inserted entry and display it back to the new member. Thanks, everybody who helped.


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
  •