PHP sql database not updating after update query, indexes are not defined?

Question

I am having an issue where I am not able to update the database. I think the error is in the update query itself, but I am new to SQL and PHP and therefore I am not 100% sure. Any help would be greatly appreciated.

I was getting an undefined index error which I fixed with but the issue still exists:

$id = $_GET["id"];
$listdate = isset($_POST['list_date']) ? $_POST['list_date'] : '';
$listprice = isset($_POST['list_price']) ? $_POST['list_price'] : '';
$solddate = isset($_POST['sold_date']) ? $_POST['sold_date'] : '';
$soldprice = isset($_POST['sold_price']) ? $_POST['sold_price'] : '';
$shipdate = isset($_POST['ship_date']) ? $_POST['ship_date'] : '';
$shipcost = isset($_POST['ship_cost']) ? $_POST['ship_cost'] : '';

After changing to the above code(which did not work) I am still having an issue updating the database. Here is the full current code:

<?

$id = $_GET["id"];
$listdate = $_POST["list_date"];
$listprice = $_POST["list_price"];
$solddate = $_POST["sold_date"];
$soldprice = $_POST["sold_price"];
$shipdate = $_POST["ship_date"];
$shipcost = $_POST["ship_cost"];

$servername = "localhost";
$username = "inventory";
$password = "*****";
$db = "products";

$conn = new mysqli($servername, $username, $password, $db);

if ($conn->connect_error){
    die("Connection failed: ". $conn->connect_error);
}

   
$sql = "UPDATE inventory SET list_date = '$listdate', list_price = '$listprice', sold_date = '$solddate', sold_price = '$soldprice', ship_date = '$shipdate', ship_cost= '$shipcost' WHERE product_id = ' .$id. '";

if($conn->query($sql) === TRUE){
    echo "Record Saved.";
    } else {
        echo "Error!";
    }

$conn->close();

?>

Even when I change the ID manually to an assigned ID with the database, the updating still does not work and makes no changes in the database. I know the form does not look pretty, but I set it up for testing. Here is how I have the form set up to capture the changes:

       <form action="/SulleySells/scripts/updateitem.php?id=<?php echo $id;?>" method="post">

    <script type="text/javascript">
        function ShowHideDiv(listed) {
            var updatel = document.getElementById("updatel");
            updatel.style.display = listed.checked ? "block" : "none";
        }
        function ShowHideDiv2(sold) {
            var updates = document.getElementById("updates");
            updates.style.display = sold.checked ? "block" : "none";
        }
    </script>
    
    <label for="listed">
    <input type="checkbox" id="listed" onclick="ShowHideDiv(this)" />
    Listed?
    </label>
    
    <label for="sold">
    <input type="checkbox" id="sold" onclick="ShowHideDiv2(this)" />
    Sold?
    </label>
    
    <hr>
    
    <div id="updatel" style="display: none">
        <h3>Update Listing Details:</h3>
        <label for="listdate">Listed Date:</label>
        <input type="date" id="updateltext" name="listdate" value=""/>
        <br>
        <label for="listprice">Listed Price:</label>
        <input type="text" id="updateltext"  name="listprice" value=""/>
        <br>
        <button>Update</button>
        <hr>
    </div>
    
     <div id="updates" style="display: none">
         <h3>Update Sale Details:</h3>
         
        <label for="solddate">Sold Date:</label>
        <input type="date" id="updatestext" name="solddate" value=""/>
        <br>
        <label for="soldprice">Sold Price:</label>
        <input type="text" id="updatestext" name="soldprice" value=""/>
        <br>
        <label for="shipdate">Ship Date:</label>
        <input type="date" id="updatestext" name="shipdate" value=""/>
        <br>
        <label for="shipcost">Ship Cost:</label>
        <input type="text" id="updatestext" name="shipcost"value=""/>
        <br>
        <button>Update Sold Info</button>
        <hr>
    </div>

    </form>

You should enable the error_reporting, and then debug. This may help you https://stackoverflow.com/questions/845021/how-can-i-get-useful-error-messages-in-php — Jun Pan, May 23 '22 at 02:44
@JunPan I did try that and did not show any new information or anything like that, unfortunately. — ckcRichard, May 23 '22 at 02:47
What's the output string of the variable `$sql`?You can `echo` it and then make sure it is a correctly Mysql statement. — Jun Pan, May 23 '22 at 02:50
@JunPan so I tried that, but the data I put into the form is not capturing. So say I select the date and enter 8.99, it just shows list_date = “ and list_price = “. Not sure why it’s not getting the input information from the form and I am not sure how to fix that. — ckcRichard, May 23 '22 at 03:01
As I can say, you want to update the when you click the `Update`, is it correct? — Jun Pan, May 23 '22 at 03:09
@JunPan That or have a button and have the form set to that action. Either way, yes that is correct. — ckcRichard, May 23 '22 at 03:12
In your previous code, you post nothing to the server, so you can't capture data via `$_POST`.You should add action to the form and a submit button. — Jun Pan, May 23 '22 at 03:17
@JunPan just added action and button to form, still having the same issue. — ckcRichard, May 23 '22 at 03:21
Can you edit the question so that we can get the latest code? — Jun Pan, May 23 '22 at 03:22
This may help you https://www.w3schools.com/php/php_forms.asp — Jun Pan, May 23 '22 at 03:30
@JunPan no, changing button to input with type update or type submit does not work. Still not getting information and still having the undefined index error. — ckcRichard, May 23 '22 at 03:33
Make sure you post data to the server and the SQL is correct — Jun Pan, May 23 '22 at 03:47
Since you are new to SQL and PHP are new to SQL injection too (make sure you validate EVERY input get from the user in a $_POST or $_GET structure ($_REQUEST too). — Eineki, May 23 '22 at 03:55
I see two issues with the query string: the main issue is at the end of the string: you write `'. $id. '` but you are inside of a double quoted string. Change it to `'$id'` or you will get an incorrect id and the statement will not match any row (assumed you do not have id like `'. 5 .'`) hence no updates. — Eineki, May 23 '22 at 04:00
Your input names are like `listdate`, `listprice`, etc but on the PHP side you are looking for fields with underscores in them, ie `$_POST['list_date']`, `$_POST['list_price']`, etc — Phil, May 23 '22 at 04:01
**Warning**: You are wide open to [SQL Injections](https://php.net/manual/security.database.sql-injection.php) and should really use parameterised **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input, especially that which comes from the client side. [Escaping is not enough](https://phpdelusions.net/top#escaping) — Phil, May 23 '22 at 04:02

Qutayba Khalid · Accepted Answer · 2022-05-23T06:05:18.260

At first you have to change:

<input type="date" id="updateltext" name="listdate" value=""/>

to:

<input type="date" id="updateltext" name="list_date" value=""/>

To match what you have in your php code and apply it to the rest of the html inputs

then change:

WHERE product_id = ' .$id. '

to:

WHERE product_id = '$id'

Notice Don't forget to format for="" in lable with id="" in the input so as not to give you a different result

PHP sql database not updating after update query, indexes are not defined?

1 Answers1