update the data in the database from the HTML form

sigmahokies · October 5, 2015

Hi everyone,

I'm learning about update the data in the database in PHP now, seem it is little tougher than register form. But I believe I am doing right code in PHP, but seem update has not enter MYSQL database yet. what did I do wrong?

Also, I set up the link from previous website like this: /insert.php?id=$row['id']&id2=$row['firstname']&id3=$row['lastname'], goes on. So, this link passed the data in the value to this update website.

<?php

$Garydb = mysqli_connect("XXXXX","XXXXX","XXXXX") or die("Could not connect MySQL Database");

mysqli_select_db($Garydb, "XXXXX") or die("Could not find a Database");

$edit = $_GET['id'];

$firstname = $_GET['id2'];

$lastname = $_GET['id3'];

$location = $_GET['id4'];

$birthdate = $_GET['id5'];

$email = $_GET['id6'];

if ($_GET['submitted']) {

$id = $_GET['id9'];

$first_name = $_GET['first_name'];

$last_name = $_GET['last_name'];

$locations = $_GET['locations'];

$birthdate = $_GET['birthdate'];

$email = $_GET['email'];

if ($first_name && $last_name && $locations && $birthdate && $email) {

$update = "UPDATE Members SET

FirstName='$first_name',

LastName='$last_name',

Locations='$locations',

birthdate='$birthdate',

Email='$email' WHERE ID='$id'";

mysqli_query($Garydb, $update);

}

?>

<!doctype html>

<html>

<head>

<title>Update Members info</title>

</head>

<body>

<table>

<tr><td>Identify Number:</td><td name="id9"><?php echo $edit ?></td></tr>

<tr><td>First Name:</td><td><input type="text" name="first_name" value="<?php echo $firstname ?>"></td></tr>

<tr><td>Locations:</td><td><input type="text" name="locations" value="<?php echo $location ?>"></td></tr>

<tr><td>birthdate:</td><td><input type="text" name="birthdate" value="<?php echo $birthdate ?>"></td></tr>

<tr><td>Email:</td><td><input type="text" name="email" value="<?php echo $email ?>"></td></tr>

</table>

</form>

</body>

</html>

Psycho · October 5, 2015

Your UPDATE query has a WHERE clause as follows:

WHERE ID='$id'

The value of $id is defined here:

$id = $_GET['id9'];

However, there is no 'id9' field in your form. You provided a name for a TD element

<td name="id9"><?php echo $edit ?></td>

A TD element is not a form field and will not be included in the data passed with the form submission. You should use a hidden input field - although I usually use a text field at first so I can ensure the field is getting populated.

There is a lot wrong with what you are doing. I hope this is just a learning exercise.

Edited October 5, 2015 by Psycho

sigmahokies · October 5, 2015

Psycho,

All right, I changed a name id9 in the input hidden in the form, Reason I put id9 in TD because I don't want to have ID number change by update when it is in form text. Yes, This is just learning exercise. That is why I am practicing on PHP, because it is not easy to understand fully, because it is still complex to write the program in code. However, it has not update in the database...I guess something else goes wrong.

ginerjm · October 5, 2015

1 - you really should use the POST method and not GET.

2 - You retrieve data from a form via the name attribute. As said above, you don't need to name your td elements, only your inputs

3 - Don't understand what you are doing with the first block of code trying to obtain the id values and then the second one trying to get the properly named values. Don't understand at all. Good tip is to practice putting MEANINGFUL text comments in for each step of your thought process to make it easy to follow at a later date.

4 - Turn on php error checking to see if something pops out. Also add some echos to see what progress is actually being made through your script.

5 - Lastly - start to learn and use more modern html. While tables are generally frowned upon nowadays, the used of td to create a label and and a separate input element is also frowned on. Use the label tag and the input tag at the very least.

Lastly again - please use the proper forum tags when posting your code here. Also try to write your code a little neater - use indentation to make it clear what belongs to what. Such as:

if (.....)

{

line

}

else

{

line

}

and so on.

mac_gyver · October 6, 2015

Also, I set up the link from previous website like this: /insert.php?id=$row['id']&id2=$row['firstname']&id3=$row['lastname'], goes on. So, this link passed the data in the value to this update website.

your link should only contain the id, not all of the data. if you were doing this for real, ALL external data must be validated before you can use it. by only passing the id in the link, you only have to validate one value, a positive integer greater-than zero.

it would appear you are working on the U part of a CRUD (Create, Read, Update, Delete) exercise. the 'work-flow' steps should be -

1) retrieve a list of data and output an 'edit' link for each record. this link should only identify which record the link corresponds to.

2) in the 'edit' page display code, condition/validate the id, and retrieve the data from the database that the id corresponds to. populate the form field values with the retrieved data. this form should use method = 'post' since it will be altering data on the server.

