如何使用PHP将记录插入sql数据库上的多对多关系表？

I just started PHP and am encountering a problem with php where the sql command shown below does not execute (inserts records into a many to many relationship table) when calling it with php while it works perfectly when manually inserting it with mysql command line interface. I am using $ds and $da (manual inputs) instead of making use of sessions to make sure that the command is executed. Noting that I have other mysql queries and work perfectly fine when executed through php using the same database connection as the one shown hereunder.

我刚刚启动PHP并且遇到了php的问题，当使用php调用它时，下面显示的sql命令不会执行（将记录插入到多对多关系表中），而当使用mysql命令行界面手动插入时，它可以正常工作。我使用$ ds和$ da（手动输入）而不是使用会话来确保执行命令。注意到我有其他的mysql查询，并且使用与下面显示的相同的数据库连接通过php执行时工作得很好。

$connectionStatua = connect_db();
mysqli_query($connectionStatus, $sql);

$username = $_SESSION["username"];
$ds = "dekna";
$da = "dsa.jpg";
$query="INSERT INTO `tbl_users_files` (`user_ID`, `file_ID`)
SELECT `u.id`, `f.id` FROM `users` as `u` CROSS JOIN `tbl_uploads` as `f`
WHERE `username` = '$ds' AND `file` = '$da'";
mysqli_query($connectionStatus, $query);

Please take a look at the structure of my tables: mySql tables structure

请看一下我表的结构：mySql表结构

Also, evidence that the command works perfectly in mysql cli can be viewed here: ("dekna" has id 1 while dsa.jpg has id 44 - hence take a look at the last record): mySql cli - Command

此外，可以在这里查看命令在mysql cli中完美运行的证据:(“dekna”具有id 1而dsa.jpg具有id 44 - 因此查看最后一条记录）：mySql cli - Command

1 个解决方案

#1

Step 1> Get (Select) id of the user from the users table:

步骤1>从users表中获取（选择）用户的id：

Step 2> Get (Select) id of the file from tbl_uploads table:

步骤2>从tbl_uploads表获取（选择）文件的id：

Step 3> Insert user_ID (from step 1) and file_ID (from step 2) to tbl_users_files table:

步骤3>将user_ID（从步骤1）和file_ID（从步骤2）插入到tbl_users_files表：

$connectionStatua = connect_db();
mysqli_query($connectionStatus, $sql);

$username = $_SESSION["username"]; //or $ds = "dekna";
$da = "dsa.jpg";

$user_ID = $file_ID = null;

//STEP 1
$query1="SELECT id FROM 'users' WHERE username = '$username'";
$result1 = mysqli_query($connectionStatus, $query1);

if( mysqli_num_rows($result1 ) > 0)
  {
    while ($row = mysqli_fetch_array($result1)) {
     $user_ID = $row['id'];
    }
      
  }
//STEP 2
$query2="SELECT id FROM 'tbl_uploads' WHERE file = '$da'";
$result2 = mysqli_query($connectionStatus, $query2);

if( mysqli_num_rows($result2 ) > 0)
  {
    while ($row = mysqli_fetch_array($result2)) {
     $file_ID = $row['id'];
    }
      
  }
//STEP 3
$query3 ="INSERT INTO tbl_users_files (user_ID, file_ID) values('$user_ID' , '$file_ID')";

mysqli_query($connectionStatus, $query3);

#1