unable to update in database

Question

I have problem the data is not updating in the database .

I got this message :

0 rows have been updated with: update studyplan set complated = '', passed = '' where courseid = '41'

<body>
<form action="up.php" name="frmAdd" method="post">
    <div align='center'>
        <table border='1' cellpadding='5' cellspacing='1' id='mytable'>
            <tr align='center'>
                <th>courseid</th>
                <th>code</th>
                <th>Title</th>
                <th>Cr</th>
                <th>prerequisite</th>
                <th>STDid</th>
                <th>complated</th>
                <th>passed</th>
            </tr>
<?php
error_reporting(0);
include('config.php');
$sql="select * from studyplan";
$result=mysql_query($sql);
while ($row = mysql_fetch_array($result)){
    $id=$row['courseid'];
    $code=$row['code'];
    $Title=$row['title'];
    $cr=$row['cr'];
    $pre=$row['prerequisite'];
    $std=$row['std_id'];
    $complated=$row['complated'];
    $passed=$row['passed'];
    echo "
            <tr  class='edit_tr' id='$id'>
                <td class='edit_td'>
                    <span class='text' id='one_$id' >$id </span>
                </td>
                <td class='edit_td'>
                    <span class='text' id='one_$id' >$code </span>
                </td>
                <td class='edit_td'>
                    <span class='text' id='two_$id' >$Title</span>
                </td>
                <td class='edit_td'>
                    <span class='text' id='three_$id' >$cr</span>
                </td>
                <td class='edit_td'>
                    <span class='text' id='three_$id' >$pre</span>
                </td>
                <td class='edit_td'>
                    <span class='text' id='three_$id' >$std</span>
                </td> 
                <td class='edit_td'>
                    <span class='text' id='three_$id' ></span>
                    <select name='complated' id='complated'>
                        <option value=''>Yes</option>
                        <option value=''>No</option>
                    </select>
                </td>
                <td class='edit_td'>
                    <span class='text' id='three_$id' ></span>
                    <select name='passed' id='passed'>
                        <option value=''>Yes</option>
                        <option value=''>No</option>
                    </select>
                </td> 
            </tr>";  
}

echo '<input type="hidden" name="courseid" value="' . $id . '" />';
?>
                <p align="center"><a href="year.html">Go Back</a> </p>
                <input type="submit" name="submit" value="save" class="button">
            </tr>
        </table>
    </form>
</div>
</body>
</html>

PHP update for complete and passed selection option when user click yes or no.

<?php 
$usr    =   "fsdf";
$pwd    =   "dfg";
$db     =   "data6";
$host   =   "localhost";
$cid    =   mysql_connect($host,$usr,$pwd);

if (!$cid) {
    echo("ERROR: " . mysql_error() . "\n");
}
?>

<?php 
if ($_SERVER['REQUEST_METHOD'] == "POST") { 
    $id=$_POST['courseid'];
    $complated=$_POST['complated'];
    $passed=$_POST['passed'];

    $SQL= "update studyplan ";
    $SQL.= " set complated = '".$complated."', ";
    $SQL .= " passed =  '".$passed."' ";
    $SQL .= "where courseid = '".$id."' ";

    $result = mysql_db_query($db,"$SQL",$cid);

    if($result) {
         echo mysql_affected_rows() . " rows have been updated with: $SQL";
    } else {
        echo "data has not been updated";
    }
}
?>