3) in the 'edit' page form processing code, check that the correct post method form was submitted, validate all the input data, and if there are no validation errors, use the data in an UPDATE query. if there are validation errors, you would display them when you re-display the form so that the user can correct any problems with the submitted data. you should always have some type error handling for database queries, so that you will know if and why they are failing. you should also have php's error_reporting set to E_ALL and for development/debugging have display_errors set to ON and when on a live server, have display_errors set to OFF and log_errors set to ON.

if your code already had validation logic and separate error messages for each of the expected input data values and error handling for database queries, it (your code) would likely be telling you why it is failing.

also, if you were doing this for real, you would have a user/permission check to insure that the current visitor is authorized to perform each of the three steps of this work-flow and that for any id value, that the visitor is authorized to update the data for that id.

Edited October 6, 2015 by mac_gyver

sigmahokies · October 6, 2015

Mac_gyver,

your link should only contain the id, not all of the data. if you were doing this for real, ALL external data must be validated before you can use it. by only passing the id in the link, you only have to validate one value, a positive integer greater-than zero.

it would appear you are working on the U part of a CRUD (Create, Read, Update, Delete) exercise. the 'work-flow' steps should be -

1) retrieve a list of data and output an 'edit' link for each record. this link should only identify which record the link corresponds to.

2) in the 'edit' page display code, condition/validate the id, and retrieve the data from the database that the id corresponds to. populate the form field values with the retrieved data. this form should use method = 'post' since it will be altering data on the server.

Really? But it is working to pass all data to next page already. Only problem that I could not update as insert in the database that show the data from previous page, For example, I put all value in php like /insert.php?id=$row['id'] inside the link, but I was told that POST cannot be done in link, only GET can. That is why I wrote GET instead of POST.

Also, I am aware that GET is visible to anyone, POST is invisible to anyone, but inside link, only GET work. I am still learning to write code in PHP. I can do Dreamweaver CS6 and CC, but I notice that many companies are less favor in Dreamweaver, I notice they rather to have someone who can do code instead of design by software. I can do update in Dreamweaver, but write in the code to create the update page is more challenger than Dreamweaver.

I changed the method, so I hope my writing in PHP is more readable. I thought I am using table to make it neat in form, I notice without table, then form will go ugly. Should I use CSS to make it neat instead ofg

Here inside link:

this link will go in the HTML form, it is working passing from previous page (insert.php) to update page.

Here more NEAT writing in PHP in update.php for ginerjm

<?php

$Garydb = mysqli_connect("XXXXX","XXXXX","XXXXX") or die("Could not connect MySQL Database");

mysqli_select_db($Garydb, "XXXXX") or die("Could not find a Database");

$edit = $_GET['id'];

$firstname = $_GET['id2'];

$lastname = $_GET['id3'];

$location = $_GET['id4'];

$birthdate = $_GET['id5'];

$email = $_GET['id6'];

