CODEWITHSUNDEEP
phpmysql
jasonkim
jasonkim

Reputation: 706

Extract columns from different tables and insert into another one

So basically I have been trying to insert columns into my third table from columns extracted from 2 tables in prior.

I have one info table which stores information of users, one image table which stores head photos of users, and one id table which is like a reference table,designed to display correct head photos associated to correct users when necessary(like user querying or simply displaying...)

My code:

$result_1 = mysql_query("SELECT info_id FROM info WHERE info_name = '$_POST[name]'");
$result_2 = mysql_query("SELECT image_id FROM image WHERE image_name = '$_FILES[file][name]'");
$sql = "INSERT INTO id_table (main_id, id_info, id_image)
         VALUES (NULL, $result_1, $result_2);";
if(!mysql_query($sql,$connect_database)){
   die('Error: ',mysql_error());
}

So the codes above simply illustrated my idea:
1. get the id of info from info table
2. get the id of image from image table
3. insert both ids into columns in id_table respectively

Then I got this error information:

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'id #4, Resource id #5 )' at line 1

I could not figure out where the problem is...

Upvotes: 0

Views: 2156

Answers (2)

Mohan Shanmugam
Mohan Shanmugam

Reputation: 690

In your code you just execute the query. But you did not fetch the field or column value. you can fetch it by using mysql_fetch_array or mysql_fetch_row.

Am modify code to your exact need:

$result_1 = mysql_query("SELECT info_id FROM info WHERE info_name = '$_POST[name]'");    
$row1 = mysql_fetch_row($result_1);
$infoid = $row1[0];
$result_2 = mysql_query("SELECT image_id FROM image WHERE image_name = '$_FILES[file][name]'"); 
$row2 = mysql_fetch_row($result_2);
$imageid = $row2[0]
 
$sql = "INSERT INTO id_table (main_id, id_info, id_image)          VALUES (NULL, $infoid, $imageid);"; 
if(!mysql_query($sql,$connect_database)){    die('Error: ',mysql_error()); }

for more information refer :- https://www.php.net/manual/en/function.mysql-fetch-row.php https://www.php.net/manual/en/function.mysql-fetch-array.php

Upvotes: 0

Naftali
Naftali

Reputation: 146310

$result_1 and$result_2 just contain pointers to the results. you still have to loop through them to get the actual values.

Try this:

$result_1 = mysql_query("SELECT info_id FROM info WHERE info_name = '$_POST[name]'");
$row1 = mysql_fetch_array($result_1); // get 1st result row

$result_2 = mysql_query("SELECT image_id FROM image WHERE image_name = '$_FILES[file][name]'");
$row2 = mysql_fetch_array($result_2); // get 2nd result row

$sql = "INSERT INTO id_table (main_id, id_info, id_image)
         VALUES (NULL, '{$row1['info_id']}', '{$row1['image_id']}');";
if(!mysql_query($sql,$connect_database)){
   die('Error: ',mysql_error());
}

Upvotes: 1

Related Questions