2

I am creating a PHP website that use Apache web server (PHPmyAdmin)

I have 3 tables :

  1. brand
    • brand_id (PRIMARY KEY) AUTO INCREMENT
    • brand_name
  2. item
    • item_id (PRIMARY KEY) AUTO INCREMENT
    • item_category
  3. model
    • model_id (PRIMARY KEY) AUTO INCREMENT
    • item_model
    • brand_id (FOREIGN KEY for brand.brand_id)
    • brand_name (FOREIGN KEY for item.item_id)
    • quantity
    • price

I have a problem when I want to insert new value into model table.

this is PHP Code that I use for inserting 

if (isset($_POST['brand_id']));
    $brand = ($_POST['brand_id']);
if (isset($_POST['item_id']));
    $cat = ($_POST['item_id']);
if (isset($_POST['model']));
    $model = ($_POST['model']);
if (isset($_POST['quantity']))
    $quantity = ($_POST['quantity']);
if (isset($_POST['price']))
    $price = ($_POST['price']);

        $sql = "INSERT INTO model (item_model, brand_id, item_id, quantity, price)
                VALUES ('$model', '$brand', '$cat', '$quantity', '$price')";

Note that I did not insert any value into the model_id because I think it will automatically increased for it is AUTO increment. I dont know whether I am right or wrong. the isset values are passed from another PHP file. so this PHP basically just catch the thrown values from previous PHP file.

I had tried to insert value directly from the PHPmyAdmin using SQL statement and it yielded this error :

Cannot add or update a child row: a foreign key 
constraint fails (`stock`.`model`, CONSTRAINT `fk_item_brand` 
FOREIGN KEY (`brand_id`) REFERENCES `brand` (`brand_id`) ON UPDATE CASCADE)

and if I tried to put in a id value into one of the column, it will yield an error: 

SQL query:

INSERT INTO `model`(`model_id`, `item_model`, `brand_id`, `item_id`, `quantity`, `price`) 
VALUES (1,'a','1','1','a','a')

MySQL said: Documentation

#1062 - Duplicate entry '1' for key 'PRIMARY'

How do I insert a row into a table with foreign key inside PHP?

jww
  • 97,681
  • 90
  • 411
  • 885
Foster
  • 345
  • 1
  • 5
  • 18
  • "I have a problem" -- what is the problem? You're doing auto increment correctly. – Barmar Sep 11 '14 at 08:03
  • You're right, you don't need to include the auto increment value. What I really don't like is how you insert URL arguments directly into your SQL command. Do NOT do this, EVER. Read more about SQL injection here: http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php – KIKO Software Sep 11 '14 at 08:05
  • you have some extra semi-colons in there at the end of your `if` statements. They could be the issue. Or perhaps it might fail if one of your variables is not instantiated because the corresponding `$_POST` variable was not set. In any case, I'd be checking what is passed to this page via `POST` (e.g. type and/or length), perhaps using `filter_var()` and also I would use prepared statements and bind the values to the statement. – adey_888 Sep 11 '14 at 08:05
  • btw if that exactly query are you doing ... than your code is open for sql injection no matter what are you using to query – NullPoiиteя Sep 11 '14 at 08:05
  • please check my newest post. I have provided new info – Foster Sep 11 '14 at 08:07
  • your first error a foreign key constraint fails (`stock`.`model`, CONSTRAINT `fk_item_brand` FOREIGN KEY (`brand_id`) REFERENCES `brand` (`brand_id`) ON UPDATE CASCADE) means that the key is not available in primary table which you are trying to insert here. – Suchit kumar Sep 11 '14 at 08:11
  • I hope your `brand` and `item` tables are not empty and to your model table you put values from those tables? – Volvox Sep 11 '14 at 08:14
  • Now I have problem with the duplicate for PRIMARY KEY, can anybody help me please? Tell me what to do?? – Foster Sep 11 '14 at 08:35

1 Answers1

-1
$sql = "INSERT INTO model (model_id,item_model, brand_id, item_id, quantity, price)
                VALUES (null,'$model', '$brand', '$cat', '$quantity', '$price')";

Try this.

Add auto increment field and pass value null

NullPoiиteя
  • 56,591
  • 22
  • 125
  • 143
Paresh Gami
  • 4,777
  • 5
  • 23
  • 41