CODEWITHSUNDEEP
phpsqlmysqlsql-insert
Answerme
Answerme

Reputation: 29

How to INSERT a value with a name from another table

I am looking to have a value from my users table column name (profile_img) insert into my news table to column name (profile_img1) with the other information the users submits.

Here is the query I am using so far

$name=$_REQUEST["title"];
$stdate=$_REQUEST["sdate"];
$endate=$_REQUEST["edate"];
$staddr=$_REQUEST["staddr"];
$addr2=$_REQUEST["staddr2"];
$city=$_REQUEST["city"];
$state=$_REQUEST["state"];
$zip=$_REQUEST["zip"];
$desc=$_REQUEST["desc"];
$file=$_REQUEST['photo'];
$link=$_REQUEST["link"];
$user=$_REQUEST["user"];
$profile_img1=$_REQUEST["profile_img1"];
$rsvp=$_REQUEST["rsvp"];        

$query = "INSERT INTO news (fname,stdate,endate,addr1,addr2,city,state,zip,name,size,type,content,link,description,user,profile_img1,rsvp) VALUES('" . mysql_real_escape_string($name) . "','$stdate','$endate','" . mysql_real_escape_string($staddr) . "','" . mysql_real_escape_string($addr2) . "','" . mysql_real_escape_string($city) . "','$state','$zip','".str_replace([",",":","\"","\\", "/", "*"," ","$","&","?",";","'","!","(",")","|","~","<",">","=","[","]","{","}","#","^","%","=","@","+","è","é"],"",$name) ."-".$stdate."-".$file."','0',' ',' ','" . mysql_real_escape_string($link)."','" . mysql_real_escape_string($desc) . "','$user','" . mysql_real_escape_string($rsvp)."')";

The name for the profile_img1 will go after the user value in the query but I cannot figure out how to get the name of the profile_img in the users table to the news table

here is what I have been trying:

$query = "INSERT INTO news (fname,stdate,endate,addr1,addr2,city,state,zip,name,size,type,content,link,description,user,profile_img1,rsvp) VALUES('" . mysql_real_escape_string($name) . "','$stdate','$endate','" . mysql_real_escape_string($staddr) . "','" . mysql_real_escape_string($addr2) . "','" . mysql_real_escape_string($city) . "','$state','$zip','".str_replace([",",":","\"","\\", "/", "*"," ","$","&","?",";","'","!","(",")","|","~","<",">","=","[","]","{","}","#","^","%","=","@","+","è","é"],"",$name) ."-".$stdate."-".$file."','0',' ',' ','" . mysql_real_escape_string($link)."','" . mysql_real_escape_string($desc) . "','$user','(SELECT profile_img FROM users WHERE username=`username`)''" . mysql_real_escape_string($rsvp)."')";

using this method causes the profile_img1 column in the news table to read (SELECT profile_img FROM users WHERE username=username) instead of what the profile_img column reads in the users table.

Also if I add a second INSERT query 

$q2 = mysql_query("INSERT INTO news (profile_img1) SELECT profile_img FROM users WHERE username='username'");

the query causes a new row to be created in the news table displaying only the profile_img from the users table, separate from the other data the user will enter.

My desired result is to have the user submit the data to the news table and the user image from the users table will be inserted into the news table in the row with the other data submitted so the data submitted and the user image is displayed together.

If you need more clarification please let me know

Upvotes: 1

Views: 75

Answers (2)

Chris Adams
Chris Adams

Reputation: 1097

The short answer is to do INSERT (...) SELECT ...

e.g.

$name=$_REQUEST["title"];
$stdate=$_REQUEST["sdate"];
$endate=$_REQUEST["edate"];
$staddr=$_REQUEST["staddr"];
$addr2=$_REQUEST["staddr2"];
$city=$_REQUEST["city"];
$state=$_REQUEST["state"];
$zip=$_REQUEST["zip"];
$desc=$_REQUEST["desc"];
$file=$_REQUEST['photo'];
$link=$_REQUEST["link"];
$user=$_REQUEST["user"];
$profile_img1=$_REQUEST["profile_img1"];
$rsvp=$_REQUEST["rsvp"];        

$query = "INSERT INTO news (fname,stdate,endate,addr1,addr2,city,state,zip,name,size,type,content,link,description,user,profile_img1,rsvp) SELECT '" . 
    mysql_real_escape_string($name) . "','$stdate','$endate','" . mysql_real_escape_string($staddr) .
    "','" . mysql_real_escape_string($addr2) . "','" . mysql_real_escape_string($city) . "','$state','$zip','".
    str_replace([",",":","\"","\\", "/", "*"," ","$","&","?",";","'","!","(",")","|","~","<",">","=","[","]","{","}","#","^","%","=","@","+","è","é"],"",$name) .
    "-".$stdate."-".$file."','0',' ',' ','" . mysql_real_escape_string($link)."','" . mysql_real_escape_string($desc) .
    "',provile_img,'" . 
    mysql_real_escape_string($rsvp)."'" .
    " FROM users WHERE username = '{$username}'";

This should turn into something like:

INSERT INTO news (fname, stdate, profile_img1, rsvp)
SELECT 'Bob', '2017-09-02', profile_img, 0
FROM users
WHERE username = 'jimbob'

The long answer involves pleas to not do your own escaping and re-structuring this bit to make it easier to read, and by extension, easier to maintain later

Upvotes: 0

deg
deg

Reputation: 445

Don't you already have the value for profile_img1 ?

$profile_img1=$_REQUEST["profile_img1"];

But anyway, of course if you do 2 inserts it will insert 2 lines.

You want to look up UPDATE .

And mysql_insert_id() to get the id of the last insert executed.

As in (at the end, after you know what you're doing):

UPDATE news SET profile_img1 = "whateveritis" where id = theidoftherowyoujustinserted

Upvotes: -1

Related Questions