I am very new to PHP and semi new to SQL so forgive me. The question of how to do this in sql has been answered many times, it doesn't seem to work through my PHP connection. I am attempting to use if else statements in the following block. The idea being that IF there is a match on two columns (SKU and Location) add the amount entered by the user. ELSE create a new row with the SKU, Location and Qty entered on the page.
'''
<?php
/**
* Use an HTML form to create a new entry in the
* users table.
*
*/
if (isset($_POST['submit'])) {
require "../common.php";
require "../config.php";
try {
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//If entered SKU and Location are in table INV_MASTER, add the qty entered to the row found. ELSE create new row of data.
$sql = ""
$conn->exec($sql);
#$statement = $connection->prepare($sql);
#$statement->execute($new_item);
} catch(PDOException $error) {
echo $sql . "<br>" . $error->getMessage();
}
}
?>
<?php require "templates/header.php"; ?>
<?php if (isset($_POST['submit']) && $statement) { ?>
> <?php echo $_POST['SKU']; ?> successfully added.
<?php } ?>
<h2>Putaway</h2>
<form method="post">
<label for="QTY">Qty</label>
<input type="number" name="Qty" id="Qty">
<label for="SKU">SKU</label>
<input type="text" name="SKU" id="SKU">
<label for= "UPC"> UPC</label>
<input type="text" name = "UPC" id ="UPC">
<input type="submit" name="submit" value="Submit">
</form>
<a href="index.php">Back to home</a>
<?php require "templates/footer.php"; ?>
'''
Editing to add why this is a little different than the linked answers though they were on the right track so it helped! I need the ability for the SKU to be repeated in multiple locations, but the locations to be unique. I've added the unique flag to the Location column in SQL but I'm having trouble understanding how to add the Qty to existing qty on a duplicate value. As an example, using the links provided I built this sql statement;
'''
$sql = "INSERT INTO INV_MASTER (SKU, Location, Qty) VALUES(':SKU' ':Location', ':Qty') ON DUPLICATE KEY UPDATE
Qty= VALUES (:Qty+Qty)";
'''
When it runs, i get this error that makes me think that I'm using the incorrect way of getting data from my form in HTML: "INSERT INTO INV_MASTER (SKU, Location, Qty) VALUES(':SKU', ':Location', ':Qty') ON DUPLICATE KEY UPDATE Qty= VALUES (:Qty+Qty) SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':Qty+Qty)' at line 2 "
