Using PDO with Forms Won't Populate

I have a program that views a list of records that are read from the db but when I press the edit button it won't populate the edit screen.  I have this program working with deprecated code thus the reason I have moved to pdo code.

this is the view-pdo.php program

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
	<title>View Records</title>
</head>
<body>
<?php
/* 
	VIEW.PHP
	Displays all data from 'home' table
*/
error_reporting(E_ALL);
ini_set('display_errors', 1);
	
$servername = "localhost";
$username = "xxx";
$password = "xxxxxxx";
$dbname = "xxxxxx";
try {
   $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
   // set the PDO error mode to exception
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  // echo "Connected successfully";
   $result = $conn->prepare("select * from home");
   $result->execute();
   $results = $result->fetchAll( PDO::FETCH_ASSOC );
   

    }

catch(PDOException $e)
   {
   echo "Connection failed: " . $e->getMessage();
   }
   

		
	// display data in table
	echo "<p><b>View All</b> | <a href='view-paginated.php?page=1'>View Paginated</a> | <a href='view-incomplete.php'>View Incompleted Tasks</a> || View = H or D || Frequency = O or R || <a href='new.php'>Add a new record</p>
</p>";
	
	echo "<table border='1' cellpadding='10'>";
	echo "<tr> <th>ID</th> <th>Chore Location</th> <th>Chore Name</th> <th>View</th><th>Quarter</th><th>Frequency</th><th>Notes</th></tr>";

	// loop through results of database query, displaying them in the table
	foreach ($results as $row) {
		
		// echo out the contents of each row into a table
		echo "<tr>";
		echo '<td>' . $row['ID'] . '</td>';
		echo '<td>' . $row['Chore_Location'] . '</td>';
		echo '<td>' . $row['Chore_Name'] . '</td>';
		echo '<td>' . $row['View'] . '</td>';
		echo '<td>' . $row['Quarter'] . '</td>';
		echo '<td>' . $row['Frequency'] . '</td>';
		echo '<td>' . $row['Notes'] . '</td>';
		echo '<td><a href="edit-pdo.php?ID=' . $row['ID'] . '">Edit</a></td>';
		echo '<td><a href="delete.php?ID=' . $row['ID'] . '">Delete</a></td>';
		echo "</tr>"; 
	} 

	// close table>
	echo "</table>";
?>
<p><a href="new-pdo.php">Add a new record</a></p>

</body>
</html>	

Open in new window



here is the edit-pdo.php code

<?php
/* 
 EDIT.PHP
 Allows user to edit specific entry in database
*/
error_reporting(E_ALL);
ini_set('display_errors', 1);
 // creates the edit record form
 // since this form is used multiple times in this file, I have made it a function that is easily reusable
 function renderForm($ID, $Chore_Location, $Chore_Name, $View, $Quarter, $Frequency, $Notes, $error)
 {
 }
 ?>
 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
 <html>
 <head>
 <title>Edit Record</title>
 </head>
 <body>
 <?php 
 // if there are any errors, display them
 if ($error != '')
 {
 echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
 }
  print_r($_POST);
 ?> 
 
 <form action="" method="post">
 <input type="hidden" name="ID" value="<?php echo $ID; ?>"/>
 <div>
 <p><strong>ID:</strong> <?php echo $ID; ?></p>
 <strong>Chore Location: *</strong> <input type="text" name="Chore_Location" value="<?php echo $Chore_Location; ?>" /><br/>
<strong>Chore Name: *</strong> <input type="text" name="Chore_Name" value="<?php echo $Chore_Name; ?>" /><br/>
 <strong>View: *</strong> <input type="text" name="View" value="<?php echo $View; ?>" /><br/>
 <strong>Quarter: *</strong> <input type="text" name="Quarter" value="<?php echo $Quarter; ?>" /><br/>
 <strong>Frequency: *</strong> <input type="text" name="Frequency" value="<?php echo $Frequency; ?>" /><br/>
 <strong>Notes: *</strong> <input type="text" name="Notes" value="<?php echo $Notes; ?>" /><br/>
