Help with UPDATE php/mysql

njfrlng · June 11, 2014

I'm making a simple data entry page where you can add employees to a database.

I think have it so you can view all employees in a table, and either view them, edit them, or delete them.

View/Delete work fine.

However, my edit doesn't seem to work at all.

I have the edit page so that it pulls the current data for the selected employee. But when you change the data and click submit the information does not get updated.

So my question is: Why is my update.php not updating the database with the edited data?

Page 1 - View.php

<?php
$BASE_PATH = 'C:\xampp\htdocs\OGS';
include_once($BASE_PATH . "\includes\layouts\header.php");
?>

<?php 
mysql_connect('localhost', 'root', '');
mysql_select_db('ogs');
?>

<div id="main">
   <div id="subnavigation">
       <?php include_once($BASE_PATH . "\mods\main_menu\index.html");?>
   </div>

 <div id="page">
           <p><b>View Employee</b></p>
			</br>
		<?php
$con=mysqli_connect("localhost","root","","ogs");
// Check connection
if (mysqli_connect_errno()) {
 echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

$result = mysqli_query($con,"SELECT * FROM people WHERE role=1");

echo "<table border='1'>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Id</th>
<th>View Employee</th>
<th>Edit Employee</th>
<th>Delete Employee</th>
</tr>";

while($row = mysqli_fetch_array($result)) {
 echo "<tr>";
 echo "<td>" . $row['first_name'] . "</td>";
 echo "<td>" . $row['last_name'] . "</td>";
 echo "<td>" . $row['id'] . "</td>";
 echo "<td><a href=\"view_form.php?id=$row[id]\"><center>View</center></a></td>";
 echo "<td><a href=\"edit_form.php?id=$row[id]\"><center>Edit</center></a></td>";
 echo "<td><a href=\"delete.php?id=$row[id]\"><center>Delete</center></a></td>";
 echo "</tr>";
}

echo "</table>";

mysqli_close($con);
?> 


</div>
</div>
</div>

Page 2 - Edit_form.php

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<title>Form Edit Data</title>
</head>

<body>

<?php
	$BASE_PATH = 'C:\xampp\htdocs\OGS';
	include_once($BASE_PATH . "\includes\layouts\header.php");
?>

<div id="main">
   <div id="subnavigation">
       <?php include_once($BASE_PATH . "\mods\main_menu\index.html");?>
   </div>

 <div id="page">
 <br><br>
<table border=1>
  <tr>
    <td align=center>Update Employee Information</td>
  </tr>
  <tr>
    <td>
      <table>
      	    <?php 
				mysql_connect('localhost', 'root', '');
				mysql_select_db('ogs');
			?>

<?php
	$emp_id= ($_GET["id"]);
	$id = 0; $firstName = ''; $lastName = '';

	$sql = "SELECT * FROM people
			WHERE id='$emp_id' LIMIT 1";
	$result = mysql_query($sql);
	$row_people = mysql_fetch_array($result);

	if(!empty($row_people)) {
		$id = $row_people['id'];
		$first_name = $row_people['first_name'];
		$last_name = $row_people['last_name'];
	}
?>

<form method="post" action="update2.php">
   <input type="text" name="id" value="<?php echo $id; ?>">
   <fieldset>
       <legend><b>Name</b></legend>
       First Name:<input type="text" name="first_name" size="20" value="<?php echo "$row_people[first_name]"; ?>">
       Last Name:<input type="text" name="last_name" size="40" value="<?php echo "$row_people[last_name]"; ?>">
   </fieldset>


<br><br>
		<fieldset>
			<legend><b>Contact Information</b></legend>
				Town:<input type="text" name="town" size="20" value="<?php echo "$row_people[town]"; ?>">
				Address:<input type="text" name="address" size="40" value="<?php echo "$row_people[address]"; ?>">
				Province:
						<?php
						$prov_sql = "SELECT id, province FROM ref_provinces ORDER BY province ASC";
						$prov_result = mysql_query($prov_sql);
							echo "<select name='province'>";

						$existing_prov_id = $row_people['province'];
							while ($row_prov = mysql_fetch_array($prov_result))
						{
						// Check if the existing id is the same as the current id we are displaying
						// If it is, set the selected attribute
						if($existing_prov_id == $row_prov['id'])
							echo "<option selected='selected' value='" . $row_people['id'] . "'>" . $row_prov['province'] . "</option>";
						else
							echo "<option value='" . $row_prov['id'] . "'>" . $row_prov['province'] . "</option>";
						}
							echo "</select>";
						?> 	



				Postal Code:<input type="text" name="postal_code" size="40" value="<?php echo "$row_people[postal_code]"; ?>">
<br><br>
				Home Phone:<input type="text" name="home_phone" size="20" value="<?php echo "$row_people[home_phone]"; ?>">
				Cell Phone:<input type="text" name="cell_phone" size="40" value="<?php echo "$row_people[cell_phone]"; ?>">
        </fieldset>
<br><br>		
		<fieldset>
			<legend><b>Emergency Contact</b></legend>
				Emergency Contact Name:<input type="text" name="first_name" size="20" value="<?php echo "$row_people[first_name]"; ?>">
				Emergency Contact Number:<input type="text" name="last_name" size="40" value="<?php echo "$row_people[last_name]"; ?>">
        </fieldset>
<br><br>		
		<fieldset>
			<legend><b>Work Information</b></legend>
				Role:
						<?php
						$role_sql = "SELECT id, role FROM ref_role ORDER BY role ASC";
						$role_result = mysql_query($role_sql);
							echo "<select name='role'>";

						$existing_role_id = $row_people['role'];
							while ($row_role = mysql_fetch_array($role_result))
						{
						// Check if the existing id is the same as the current id we are displaying
						// If it is, set the selected attribute
						if($existing_role_id == $row_role['id'])
							echo "<option selected='selected' value='" . $row_people['id'] . "'>" . $row_role['role'] . "</option>";
						else
							echo "<option value='" . $row_role['id'] . "'>" . $row_role['role'] . "</option>";
						}
							echo "</select>";
						?> 	




				Employer:
					<?php
						$company_sql = "SELECT id, company_name FROM companies ORDER BY company_name ASC";
						$company_result = mysql_query($company_sql);
							echo "<select name='company_works_for'>";

						$existing_company_name_id = $row_people['company_works_for'];
							while ($row_company = mysql_fetch_array($company_result))
						{
						// Check if the existing id is the same as the current id we are displaying
						// If it is, set the selected attribute
						if($existing_company_name_id == $row_company['id'])
							echo "<option selected='selected' value='" . $row_people['id'] . "'>" . $row_company['company_name'] . "</option>";
						else
							echo "<option value='" . $row_company['id'] . "'>" . $row_company['company_name'] . "</option>";
						}
							echo "</select>";
					?> 			
<br><br>
				Department:
					<?php
						$dept_sql = "SELECT id, department FROM ref_department ORDER BY department ASC";
						$dept_result = mysql_query($dept_sql);
							echo "<select name='department'>";

						$existing_dept_id = $row_people['department'];
							while ($row_dept = mysql_fetch_array($dept_result))
						{
						// Check if the existing id is the same as the current id we are displaying
						// If it is, set the selected attribute
						if($existing_dept_id == $row_dept['id'])
							echo "<option selected='selected' value='" . $row_people['id'] . "'>" . $row_dept['department'] . "</option>";
						else
							echo "<option value='" . $row_dept['id'] . "'>" . $row_dept['department'] . "</option>";
						}
							echo "</select>";
					?> 

				Position:
					<?php
						$position_sql = "SELECT id, position FROM ref_positions ORDER BY position ASC";
						$position_result = mysql_query($position_sql);
							echo "<select name='position'>";

						$existing_position_id = $row_people['position'];
							while ($row_position = mysql_fetch_array($position_result))
						{
						// Check if the existing id is the same as the current id we are displaying
						// If it is, set the selected attribute
						if($existing_position_id == $row_position['id'])
							echo "<option selected='selected' value='" . $row_people['id'] . "'>" . $row_position['position'] . "</option>";
						else
							echo "<option value='" . $row_position['id'] . "'>" . $row_position['position'] . "</option>";
						}
							echo "</select>";
					?> 	

<br><br>
				Is Supervisor?:
						<input type="radio" name="is_supervisor" value="<?php echo "$row_people[is_supervisor]"; ?>"> Yes
						<input type="radio" name="is_supervisor" value="<?php echo "$row_people[is_supervisor]"; ?>"> No
<br><br>		
				Is Active?:
						<input type="radio" name="active_employee" value="<?php echo "$row_people[active_employee]"; ?>"> Yes
						<input type="radio" name="active_employee" value="<?php echo "$row_people[active_employee]"; ?>"> No
<br><br>
				Start Date:<input type="text" name="start_date" size="40" value="<?php echo "$row_people[start_date]"; ?>">
        </fieldset>


            <input name="update" type="submit" id="update" value="Update">

      </form>




  </div>
  </div>
</body>
</html>

Page 3 - Update.php

<?php
$BASE_PATH = 'C:\xampp\htdocs\OGS';
include_once($BASE_PATH . "\includes\layouts\header.php");
?>

<div id="main">
   <div id="subnavigation">
       <?php include_once($BASE_PATH . "\mods\main_menu\index.html");?>

   </div>

 <div id="page">

<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="ogs"; // Database name
$tbl_name="people"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$emp_id= $_POST["id"];
$first_name = $_POST['first_name'];
$last_name = $_POST["last_name"];

// update data in mysql database
$sql="UPDATE $tbl_name SET first_name='$first_name', last_name='$last_name' WHERE id='$emp_id' LIMIT 1";
$result=mysql_query($sql);

// if successfully updated.
if($result){
echo "Successful";
echo "<BR>";
}

else {
echo "ERROR";
}

?> 

</div>
</div>

Please don't mind the mess!

I am also aware I leave myself wide open for sql injection and other problems. But right now this is just on my local machine and will not be accessible by anyone else. I intend to go back and fix things as I learn more.

sniko · June 11, 2014

A few things I noticed when scimming over your code;

No need to encase variables with quotes
Indenting code could help you a lot
No constants set for database connections
- If you want to change from user root, or change the password, or even the database name (changing from root and setting a password is all advised when going into production environment), you'd have to go through quite a lot of files, assuming your current logic is adopted throughout your entire application - hard-coded values throughout.
[*]Mixing of HTML and PHP
- Perhaps look into MVC, and even a template engine, like twig.
[*]Mixing of mysql_* and mysqli_*

The action value in edit_form.php is to update2.php, which doesn't exist in the provided code.

Now, in your update.php script, can you echo $sql and then run the query manually; or even replace update.php with this;

<?php
$BASE_PATH = 'C:\xampp\htdocs\OGS';
include_once($BASE_PATH . "\includes\layouts\header.php");
?>

<div id="main">
   <div id="subnavigation">
       <?php include_once($BASE_PATH . "\mods\main_menu\index.html");?>

   </div>

 <div id="page">

<?php
$host="localhost"; // Host name
$username="root"; // Mysql username
$password=""; // Mysql password
$db_name="ogs"; // Database name
$tbl_name="people"; // Table name

// Connect to server and select database.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");

$emp_id= $_POST["id"];
$first_name = $_POST['first_name'];
$last_name = $_POST["last_name"];

// update data in mysql database
$sql="UPDATE $tbl_name SET first_name='$first_name', last_name='$last_name' WHERE id='$emp_id' LIMIT 1";
$result=mysql_query($sql) or die(mysql_error()); //this line changed.

// if successfully updated.
if($result){
echo "Successful";
echo "";
}

else {
echo "ERROR";
}

?> 

</div>
</div>

Edited June 11, 2014 by sniko

Script47 · June 11, 2014

It might just be me having a derp moment, but in your view.php I see no form which is being used to pass the data? Are you sure the update.php $_POST's actually hold a value when you debug them?

sniko · June 11, 2014

It might just be me having a derp moment, but in your view.php I see no form which is being used to pass the data? Are you sure the update.php $_POST's actually hold a value when you debug them?

You're having a derp moment.

In view, you can go to edit_form.php

echo "<td><a href=\"edit_form.php?id=$row[id]\"><center>Edit</center></a></td>";

In edit_form.php, it fetches the user stuff

       $emp_id= ($_GET["id"]);
       $id = 0; $firstName = ''; $lastName = '';

       $sql = "SELECT * FROM people
               WHERE id='$emp_id' LIMIT 1";
       $result = mysql_query($sql);
       $row_people = mysql_fetch_array($result);

In edit_form.php, it posts the new data to update.php (OP has confirmed that update2.php was just him testing, and it actually points to his correct update file)

<form method="post" action="update2.php">

njfrlng · June 11, 2014

It might just be me having a derp moment, but in your view.php I see no form which is being used to pass the data? Are you sure the update.php $_POST's actually hold a value when you debug them?

I barely know what I am doing, lol.

My understanding/thought process is:

My view.php grabs all the info in my database based on the column role.

It then drops the employees first and last name and their id from the database into a table and also gives me the option to view/edit/delete that employee.

If I click edit, it redirects me to my edit_form.php file and uses the ID from the table ( via adding this to the url ?id=$row[id])

The edit_form.php has all the forms prepopulated with the data from the database based on the id passed along from the ID i selected in view.php

I edit the data and click update which should update the data.

However it doesnt.

DidNotCompute · June 11, 2014

...

You do realise it will only update the first name and last name from your edit_form.php and NOT all the other fields in that form?

Edited June 11, 2014 by DidNotCompute

sniko · June 11, 2014

You do realise it will only update the first name and last name from your edit_form.php and NOT all the other fields in that form?

Been chatting on Skype.

POST data seems to be interrupted, and even that is sending through the default values, even if you change the form values. So only updating those two fields isn't the initial problem.

njfrlng · June 11, 2014

You do realise it will only update the first name and last name from your edit_form.php and NOT all the other fields in that form?

Yes, no sense worrying about all the other data if i cant get anything to work.

Edited June 11, 2014 by njfrlng

njfrlng · June 11, 2014

I've also uploaded this to a live site should anyone wish to see:

http://www.nicfurlong.com

Please excuse the mess, im learning! lol

sniko · June 11, 2014

These fields are named the same in edit_form.php;

<fieldset>
       <legend><b>Name</b></legend>
       First Name:<input type="text" name="first_name" size="20" value="<?php echo "$row_people[first_name]"; ?>">
       Last Name:<input type="text" name="last_name" size="40" value="<?php echo "$row_people[last_name]"; ?>">
   </fieldset>

           <fieldset>
               <legend><b>Emergency Contact</b></legend>
                   Emergency Contact Name:<input type="text" name="first_name" size="20" value="<?php echo "$row_people[first_name]"; ?>">
                   Emergency Contact Number:<input type="text" name="last_name" size="40" value="<?php echo "$row_people[last_name]"; ?>">
           </fieldset>

Try rename either groups field names.

njfrlng · June 11, 2014

wow, that fixed it! lmao thanks so much :D

Help with UPDATE php/mysql

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

Join the conversation