Unable to UPDATE data in MySQL using php any suggestions?

Question

I am building php mysql based simple inventory management system. Also, I have no formal knowledge about the things or assigned names like arrays, functions, etc, but I can understand.

Here is the issue, I am facing right now where needs your guidance/assistance:

As far as my knowledge, my code has to work.

I like to update my products row but can't Affecting 0 rows using these queries:

if (isset( $_POST['prod_edit'] )) {

    $prodId = $_POST['prodId'];

    $pUpdate = $con->prepare("UPDATE products SET prodTeng = ? , prodTurd = ? , prodSKU = ? , prodBC = ? , prodPUPP = ? , prodPUSP = ? , prodCate = ? , prodQuan = ? WHERE prodId = ?  ");
    $pUpdate->bind_param("ssssssssi", $_POST['prodTeng'], $_POST['prodTurd'], $_POST['prodSKU'], $_POST['prodBC'], $_POST['prodPUPP'], $_POST['prodPUSP'], $_POST['prodCate'], $_POST['prodQuan'], $prodId);
    $pUpdate->execute();
    if ($pUpdate->affected_rows === 0) {
    echo'<script> window.location.replace("home.php?p=inventory&alert=0"); </script>';
    }
    if ($pUpdate->affected_rows === 1) {
    echo'<script> window.location.replace("home.php?p=inventory&alert=3"); </script>';
    }
    $pUpdate->close();
    }

FORM:

<?php
$products_list = $con->query("SELECT * FROM products WHERE cid = '$cid' ");
while($row = $products_list->fetch_assoc()) { 
    $prodCate = $row['prodCate'];
    $category_list = $con->query("SELECT * FROM categories WHERE cateId = '$prodCate' ");
    while($cate = $category_list->fetch_assoc()) { 
?>
<div class="modal fade modal-right" id="editProductRight<?=$row['prodId']; ?>" tabindex="-1" role="dialog" aria-labelledby="editProductRight<?=$row['prodId']; ?>" style="display: none;" aria-hidden="true">
            <div class="modal-dialog" role="document">
                <div class="modal-content">
                    <div class="modal-header">
                        <h5 class="modal-title">Edit Product Details</h5>
                        <button type="button" class="close" data-dismiss="modal" aria-label="Close">
                            <span aria-hidden="true">×</span>
                        </button>
                    </div>
                    <form action="home.php?p=inventory" method="post">
                        <div class="modal-body">
                            <div class="form-group mb-2">
                                <input type="text" class="form-control" name="prodTeng" placeholder="Name in English" required value="<?=$row['prodTeng']; ?>">
                            </div>
                            <div class="form-group mb-2">
                                <input type="text" class="form-control" name="prodTurd" placeholder="اردو میں نام" dir="rtl" value="<?=$row['prodTurd']; ?>">
                            </div>
                            <div class="form-group mb-2">
                                <input type="text" class="form-control" name="prodSKU" placeholder="SKU" required value="<?=$row['prodSKU']; ?>">
                            </div>
                            <div class="form-group mb-2">
                                <input type="text" class="form-control" name="prodBC" placeholder="Bar Code" value="<?=$row['prodBC']; ?>">
                            </div>
                            <div class="input-group mb-2 mr-sm-2">
                                <div class="input-group-prepend">
                                    <div class="input-group-text">PKR</div>
                                </div>
                                <input type="number" class="form-control" name="prodPUPP" id="inlineFormInputGroupUsername2" placeholder="Per Unit Purchase Price" value="<?=$row['prodPUPP']; ?>">
                            </div>
                            <div class="input-group mb-2 mr-sm-2">
                                <div class="input-group-prepend">
                                    <div class="input-group-text">PKR</div>
                                </div>
                                <input type="number" class="form-control" name="prodPUSP" id="inlineFormInputGroupUsername2" placeholder="Per Unit Sell Price" value="<?=$row['prodPUSP']; ?>">
                            </div>
                            <div class="form-group mb-2">
                                <input type="number" class="form-control" name="prodQuan" placeholder="On-Hand Quantity" value="<?=$row['prodQuan']; ?>">
                            </div>
                            <div class="form-group">
                                <select class="form-control" name="prodCate" required>
                                    <option value="<?=$cate['cateId']; ?>"><?=$cate['cateTeng']; ?> | <?=$cate['cateTurd']; ?></option>
                                    <?php
                                    $stmt = $con->query("SELECT * FROM categories WHERE cid = '$cid' ");
                                    while($row = $stmt->fetch_assoc()) { 
                                    ?>
                                    <option value="<?=$row['cateId']; ?>"><?=$row['cateTeng']; ?> | <?=$row['cateTurd']; ?></option>
                                    <?php 
                                    }
                                    $stmt->close();
                                    ?>
                                </select>
                            </div>
                        </div>
                        <hidden style="visibility:hidden;"><input type="text" value="<?=$row['prodId']?>" name="prodId" ></hidden>
                        <div class="modal-footer">
                            <button type="button" class="btn btn-outline-primary" data-dismiss="modal">Cancel</button>
                            <button type="submit" name="prod_edit" class="btn btn-primary">Submit</button>
                        </div>
                        
                    </form>
                </div>
            </div>
        </div>
<?php
    }
    $category_list->close();
}
$products_list->close();
?>

