UPDATE data in the database

Question

I want to show the selected ID data in the form and EDIT it and UPDATE in the database. I selected the data from the database and put it in the input tag but it doesn't work. Please help!

<html>
<body>
<?
$db = mysql_connect("localhost", "root","");
mysql_select_db("db_ncs",$db);
$id = $_GET['s_id'];
if($id)
{
    $result=mysql_query("SELECT * FROM tbl_student WHERE s_id=$id");
    $row = mysql_fetch_assoc($result);
}
?>

<form method="post" action="update.php">
    Name:<input type="Text" name="name" value="<?php echo $row['s_name'];?>" /><br>
    Contact:<input type="Text" name="contact" value="<?php echo $row['s_contact'];?>" /><br>
    Address:<input type="Text" name="address" value="<?php echo $row['s_address'];?>" /><br>
    E-mail:<input type="Text" name="email" value="<?php echo $row['s_email'];?>" /><br>
    <input type="submit" name="update" value="Update">
</form>

<?
if(isset($_POST['update']))
    {
    $name = $_POST['s_name'];
    $contact = $_POST['s_contact'];
    $address = $_POST['s_address'];
    $email = $_POST['s_email'];
    $sql = "UPDATE tbl_student
            SET (s_name='$name', s_contact='$contact', s_address='$address', s_email='$email')
            WHERE s_id=$id";
    $res = mysql_query($sql);       
    if($res)
        {
            echo "Upadate Successfull!";
        }
        else
        {
            echo "Sorry!";
        }
    }

?>

</body>
</html>

Your SQL query is not valid. You have to add quotes around string values. — Florent, Jul 12 '12 at 10:13
This code is susceptible to sql injection - http://stackoverflow.com/questions/60174/best-way-to-prevent-sql-injection-in-php — Will03uk, Jul 12 '12 at 10:23

score 2 · Accepted Answer · answered Jul 12 '12 at 10:13

2

You forgot to pass the id.

Add this between the <form> tags.

<input type="hidden" name="s_id" value="<?php echo $id;?>" />

You also need to make your methods consistent. The form submits the data via method="get" but you ask for it via $_POST. You also need to make the input names consistent with the names you ask for, by either adding or removing the "s_" in the appropriate places.

answered Jul 12 '12 at 10:13

Ruben

3,452
31
47

but why type=hidden and name=s_id – manishrestha529 Jul 12 '12 at 10:16
@mani Because you don't want the user to change the data of an item he hasn't viewed, so he shouldn't change it while editing. `name=s_id` because that's what you called it in the `$_GET` statement. It doesn't need to be any specific value, the just have to match. – Ruben Jul 16 '12 at 08:14

Florent · Answer 2 · 2012-07-12T10:24:17.610

In the form you have method="get" but you use $_POST in your PHP code. Try to define your form as below:

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

Your SQL query should be (added quotes):

$sql = "UPDATE tbl_student
        SET (s_name='$name', s_contact='$contact', s_address='$address', s_email='$email')
        WHERE s_id=$id";

Try adding this after mysql_query:

 $result = mysql_query($sql) or die(mysql_error());

Do not use mysql_* functions, they are no longer maintained: use PDO of MySQLi.

score 0 · Answer 3 · answered Jul 12 '12 at 10:21

Not really an answer to your question, but i have to point you to some omissions in your code:

if $_POST['update'] is set, that doesn't mean the other variables are also set. They can be empty if user didn't enter anything in a field. You should check if every $_POST or $_GET variables are set by using isset or empty.
your code is so insecure! You should escape every variable before using it in a query. Use mysql_real_escape_string() for that. I also suggest you to use strip_tags() along with escaping.

Milindu Sanoj Kumarage · Answer 4 · 2012-07-12T11:45:32.720

Doesn't he have to use the $row = mysql_fetch_assoc($result) to get the results?

// Perform Query
$result = mysql_query($query);

// Check result
// This shows the actual query sent to MySQL, and the error. Useful for debugging.
if (!$result) {
    $message  = 'Invalid query: ' . mysql_error() . "\n";
    $message .= 'Whole query: ' . $query;
    die($message);
}

// Use result
// Attempting to print $result won't allow access to information in the resource
// One of the mysql result functions must be used
// See also mysql_result(), mysql_fetch_array(), mysql_fetch_row(), etc.
while ($row = mysql_fetch_assoc($result)) {
    echo $row['firstname'];
    echo $row['lastname'];
    echo $row['address'];
    echo $row['age'];
}

http://php.net/manual/en/function.mysql-query.php

above is just an example.

update:

$result=mysql_query("SELECT * FROM tbl_student WHERE s_id=$id");

$row = mysql_fetch_assoc($result); //  I think you have to add this line here, don't you?
?>



<form method="post" action="update.php">
    <input type="hidden" name="s_id" value="<?php echo $id;?>" />
    Name:<input type="Text" name="name" value="<?php echo $row['s_name'];?>" /><br>
    Contact:<input type="Text" name="contact" value="<?php echo $row['s_contact'];?>" /><br>
    Address:<input type="Text" name="address" value="<?php echo $row['s_address'];?>" /><br>
    E-mail:<input type="Text" name="email" value="<?php echo $row['s_email'];?>" /><br>
    <input type="submit" name="update" value="Update">
</form>

update 2:

when you are going to update, the method up there $id = $_GET['s_id']; is still looking for a param called 's_id' will come via HTTP GET, but it doesn't!

a quick workaround may be this, <form method="post" action="update.php?<?php echo $id;?>">

and don't forget to add,

$id= $_POST['s_id']; after $email = $_POST['s_email'];!

update 3:

Hmm, You still need this <input type="hidden" name="s_id" value="<?php echo $id;?>" /> and don't forget to add,

$id= $_POST['s_id']; after $email = $_POST['s_email'];!

`$row = mysql_fetch_assoc($result);` I think you have to add this line, don't you? — Milindu Sanoj Kumarage, Jul 12 '12 at 10:38
and notice the `$result=mysql_query()` , assignment of the resultSet to var `$result` — Milindu Sanoj Kumarage, Jul 12 '12 at 10:45
I get this error while updating...... Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\wamp\www\ncs\update.php on line 8 — manishrestha529, Jul 12 '12 at 10:49
I have edited the post... everything is fine but when i press UPDATE it gives me SORRY... — manishrestha529, Jul 12 '12 at 11:21

score 0 · Answer 5 · answered Jul 12 '12 at 12:09

Your form has fields like name="contact", but when you try to get the values you use $_POST['s_contact']. These need to match.

The reason you need the hidden s_id field in the form is so that you will update the same row that was edited. Your UPDATE statement contains WHERE s_id=$id, so you need to get the original id this way. It's hidden because you don't want the user to be able to change the ID when editing.

UPDATE data in the database

5 Answers5