PHP MySQL PDO: get last inserted ROW not ID

Question

I have currently a script to insert new data automatically when using the form to add a new element.

I use

 public string PDO::lastInsertId ([ string $name = NULL ] )

Logic of course. Now I am only wondering if this might cause any problems in case a table has no ID field and no auto increment field.

Aside the fact if this means there is a bad database structure used. I want to make sure that this routine works in any situation. Good database or a bad database structure. It should not matter.

What I mean is that I would like to get back the row with data so I can search for it.

E.G.

1

Insert new data into the database

$query = $pdo->prepare("INSERT INTO `tablename` SET `data_name`='John', `data_familyname`='Doe', 'data_age`=45, `data_wife`='Sarah Doe'");
$query->execute();

2

Fetch the last inserted row entirely

///$LastID = $pdo->lastInsertId(); 
$query = $pdo->prepare("SELECT `data_name`,`data_familyname`,`data_age`,`data_wife` FROM `tablename` WHERE LAST IDFIELD = ".$pdo->lastInsertId());
$query->execute();
$row = $query->fetch();

Row contains

array(
        'data_name'         =>      'John',
        'data_familyname'   =>      'Doe',
        'data_age'          =>      '45',
        'data_wife'         =>      'Sarah Doe'
    );

3

Then update the last inserted row with exactly the same data as just inserted

$query = $pdo->prepare("UPDATE `tablename` SET `data_name`='Chris' WHERE `data_name`=? AND `data_familyname`=? AND 'data_age`=? AND `data_wife`=?");
$query->execute(array_values($row));

The last query is basically:

UPDATE `tablename` SET `data_name`='Chris' 
WHERE `data_name`='John' AND `data_familyname`='Doe' AND 'data_age`='45' AND `data_wife`='Sarah Doe'

Of course there is the possibility that duplicate data exists. And you want to modify only the last row. That is where I meant aside from the fact if the database has any good structure.

But to prevent duplicate data one could add:

Row contains

array(
        'data_id'           =>      20,
        'data_name'         =>      'John',
        'data_familyname'   =>      'Doe',
        'data_age'          =>      '45',
        'data_wife'         =>      'Sarah Doe'
    );

The point is

Step 2

Does not work since I just made it up a couple of minutes a go. However I would like to know if there are any similar functions or routines that do the same as I just mentioned.

Hope that it is clear what I want.

My solution eventually is:

$sth = $pdo_connection->prepare("INSERT INTO `pdo_test` SET 
                            `pdo_name`              =       'John Doe',
                            `pdo_description`       =       'John Doe who served in Vietnam and every other war game as a default name',
                            `pdo_text`              =       'Some text BLABLA'
                        ");
$sth ->execute();

$stmt               = $pdo_connection->query("SELECT LAST_INSERT_ID()");
$result             = $stmt->fetch(PDO::FETCH_ASSOC);
$lastID             = $result['LAST_INSERT_ID()'];


$stmt               = $pdo_connection->prepare("SHOW COLUMNS FROM `pdo_test` WHERE EXTRA LIKE '%AUTO_INCREMENT%'");
$stmt->execute();
$row                = $stmt->fetch(PDO::FETCH_ASSOC);
$AutoIncrementField = $row['Field'];


$stmt               = $pdo_connection->prepare('SELECT * FROM `pdo_test` WHERE `'.$AutoIncrementField.'` = ?');
$stmt->execute(array($lastID));
$result             = $stmt->fetch(PDO::FETCH_ASSOC);

echo print_r($result);

Result:

Array
(
    [pdo_id]            =>      64
    [pdo_name]          =>      John Doe
    [pdo_counter]       =>      0
    [pdo_description]   =>      John Doe who served in Vietnam and every other war game as a default name
    [pdo_text]          =>      Some text BLABLA
)

But an AutoIncrement field seems to be essential.

Second solution but still with an AutoIncrement field.

(as proposed by: Edwin Lambregts)

Note that this solution is not failsafe!!! Because the query selects the last inserted row... But by ALL instances! Hence if another user just inserted a new row... You will get to see his input and not yours. This can occur if the update happens in between.

$sth = $pdo_connection->prepare("INSERT INTO `pdo_test` SET 
                            `pdo_name`              =       'John Doe',
                            `pdo_description`       =       'John Doe who served in Vietnam and every other war game as a default name',
                            `pdo_text`              =       'Some text BLABLA'
                        ");
$sth ->execute();


$stmt               = $pdo_connection->prepare("SHOW COLUMNS FROM `pdo_test` WHERE EXTRA LIKE '%AUTO_INCREMENT%'");
$stmt->execute();
$row                = $stmt->fetch(PDO::FETCH_ASSOC);
$AutoIncrementField = $row['Field'];


$stmt               = $pdo_connection->prepare('SELECT * FROM `pdo_test` ORDER BY `'.$AutoIncrementField.'` DESC LIMIT 0,1');
$stmt->execute();
$result             = $stmt->fetch(PDO::FETCH_ASSOC);

The outcome is:

Array
(
    [pdo_id]            =>      67
    [pdo_name]          =>      John Doe
    [pdo_counter]       =>      0
    [pdo_description]   =>      John Doe who served in Vietnam and every other war game as a default name
    [pdo_text]          =>      Some text BLABLA
)

Answer 1

Getting the last inserted record/row certainly is possible without the usage of id's.

MySQL syntax:

SELECT column_name FROM table_name ORDER BY column_name DESC LIMIT 1

It will simply show the last inserted record.

Source: http://www.w3schools.com/sql/sql_func_last.asp