MySQL update in php not working

Question

The update query is not working.

The problem is that the value of $rs->TestAssignmentId, $currecord, is not passed to the if block.

This should happen when user presses the submit button.

$sql="SELECT * FROM tbltestassignment
    WHERE TeacherId=".$_SESSION["UserId"]." AND StudentId=".$_GET["StudentId"]." AND TestId=".$_GET["TestId"];
$searchRes1 = Execute($sql);
while($rs=mysql_fetch_object($searchRes1)) {
    $temp = GetSingleReconrd('tbltestassignment','QuestionIds','StudentId',$rs->StudentId);
    $quesIds = explode(',',$temp);
    $totalQues = count($quesIds);
    $currecord = $rs->TestAssignmentId;
    $temp = GetSingleReconrd('tbltestassignment','AnswerIds','StudentId',$rs->StudentId);
    $ansIds = explode(',',$temp);
}

    //echo "<pre>".$currecord."</pre>";
if(isset($_REQUEST["epSave"])) {
    $score = array_filter($_REQUEST['score']);
    $sqlup = 'UPDATE tbltestassignment SET `Score` = "'.$score.'" WHERE `TestAssignmentId` = '.$currecord;
    mysql_query($sqlup);

    $_SESSION["_msg"] = "Marks Successfully Added..!";
    $_SESSION["_msgtype"] = 1;
    $a=8;
    $url = "correct.php?TestId=".$a;//$_GET['TestId'];
    header("location:".$url);
    die();
}

<?php
    for($i=0; $i<$totalQues; $i++){
?>
<tr>
    <td style="text-align:center">
        <input name="score[]" id="score<?php echo $i ?>" type="text" placeholder="Enter marks here">
    </td>
</tr><?php } ?>

@Gerton - No errors ... the record doesn't get affected after running the query — Shreyas Tripathy, Jun 22 '15 at 09:53
echo your UPDATE query & copy/paste in phpMyAdmin. Check it's working or not. — test, Jun 22 '15 at 09:54
@Jack - DId that, the query is running perfectly in phpMyAdmin — Shreyas Tripathy, Jun 22 '15 at 09:54
Also at the end of the first sql you are missing a double quote ". And check the names of your functions very well. — Gideon Appoh, Jun 22 '15 at 09:59
@Voitcus - Everything else is working ... it's just this particular query that is giving me trouble — Shreyas Tripathy, Jun 23 '15 at 04:54
I would test step by step - how does `$sql` variable look like? Do you have correct `$_SESSION` and `$_GET` variables? What is the result of `Execute()` procedure? Is `$searchRes1` a resource? Does the program enter the `while()` loop? For example, there maybe a case-sensitivity problem in your tables/columns names, when you are on Linux etc. I'm afraid no-one can help without knowing this — Voitcus, Jun 23 '15 at 05:02
Okay, I think I found the issue. The variable `$currecord` is not giving me a value and I am guessing that is because of scope. @Voitcus - Could you tell me how I could get the value of `$rs->TestAssignmentId` into the `if` condition which BTW is called when the user presses the **Submit** (or **Save** in this case) — Shreyas Tripathy, Jun 23 '15 at 05:16
I don't know what this function does, did you write it yourself or is it a part of a framework? but the value is the output of the function, so you can test `if($rs->TestAssignmentId == ...)` — Voitcus, Jun 23 '15 at 05:19

score 0 · Accepted Answer · edited May 23 '17 at 12:14

Stop using mysql_*

Start using something like PDO, here is a nice tutorial for MySQL developers. You should change as soon as possible because you code is open to sql injection. In a simple form, with some of your table names:

$stmt = $db->prepare( "SELECT FROM tbltestassignment WHERE TeacherId=?" );
$stmt->execute(array(
    $_SESSION["UserId"]),
);
$rows = $stmt->fetch( \PDO::FETCH_ASSOC );

$currecord is probably not defined because

while($rs=mysql_fetch_object($searchRes1)){

doesn't happen. The flow of this should be revised, it seems the while should happen only in the if since it's result are used only there (AFAIK)

very simply, I removed some part to express only the logic, that flow might help you:

if ( isset($_REQUEST["epSave"]) ) {
    $sql = "SELECT * FROM tbltestassignment WHERE TeacherId=? AND StudentId=? AND TestId=?";
    $stmt = $pdo ->prepare($sql);
    $stmt->execute([
        $_SESSION["UserId"],
        $_GET["StudentId"],
        $_GET["TestId"],
    ]);
    if ($stmt->rowCount() > 0) {
        while( $rs = $stmt->fetch(\PDO::FETCH_ASSOC) ) {
            // here you know what to do, $rs is an array
        }
    }
}

Thanks for the PDO suggestion and this would work but I found a workaround for it by setting cookies. Yes I know using `mysql_*` is a bad practice but I am currently working on a legacy system and a complete overhaul is in the pipeline. I will be switching to `mysqli_*` and would be applying _special character deprecation_ to safeguard the system from sql injection — Shreyas Tripathy, Jun 24 '15 at 03:18

MySQL update in php not working

1 Answers1