If there is any suggestion or other way to do that, then please guide!

`` isn't a valid HTML tag. Try ``. See https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/hidden — Phil, Aug 25 '20 at 01:57
You can do some debugging by adding `print_r($_POST);` to the top of your php script, as well as enable error display with: `ini_set('display_startup_errors', true); ini_set('display_errors', true); error_reporting(E_ALL); ` and seeing `bind_param` I'm assuming you are using the `mysqli` object, and not `PDO`, so toss this up there too: `mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);`. — IncredibleHat, Aug 25 '20 at 02:01
Not sure where `$cid` comes from, but you could easily be opening up a sql injection point with `$con->query("SELECT * FROM products WHERE cid = '$cid' ");` ... you should always prepare statements that have any variable inputs. — IncredibleHat, Aug 25 '20 at 02:02
Use placeholder values for *all* data. Don't assume or you'll end up in trouble. You have a prepared statement in the first bit of code, but then you apparently got sloppy and started injecting into your SQL. That's how huge problems happen. — tadman, Aug 25 '20 at 02:13
@IncredibleHat, Okay let me try debugging as you guided. - Thank you for your guidance. — Hamza Afzal, Aug 25 '20 at 02:14
It's worth noting these column names are bordering on ridiculous as they need a lot of work to decode. Instead of `prodQuan` just go with `quantity` if that's what it is. Ditch the prefix, that's what the table is there for. — tadman, Aug 25 '20 at 02:15
@Phil, Thank you, but `prodId` will be `$_POST` as `type="text"` right? — Hamza Afzal, Aug 25 '20 at 02:18
@HamzaAfzal the input `type` does not dictate the data type. All data sent via POST (for standard form submissions) will be a string anyway. — Phil, Aug 25 '20 at 02:21
@tadman Sure I will fix SQL injection things as guided. Also thanks for the prefix suggestion but I like this way :D to avoid any same names. — Hamza Afzal, Aug 25 '20 at 02:24
@Phil not yet. I will update the question when I find the solution. — Hamza Afzal, Aug 25 '20 at 02:31
First thing to do is make sure you can see all errors. Check out [How can I get useful error messages in PHP?](https://stackoverflow.com/questions/845021/how-can-i-get-useful-error-messages-in-php) and [How to get the actual mysql error and fix it?](https://stackoverflow.com/questions/22662488/mysqli-fetch-assoc-expects-parameter-call-to-a-member-function-bind-param) — Phil, Aug 25 '20 at 02:33
@Phil I did use 'print_r($_POST);' and found that the `prodId` was in the end as shown in the question. Then I just changed the location of prodId `` tag at the top & It Works (-_-) & I don't know what to say. Results of prodId `` tag in the end: `Array ( [prodTeng] => asdasd [prodTurd] => Aasd [prodSKU] => asd [prodBC] => [prodPUPP] => 3 [prodPUSP] => 3 [prodQuan] => 2 [prodCate] => 1 [prodId] => 3 [prod_edit] => )` — Hamza Afzal, Aug 25 '20 at 02:44
The issue is resolved thank you everyone for help. Have a Good day (or Night). — Hamza Afzal, Aug 25 '20 at 02:49
Please remove the question, or otherwise provide an answer and 'accept' it (when the system allows you to do so) — Strawberry, Aug 25 '20 at 06:33

score 0 · Accepted Answer · answered Aug 26 '20 at 08:34

I did use print_r($_POST); and found that the prodId was in the end as shown in the question. Then I just changed the position/location of prodId <input> tag at the top & It Works (-_-).

Results of prodId <input> tag in the end was:

Array ( [prodTeng] => asdasd [prodTurd] => Aasd [prodSKU] => asd [prodBC] => [prodPUPP] => 3 [prodPUSP] => 3 [prodQuan] => 2 [prodCate] => 1 [prodId] => 3 [prod_edit] => )

Which end up effecting 0 rows

Results of prodId tag at top was:

Array ( [prodId] => 3 [prodTeng] => asdasd [prodTurd] => Aasd [prodSKU] => asd [prodBC] => [prodPUPP] => 3 [prodPUSP] => 3 [prodQuan] => 2 [prodCate] => 1 [prodId] => 3 [prod_edit] => )

Which successfully effecting rows.

Unable to UPDATE data in MySQL using php any suggestions?

1 Answers1