<p>* Required</p>
 <input type="submit" name="submit" value="Submit">
 </div>
 </form> 
 </body>
 </html> 
 <?php
 


 // connect to the database
$servername = "localhost";
$username = "xxxx";
$password = "xxxxxx";
$dbname = "xxxxxxx";

 // connect to the database
try {
   $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
   // set the PDO error mode to exception
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully";
   $result = $conn->prepare("UPDATE INTO home(Chore_Location = :Chore_Location, Chore_Name = :Chore_Name, View = :View, Quarter = :Quarter, Frequency = :Frequency, Notes = :Notes WHERE ID = :ID");
   
   $results = $result->fetchAll( PDO::FETCH_ASSOC );
   
  
    }

catch(PDOException $e)
   {
   echo "Connection failed: " . $e->getMessage();
   }
   
   


 // check if the form has been submitted. If it has, process the form and save it to the database
 if (isset($_POST['submit']))
 { 
 // confirm that the 'id' value is a valid integer before getting the form data

 if (is_numeric($_POST['ID']))
 {
	  
	  
 // get form data, making sure it is valid
 $id = $_POST['ID'];
 $Chore_Location = $_POST['Chore_Location'];
 $Chore_Name = $_POST['Chore_Name'];
 $View = $_POST['View'];
 $Quarter = $_POST['Quarter'];
 $Frequency = $_POST['Frequency'];
 $Notes = $_POST['Notes'];

 
 // check that all fields are filled in
 if ($Chore_Location == '' || $Chore_Name == '' || $View == '' || $Quarter == '' || $Frequency == '')
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';
 
 //error, display form
 renderForm($ID, $Chore_Location, $Chore_Name, $View, $Quarter, $Frequency, $Notes, $error);
 }
 else
 {
 // save the data to the database
  $result->execute(array(':Chore_Location' => $Chore_Location, ':Chore_Name' => $Chore_Name, ':View' => $View, ':Quarter' => $Quarter, ':Frequency' => $Frequency, ':Notes' => $Notes));
  
 // once saved, redirect back to the view page
 header("Location: view-pdo.php"); 
 }
 }
 else
 {
 // if the 'id' isn't valid, display an error
 echo 'Error!';
 }
 }
 else
 // if the form hasn't been submitted, get the data from the db and display the form
 {
 
 // get the 'id' value from the URL (if it exists), making sure that it is valid (checing that it is numeric/larger than 0)
 if (isset($_GET['ID']) && is_numeric($_GET['ID']) && $_GET['ID'] > 0)
 {
 // query db
 $ID = $_GET['ID'];
 $result = $conn->prepare("select * from home WHERE ID = :ID");
   //$result->execute();
   $result->execute(array(':ID' => $ID));
   //$results = $result->fetchAll(PDO::FETCH_ASSOC); 
 // check that the 'id' matches up with a row in the databse
 //$results = array_values($results);



if ($results) {
 while($results = $result->fetch(PDO::FETCH_ASSOC)) {
   
 // get data from db
 
 		$Chore_Location = $results['Chore_Location'];
		$Chore_Name = $results['Chore_Name'];
		$View = $results['View'];
		$Quarter = $results['Quarter'];
		$Frequency = $results['Frequency'];
		$Notes = $results['Notes'];

 }
 echo '<pre>';
print_r($results);
echo '</pre>';
 // show form
 renderForm($ID, $Chore_Location, $Chore_Name, $View, $Quarter, $Frequency, $Notes, ' ');
 }
 else 
 // if no match, display result

 echo "No results!";
 }
 
 else {
 // if the 'id' in the URL isn't valid, or if there is no 'id' value, display an error
 }
 echo 'Error!'; {
 }
}
 
?>

Open in new window


here is the edit-pdo.php showing the errors

https://gyazo.com/60c82787f20787b62958cc2582aa2104

I need the record I select via the id on view-pdo.php to be populated in the edit screen and permit me to make the appropriate changes and then save it back to the db.

