PHP无法更新SQL

Question

I'm using a php part in my site, where I have a textarea that get a text from a database. The user can edit this text and after he finish press the save button, and using UPDATE I will change the text in the database. Here is my code:

<?php 
    $con=mysqli_connect("localhost","userdb","codedb","projectdb");
    mysqli_set_charset($con, 'utf8');  
    // Check connection
    if (mysqli_connect_errno()) {
        echo "Failed to connect to MySQL: " . mysqli_connect_error();
    }


    $myQueryfac="SELECT text FROM main WHERE id=1";
    $result = mysqli_query($con,$myQueryfac);

    while($row = mysqli_fetch_array($result)) { 
        $t1=$row['text'];
    }

    $form="<form action='adminindex.php' method='post'>
        <textarea name='area1' maxlength='1500' cols='50' rows='10'>$t1</textarea>
        <input type='submit' name='enter' value='Save'>
        </form>";


    if($_POST['enter']) {
        $t1=$_POST['area1'];
        mysqli_query($con,"UPDATE main SET text='$t1' WHERE id='1'");
    }

    echo $form;
    mysqli_close($con);
?>

My problem is in the UPDATE query it seems like it ignores $t1 and nothing change in database. But if I put something random in there, "RANDOM TEXT", change it successful.

Answer 1

This is how you do it:

test.php

// DB Connect
$con=mysqli_connect("localhost","userdb","codedb","projectdb");
mysqli_set_charset($con, 'utf8');  
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Handle POST
if (count($_POST))
{
    // Save In DB
    mysqli_query($con, sprintf("UPDATE main SET `text`='%s' WHERE id=%d",
        mysqli_real_escape_string($con, $_POST['area1']),
        1)); // id

    // Success
    echo "<p>Data updated.</p>";
}

// Load Existing Data
$myQueryfac="SELECT `text` FROM main WHERE id=1";
$result = mysqli_query($con, $myQueryfac);
$row = mysqli_fetch_array($result);

// Display Form
echo "<form action='test.php' method='post'>
    <textarea name='area1' maxlength='1500' cols='50' rows='10'>". $row['text'] ."</textarea>
    <input type='submit' name='enter' value='Save'>
    </form>";

// DB Close
mysqli_close($con);

?>

---

What I've changed

• Moved the post hander up (above the select statement), so that if an update occurs, the form will show the latest updated data
• Your update query was treating the id as string, I formatted it to be a digit (%d)
• Removed the while loop, you don't need it as it is a single row being returned
• added sql-injection prevention (using sprintf and mysqli_real_escape_string)
• added backticks `` around the db field name text (wasn't sure if this is a reserved word, because it's one of the sql data types)