PHP PDO Fetch/FetchAll

BraisbyI · January 26, 2014

Hi,

Although I have been programming for a long time now I have been asked to help a friend out with some PHP and MySql (never touched either of them before this week) I find some tasks which should be obvious to me are a little alien.

My problem is that I want to read a record from a user file using the 'id' and then using data from that record to read 1 or 2 records from a contact/address file. I do not know whether it will be 1 or 2 records as this data is entered by the user.

The sample code here shows the user file "valid_user" and the contact/address file called "carer", not having used this form of syntax before I am not 100% sure that the SELECT statement is correct except that it does produce the correct data when run (well the first contact details).

$myid = $_SESSION['id'];

try

{

$query="SELECT * FROM carer

INNER JOIN valid_users

ON valid_users.id = carer.valid_users_id

WHERE valid_users.id='$myid'";

$stmt = $dbh->prepare($query);

$stmt->execute();

$count = $stmt->rowCount();

$row = $stmt->fetch(PDO::FETCH_ASSOC);

$myfirstname = $row['firstname'];

$mysurname = $row['surname'];

$myaddress1 = $row['address1'];

$myaddress2 = $row['address2'];

$mytown = $row['town'];

$mycounty = $row['county'];

$mypostcode = $row['postcode'];

$myhome = $row['landline'];

$mymobile = $row['mobile'];

$myemail = $row['email'];

$myecontact = $row['econtact'];

$myephone = $row['ephone'];

This gets me details for the first contact, which is displayed quite happily but I do not understand how I go about fetching the 2nd contact record (if there is one). I understand I could use fetchAll but I cant then work out how i populate the variables for both the first contact and the second contact when the variables are not the same i.e. the firstname field in "contact" record 1 will populate $myfirstname whereas the firstname field in "contact" record 2 will populate $myfirstname2.

It is done this way because I am displaying both sets of contact details on the screen. In my past experience I would just do a Read Next to get the second contact record but obviously no such syntax is available. I also use the record count later on in the program.

I know this probably has an easy answer but all the documentation I have read want to talk about apples and pears and whether they are red or yellow and then shows you how to print the array without explaining how you get the separate elements of the array in to variables.

Any help would be appreciated

Ch0cu3r · January 26, 2014

You will need to use a while loop to loop over the results of the from the query

while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{ 
      $myfirstname  = $row['firstname'];
      $mysurname    = $row['surname'];
      $myaddress1   = $row['address1'];
      $myaddress2   = $row['address2'];
      $mytown       = $row['town'];
      $mycounty     = $row['county'];
      $mypostcode   = $row['postcode'];
      $myhome       = $row['landline'];
      $mymobile     = $row['mobile'];
      $myemail      = $row['email'];
      $myecontact   = $row['econtact'];
      $myephone     = $row['ephone'];

      // code for displaying record here
}

ginerjm · January 26, 2014

You process the results set with a while loop as in :

while ( $row = $stmt->fetch(PDO::FETCH_ASSOC) )

{

do your thing

}

Also - you have taken advantage of PDO's prepared queries but you are not doing the primary thing behind them: substitute parmaters.

Change the where clause to :

WHERE valid_users.id=:id ";

and the Execute statement to:

$stmt->execute(array('id'=>$myid));

This is the whole reason to use a prepared query.

BraisbyI · January 26, 2014

ginerjm, yes I can agree on that, it would make good sense to use that function.

Ch0cu3r, I did look at a loop and in the end may have to use that but the problem is that the 2 carer records I need are going in to 2 different sets of variables, I suppose I could look at making the variables into arrays and using the same variable name with array[0] and array[1] but it seams a very cumbersome way to deal with it.

Many thanks for the help, I will go and look at this again,

I may still look at trying to do a fetchAll and then assign variables from the array returned.

Ch0cu3r · January 26, 2014

I dont follow. This is the method you need to do in order loop over the results returned form a query. Your code for displaying the record needs to go into the while loop with the variables.

My code example expanded

while($row = $stmt->fetch(PDO::FETCH_ASSOC))
{ 
      $myfirstname  = $row['firstname'];
      $mysurname    = $row['surname'];
      $myaddress1   = $row['address1'];
      $myaddress2   = $row['address2'];
      $mytown       = $row['town'];
      $mycounty     = $row['county'];
      $mypostcode   = $row['postcode'];
      $myhome       = $row['landline'];
      $mymobile     = $row['mobile'];
      $myemail      = $row['email'];
      $myecontact   = $row['econtact'];
      $myephone     = $row['ephone'];

      // code for displaying record here

      // displayes the name, address and town for each record in the result
      echo "
<p>Your name: $myfirstname</p>
<p>Address: $myadddress1<br>$myaddress2<br>$myaddress3</p>
<p>Town: $mytown<p>
..etc...
";
}

This will loop over the results displaying the information for each user.

If you used fetchAll you still need to use some form of loop,

// loop over all the results 
foreach($stmt->fetchALL(PDO::FETCH_ASSOC) as $row)
{ 
      $myfirstname  = $row['firstname'];
      $mysurname    = $row['surname'];
      $myaddress1   = $row['address1'];
      $myaddress2   = $row['address2'];
      $mytown       = $row['town'];
      $mycounty     = $row['county'];
      $mypostcode   = $row['postcode'];
      $myhome       = $row['landline'];
      $mymobile     = $row['mobile'];
      $myemail      = $row['email'];
      $myecontact   = $row['econtact'];
      $myephone     = $row['ephone'];

      // code for displaying record here

      // displayes the name, address and town for each record in the result
      echo "
<p>Your name: $myfirstname</p>
<p>Address: $myadddress1<br>$myaddress2<br>$myaddress3</p>
<p>Town: $mytown<p>
..etc...
";
}

This will have the same out come as the while loop

Edited January 26, 2014 by Ch0cu3r

BraisbyI · January 26, 2014

Ch0cu3r, Obviously this is my misunderstanding of how the fetchAll works, I assumed that both records would be returned but held in an array which would be accessible in some way to assign the $myfirstname=firstname[0] & $myfirstname2=firstname[1] and this is the part I did not understand how to do.

The system I am working on displays a screen with 2 Name & Address details side by side. The user must have at least 1 of these filled in and the other can be blank, but at any stage the user is allowed to amend either set of details, which is why I believed they had to be referenced with 2 sets of variables. This data is not just displayed on the screen it is amendable by the user and when the user presses the update button if any fields have been modified I have to be able to put the data which is on the screen back in to the carer file records.

If i do a fetch with a while loop or a fetchAll with a foreach loop the only way I can think of making this work is for the variables ($myfisrtname, $mysurname etc) to be arrays themselves?

Ch0cu3r · January 26, 2014

Ch0cu3r, Obviously this is my misunderstanding of how the fetchAll works, I assumed that both records would be returned but held in an array which would be accessible in some way to assign the $myfirstname=firstname[0] & $myfirstname2=firstname[1] and this is the part I did not understand how to do.

No, fetchAll will return all records in a multiple dimension array, Each set of records will be in a separate array eg

$row = $stmt->fetchAll();// get all results

// first record
$row[0] // will contain an array of values for first result, eg
echo 'First name for first result: ' . $row[0]['firstname'];

// second secound
$row[1] //will contain an array of values for second result
echo 'First name for second result: ' . $row[1]['firstname'];

The system I am working on displays a screen with 2 Name & Address details side by side. The user must have at least 1 of these filled in and the other can be blank, but at any stage the user is allowed to amend either set of details, which is why I believed they had to be referenced with 2 sets of variables. This data is not just displayed on the screen it is amendable by the user and when the user presses the update button if any fields have been modified I have to be able to put the data which is on the screen back in to the carer file records.

So your query will return the contact/address details of each user assigned to a carer. And you want to display two forms for updating each users contact/address details?

Edited January 26, 2014 by Ch0cu3r

kicken · January 26, 2014

There are numerous ways such a task could be accomplished. Separating things out into a bunch of separate variables is probably the least efficient way of handling it though. Use arrays, and rather than using a bunch of separate arrays, just use a single array called $records or similar.

$query="SELECT * FROM carer
INNER JOIN valid_users
ON valid_users.id = carer.valid_users_id
WHERE valid_users.id=:id";
 
$stmt = $dbh->prepare($query);
$stmt->execute(array(':id' => $myid));
$records = $stmt->fetchAll(PDO::FETCH_ASSOC);

$records[0] would be your first contact, $records[1] would be your second (if it exists). You can check if there is one or two contacts by using count($records).

Setup your form using a similar array structure so it's easy to handle when submitting changes.

<fieldset>
 <legend>First Contact</legend>
 <p>Firstname: <input type="text" name="records[0][firstname]" value="<?=htmlentities($records[0]['firstname])?>"></p>
 <p>Lastname: <input type="text" name="records[0][lastname]" value="<?=htmlentities($records[0]['lastname])?>"></p>
 ...
</fieldset>
<fieldset>
 <legend>Second Contact</legend>
 <p>Firstname: <input type="text" name="records[1][firstname]" value="<?=htmlentities($records[1]['firstname])?>"></p>
 <p>Lastname: <input type="text" name="records[1][lastname]" value="<?=htmlentities($records[1]['lastname])?>"></p>
 ...
</fieldset>

When submitting the data you can just loop the records to save the details:

foreach ($_POST['records'] as $data){
   //Insert or update using $data['firstname'], $data['lastname'], etc
}

BraisbyI · January 26, 2014

Guru, that's what I am looking for but didn't know the syntax for "records[1][firstname]". I couldn't work out how to get the array[1] with the fieldname [firstname].

Many thanks for all the help with this, now to get this done and out the door.

BraisbyI · February 3, 2014

I thought this problem had been fixed by Guru, well the initial record read was fixed.

My code has already allowed a user to login and has stored some user data in $_SESSION vars, I then use the userID to find the linked carer records (Name & Addresses). After help from Guru I got these records successfully output to the screen where the user is allowed to modify any of the data. After pressing "Submit" the data then needs writing back to the database and this is where my current problem is.

I have output the data at different points to a text file and at each stage it is the updated data, I have even checked this data after what appears to be a successful "commit" but the screen immediately reverts to the original data and on checking the database I find that this has not changed.

My code is as follows;

The read which was corrected by Guru is:

$usrid = $_SESSION['id'];

$username = $_SESSION['username'];

$usertype = $_SESSION['usertype'];

try

{

$query = "SELECT * FROM carer WHERE valid_users_id = :usrid";

$stmt = $dbh->prepare($query);

$stmt -> execute(array(':usrid' => $usrid));

$records = $stmt->fetchAll(PDO::FETCH_ASSOC);

$carer_id1 = $records[0]['id'];

$carer_id2 = $records[1]['id'];

}

catch(PDOException $ex)

{

error message output to screen and error file.

}

I then output this data to the screen with:

<legend><h2>1st Parent / Carer</h2></legend>

<label for="fname">First Name:</label>

<input id="fname" type="text" size="20" name="records[0][firstname]" value="<?= htmlentities($records[0]['firstname'])?>" autofocus />

.......

</fieldset>

..

<legend><h2>2nd Parent / Carer</h2></legend>

<label for="fname2">First Name:</label>

.........

</fieldset>

This all works well, and then the user presses "Submit" and I do:

if($_POST['update']=='Update')

{

$dbh->beginTransaction();

$loop = 1;

foreach ($_POST['records'] as $data)

{

try

{

$query = "UPDATE carer

SET firstname = ':firstname',

surname = ':surname',

address1 = ':address1',

address2 = ':address2',

town = ':town',

county = ':county',

postcode = ':postcode',

landline = ':landline',

mobile = ':mobile',

email = ':email',

econtact = ':econtact',

ephone = ':ephone'

WHERE valid_users_id = ':usrid'";

$stmt = $dbh->prepare($query);

$stmt->bindParam(':firstname', $data['firstname'], PDO::PARAM_STR);

$stmt->bindParam(':surname', $data['surname'], PDO::PARAM_STR);

$stmt->bindParam(':address1', $data['address1'], PDO::PARAM_STR);

$stmt->bindParam(':address2', $data['address2'], PDO::PARAM_STR);

$stmt->bindParam(':town', $data['town'], PDO::PARAM_STR);

$stmt->bindParam(':county', $data['county'], PDO::PARAM_STR);

$stmt->bindParam(':postcode', $data['postcode'], PDO::PARAM_STR);

$stmt->bindParam(':landline', $data['landline'], PDO::PARAM_STR);

$stmt->bindParam(':mobile', $data['mobile'], PDO::PARAM_STR);

$stmt->bindParam(':email', $data['email'], PDO::PARAM_STR);

$stmt->bindParam(':econtact', $data['econtact'], PDO::PARAM_STR);

$stmt->bindParam(':ephone', $data['ephone'], PDO::PARAM_STR);

$stmt->bindParam(':usrid', $_SESSION['id'], PDO::PARAM_INT);

$stmt->execute();

}

catch(PDOException $ex)

{

...... output any error message ........

$dbh->rollback();

}

file_put_contents('PDOdata.txt', $data, FILE_APPEND); // Development testing shows correct data

if ($loop == 1)

{

$_SESSION['firstname'] = $data['firstname'];

$_SESSION['surname'] = $data['surname'];

$_SESSION['address1'] = $data['address1'];

$_SESSION['address2'] = $data['address2'];

$_SESSION['town'] = $data['town'];

$_SESSION['county'] = $data['county'];

$_SESSION['postcode'] = $data['postcode'];

$_SESSION['landline'] = $data['landline'];

$_SESSION['mobile'] = $data['mobile'];

$_SESSION['email'] = $data['email'];

$_SESSION['econtact'] = $data['econtact'];

$_SESSION['ephone'] = $data['ephone'];

$loop++;

}

$subject = 'Profile Update';

$msg = 'Thank you for updating your Profile information';

send_mail('admin@kids4us.org.uk', $_SESSION['email'],

$subject,

'You\'ve received this E-Mail because your PROFILE has been changed. If you did not make this change, and believe this account modification to be fraudulant, please contact us');

Email gets sent and the SESSION data is updated

$dbh->commit();

}

After this the display reverts back to the unchanged data and the database is unchanged?

Any help would be appreciated.

mac_gyver · February 3, 2014

it's likely that one of your prepare, bind, or execute statements is failing, throwing an exception, and rolling back the data or perhaps you don't have pdo setup to throw an exception for those statements at all. your catch{} block isn't notifying you in any way that something failed AND you are allowing the remainder of your code to run if there is an error.

btw - one of the points of using prepared queries is to reuse the prepared query statement, with multiple different sets of data values. the prepare and binding logic should be before the start of the foreach() loop. the only thing that belongs inside the foreach(){} loop would be the $stmt->execute() statement.

BraisbyI · February 3, 2014

mac_gyver

It maybe my lack of understanding with PHP & PDO but I thought that any failure within the "try" statement would be reported by the "catch" statement which immediately follows it?

I have the prepare, bind and execute inside the "try" statement with a "catch" immediately after it which has an echo to the screen that an error has occurred, it also outputs the error to a text file before issuing the rollback. I have this problem with or without the rollback being present.

I do not understand how you can move the bind statements outside the foreach $data statement and expect anything to work, how does the data get to the record if the bind does not happen inside the foreach? Am I missing something here or is my understanding of this wrong?

I do understand that somewhere something must be failing but I do not see how the rollback is happening without the error being reported as the only rollback statement present is within the catch statement which outputs the error to the screen and a text file before the rollback can be initiated.

My Catch statement in full which is immediately after the try block is:

catch(PDOException $ex)

{

echo "An Error Occured While Updating " . db_name . "!";

file_put_contents('PDOerror.txt', $ex->getMessage(), FILE_APPEND);

echo $ex->getMessage();

$dbh->rollback();

}

file_put_contents('PDOdata.txt', $data, FILE_APPEND);

I did not put this in my last message because I assumed the " ...... output any error message ........ " line would show I am dealing with any error, the file_put_contents command after the catch sends the updated data held within $data to a text file, but this command is not initiated from within the catch.

Thank you for your input, I do welcome any ideas on this problem.

kicken · February 3, 2014

It maybe my lack of understanding with PHP & PDO but I thought that any failure within the "try" statement would be reported by the "catch" statement which immediately follows it?

Your PDO setup needs to be configured to throw exceptions on an error for a try/catch to be useful. If it's set to only issue a warning or be silent you will not be catching any of the errors.

Wherever you connect to your database at, make sure you have set the PDO::ATTR_ERRMODE attribute to PDO::ERRMODE_EXCEPTION.

I do not understand how you can move the bind statements outside the foreach $data statement and expect anything to work, how does the data get to the record if the bind does not happen inside the foreach? Am I missing something here or is my understanding of this wrong?

bindParam accepts a variable by reference. As such you only need to call bindParam once outside your loop, then within your loop just overwrite the variable with the data. For example:

$dbh->beginTransaction();
$query = "
UPDATE carer
SET 
	firstname = ':firstname',
	surname   = ':surname',
	address1  = ':address1',
	...
WHERE valid_users_id = ':usrid'
";
$stmt = $dbh->prepare($query);

$stmt->bindParam(':firstname',      $bind_firstname, PDO::PARAM_STR);
$stmt->bindParam(':surname',        $bind_surname, PDO::PARAM_STR);
$stmt->bindParam(':address1',       $bind_address1, PDO::PARAM_STR);
...
$stmt->bindParam(':usrid',          $_SESSION['id'], PDO::PARAM_INT);

foreach ($_POST['records'] as $data)
{
	//Overwrite the bind_* variables with data from the arrray
	extract($data, EXTR_PREFIX_ALL, 'bind');
	//Run the query
	$stmt->execute();
}

I do understand that somewhere something must be failing but I do not see how the rollback is happening without the error being reported as the only rollback statement present is within the catch statement which outputs the error to the screen and a text file before the rollback can be initiated.

Another possibility is your encountering a problem before your call to commit(), such as in your emailing function. If the code ends prior to calling commit then the default action is to rollback.

As your code is now, a failure would cause additional problems anyway as your code is not properly structured. For each call to ->beginTransaction you can only call either ->commit or ->rollback once. Currently you call ->beginTransaction before your loop, you potentially call ->rollback within the loop (so it may be called several times) and finally you call ->commit after the email regardless of if the transaction was already rolled back or not.

You need to re-structure the code to ensure you only call ->commit or ->rollback once depending on if errors occurred. For example:

$query = "
UPDATE carer
SET 
	firstname = ':firstname',
	surname   = ':surname',
	address1  = ':address1',
	...
WHERE valid_users_id = ':usrid'
";
$stmt = $dbh->prepare($query);

$stmt->bindParam(':firstname',      $bind_firstname, PDO::PARAM_STR);
$stmt->bindParam(':surname',        $bind_surname, PDO::PARAM_STR);
$stmt->bindParam(':address1',       $bind_address1, PDO::PARAM_STR);
...
$stmt->bindParam(':usrid',          $_SESSION['id'], PDO::PARAM_INT);

$dbh->beginTransaction();
try {
	foreach ($_POST['records'] as $data)
	{
		//Overwrite the bind_* variables with data from the arrray
		extract($data, EXTR_PREFIX_ALL, 'bind');
		//Run the query
		$stmt->execute();
	}

	send_mail(...);

	$dbh->commit();
}
catch (PDOException $e){
	//Log Error...

	$dbh->rollback();
}

Edited February 3, 2014 by kicken

BraisbyI · February 3, 2014

kicken,

OK my understanding of this was totally wrong, I will rewrite this program and while I am at it change other parts of this system to match.

Many thanks for your help with this, I am always learning and this has really helped my understanding of the structure of PDO.

Hopefully when I get this done correctly the problem will be fixed.

Sign In

PHP PDO Fetch/FetchAll

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information