Thanks,
sharingsunshineAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Marco GasiFreelancerCommented:
We need to see the controller method at least, maybe the model function involved.
Marco GasiFreelancerCommented:
Have you passed data to the vew?
sharingsunshineAuthor Commented:
I am not clear what the controller method means.  Yes, I did pass data to the view using this program.

this is the new-pdo.php program

<?php
/* 
 NEW.PHP
 Allows user to create a new entry in the database
*/
 
 // creates the new record form
 // since this form is used multiple times in this file, I have made it a function that is easily reusable
 function renderForm($Chore_Location, $Chore_Name, $View, $Quarter, $Frequency, $Notes, $error)
 {
 }
 ?>
 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
 <html>
 <head>
 <title>New Record</title>
 </head>
 <body>
 <?php 
 // if there are any errors, display them
 if ($error != '')
 {
 echo '<div style="padding:4px; border:1px solid red; color:red;">'.$error.'</div>';
 }
 ?> 
 
 <form action="" method="post">
 <div>
 <!--<strong>ID: *</strong> <input type="text" name="ID" value="<?php echo $ID; ?>" /><br/>-->
 <strong>Chore Location: *</strong> <input type="text" name="Chore_Location" value="<?php echo $Chore_Location; ?>" /><br/>
<strong>Chore Name: *</strong> <input type="text" name="Chore_Name" value="<?php echo $Chore_Name; ?>" /><br/>
 <strong>View: *</strong> <input type="text" name="View" value="<?php echo $View; ?>" /><br/>
 <strong>Quarter: *</strong> <input type="text" name="Quarter" value="<?php echo $Quarter; ?>" /><br/>
 <strong>Frequency: *</strong> <input type="text" name="Frequency" value="<?php echo $Frequency; ?>" /><br/>
 <strong>Notes: *</strong> <input type="text" name="Notes" value="<?php echo $Notes; ?>" /><br/>
 <p>* required</p>
 <input type="submit" name="submit" value="Submit">
 </div>
 </form> 
 </body>
 </html>
 <?php 
 
 
 
 

 // connect to the database
$servername = "localhost";
$username = "xxx";
$password = "xxxxxxx";
$dbname = "xxxxxx";

 // save the data to the database
try {
   $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
   // set the PDO error mode to exception
   $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
  echo "Connected successfully";
   $result = $conn->prepare("INSERT INTO home(Chore_Location, Chore_Name, View, Quarter, Frequency, Notes) VALUES(:Chore_Location, :Chore_Name, :View, :Quarter, :Frequency, :Notes)");
   
   //$results = $result->fetchAll( PDO::FETCH_ASSOC );
   
  
    }

catch(PDOException $e)
   {
   echo "Connection failed: " . $e->getMessage();
   }
 // check if the form has been submitted. If it has, start to process the form and save it to the database
 if (isset($_POST['submit']))
 { 
 $form=$_POST;
 
 // get form data, making sure it is valid
 $Chore_Location = $form['Chore_Location'];
 $Chore_Name = $form['Chore_Name'];
 $View = $form['View'];
 $Quarter = $form['Quarter'];
 $Frequency = $form['Frequency'];
 $Notes = $form['Notes'];

 
 // check to make sure all fields are entered
 if ($Chore_Location == '' || $Chore_Name == '' || $View == '' || $Quarter == '' || $Frequency == '')
 {
 // generate error message
 $error = 'ERROR: Please fill in all required fields!';
 
 // if either field is blank, display the form again
 renderForm($Chore_Location, $Chore_Name, $View, $Quarter, $Frequency, $Notes, $error);
 }
 else
 {
 // save the data to the database
 $result->execute(array(':Chore_Location' => $Chore_Location, ':Chore_Name' => $Chore_Name, ':View' => $View, ':Quarter' => $Quarter, ':Frequency' => $Frequency, ':Notes' => $Notes));
 
 // once saved, redirect back to the view page
 header("Location: /chores/view-pdo.php"); 
 }
 }
 else
 // if the form hasn't been submitted, display the form
 {
 renderForm('','','','','','','');
 }
?>