$result = mysql_db_query($db,"$SQL",$cid); should be $result = mysql_db_query($db,**$SQL**,$cid); — Pathik Vejani, Dec 23 '15 at 06:28
Do you see the updated results if you run the SELECT query again in: 1. Code 2. Phpmyadmin — Ozair Kafray, Dec 23 '15 at 06:28
Please fix your HTML & stop using deprecated `mysql_*` functions. The codes are poorly written. Consider rewriting it — Raptor, Dec 23 '15 at 06:29
@OzairKafray No i can not see the update. its show the data already stored in database without change. — user5495621, Dec 23 '15 at 06:39
Try using some debugging lines in your code eg after creating your $SQL string add echo $SQL . "
\n"; Then run it and copy the string printed to the screen and try it in phpMyAdmin to see what happens. — Trevor, Dec 23 '15 at 06:53
To debug, replace `echo "Data has been updated";` with `echo mysql_affected_rows() . " rows have been updated with: $SQL";` and put in your question what you get. — trincot, Dec 23 '15 at 07:46
Thanks for adding the output, now check in your table whether you have a record with id 41 in phpMyAdmin, and also execute that SQL statement directly there to see if it produces any different result. If it is the same (no updated records) it has nothing to do with PHP. — trincot, Dec 23 '15 at 08:17
Related: [Why shouldn't I use mysql_* functions in PHP?](https://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php) and [How can I prevent SQL-injection in PHP?](https://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php). — Hexaholic, Dec 23 '15 at 08:19
@ trincot . Thanks for your Debug statement. In database no update . The stored data for complete and passed is No I wanted to user to select option Yes than that yes will be updated instead of the No . But its not coming the selection user clicked . What is the ideas of doing that ? Its not the update i wrote ? How I can reach what I want ? — user5495621, Dec 23 '15 at 08:36
@trincot . You mean do the update in the phpMyAdmin. Yes i did and its work there 1 row affected. (Query took 0.1900 sec) UPDATE `studyplan` SET `complated` = 'Yes', `passed` = 'No' WHERE courseid =33. I want to do same by php .How ? — user5495621, Dec 27 '15 at 03:10
Wait, you tried with `courseid=33`, while your debug message is about `courseid=41`. I would like to know what happens when executing *exactly the same* SQL in phpAdmin as in PHP, — trincot, Dec 27 '15 at 08:21
There are also problems with your HTML: you have more closing `TR` tags than opening. If you have more than one data row, the *id* attribute values will not be unique, which is not allowed in HTML. The "go back" link and submit button occur at an invalid position in HTML. If you have more than one row, PHP will get multiple rows to process after submit, which your code does not do. You should use array notation for your names. In short, there are just too many issues for one question. — trincot, Dec 27 '15 at 08:48
And... it is a bad idea to set error reporting to zero when you are trying to debug, like now. Remove that line. — trincot, Dec 27 '15 at 08:58

score 0 · Answer 1 · edited May 23 '17 at 12:15

0

                <select name='complated' id='complated'>
                    <option value=''>Yes</option>
                    <option value=''>No</option>
                </select>

And

                   <select name='passed' id='passed'>
                    <option value=''>Yes</option>
                    <option value=''>No</option>
                </select>

Niether does complated pass any value to your PHP, nor does passed.

Since no values are coming in and you have already blanked them out due to this mistake, data is not getting updated any longer.

Hint

<option value='1'>Yes</option>
               ^

And , Oh

How can I prevent SQL injection in PHP?

edited May 23 '17 at 12:15

Community

1
1

answered Dec 23 '15 at 08:25

Hanky Panky

46,730
8
72
95

Thank you very much its very clear. I have question why its only updating in row 40 why not all of the table wherever user click ? . – user5495621 Dec 23 '15 at 08:51
because of your `WHERE` clause – Hanky Panky Dec 23 '15 at 09:00

score 0 · Accepted Answer · answered Dec 27 '15 at 10:29

There are several issues with the current code:

it's vulnerable to SQL injection. You should use prepared SQL statements;
it uses deprecated mysql_ functions, you should use mysqli_ instead (or PDO);
it suppresses errors with error_reporting(0), which is counter-productive when trying to resolve problems, but should really be avoided always;
the HTML will have non-unique id attribute values, which is not allowed;
there are more TR closing than opening tags;
some elements, such as the submit button, appear in an invalid location in the table. You should place them outside the table;
while the form will submit multiple complated and passed values, there is no support for processing multiple items in the current PHP code. You could append the courseid to each name;
the value $id is placed in a hidden input outside the while loop, so that only the last row's id is submitted. You should place it inside the loop (in a TD), and could use a name that PHP can process as an array of values (name=courseid[]);
the form does not display the current database values for the complated and passed columns. You should use the selected option attribute.

Below is some untested code to deal with the above mentioned problems.

Form:

<body>
<form action="up.php" name="frmAdd" method="post">
    <div align='center'>
        <table border='1' cellpadding='5' cellspacing='1' id='mytable'>
            <tr align='center'>
                <th>courseid</th>
                <th>code</th>
                <th>Title</th>
                <th>Cr</th>
                <th>prerequisite</th>
                <th>STDid</th>
                <th>complated</th>
                <th>passed</th>
            </tr>
<?php
// don't set error reporting to 0!
include('config.php');
$sql="select * from studyplan";
$result=mysql_query($sql);
while ($row = mysql_fetch_assoc($result)) { // use assoc
    $id=$row['courseid'];
    // use HTML with injected PHP values, and use bracket names for allowing
    // multiple rows to be submitted. The ID attributes must be unique
?>
            <tr  class='edit_tr' id='<?=$id?>'>
                <td class='edit_td'>
                    <input type="hidden" name="courseid[]" value="<?=$id?>" />
                    <span class='text' id='id_<?=$id?>' ><?=$id?></span>
                </td>
                <td class='edit_td'>
                    <span class='text' id='code_<?=$id?>' ><?=$row['code']?></span>
                </td>
                <td class='edit_td'>
                    <span class='text' id='title_<?=$id?>' ><?=$row['title']?></span>
                </td>
                <td class='edit_td'>
                    <span class='text' id='cr_<?=$id?>' ><?=$row['cr']?></span>
                </td>
                <td class='edit_td'>
                    <span class='text' id='pre_<?=$id?>' ><?=$row['prerequisite']?></span>
                </td>
                <td class='edit_td'>
                    <span class='text' id='std_<?=$id?>' ><?=$row['std_id']?></span>
                </td> 
                <td class='edit_td'>
                    <select name='complated_<?=$id?>' id='complated_<?=$id?>'>
                        <option value='Yes' <?=$row['complated']=='Yes' ? 'selected' : '' ?> >Yes</option>
                        <option value='No' <?=$row['complated']=='No' ? 'selected' : '' ?> >No</option>
                    </select>
                </td>
                <td class='edit_td'>
                    <select name='passed_<?=$id?>' id='passed_<?=$id?>'>
                        <option value='Yes' <?=$row['passed']=='Yes' ? 'selected' : '' ?> >Yes</option>
                        <option value='No' <?=$row['passed']=='No' ? 'selected' : '' ?> >No</option>
                    </select>
                </td> 
            </tr>
<?php
    }
?>
        </table>
        <p align="center"><a href="year.html">Go Back</a> </p>
        <input type="submit" name="submit" value="save" class="button">
    </form>
</div>
</body>
</html>

up.php

// use mysqli instead of mysql:
$cid    =   mysqli_connect($host, $usr, $pwd);
if (mysqli_connect_errno()) {
    die ("Connect failed: " . mysqli_connect_error());
}
if ($_SERVER['REQUEST_METHOD'] == "POST") { 
    // avoid SQL injection, by preparing the statement with parameters
    $SQL = "update studyplan set complated = ?, passed = ? where courseid = ?";
    $stmt = mysqli_prepare($cid, $SQL);
    // keep count of updated records
    $affected = 0;

    // we will receive several courseid values, so expect an array:
    $ids = $_POST['courseid'];
    foreach($ids as $id) {
        $complated = $_POST["complated_$id"];
        $passed = $_POST["passed_$id"];
        mysqli_stmt_bind_param($stmt, "ssi", $complated, $passed, intval($id));
        if(mysqli_stmt_execute($stmt)) {
             $affected += mysqli_affected_rows();
        } else {
            echo "error: data has not been updated for course $id<br>";
        }
    }
    echo "$affected records have been updated<br>";
    mysqli_stmt_close($stmt);
}

As said, the above is untested, and leaves still some error handling to implement (e.g. testing the validity of the post parameters). Also, it will always update all rows when you submit, even if you didn't change any value.

But this should at least help you to resolve the many issues you are currently facing. After that you can think to further improve and optimise the code.

unable to update in database

2 Answers2