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

Question

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

Try using `LIMIT 1` in your subquery to make sure it returns just a single record — Can O' Spam, Feb 10 '16 at 13:20
Possible duplicate of [subquery returns more than 1 row](http://stackoverflow.com/questions/14841945/subquery-returns-more-than-1-row) — Can O' Spam, Feb 10 '16 at 13:23
What defines the specific Uni instance to be added to the Favorites table? — Giorgos Betsos, Feb 10 '16 at 13:25
`WHERE uni_id=uni_id` is going to return every row from your `uni_name` table, but you are only allow to return one row when using the result in an `INSERT` — Patrick Q, Feb 10 '16 at 13:29
I should clarify that a multi-row result can be used with an `INSERT`, but not if you are using individual `SELECT`s for each column. What you probably want is a `JOIN`ed multi-row result, along the lines of the answer provided by sagi. That way, you just have one result-set with multiple rows. If you are looking to insert a row for each `uni_id` in `uni_name`, then you don't even need the `WHERE` clause for that table. If you only want to insert one specific `uni_id`, you need some way of specifying/finding it. — Patrick Q, Feb 10 '16 at 13:40

sagi · Answer 1 · 2016-02-10T13:24:38.670

1

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.

edited Feb 10 '16 at 13:24

answered Feb 10 '16 at 13:23

sagi

40,026
6
59
84

So I tried using your statement, but it didn't work, it actually just gives me a new error "Unknown column 's.uni_id' in 'on clause'" Which I believe is because that was not set earlier. Just to make it clear what I want to happen. Once the user clicks on add, what should happen is it should pick that uni_id from the uni_name table and grab the User_FP_ID from the session as the user can only be logged in to do this and insert it into the favorites table...Hope that makes sense. – Habeeb Hussain Feb 10 '16 at 13:27
In uni_name table, is there a user_fp_id to? if so I'll update my answer – sagi Feb 10 '16 at 13:35
No there is not a user_fp_id in there. I have changed your code so it looks 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']}');" I deleted everything from the Favorites table and tried inserting from the button, but it adds all the universities in the table and links it to the user that is logged in instead of adding that one specific university. Does that make sense? – Habeeb Hussain Feb 10 '16 at 13:39
It is hard to understand what might be wrong without an sql fiddle or at least being able to see the columns in each of the two tables. Can you give more information? In any case, to debug, split the query up and see what each half of the join returns. SELECT s.User_FP_ID FROM users_FP s WHERE s.User_FP_ID = '{$_SESSION['User_FP_ID']}' will give only one row if User_FP_ID is unique (if it's not there's your problem). Likewise, SELECT t.uni_id FROM uni_name t should give you all the universities. Does this happen? One more suggestion, use prepared statements to avoid problems with sql injection – geco17 Feb 10 '16 at 14:22

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

1 Answers1