STOP INSERTING VALUES TO THE TABLE

Question

I have two table. one is item table and the other is issue table. issue table uses foreign key reference to item_id.

I am using the issue tables to issue items to my employee and deduct the item quantity to my item table. No problem issuing the items and deducting the quantity on item table, What I want is when the quantity becomes zero the employee name will stop inserting values to the issue table. Because everytime I tried to issue the value is inserting in the issue table even the quantity in the item table is zero.

if row is greater than zero, do not proceed. else insert it to this table.

hope you understand.

Please post your code. A `JOIN` or `CASE` functions might be helpful — Luffy, May 09 '18 at 05:16
You should let MySQL handling this with a [BEFORE INSERT TRIGGER](https://stackoverflow.com/questions/2981930/mysql-trigger-to-prevent-insert-under-certain-conditions/22489342#22489342). If you want help doing this, you should show your table structure and data. — Thomas G, May 09 '18 at 07:11
You can use a trigger, or you can include the constraint within the INSERT itself. — Strawberry, May 09 '18 at 07:28

score 0 · Accepted Answer · answered May 09 '18 at 10:22

Crudely...

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table (id SERIAL PRIMARY KEY,balance INT NOT NULL);

INSERT INTO my_table VALUES (1,100);

INSERT INTO my_table (balance) SELECT MIN(balance-50) FROM my_table HAVING MIN(balance-50) >= 0;

SELECT * FROM my_table;
+----+---------+
| id | balance |
+----+---------+
|  1 |     100 |
|  2 |      50 |
+----+---------+

INSERT INTO my_table (balance) SELECT MIN(balance-50) FROM my_table HAVING MIN(balance-50) >= 0;

SELECT * FROM my_table;
+----+---------+
| id | balance |
+----+---------+
|  1 |     100 |
|  2 |      50 |
|  3 |       0 |
+----+---------+

INSERT INTO my_table (balance) SELECT MIN(balance-50) FROM my_table HAVING MIN(balance-50) >= 0;

SELECT * FROM my_table;
+----+---------+
| id | balance |
+----+---------+
|  1 |     100 |
|  2 |      50 |
|  3 |       0 |
+----+---------+

Great. Accept an answer, or supply one yourself, and accept that, or delete the question — Strawberry, May 10 '18 at 00:02

score -1 · Answer 2 · answered May 09 '18 at 07:30

$con=mysqli_connect(SERVER, DBUSER, DBPASS, DATABASE);

// Check connection
if (mysqli_connect_errno()) {
    echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
$sql = "SELECT quantity FROM item  WHERE item_id = 1;";
$result = mysqli_query($con,$sql);

$row = mysqli_fetch_array($result,MYSQLI_ASSOC);
if($row["quantity"] > 0) {
    //insert
}

STOP INSERTING VALUES TO THE TABLE

2 Answers2