I only want to update one MySQL row with PHP

Question

I am working on a page that I need to be able to update single records from my MySQL database using PHP. Can someone please help me? When I try to update one record all my other records are updated.

    <form action="" method="post">
    <input type="hidden" name="id" value="<?php echo $id; ?>"/>
    <div>
    <p><strong>ID:</strong> <?php echo $id; ?></p>
    <strong>Name: *</strong> <input class="form-control" type="text" name="name" value="<?php echo $name; ?>" /><br/>
    <strong>Month: *</strong> <input class="form-control" type="text" name="month" value="<?php echo $month; ?>" /><br/>
    <strong>event1: *</strong> <input class="form-control" type="text" name="event1" value="<?php echo $event1; ?>" /><br/>
    <strong>event2: </strong> <input class="form-control" type="text" name="event2" value="<?php echo $event2; ?>" /><br/>
    <strong>event3: </strong> <input class="form-control" type="text" name="event3" value="<?php echo $event3; ?>" /><br/>
    <strong>event4: </strong> <input class="form-control" type="text" name="event4" value="<?php echo $event4; ?>" /><br/>
    <strong>timesub: </strong> <input class="form-control" type="text" name="timesub" value="<?php echo $timesub; ?>" readonly /><br/>
    <p>* Required</p>
    <input type="submit" name="submit" value="Submit" class="btn btn-info">
    <input type=reset name="reset" value="Reset" class="btn btn-danger">
    </div>
    </form>

That is my form, and then I follow it with this code:

    <?php
    }
    include('db_connect.php');

    if (isset($_POST['submit'])) {

    // confirm that the 'id' value is a valid integer before getting the form data
    if (is_numeric($_POST['id'])) {

    $id = $_POST['id'];
    $name = mysql_real_escape_string(htmlspecialchars($_POST['name']));
    $month = mysql_real_escape_string(htmlspecialchars($_POST['month']));
    $event1 = mysql_real_escape_string(htmlspecialchars($_POST['event1']));
    $event2 = mysql_real_escape_string(htmlspecialchars($_POST['event2']));
    $event3 = mysql_real_escape_string(htmlspecialchars($_POST['event3']));
    $event4 = mysql_real_escape_string(htmlspecialchars($_POST['event4']));
    $timesub = mysql_real_escape_string(htmlspecialchars($_POST['timesub']));

    // check thatfields are filled in
    if ($name == '' || $month == '' || $event1 == '' || $timesub == ''){

    // generate error message
    $error = 'ERROR: Please fill in all required fields!';

    //error, display form
    renderForm($id, $name, $month, $event1, $event2, $event3, $event4, $timesub, $error);
    } else {
    // save the data to the database
    mysql_query("UPDATE announcement SET name='$name', month='$month', event1='$event1', event2='$event2', event3='$event3', event4='$event4', timesub='$timesub'")
    or die(mysql_error());

    // once saved, redirect back to the view page
    header("Location: view.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 = mysql_query("SELECT * FROM announcement WHERE id=$id")
    or die(mysql_error());

    $row = mysql_fetch_array($result);

    // check that the 'id' matches up with a row in the databse
    if($row) {

    // get data from db
    $name = $row['name'];
    $month = $row['month'];
    $event1 = $row['event1'];
    $event2 = $row['event2'];
    $event3 = $row['event3'];
    $event4 = $row['event4'];
    $timesub = $row['timesub'];

    // show form
    renderForm($id, $name, $month, $event1, $event2, $event3, $event4, $timesub, '');

    } 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!';

    }
    }
    ?>

I got the code from: http://www.killersites.com/community/index.php?/topic/1969-basic-php-system-vieweditdeleteadd-records/

Great article!

Thanks in advance for the help.

You need to learn basic SQL, specifically [WHERE](https://en.wikipedia.org/wiki/Where_(SQL)) clauses. And note that you're vulnerable to [sql injection attacks](http://bobby-tables.com). — Marc B, Oct 06 '16 at 19:39
Because u need to add where clause in query like `WHERE id = $id` . also note that mysql_* is deprecated and closed in Php 7. Use mysqli_* or PDO — devpro, Oct 06 '16 at 19:39
Thanks Marc B; I am just now starting with SQL, where is my vulnerability to the SQL injection? — Sam, Oct 06 '16 at 19:44
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Oct 06 '16 at 20:10

score 3 · Accepted Answer · edited May 23 '17 at 11:48

3

First of all stop using mysql_* its deprecated and closed in PHP 7. You can use mysqli_* or PDO.

Whats wrong with your query:

This is very important, if you not use WHERE CLAUSE in your UPDATE STATEMENT than it will update the all rows.

You must need to add WHERE CLAUSE in your query at end, something like:

WHERE id = '$id'

Also note that, your code is open for SQL INJECTION, you must need to prevent with SQL INJECTION, you can learn about the prepared statement. This post will help you to understand: How can I prevent SQL injection in PHP?

*If you want to know How mysqli_* works, this document will help you: http://php.net/manual/en/book.mysqli.php

If you want to work on PDO, you can follow this manual: http://php.net/manual/en/book.pdo.php

edited May 23 '17 at 11:48

Community

1
1

answered Oct 06 '16 at 19:46

devpro

16,184
3
27
38

How would I change the current code into mysqli_* ? – Sam Oct 06 '16 at 22:06
@SamFonseca: chk the reference links which i have shared in bottom – devpro Oct 07 '16 at 06:09
Thank you, I am checking them... I am very new to SQL; I only used basic commands such as UPDATE, SELECT, and INSERT INTO – Sam Oct 07 '16 at 17:32
@SamFonseca if answer works for u accept it. Will help to others – devpro Oct 07 '16 at 17:44

score 0 · Answer 2 · answered Oct 06 '16 at 19:41

0

Take a close look at your query:

mysql_query("UPDATE announcement SET name='$name', month='$month', event1='$event1', event2='$event2', event3='$event3', event4='$event4', timesub='$timesub'")

Your query is missing a WHERE clause. Without a WHERE clause, your query will update all rows in the table announcement. You need to add something like:

WHERE id='$id'

This will restrict the update to only the row you want to update.

answered Oct 06 '16 at 19:41

Robert Columbia

6,313
15
32
40

Glad I could help. – Robert Columbia Oct 06 '16 at 19:44

I only want to update one MySQL row with PHP

2 Answers2