如何使用PHP插入或更新外键值

I did 2 tables in mysql database

我在mysql数据库中做了2个表

user_details
user_details
bank_details
银行明细

In user_details am create following entity

在user_details中，我创建以下实体

user_id as a Primary Key
user_id作为主键
username
用户名
password
密码
address
地址

In bank_details am create following entity

在bank_details中创建以下实体

id as a Primary Key
id作为主键
user_id as a Foreign Key
user_id作为外键
bank_name
银行名
ac_no
ac_no

First am insert user details using following code

首先使用以下代码插入用户详细信息

<?php
$un = $_POST['un'];
$ps = $_POST['ps'];
$adr = $_POST['adr'];

$sql = mysql_query("insert into user_details username='$un', password='$ps', address='$adr'");
?>

Now i need to insert Bank Details in bank_details table

现在我需要在bank_details表中插入银行详细信息

<?php
$bn = $_POST['bn'];
$ac_no = $_POST['ac'];
$sql = mysql_query("insert into bank_details user_id= ?? bank_name='$bn', ac_no='$ac_no'");
?>

How can i define that foreign key values here ?

我如何在这里定义外键值？

1 个解决方案

#1

Your query omits the MYSQL SET keyword. Anyway, you can do this, as per your code convention:

您的查询省略了MYSQL SET关键字。无论如何，您可以按照您的代码约定执行此操作：

<?php
$mysql = mysql_connect([...]

$un = mysql_real_escape_string($_POST['un'], $mysql);
$ps = mysql_real_escape_string($_POST['ps'], $mysql);
$adr = mysql_real_escape_string($_POST['adr'], $mysql);

$sql = mysql_query("insert into user_details SET username='$un', password='$ps', address='$adr'", $mysql);

if(!$sql)
{
  // something went wrong with the query, add error handling here
  trigger_error('query failed', E_USER_ERROR);
}
else
{
    $user_id = mysql_insert_id(); //get the id of the last inserted query/user
    $bn = mysql_real_escape_string($_POST['bn'], $mysql);
    $ac_no = mysql_real_escape_string($_POST['ac'], $mysql);
    $sql = mysql_query("insert into bank_details SET user_id = $user_id, bank_name='$bn', ac_no='$ac_no'", $mysql);
    if(!$sql)
    {
      // something went wrong with the query, add error handling here
      trigger_error('query failed', E_USER_ERROR);
    }
}
?>

I must point out, however, that using the mysql_* family of functions is deprecated, and you should seriously start using mysqli_* functions instead.

但是，我必须指出，不推荐使用mysql_ *系列函数，你应该认真地开始使用mysqli_ *函数。

UPDATE:

更新：

As Per CodeGodie's suggestion, here's the re-written code using mysqli_* functions:

根据Per CodeGodie的建议，这是使用mysqli_ *函数重写的代码：

<?php
$mysqli = mysqli_connect(SERVER_NAME, USER_NAME, PASSWORD, DB_NAME);

$un = mysqli_real_escape_string($_POST['un']);
$ps = mysqli_real_escape_string($_POST['ps']);
$adr = mysqli_real_escape_string($_POST['adr']);

$sql = mysqli_query($mysqli, "insert into user_details SET username='$un', password='$ps', address='$adr'");

if(!$sql)
{
  // something went wrong with the query, add error handling here
  trigger_error('query failed', E_USER_ERROR);
}
else
{
    $user_id = mysqli_insert_id($mysqli); //get the id of the last inserted query/user
    $bn = mysqli_real_escape_string($_POST['bn']);
    $ac_no = mysqli_real_escape_string($_POST['ac']);
    $sql = mysqli_query($mysqli, "insert into bank_details SET user_id = $user_id, bank_name='$bn', ac_no='$ac_no'");
    if(!$sql)
    {
      // something went wrong with the query, add error handling here
      trigger_error('query failed', E_USER_ERROR);
    }
}
?>

#1