php - Update database if data exists -
i have database contains 2 columns. product's name , it's quantity. have 2 forms, input user add new product, , input him add quantity. other form updating quantity when product gets sold.
the issue facing when user uses second form in order update quantity of specific product, of product's quantity inside database subtracted well. hope explained problem right.
full code:
<?php class tablerows extends recursiveiteratoriterator { function __construct($it) { parent::__construct($it, self::leaves_only); } } $servername = "localhost"; $username = "root"; $password = ""; try { $conn = new pdo("mysql:host=$servername;dbname=program", $username, $password); $conn->setattribute(pdo::attr_errmode, pdo::errmode_exception); echo "<script>alert('connected successfully');</script>"; } catch(pdoexception $e) { echo "connection failed: " . $e->getmessage(); } if (isset($_post['sub']) == 1) { $id2 = $_post['id2']; $qty2 = $_post['qty2']; $to = "xxxxxx"; $subject = "new sale report"; $header = "from shop"; $stmt = $conn->prepare("select brand_id brands brand_id = :id2"); $stmt->bindparam(':id2', $id2); $stmt->execute(); $result = $stmt->setfetchmode(pdo::fetch_assoc); foreach(new tablerows(new recursivearrayiterator($stmt->fetchall())) $k=>$v) { $r = $v; $s = $r - $qty2; if ($stmt->rowcount() === 1) { echo $s; $sql_new = "update brands set brand_qty='$s'"; $conn->exec($sql_new); } echo "<script>alert('new record updated.')</script>"; } $stmt_m = $conn->prepare("select brand_id brands"); $stmt_m->execute(); $results = $stmt_m->setfetchmode(pdo::fetch_assoc); foreach ( new tablerows(new recursivearrayiterator($stmt_m->fetchall())) $t=>$d ) { $body = "item sold: ". $d ."\n\n quantity sold: ". $s ." "; } //mail($to, $subject, $body, $header); } $id = $_post['id']; $qty = $_post['qty']; if (isset($_post['submit']) == 1) { $stmt_mt = $conn->prepare("select brand_id brands brand_id = :id"); $stmt_mt->bindparam(':id', $id); $stmt_mt->execute(); if($stmt_mt->rowcount() === 1) { echo "<script>alert('exists in database.')</script>"; } else { echo "<script>alert('adding hoodie database.')</script>"; $sql = "insert brands (brand_id, brand_qty) values ('$id', '$qty')"; $conn->exec($sql); echo "<script>alert('new record inserted succesfully.')</script>"; } } ?> <!doctype html> <html> <head> <title></title> </head> <body> <form method="post"> <input type="text" placeholder="enter id" name="id" /> <input type="number" placeholder="quantity" name="qty" /> <input name="submit" type="submit" value="submit" /> </form> <form method="post"> <input type="text" placeholder="enter id" name="id2" /> <input type="number" placeholder="number of sales" name="qty2" /> <input name="sub" type="submit" value="submit" /> </form> </body> </html>
looks you're updating entire table update - try changing update query include brand_id
if ($stmt->rowcount() === 1) { echo $s; $stmt2 = $conn->prepare("update brands set brand_qty='$s' brand_id = :id2"); $stmt2->bindparam(':id2', $id2); $stmt2->execute(); }
Comments
Post a Comment