CODEWITHSUNDEEP
phpmysql
chronotrigga
chronotrigga

Reputation: 609

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

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 [email protected]  
 2   email john334   SDF*#EJEE [email protected]

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.

Upvotes: 0

Views: 1417

Answers (1)

Dimi
Dimi

Reputation: 1277

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'].'")';

Upvotes: 2

Related Questions