set value in foregin key automatically

56
June 22, 2018, at 11:00 AM

I create two tables

Table brand

Brand_id(primary key),Brand_name
1                      gucci

Table Product

Product_id, Brand_id(foeign_key),   Product_name
1                                   holand

Both table is linked using keys, I want after I update brand name in brand table, the primary key id from brand table automatically insert in product brand_id. Is there any possible whenever I enter product name,the foreign key brand_id refelect same like primary key brand_id,

i also try to add code for primary key id match the foreign key id, but it doesnt work.

<?php
session_start();
$_SESSION['message']=''; 
$mysqli=new MySQLi('127.0.0.1','root','','demo');
if(isset($_POST["login"])) {
    $product_name = $mysqli->real_escape_string($_POST['product_name']);
    $brand_id = 'brand.brand_id';
    $sql ="INSERT INTO product(product_name,brand_id)"
        ."VALUES ('$product_name','brand_id')";     
    if($mysqli->query($sql)=== true) {
        $_SESSION['message'] =  "Registration successful!
                                                  Added  to the database!";
        header("location:multiple_table.php");
    }
}
else {
    $_SESSION['message'] = "User could not be added to the database!";      
}
?> 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>form in Design</title>
<link rel="stylesheet" type="text/css" href="mystyle.css" />
<link rel="stylesheet" type="text/css" href="form.css" />
</head>
<body>
<h1><?=$_SESSION['brand_name']?></h1>
<form method="POST" action=""><?=$_SESSION['message']?>
<div class="form_input">
<input type="text" name="product_name" placeholder="Enter your Product name"/>
</div>
<input type="submit" name="login" value="login" class="btn-login" />
</form>
</body>
</html>
Answer 1

I assume you want to use the brand whose name is in $_SESSION['brand_name']. You can use that to get the brand_id.

<?php
session_start();
$_SESSION['message']=''; 
$mysqli=new MySQLi('127.0.0.1','root','','demo');
if(isset($_POST["login"])) {
    $sql ="INSERT INTO product(product_name,brand_id)
           SELECT ?, brand_id
           FROM brand
           WHERE brand_name = ?";
    $stmt = $mysqli->prepare($sql);
    $stmt->bind_param("ss", $_POST['product_name'], $_SESSION['brand_name']);
    if($stmt->execute()) {
        $_SESSION['message'] =  "Registration successful!
                                                  Added  to the database!";
        header("location:multiple_table.php");
    }
}
else {
    $_SESSION['message'] = "User could not be added to the database!";      
}
?> 

It would be even easier if you put the brand ID into another session variable, like $_SESSION['brand_id']. Then you don't need to do a query to get the ID.

You should also learn to use prepared statements instead of substituting variables. Even using $mysqli->real_escape_string() is not as safe from SQL injection.

READ ALSO
Woocommerce Registration - Phone Number Verification Via OTP

Woocommerce Registration - Phone Number Verification Via OTP

I have a Custom Woocommerce Registration formI am trying to verify customers Mobile Number by sending an OTP before registration

81
How to change array header and insert the array into MySQL database in PHPExcel?

How to change array header and insert the array into MySQL database in PHPExcel?

I have a piece of code for making an Excel Import (xlsx)

135
How can I add part of each file name to an array generated by glob?

How can I add part of each file name to an array generated by glob?

PHP beginner here; this is a farily basic question and I can't find a simple explanation or example on SE after searching

79
Show a slider per each product category and attribute

Show a slider per each product category and attribute

I have a problem with a code that I add in my archive-productphp, I want to add a slider per each category and attribute, so I have this code:

58