CODEWITHSUNDEEP
phpmysql
Habeeb Hussain
Habeeb Hussain

Reputation: 1

How do I Insert data from two tables into one table using MySQL PHP

I am new to this and sorry for being a bit of a noob. Basically what I have is three tables. One is a users table called "users_FP" which has a PKEY of "User_FP_ID", the next table is "uni_name" with a PKEY of "uni_id" and finally the junction table called "Favorites" with the same keys from the other two tables.

I am trying to create an "Add to favourite" function that will allow users to add a uni to their favourite. I have all the relationships set in PHPMyadmin. I am just haveing problems with the statement itself.

This is my code....

if ($result->num_rows > 0) {
    // output data of each row
echo "<form action='' method='post'>
<table class='table'>
<thead><tr>
<th>Name</th>
<th>Description</th>
<th>Address</th>
<th>Website</th>
<th>Favourites</th>
</tr>";
    while($row = $result->fetch_assoc()) {
       echo "<tr>";
       echo  '<td>'.$row['name'].'</td>';
       echo  '<td>'.$row['description'].'</td>';
       echo  '<td>'.$row['address'].'</td>';
       echo  "<td><a href='".$row['url']."'>Visit Site</a></td>";
       echo  '<td><input type="submit" name="submit" value="Add" class="btnAddAction"  /></td>';
       echo  "</thead></tr>";

    }
} else {
    echo "0 results";
}

if(isset($_POST['submit'])){

   $sql1 = "INSERT INTO Favorites(User_FP_ID,uni_id)
 VALUES ((SELECT User_FP_ID FROM users_FP WHERE User_FP_ID = '{$_SESSION['User_FP_ID']}'),
 (SELECT uni_id FROM uni_name WHERE uni_id=uni_id));";



if ($conn->query($sql1) === TRUE) {
    echo "New record added successfully";
} else {
    echo "Error: " . $sql1 . "<br>" . $conn->error;
}
}

Can anyone help me? it gives me the error "Subquery returns more than 1 row", but I don't understand why. Auto_increment is set

Upvotes: 0

Views: 786

Answers (1)

sagi
sagi

Reputation: 40491

You can't insert from two selects that return more then 1 result, it wont match so you have to join them like this:

"INSERT INTO Favorites(User_FP_ID,uni_id)
(SELECT s.User_FP_ID,t.uni_id FROM users_FP s
INNER JOIN uni_name t on t.uni_id = s.uni_id
WHERE User_FP_ID = '{$_SESSION['User_FP_ID']}');"

It looks like you were trying to join them because of that strange condition(uni_id = uni_id) but you didn't use join, so tell me if thats what you meant to do.

Upvotes: 1

Related Questions