CODEWITHSUNDEEP
phpmysqldatabasewebmany-to-many
Mohamed Dekna
Mohamed Dekna

Reputation: 11

How to insert records to a many to many relationship table on an sql database using php?

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.

$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

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

Upvotes: 0

Views: 2638

Answers (1)

Captain Red
Captain Red

Reputation: 1171

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

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

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

$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);

Upvotes: 1

Related Questions