MySQL Table Update with PHP HTML Form isn't Working

Question

I am trying to update mySQL table using a form with PHP. Currently I have all the code set up but when I am updating my table data, age, it sets all the ages in the table to '0'. I am not sure why but any guidance would be strongly appreciated. Thanks.

Kelsey

<?php
    $hostname = "---------";//host name
    $dbname = "-------";//database name
    $username = "-------------";//username you use to login to php my admin
    $password = "--------";//password you use to login

    //CONNECTION OBJECT
    //This Keeps the Connection to the Databade
    $conn = new MySQLi($hostname, $username, $password, $dbname) or die('Can not connect to database')      
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<body>
<?php

$id=$_GET['FirstName'];

//Create a query
$sql = "SELECT * FROM Persons";
//submit the query and capture the result
$result = $conn->query($sql) or die(mysql_error());
$query=getenv(QUERY_STRING);
parse_str($query);
?>
<h2>Update Record <?php echo $sql;?></h2>
<form action="" method="post">
<?php


    while ($row = $result->fetch_assoc()) {?>

<table border="0" cellspacing="10">
<tr>
<td>age:</td> <td><input type="text" name="Age" value="<?php echo $row['Age']; ?>"></td>
</tr>
<tr>
<td><INPUT TYPE="Submit" VALUE="Update the Record" NAME="Submit"></td>
</tr>
</table>
<?php   }
    ?>
</form>
<?php
    if(isset($_POST['Submit'])){//if the submit button is clicked

    $sql="UPDATE Persons SET Age='".$_POST['Age']."'";
    $conn->query($sql) or die("Cannot update");//update or error
    }
?>


</body>
</html>

It looks like you're using PDO, which is good, but you're not using prepared statements and have created a gigantic SQL injection bug. NEVER put $_POST or $_GET data directly in a query. — tadman
– tadman, Commented Feb 4, 2014 at 16:48
When I update any age other than the one at the very bottom and I refresh the page afterwards it still keeps the age unupdated but instead resets the very bottom age back to zero. — Kelsey
– Kelsey, Commented Feb 4, 2014 at 18:17

mittens · Accepted Answer · 2014-02-04 16:40:02Z

1

The UPDATE query as its written right now updates the entire Persons table, not an individual record.

UPDATE Persons SET Age=15 WHERE id = 5 will only update one record as apposed to the entire table's values.

Also, it's not good (aka massive security risk) to put raw post values directly into an SQL string. You should always sanity check your values before putting them into a database query.

answered Feb 4, 2014 at 16:40

mittens

7466 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kelsey Over a year ago

Thank you but it is still saying it cannot update. Although when I put in your code it changed the zeros to 43's. Strange.

remedy. · Accepted Answer · 2014-02-04 17:47:02Z

0

Not a good way to add the POST inside the statement itself.

Try this:

<?php
if(isset($_POST['Submit'])){//if the submit button is clicked

$age = $_POST['Age'];

$sql="UPDATE Persons SET Age='".$age."' WHERE firstname ='".$id."'";
$conn->query($sql) or die("Cannot update");//update or error
}
?>

Your page is confusing. You didn't even output the result correctly. Try this:

<?php
$hostname = "---------";//host name
$dbname = "-------";//database name
$username = "-------------";//username you use to login to php my admin
$password = "--------";//password you use to login

//CONNECTION OBJECT
//This Keeps the Connection to the Databade
$conn = new MySQLi($hostname, $username, $password, $dbname) or die('Can not connect to database')      
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
<title>Untitled Document</title>
</head>
<body>

<?php

//$id=$_GET['FirstName'];

//Create a query
$sql = "SELECT * FROM Persons";
//submit the query and capture the result
$result = $conn->query($sql) or die(mysql_error());
$query=getenv(QUERY_STRING);
parse_str($query);
?>
<h2>Update Record <?php echo $sql;?></h2>
<form action="" method="post" enctype='multipart/form-data'>
<?php


while ($row = $result->fetch_assoc()) {?>

<table border="0" cellspacing="10">
<tr>
<td>Age:</td>
<td><?php echo $row['Age'];?></td> 
<td><?php echo $row['FirstName'];?></td>
<td><input type="text" name="Age"></td>
<td><input type=hidden" name="firstName" value="<?php echo $row['FirstName'];?>"></td>

</tr>

<tr>
<td><INPUT TYPE="Submit" VALUE="Update the Record" NAME="Submit"></td>
</tr>
</table>
<?php   }
?>
</form>

<?php
if(isset($_POST['Submit'])){//if the submit button is clicked
$age = $_POST['Age'];
$id = $_POST['firstName'];

$sql="UPDATE Persons SET Age='".$age."' WHERE firstname ='".$id."'";
$conn->query($sql) or die("Cannot update");//update or error
}
?>


</body>
</html>

edited Feb 4, 2014 at 17:47

answered Feb 4, 2014 at 16:56

remedy.

2,0223 gold badges28 silver badges50 bronze badges

15 Comments

Kelsey Over a year ago

Still doesn't work... I am so lost. I have the columns in my table as FirstName LastName and Age... I go to my webpage at thetotempole.ca/phptester/editpage.php and type something in and nothings updated, it also says cannot update.

remedy. Over a year ago

Also, I'm assuming that this is the entire code for your page. You used $id = $_GET['firstname']; Where did you get it from?

Kelsey Over a year ago

Okay, I implemented the updated code and it is no longer giving me errors, it is just not updating the ages. I got that from another code source that I was trying to base mine off of. FirstName is one of my columns for my table. So I guess I was trying to distinguish each input by their first name.

remedy. Over a year ago

Okay, hang on a moment.

Kelsey Over a year ago

How do i make it so WHERE FirstName is = to the row of the age input you are editing? Okay, take your time.

|

Collectives™ on Stack Overflow

MySQL Table Update with PHP HTML Form isn't Working

2 Answers 2

1 Comment

15 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

15 Comments

Your Answer

Sign up or log in

Post as a guest

Related