How do I insert data into a third table using INNER JOIN?

Question

I'm using PHP to use inner join on two tables in mySQL and then trying to input all the data into a third table but am receiving a T_STRING error. My three tables are: mdl_user, mdl_courses, and emails (third table that is empty).

mdl_user

 id  auth  username  password  email  
 ============================================
 1   email thomas_90 #$(*#($SL tom@gmail.com  
 2   email john334   SDF*#EJEE john@gmail.com

mdl_courses

 id  userid  course
 ===================
 1   2     2
 2   11    2

emails

 userid  course  username  email
 ===============================

My php code is here: http://codepad.org/GBNqg1S7

<?php
$dbhost = 'm2697.sgded.com';
$dbuser = 'xxxxxxx';
$dbpass = 'xxxxxxx';
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
  die('Could not connect: ' . mysql_error());
}
$sql = 'SELECT userid, course, username, email 
        FROM mdl_courses
        INNER JOIN mdl_user
        ON mdl_courses.userid = mdl_user.id';


mysql_select_db('course_training');
$retval = mysql_query( $sql, $conn );
if(! $retval )
{
  die('Could not get data: ' . mysql_error());
}
while($row = mysql_fetch_array($retval, MYSQL_ASSOC))
{

$sql = 'INSERT INTO emails (userid, course, username, email) 
VALUES ($row['userid'], $row['course'], $row['username'], $row['email'])';


    echo "User ID:{$row['userid']}  <br> ".
         "Course: {$row['course']} <br> ".
         "Username: {$row['username']} <br> ".
         "Email: {$row['email']} <br> ".
         "--------------------------------<br>";
} 
echo "Fetched data successfully\n";
mysql_close($conn);
?>

Can anyone give some insight as to what I'm doing wrong? I thought I could use $row['variable'] to insert each row from the inner join.

Possible duplicate of [PHP Parse/Syntax Errors; and How to solve them?](http://stackoverflow.com/questions/18050071/php-parse-syntax-errors-and-how-to-solve-them) — Rajdeep Paul, Feb 10 '17 at 17:12

score 2 · Accepted Answer · answered Feb 10 '17 at 17:10

2

first of all, use PDO. Your code still has a lot of issues, most of which can be solved by using PDO.

Secondly, you can use $row['blah'] in php, but only if the string is surrounded by double quotes ". In your case, you can simply concatenate multiple values with your string and be done with it.

$sql = 'INSERT INTO emails (userid, course, username, email) 
VALUES ($row['userid'], $row['course'], $row['username'], $row['email'])';

is not valid php.

correct it to

$sql = 'INSERT INTO emails (userid, course, username, email) 
VALUES ("'.$row['userid'].'", "'.$row['course'].'", "'.$row['username'].'", "'.$row['email'].'")';

answered Feb 10 '17 at 17:10

Dimi

1,255
11
20

Thank you for your answer. I will switch PDO to connect to the db. As for inserting into the table, the values are not showing up in my empty third table. Is there any other approach I may be missing? – chronotrigga Feb 10 '17 at 17:17
do you actually run your sql command? mysql_query($sql,$conn); – Dimi Feb 10 '17 at 17:20
The data echos fine but just is not inserting. I believe line 17 is operating fine. – chronotrigga Feb 10 '17 at 17:49
Thank you for your help, mysql_query ran great! – chronotrigga Feb 10 '17 at 19:11

How do I insert data into a third table using INNER JOIN?

1 Answers1