Open in new window

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Ray PaseurCommented:
This article maps the functionality of the familiar but obsolete MySQL extension to the current MySQL extensions for MySQLi and PDO.  Please read it over and post back if you still have questions about how to move up to PDO.  I think you may want to look at the ways to detect PDO errors and how to determine the number of rows in a PDO results set.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/PHP_Databases/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html
Marco GasiFreelancerCommented:
Sorry, I thought you were in an MVC environment. Now I read more carefully your code and the problem si quite simple: when you edit the edit button in VIEW.PHP you don't submit a form with all its data because you're using a simple link which pass in the url the id of the element you want to edit.
So, in EDIT.PHP you must get the id passed by VIEW.PHP and then run a query to get all data for that id and with these data populate the form. That's all.
Cheers
sharingsunshineAuthor Commented:
So, in EDIT.PHP you must get the id passed by VIEW.PHP and then run a query to get all data for that id and with these data populate the form. That's all.

That is what I am attempting to do and you can see the id is being passed but when I do a dump on the $_POST variable there is nothing there.  So for some reason that isn't happening like it should.  So can you give me specifics?
Marco GasiFreelancerCommented:
I don't see any query to the database in EDIT.PHP. Anyway, you're looking for the ID in $_POST array but you'll fiond it in $_GET one
Marco GasiFreelancerCommented:
As I said before, you're not posting the form, you're just using a link with a parameter:

<a href="edit-pdo.php?ID=' . $row['ID'] . '">Edit</a>

which produces an url like this:

www.yourdomain.com/edot.php?ID=<xxxxxx>

The value of this parameter must be grabbed from the $_GET array:
$id = $_GET['ID'];
Then you have to use $id to get data from the database:
$query = "SELECT * FROM tablename WHERE id='$id'";
sharingsunshineAuthor Commented:
Doesn't the UPDATE statement do that?  Or, am I mistaking?


 $result = $conn->prepare("UPDATE INTO home(Chore_Location = :Chore_Location, Chore_Name = :Chore_Name, View = :View, Quarter = :Quarter, Frequency = :Frequency, Notes = :Notes WHERE ID = :ID");
   
   $results = $result->fetchAll( PDO::FETCH_ASSOC );
   

Open in new window

Marco GasiFreelancerCommented:
Sorry, but this is what I understand: you select a record in VIEW.PHP and need to be able to get all data available in EDIT.PHP form to make changes and then post them in the database.

The update query should update the database, but:

1 -  in UPDATE query you're using a non-existent $_POST['ID'] variable: I repeat, you're passing the id qithin the url and you must get it in the S_GET array: $id = $_GET['ID'];

2 - before to post changes in the database you have to get all data from the databse for that is and using them to populate your form in EDIT.PHP

Hope this is clear enough. :-)
sharingsunshineAuthor Commented:
I understand that it needs to be a $_GET variable and I have made this change.  To give you a reference I also am including the line that was below it already.

$ID=$_GET['ID'];
 
 function renderForm($ID, $Chore_Location, $Chore_Name, $View, $Quarter, $Frequency, $Notes, $error)

Open in new window


you're using a non-existent $_POST['ID'] variable:
this statement I don't understand why it is a Post variable.  I changed the method to GET on the form on edit-pdo.php but there seems to be no change.

I am sorry to be so slow on understanding this but I am new to PHP.
Ray PaseurCommented:
I believe that this may be a SQL syntax error: UPDATE INTO; that is why you must test the return values from the queries and visualize any error information.  The article shows how to do this.

If you're new to PHP and want to learn the language, this article will help you find dependable learning resources and trustworthy examples.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_11769-And-by-the-way-I-am-new-to-PHP.html
Marco GasiFreelancerCommented:
Think to $_POST and $_GET array as two different email boxes. When you write something like this:
<form action="edit.php" method= "post">
<input type="text" name="ID" value="<?php echo $row['ID']; ?>" />
<input type="submit" />
</form>

Open in new window

and click the submit button you're sending your data (the ID) to the $_POST inbox, so in edit.php you'll go to $_POST inbox to find your data:
$id = $_POST['ID'];

