Session Variables to insert data from one table to another.

Question

I have set up a session variable for the Users ID when the user logs in - here's the code:

<cflock timeout=999 scope="Session" type="Exclusive">
  <cfset Session.IDUsers = "">
</cflock>

Then I am trying to use this session variable to insert the Users ID into another table called 'Comments' when the users add a comment - though it is not working, here's my code for the insert comment + sessionvariable:

<cfquery ...>
  INSERT INTO comment (.... IDUsers)
  VALUES (...info ...)
  SELECT IDUsers
  FROM users
  WHERE users.IDUsers = <cfqueryparam value="#Session.IDUsers#">
</cfquery>

Though this isn't working. Here's the 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 'SELECT IDUsers FROM users WHERE users.IDUsers = ''' at line 9. 

Anyone know what I'm to do?

Answer 1

you cant include the "VALUES (...info...)" AND a select. you have to choose one or the other and I'm guessing thats the SQl problem. Also I dont see much point in doing the select when you already have the id. So you should just do this:

<cfquery ...>
  INSERT INTO comment (.... IDUsers)
  VALUES (other fields...<cfqueryparam value="#Session.IDUsers#">)
</cfquery>

if you must do the other query to make sure it's in the db then just do another query above this one to get the id.

Answer 2

Have you dumped the session scope to confirm that the IDUser is not blank?

<cfdump var="#session.IDUser#">

You might consider testing that the Session.IDUsers value is valid before running the query. If that value is blank I suspect it would throw an error.

if it's text based:

<cfif len(session.IDUsers)>
    <cfquery...

if it's numerical:

<cfif isNumeric(session.IDUsers)>
    <cfquery...

If possible, I would also recommend running your query in the MySQL query browser as it would help you troubleshoot your SQL syntax.

Answer 3

It's actually answered in Dan's post above, but as it looks like you've not got it working yet ...

<cfif len(session.IDUsers)>
 <!--- the session variable has some value assigned so it can be used in the query --->
 <cfquery datasource='yourDataSourceName' name="addComment">
    INSERT INTO comment (IDUsers, column1, anotherDataColumn)
    VALUES (<cfqueryparam value="#Session.IDUsers#">, #column1Data#, #anotherColumnData#)
 </cfquery>
<cfelse>
 <!--- session.IDUsers is null length --->
 <cfoutput>session.IDUsers is not yet assigned a value and therefore has no length</cfoutput>
 <cfabort>
</cfif>