INSERT combining SELECT and php variables

[bilibytes]

Hi,

i am trying to INSERT into a table lets say 4 values:
- value1
- value2
- value3
- value4

all these values are stored in variables($var1, $var2, $var3..), except one of them which is part of the final value ($var4).

$var4 may return the final value4 only if with a query to another table.
$var4 stores the name of a user.
and but I want to INSERT not the user_name, but the user_id.

"user_id" is in Table "users" and i can obtain it by doing :

SELECT user_id
FROM users
WERE user_name = $var4

but the insertion i want to do is into another table. so i would want to do for my final query:

INSERT INTO some_table (var1_col, var2_col, var3_col, user_id)
VALUES ($var1, $var2, $var3, (SELECT users.user_id 
FROM users
WHERE users.user_name = $var4 
) )

could this work?

should i specify in the INSERT that the cols pertain to some_table like:

INSERT INTO some_table (some_table.var1_col, some_table.var2_col, some_table.var3_col, some_table.user_id)
VALUES ($var1, $var2, $var3, (SELECT users.user_id 
FROM users
WHERE users.user_name = $var4 
) )

or am i totally wrong?

or should i first run the query to get the user_id and then make another one to INSERT?

thankyou

bilibytes

[r035198x]

From your explanation I gathered (correct me if I'm wrong) that it is possible that value4 may not contain a value to be inserted if the user check fails. Should
that mean that the whole insert should fail? If that is the case then you need to run the user check first to determine if you need to construct the insert statement at all.

[bilibytes]

Hi, thanks for reply

No, the insertion will always be successful.
The user is authorized to do this query because he authenticated well. So i don't need to check for his credentials at this point. I only want to get the value of his id, because i don't want the names of the users to appear on the table i make the insertion into.


So my question is:

how do i make an INSERTion into a table which needs to receive a value that is located on another table.

/**
 * User basic information
 */
Table 1-------------
-user_id
-user_name
 

/**
 * User extra information
 */
Table 2------------
-user_id
-mood
-foo
-foo2
...
 

the first table has the value i need (user_id) to get for the INSERTion on Table 2. I have all the information to be inserted into Table 2 except the user_id which is available from Table 1 by giving the user name.

i could do this to get my job done:

$resulting_user_id = "SELECT user_id FROM Table1 WHERE user_id = '$user_name'"

and then i would reuse the previos result ($resulting_user_id)to insert all the values:

"INSERT INTO Table2 (user_id, mood, foo, foo2) VALUES ($resulting_user_id, rest of the values already available)"

for that, i was wondering if i could make the query i posted on my first post, which shrinks all in a single one

if you have any suggestion, please let me know

Thank you

bilibytes

[r035198x]

Ok. You can put another select inside the values clause.

i.e values ('a', 'b', (select id from user where name = 'aName'))

[bilibytes]

Ok. You can put another select inside the values clause.

i.e values ('a', 'b', (select id from user where name = 'aName'))

thank you very much!