PHP MYSQLi Database not updating

Question

I've been trying to find the problem for 3 nights. It gave me nightmares. Please help. I think my code is already perfect. Can someone please fix my code and tell me whats wrong?

$sql = "SELECT py.idPembayaran, p.idPelajar, p.nama, b.namaBarangan, 
pb.kuantiti, b.harga, py.jumlahBayaran, py.statusPembayaran, 
py.statusPenghantaran, pb.tarikhPembelian FROM barangan b
INNER JOIN pembelian pb on pb.idBarangan = b.idBarangan
INNER JOIN pembayaran py on py.idPembelian = pb.idPembelian
INNER JOIN pelajar p on p.idPelajar = pb.idPelajar";

$result = mysqli_query($conn, $sql);

if (mysqli_num_rows($result) > 0) {
// output data of each row

while($row = mysqli_fetch_assoc($result)) {

    echo '
    <form method="POST" action="purchaselist.php">

        <tr>
        <input type="hidden" name="idPembayaran" value="<?php echo $idPembayaran ?>">
            <td>'.$row["idPembayaran"].'</td>
            <td>'.$row["idPelajar"].'</td>
            <td>'.$row["nama"].'</td>
            <td>'.$row["namaBarangan"].'</td>
            <td>'.$row["kuantiti"].'</td>
            <td>'.$row["harga"].'</td>
            <td>'.$row["jumlahBayaran"].'</td>


            <td>
                <select name="statusPembayaran">
                    <option value="In process">In process</option>
                    <option value="Successful">Successful</option>
                </select>
            </td>

            <td>
                <select name="statusPenghantaran">
                    <option value="In process">In process</option>
                    <option value="Arrived">Arrived</option>
                </select>
            </td>

            <td>'.$row["tarikhPembelian"].'</td>
            <td><input type="submit" name="submit" value="Update"></td>
        </tr>
    </form>
    ';
}
}

if (!empty($_POST["submit"])) {

$idPembayaran = $_POST["idPembayaran"];
$statusPembayaran = $_POST["statusPembayaran"];
$statusPenghantaran = $_POST["statusPenghantaran"];

$sql = "UPDATE pembayaran SET statusPembayaran ='".$statusPembayaran."', statusPenghantaran ='".$statusPenghantaran."' WHERE idPembayaran = '".$idPembayaran."'";

if(mysqli_query($conn, $sql)) {

    echo "
        <script>
        alert('test');
        window.location.href = 'purchaselist.php';
        </script>
    ";
}
else {
    echo "Update error.";
}
}

Im trying to update table "pembayaran" but it is not updating. There is only two column that i want to update which is "statusPembayaran" and "statusPenghantaran" in that table. The value is from select option.

echo "Update error."; that won't help you here. Use mysqli_error($conn) instead and enable error reporting. — Funk Forty Niner
– Funk Forty Niner, Commented Nov 8, 2018 at 18:42
Thanks for the quick reply. But thats not the problem. The if(mysqli_query($conn, $sql)) { is executed and it has no error but the database is not updating after I press the button. — Hisywm
– Hisywm, Commented Nov 8, 2018 at 18:48
what about > php.net/manual/en/function.error-reporting.php ? — Funk Forty Niner
– Funk Forty Niner, Commented Nov 8, 2018 at 18:49
Also, when you are done, you'll probably want to swap that concatenated SQL string out for a prepared statement. The code as is is vulnerable to SQL injection. In fact, if your input includes things like quotes or apostrophes or other mySQL syntax, you may be breaking your query with bad input — Nosajimiki
– Nosajimiki, Commented Nov 8, 2018 at 18:56

Nigel Ren · Accepted Answer · 2018-11-08 19:05:14Z

1

When you set the value for the field idPembayaran in the first place, you have...

<input type="hidden" name="idPembayaran" value="<?php echo $idPembayaran ?>">

at this point $idPembayaran isn't set, it should be $row["idPembayaran"] which is the value from the SELECT...

<input type="hidden" name="idPembayaran" value="<?php echo $row["idPembayaran"]; ?>">

answered Nov 8, 2018 at 19:05

Nigel Ren

57.3k11 gold badges49 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Hisywm Over a year ago

Thank you very much bro. I can now sleep in peace. Finally someone fixed my problem!

Collectives™ on Stack Overflow

PHP MYSQLi Database not updating

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest