My Update statement in PHP does not update the database

Question

When I echo the output the desired updated value is displayed. But when the same value is passed to update the database the update statement does nothing.

The portion of the code is

if (empty($_POST) === false)
{
    $fname= $_POST['fname'];
    $srno=  $_POST['SRNO'];
    echo $fname.' and'. $srno;

    mysql_query('update names set fname="$fname" where SRNO="$srno"');          
}

and the complete code is

<!DOCTYPE html>
<html>
    <head>
        <title>List of users</title>
    </head>
<body>
    <?php
        mysql_connect("localhost","root","") or die (mysql_error());
        mysql_select_db("list") or die (mysql_error());

        if (empty($_POST) === false)
        {
            $fname= $_POST['fname'];
            $srno=  $_POST['SRNO'];
            echo $fname.' and'. $srno;

            mysql_query('update names set fname="$fname" where SRNO="$srno"');          
        }


        if(isset($_GET['edit']))
        {
            $getedit=mysql_query('SELECT SRNO, fname, lname, phone, email from names where SRNO='.mysql_real_escape_string((int)$_GET['edit']));
            while ($get_row=mysql_fetch_assoc($getedit))
                {
                    echo '<form method="POST" action="">';
                    echo 'Sr. No: '.$get_row['SRNO'].'<br />';
                    echo 'Sr.No:<input type="text" value='.$get_row['SRNO'].' name="SRNO" readonly="readonly">';
                    echo 'First Name: <input type="text" value='.$get_row['fname'].' name="fname"><br />';
                    echo '<input type="submit" name="submit" value="save">';
                    echo '</form>';                                 
                }   

        }

        $get=mysql_query('SELECT  SRNO, fname, lname, email, phone, address, comments from names ORDER BY SRNO ASC');

        if (mysql_num_rows($get)==0)
        {
            echo 'There are no entries';
        }
            else
        {
            echo '<table border=0>';
            echo'<tr><th>Sr. No</th><th>First Name</th><th>Last Name</th><th>Phone No</th><th>E-mail</th><th>Modify</th></tr>';     
            while($get_row=mysql_fetch_assoc($get))
                {
                    echo '<tr><td>'.$get_row['SRNO'].'</td><td>'.$get_row['fname'].'</td><td>'.$get_row['lname'].'</td><td>'.$get_row['phone'].'</td><td>'.$get_row['email'].'</td><td><a href="index.php?edit='.$get_row['SRNO'].'">Edit</a></td></tr>';

                }
            echo '</table>';
        }       
    ?>

</body> 
</html>

Your script is prone to sql injections. For the first query, `update names....`, you haven't used mysql_real_escape_string(), but should have. For the second query, `SELECT ... where SRNO=`, you have used mysql_real_escape_string but should not - or should have made that parameter a string literal. Please read up on how to sanitize/encode those queries and/or on prepared statements. — VolkerK, Mar 10 '13 at 20:24
***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, Feb 28 '18 at 17:28
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Feb 28 '18 at 17:29

score 1 · Answer 1 · edited Feb 28 '18 at 17:29

1

Variable names are not replaced within single quotation marks with their value. Try this:

update names set fname="'.$fname.'" where SRNO="'.$srno.'"

edited Feb 28 '18 at 17:29

Jay Blanchard

34,243
16
77
119

answered Mar 10 '13 at 20:15

Zoltán Király

259
1
12

score 0 · Answer 2 · answered Mar 10 '13 at 20:19

try this it may help you,

<!DOCTYPE html>
<html>
 <head>
    <title>List of users</title>
</head>
<body>
<?php
    mysql_connect("localhost","root","") or die (mysql_error());
    mysql_select_db("list") or die (mysql_error());

    if (isset($_POST['fname']))
    {
        $fname= $_POST['fname'];
        $srno=  $_POST['SRNO'];
        echo $fname.' and'. $srno;

        mysql_query("update `names` set fname='$fname' where SRNO='$srno'");          
    }


    if(isset($_GET['edit']))
    {
        $getedit=mysql_query('SELECT SRNO, fname, lname, phone, email from names where     SRNO='.mysql_real_escape_string((int)$_GET['edit']));
        while ($get_row=mysql_fetch_assoc($getedit))
            {
                echo '<form method="POST" action="">';
                echo 'Sr. No: '.$get_row['SRNO'].'<br />';
                echo 'Sr.No:<input type="text" value='.$get_row['SRNO'].' name="SRNO" readonly="readonly">';
                echo 'First Name: <input type="text" value='.$get_row['fname'].' name="fname"><br />';
                echo '<input type="submit" name="submit" value="save">';
                echo '</form>';                                 
            }   

    }

    $get=mysql_query('SELECT  SRNO, fname, lname, email, phone, address, comments from names ORDER BY SRNO ASC');

    if (mysql_num_rows($get)==0)
    {
        echo 'There are no entries';
    }
        else
    {
        echo '<table border=0>';
        echo'<tr><th>Sr. No</th><th>First Name</th><th>Last Name</th><th>Phone No</th><th>E-mail</th>   <th>Modify</th></tr>';     
        while($get_row=mysql_fetch_assoc($get))
            {
                echo '<tr><td>'.$get_row['SRNO'].'</td><td>'.$get_row['fname'].'</td><td>'.$get_row['lname'].'</td><td>'.$get_row['phone'].'</td><td>'.$get_row['email'].'</td><td><a href="index.php?edit='.$get_row['SRNO'].'">Edit</a></td></tr>';

            }
        echo '</table>';
    }       
?>

score 0 · Answer 3 · answered Mar 10 '13 at 20:19

I would suggest you echo out the query when you have problems so you can copy/paste it in to the mysql client and test for errors.

Try changing it to:

$query = "update names set fname='$fname' where SRNO='$srno'";
echo $query;
mysql_query($query);

I would recommend using single quotes in your query, too (like my example above). I'm not sure how well MySQL handles double quotes, or if it does at all. I've always made it a habit to use single quotes. Maybe that's just a personal preference.

My Update statement in PHP does not update the database

3 Answers3