I have 3 table involved in this transaction, the item, transaction and sales. the problem appears only in the sales table because it only saves into the table sales if the value to be inserted to the field customer are number but do not save if the values to be inserted is name of the customer. It seems that it only accepts numbers but my the customers datatype is text it must accept characters such as our names. sorry my english is not good but I hope you will help me the code and table is below
<?php
include ('dbconnect.php');
$itemcode = $_POST['itemcode'];
$cusname = $_POST['cusname'];
$price = $_POST['price'];
$qty = $_POST['qty'];
$left = $_POST['left'];
$newstock = ($left - $qty);
$amount = $_POST['amount'];
$tobepaid = ($price * $qty);
$change = ($amount - $tobepaid);
if ($amount<$tobepaid){
echo "<script language='javascript'>
alert('The amount tendered is insufecient for the item');
document.location.href='stockout.php';
</script>";
}
else{
mysql_query("update item set
qty = '$newstock'
where itemcode = '$itemcode'");
$result = mysql_query("INSERT INTO `transaction` (`transtype`, `itemcode`, `prevstock`, `qty1`, `currentstock`) VALUES ('stockout',$itemcode,$left,$qty,$newstock)");
$result = mysql_query("INSERT INTO `sales` (`itemcode`,`customer`, `qty2`,`tobepaid`, `amount`,`change`) VALUES ($itemcode,$cusname,$qty,$tobepaid,$amount,$change);");
echo "<script language='javascript'>
alert('Updated $cusname');
document.location.href='stockout.php';
</script>";
mysql_close($con);
}
?>mysql> select * from sales;
+---------------------+----------+----------+------+----------+--------+--------+
| datetime | itemcode | customer | qty2 | tobepaid | amount | change |
+---------------------+----------+----------+------+----------+--------+--------+
| 2017-04-09 17:00:07 | 2901 | 12 | 1 | 15000 | 15000 | 0 |
+---------------------+----------+----------+------+----------+--------+--------+
1 row in set (0.00 sec)
mysql> select * from transaction;
+---------------------+-----------+----------+-----------+------+--------------+
| datetime | transtype | itemcode | prevstock | qty1 | currentstock |
+---------------------+-----------+----------+-----------+------+--------------+
| 2017-04-09 16:11:31 | stockout | 2901 | 19 | 1 | 18 |
| 2017-04-09 16:12:53 | stockout | 2901 | 18 | 1 | 17 |
| 2017-04-09 16:13:42 | stockout | 2901 | 17 | 2 | 15 |
| 2017-04-09 16:14:41 | stockout | 4526 | 16 | 1 | 15 |
| 2017-04-09 16:16:26 | stockout | 26350 | 32 | 1 | 31 |
| 2017-04-09 16:18:18 | stockout | 26350 | 31 | 1 | 30 |
| 2017-04-09 17:00:07 | stockout | 2901 | 15 | 1 | 14 |
| 2017-04-09 17:01:14 | stockout | 2901 | 14 | 1 | 13 |
| 2017-04-09 17:03:39 | stockout | 33321 | 27 | 1 | 26 |
| 2017-04-09 17:07:37 | stockout | 12000 | 24 | 1 | 23 |
| 2017-04-09 17:08:08 | stockout | 2901 | 13 | 1 | 12 |
+---------------------+-----------+----------+-----------+------+--------------+
11 rows in set (0.00 sec)
mysql> select * from item;
+----------+-------------------------+-----+-------+
| itemcode | item_abb | qty | price |
+----------+-------------------------+-----+-------+
| 2901 | King Bed | 12 | 15000 |
| 26350 | King Size Dinning Table | 30 | 15000 |
| 33321 | Sofa | 26 | 4500 |
| 4526 | chandelier | 15 | 5000 |
| 12000 | Swinging Chair | 23 | 3500 |
+----------+-------------------------+-----+-------+this the form where to get the values
</div>