我只想用PHP更新一个MySQL行

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.

Answer 1

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