Problem in retrieving database entry for website registrants

Could you please post your MySQL table structure that you’re using in this case?Or the field id in your table is AUTO_INCREMENT?And for the second problem,are you see this warning here?

Array ( [0] => 00000 [1] => [2] => )

You are now registered with the following details:
array(1) { [0]=> array(9) { ["firstname"]=> string(4) "Fred" ["lastname"]=> string(6) "Bloggs" ["jobtitle"]=> string(6) "Editor" ["organisation"]=> string(12) "Daily Gossip" ["country"]=> string(2) "UK" ["landline"]=> string(13) "0111 222 3333" ["cellphone"]=> string(13) "07777 888 999" ["email"]=> string(18) "fred@blahblah.blah" ["password"]=> string(8) "********" } }


[COLOR="#FF0000"]Warning: htmlspecialchars() expects parameter 1 to be string, array given in //the pathway to that line[/COLOR] 

Why did you prefer to believe Yank’s code is right and don’t do it like below for a test?

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Registration Details</title>
  </head>
  <body>
    <p>You are now registered with the following details:</p>
    <?foreach ($regdetails as $item): ?>
      <blockquote>
        <p>
          <?php foreach($item as $field) echo htmlspecialchars($field, ENT_QUOTES, 'UTF-8');?>
        </p>
      </blockquote>
    <?php endforeach; ?>
  </body>
</html>

Hi blue_sky,

Thanks for that. I tried your suggested code and there is a breakthrough! The details are outputting like this:

jArray ( [0] => 00000 [1] => [2] => )

You are now registered with the following details:

BillBloggsEditorDaily GossipUK0111 222 333307777 888 [email]999bill@blahblah.blah[/email]******

I’m pleased that it works, but puzzled because Yank’s code also works with its different technique.

Also, I don’t understand this:

jArray ( [0] => 00000 [1] => [2] => )

How can I separate the fields when outputted, so that they appear as a list?

I still can’t make my database query produce the last entry with LAST_INSERT_ID(). It only outputs a result if the query is of a known existent id number. This is no good for my intention - to show the registrant the entry that they just made as a confirmation check.

The database structure is that the id field in an integer that auto-increments, whilst the others are all varchar (255)

The “Array. …” bit is the ErrorInfo printing out; it shows an error code of 00000 (which means no error occured). I was checking to see if your database was rejecting the query; it is not.

Incidentally; this method of “the entry they just made” is flawed and should not be used in a production environment. It’s good for learning purposes, but you have no way of checking to see if the person who submitted the data is the same one for whose data you are retrieving; if two people submit their data near-simultaneously, you’ll display the second person’s data to both people.

You more likely should be storing a value from the database object’s values on submission (see PDO::lastInsertId), possibly utilizing a session as well (bit more of an advanced subject.).

How can I separate the fields when outputted, so that they appear as a list?

<!DOCTYPE html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <title>Registration Details</title>
  </head>
  <body>
    <p>You are now registered with the following details:</p>
    <?foreach ($regdetails as $item): ?>
     <?foreach ($item as $field): ?>
      <blockquote>
        <p>
          <?php echo htmlspecialchars($field, ENT_QUOTES, 'UTF-8');?>
        </p>
      </blockquote>
     <?php endforeach; ?>
    <?php endforeach; ?>
  </body>
</html>

Thanks, StarLion,

I’d like to make this LAST_INSERT_ID () method work to return the last db entry as it seems as though it has been created for exactly that purpose. There must be something wrong with the way I’m using it.

If I insert id into the brackets I can at least get an output of every single db entry


try
{
  $sql =  'SELECT name, jobtitle, organisation, country, landline, cellphone, email, password 
          FROM tablename                                                                                                                       //appropriately substituted                
          WHERE id = LAST_INSERT_ID (id)';

  $results = $pdo->query($sql);
}

but if I leave the brackets empty then no array is even created. If I replace LAST_INSERT_ID() with an existent known id number it outputs fine.

I tried re-writing the query as


try
{
  $sql =  'SELECT LAST_INSERT_ID( id, name, jobtitle, organisation, country, landline, cellphone, email, password) FROM tablename';   //appropriately substituted

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

but that simply gives the following error:

Error fetching registration information:SQLSTATE[42000]: Syntax error or access violation: 1582 Incorrect parameter count in the call to native function ‘LAST_INSERT_ID’

In terms of, “storing a value from the database object’s values on submission (see PDO::lastInsertId), possibly utilizing a session as well”, I can read up again on sessions in Kevin Yank’s Novice to Ninja, but can you suggest a manual that I could get, which will explain the preferred approach more fully?

Thanks for the outputting advice, blue_sky. That worked!

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

Thanks, Mittineague

I altered the select query to


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:

http://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-29.html

It must be the way I am using LAST_INSERT_ID() that is the problem.

You forgot to associate lastInsertId() with your instance of the PDO object.

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:

	//check this is the last album_ID
	$last_album = $PDO->lastInsertId();

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.

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() ):


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:


// 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);

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:


<?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:


 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’

Case typo
$last_inserted = $pdo->lastInsertID();
should be
$last_inserted = $pdo->lastInsertId();

No, it wasn’t that. I just tried again with the correct case but it made no difference - same error message.

Full quote query so variable can be phrased.

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();

Thanks, Drummin

that’s exactly what I have, followed by the while loop that is supposed to create the $regdetails variable as below:


  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?

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/en/information-functions.html#function_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.