if ($_GET['submitted']) {

$id = $_POST['id9'];

$first_name = $_POST['first_name'];

$last_name = $_POST['last_name'];

$locations = $_POST['locations'];

$birthdate = $_POST['birthdate'];

$email = $_POST['email'];

if ($first_name && $last_name && $locations && $birthdate && $email) {

$update = "UPDATE Members SET FirstName='$first_name', LastName='$last_name', Locations='$locations', birthdate='$birthdate', Email='$email' WHERE ID='$id'";

mysqli_query($Garydb, $update);

<title>Update Members info</title>

</head>

<body>

<table>

<tr><td>Identify Number:</td><td><input type="hidden" name="id9" value="<?php echo $edit ?>"></td></tr>

<tr><td>First Name:</td><td><input type="text" name="first_name" value="<?php echo $firstname ?>"></td></tr>

<tr><td>Locations:</td><td><input type="text" name="locations" value="<?php echo $location ?>"></td></tr>

<tr><td>birthdate:</td><td><input type="text" name="birthdate" value="<?php echo $birthdate ?>"></td></tr>

<tr><td>Email:</td><td><input type="text" name="email" value="<?php echo $email ?>"></td></tr>

Psycho · October 6, 2015

Really? But it is working to pass all data to next page already. Only problem that I could not update as insert in the database that show the data from previous page, For example, I put all value in php like /insert.php?id=$row['id'] inside the link, but I was told that POST cannot be done in link, only GET can. That is why I wrote GET instead of POST.

sigmahokies,

You need to understand that just because something works does not mean it is correct. I can store a user's password in plain text in the database and it will work, but is absolutely wrong. Yes you "can" pass all of the data and it will work. But, it is not correct and will eventually cause you problems either in this project or another one some time later if you do the same thing. You should treat ALL user supplied data as "dirty". It could be that something is corrupted unintentionally or, worse, it malicious data intended to do harm.

For your page above, it should only accept the ID for the process of setting up the form for edit. The code should do a SELECT query to get the current data instead of relying upon data submitted by the user via $_GET.

Here is a quick rewrite of your code in a more logical format. This is not what I would consider complete, but is more complete than the current code and shows an example of a logical flow. There may be some minor errors in syntax as I did not test it.

<?php
 
$Garydb = mysqli_connect("XXXXX","XXXXX","XXXXX") or die("Could not connect MySQL Database");
mysqli_select_db($Garydb, "XXXXX") or die("Could not find a Database");
 
$response = '';
 
if(isset($_GET['id']))
{
    //User passed the ID of a record to be updated
    //Get current values to populate form field
    $id = intval($_GET['id']);
$query = "SELECT First_name, Last_name, Locations, birthdate, Email
         FROM
 WHERE ID = {$id}";
    $result = mysqli_query($Garydb, $query);
if(!mysqli_num_rows($result))
{
        //No record matching the passed ID
        $response = 'Error: No record matching requested id.';
}
else
    {
        //Define variables for form fields from current DB result
        $row = mysqli_fetch_assoc($result);
        $first_name = $row['first_name'];
        $last_name  = $row['last_name'];
        $locations  = $row['locations'];
        $birthdate  = $row['birthdate'];
        $email      = $row['email'];
    }
}
elseif ($_SERVER['REQUEST_METHOD']!='POST')
{
    //User posted a form if data to be updated for a record
    $id         = intval($_POST['id']);
    $first_name = trim($_POST['first_name']);
    $last_name  = trim($_POST['last_name']);
    $locations  = trim($_POST['locations']);
    $birthdate  = trim($_POST['birthdate']);
    $email      = trim($_POST['email']);
 
    if ($id && $first_name && $last_name && $locations && $birthdate && $email)
{
        //All the posted value are not empty/zero
        //Should really have better validation logic
        $update = "UPDATE Members
          SET FirstName = '$first_name', 
                       LastName  = '$last_name', 
                       Locations = '$locations', 
                       birthdate = '$birthdate', 
                       Email     = '$email'
  WHERE ID = $id";
        if(!mysqli_query($Garydb, $query)  || )
        {
            //Query failed
            $response = 'Error: Unable to update record.';
        }
        elseif(!mysqli_affected_rows($link))
        {
            //No records were updated. Record may have been deleted or ID manipulated
            $response = 'Error: No records updated.';
        }
        else
        {
            $response = 'Record was updated.';
        }
    }
else
{
        //Not all fields have values or non-zero
   $response = 'Error: Missing data required for update.';
}
}
else
{
    //No POST or GET data submitted
    $response = 'Error: No data received.';
}
?>
<!doctype html>
<html>
<head>
<title>Update Members info</title>
</head>
<body>
<?php
 
    //If there was a response, show it. Else show form.
    if($response)
    {
        echo "<div style=\"color:red;\">{response}</div>";
        //Could add a link to go back to a listing page or somewhere appropriate
    }
    else
    {
 
?>
 
<form action="insert.php" method="POST">
<table>
<tr>
<td>Identify Number:</td>
<td><?php echo $id ?><input type="hidden" name="id" value="<?php echo $id; ?>"</td>
</tr>
<tr>
<td>First Name:</td>
<td><input type="text" name="first_name" value="<?php echo $first_name; ?>"></td>
</tr>
<tr>
<td>Last Name:</td>
<td><input type="text" name="last_name" value="<?php echo $last_name; ?>"></td>
</tr>
<tr>
<td>Locations:</td>
<td><input type="text" name="locations" value="<?php echo $locations; ?>"></td>
</tr>
<tr>
<td>birthdate:</td>
<td><input type="text" name="birthdate" value="<?php echo $birthdate; ?>"></td>
</tr>
<tr>
<td>Email:</td>
<td><input type="text" name="email" value="<?php echo $email; ?>"></td>
</tr>
<tr>
<td colspan="2"><button type="submit">Update</button></td>
</tr>
</table>
</form>
<?php
    }
?>
</body>
</html>

sigmahokies · October 6, 2015

Look like i have to go to school that studying in PHP, I will visit Radford university soon as I can.

mac_gyver · October 7, 2015

Here more NEAT writing in PHP in update.php for ginerjm

except that your form's action="insert.php" attribute is submitting to insert.php, not update.php.

sigmahokies · October 7, 2015

Psycho,

your script in PHP about passing the value, it got few error but i fixed it, it works! seem it is not require to have pass all value; just select ID, then pass one value to other, then use SQL with ID, then all data has been place in the HTML form. Now, I am testing to UPDATE the data in the database. Thank you!

Gary

Sign In

update the data in the database from the HTML form

Recommended Posts

sigmahokies

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

sigmahokies

Link to comment

Share on other sites

ginerjm

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

sigmahokies

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

sigmahokies

Link to comment

Share on other sites

mac_gyver

Link to comment

Share on other sites

sigmahokies

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information