php - 插入具有相同数据库但具有相同ID的两个表

Question

我想创建一个将值插入表的 php 代码，但我希望表一“id”和表二“product_id”是同一件事。这是我下面的代码，将值插入数据库，但表一“id”与表二“product_id”不对应

<?php

if($_SERVER['REQUEST_METHOD']=='POST'){

$name = $_POST['name'];
$image = $_POST['image'];
$price = $_POST['price'];
$stock = $_POST['stock'];
$description = $_POST['description'];
$status = $_POST['status'];

require_once('dbConnect.php');

$sql ="SELECT id FROM product ORDER BY id ASC";

$res = mysqli_query($con,$sql);

$id = 0;

while($row = mysqli_fetch_array($res)){
    $id = $row['id'];
}

$imagename = "$id.png";

$path = "uploads/$id.png";
$storage = "$id.png";

$actualpath = "http://localhost/markeet/$path";


$sql = "INSERT INTO product (name,image,price,stock,draft,description,status,created_at,last_update) VALUES ('$name','$storage','$price','$stock','0','$description','$status','','');";

$sql .= "INSERT INTO product_category (product_id, category_id)
VALUES ('', '$stock');";

if ($con->multi_query($sql) === TRUE) {
    echo "New records created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

if(mysqli_query($con,$sql)){
    file_put_contents($path,base64_decode($image));
    echo "Successfully Uploaded";
}

mysqli_close($con);
}else{
    echo "Error";
}

Answer 1

在您的代码中，您使用 multi_query() 依次运行两个查询。

我建议如下:

// Insert product
    $queryInsertProduct = "INSERT INTO product (name,image,price,stock,draft,description,status,created_at,last_update) VALUES ('$name','$storage','$price','$stock','0','$description','$status','','');";

/**
 * @TODO: 
 *  1. Alter table `product_category` and do `product_id` to match the field 
 *     type from table `product`.`product_id`
 *  2. Alter table `product_category` and create its own ID primary key field, 
 *     which can be different from `product_id` 
 */
    $queryInsertProductCategory = "INSERT INTO product_category (product_id, category_id)
    VALUES ('', '$stock');";


 // Run here first query to insert product $queryInsertProduct using mysqli_query()

 // Take ID of the insert product using function mysqli_insert_id()

 // Run second query $queryInsertProductCategory and provide the id from new insert product to the product_id field