Open in new window

But if you write
<form action="edit.php" method= "get">
<input type="text" name="ID" value="<?php echo $row['ID']; ?>" />
<input type="submit" />
</form>

Open in new window

or, as you did,
<a href="edit-pdo.php?ID=' . $row['ID'] . '">Edit</a>

Open in new window

you're sending your data to the $_GET inbox, so you'll have to look for them there:
$id = $_GET['ID'];

Open in new window

So, all this code just doesn't work:
 if (isset($_POST['submit']))
 { 
 // confirm that the 'id' value is a valid integer before getting the form data

 if (is_numeric($_POST['ID']))
 {
 // get form data, making sure it is valid
 $id = $_POST['ID'];

Open in new window

because the $_POST array is empty: you chose to use the $_GET when you wrote
<a href="edit-pdo.php?ID=' . $row['ID'] . '">Edit</a>

Open in new window


Go on. At the top of EDIT.PHP you have your form and you try to populate this way:
 // get form data, making sure it is valid
 $id = $_POST['ID'];
 $Chore_Location = $_POST['Chore_Location'];
 $Chore_Name = $_POST['Chore_Name'];
 $View = $_POST['View'];
 $Quarter = $_POST['Quarter'];
 $Frequency = $_POST['Frequency'];
 $Notes = $_POST['Notes'];

Open in new window

But $_POST array is empty so empty remains the form too. Did you replace $_POST with $_GET? Not enough! In $_GET you have only the id of the record so you have to use the id to retrieve all data:
$id = $_GET['ID'];
$query = "SELECT * FROM tablename WHERE id='$id'"; 

Open in new window

Once you execute the query you'll have a record set and you'll can use it to fill your variables
    $stmt = $conn>prepare("SELECT * FROM `home` WHERE `id`=?");
    $stmt->execute($id);
    while ($row = $stmt->fetchAll(PDO::FETCH_ASSOC)){
 		$Chore_Location = $row ['Chore_Location'];
		$Chore_Name = $row ['Chore_Name'];
		$View = $row ['View'];
		$Quarter = $row ['Quarter'];
		$Frequency = $row ['Frequency'];
		$Notes = $row ['Notes'];      
    }

 <form action="" method="post">
 <input type="hidden" name="ID" value="<?php echo $ID; ?>"/>
 <div>
 <p><strong>ID:</strong> <?php echo $ID; ?></p>
 <strong>Chore Location: *</strong> <input type="text" name="Chore_Location" value="<?php echo $Chore_Location; ?>" /><br/>
<strong>Chore Name: *</strong> <input type="text" name="Chore_Name" value="<?php echo $Chore_Name; ?>" /><br/>
 <strong>View: *</strong> <input type="text" name="View" value="<?php echo $View; ?>" /><br/>
 <strong>Quarter: *</strong> <input type="text" name="Quarter" value="<?php echo $Quarter; ?>" /><br/>
 <strong>Frequency: *</strong> <input type="text" name="Frequency" value="<?php echo $Frequency; ?>" /><br/>
 <strong>Notes: *</strong> <input type="text" name="Notes" value="<?php echo $Notes; ?>" /><br/>
<p>* Required</p>
 <input type="submit" name="submit" value="Submit">

Open in new window

Now, once you submit the form the last part should work updating the database.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marco GasiFreelancerCommented:
Oh, yes, I didn't see that, Ray! Uodate wont work because of that syntax error. In addition, you should rewrite the code with some more rule.

And to learn PHP I suggest a book which will guide you through the language secrets step by step: http://www.sitepoint.com/books/phpmysql5/. Maybe you wont become a ninja, but you'll discover a tons of useful things :-)
sharingsunshineAuthor Commented:
I have been working on this for quite some time and still not getting it.  So I did some research and found a much better example that seemed more logical in its approach too.

I commented out sections of code and realized it is much more than the update statement so I want to thank you for giving me the tips you have given me Marco.  Ray your links will be something I will keep checking on as time goes by I am sure.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.