Adding multiple tables to insert query in PHP

Question

I am inserting a record to PHP Query with one table and it works fine.

    $Query="INSERT INTO siparisler (firma)
SELECT musteri_ismi from musteri_ekle where musteri_id='$Musteri_id'";

However, when I try to insert the query from the second table, it does not work:

    $Query="INSERT INTO siparisler (firma,urun)
SELECT musteri_ismi from musteri_ekle where musteri_id='$Musteri_id',
urun_id from urun_ekle where urun_id='$Urun_id'";

What is the way to insert two data from different tables to a Query?

You specify TWO columns you are inserting into `(firma,urun)` BUT you are only selecting a single column in your SELECT statement: `SELECT musteri_ismi from musteri_ekle... ` If you would like to fill both `firma` and `urun` you will need to specify two columns to fill those respectively from your SELECT` — JNevill, Jun 22 '18 at 13:35
Furthermore "It does not work" is a little vague here. You are getting an error, or you are expecting something to happen and something else is happening instead. It would immensely to turn PHP error reporting on and share the error, or dig through your log and share the error, or specify exactly what you want to have happen and what is happening instead. — JNevill, Jun 22 '18 at 13:37
@JNevill He actually tries to select two columns. Just look at `urun_id `. But he's doing it wrong since it's not SQL. — revo, Jun 22 '18 at 13:44
@JNevill I try to insert two variables from two different tables to two rows of table "siparisler". If I select both variables in one select query, how can I insert it to two different columns which are (firma, urun) ? — Jefffy, Jun 22 '18 at 13:56
@revo I see that now. I've never seen an attempt like that so I totally overlooked it. OP, Your SELECT statement needs to execute and return a result set. That result set is then inserted into the respective columns specified in your `INSERT INTO` line. You need to craft a SELECT statement that can actually run, the one you've written is nonsense. You'll either need to use a JOIN in your FROM clause or execute two separate statements (an INSERT than an UPDATE). If you can share some sample data and your desired inserted record we can help you craft the SQL. — JNevill, Jun 22 '18 at 14:08
While debugging this, use your `phpmyadmin` UI (or a mysql client) to craft your `SELECT` statement and test that it's returning two columns with the data you want to insert. Then once that is built properly you can stick it in your PHP. Lastly I STRONGLY caution you to switch to prepared statements. As written your code is susceptible to a SQL Injection Attack. Check out [mysqli bind_param](http://php.net/manual/en/mysqli-stmt.bind-param.php) or [PDO](http://php.net/manual/en/book.pdo.php) documentation to learn how to properly parameterize your SQL and avoid a costly hack. — JNevill, Jun 22 '18 at 14:10
Lastly, lastly... [turn on PHP error reporting](https://stackoverflow.com/questions/1053424/how-do-i-get-php-errors-to-display) so you can see specifically why your SQL is failing. Mysql is pretty verbose and it's error messages can help you hone in on the issues. Spending some time understanding how an RDBMS works and [what a JOIN is](https://www.w3schools.com/sql/sql_join.asp) would be helpful too. — JNevill, Jun 22 '18 at 14:13
Solved: I am new to PHP and MYSQL so I had to look for inner join. The main problem was, I was getting the id number from dropdown menu and tried to change it to the value of string in order to get the value from that unique table. That is why I tried to insert string value into the row to that table. However by using INNER JOIN and creating foreign key from musteri_id to the other table was sufficient to take all data. $ViewQuery="SELECT * FROM siparisler INNER JOIN musteri_ekle ON siparisler.musteri_id = musteri_ekle.musteri_id"; Thanks for help for inner join. — Jefffy, Jun 23 '18 at 09:46

score 0 · Answer 1 · answered Jun 22 '18 at 19:47

INSERT
     INTO  siparisler (firma, urun) 
SELECT
        (
        SELECT  musteri_ismi
            from  musteri_ekle
            where  musteri_id='$Musteri_id' 
        ) AS firma, 
        (
        SELECT  urun_id
            from  urun_ekle
            where  urun_id='$Urun_id' 
        ) AS urun ;

Adding multiple tables to insert query in PHP

1 Answers1