multiple queries on one page

im sure this is probably simple, but what i have is on a particluar page the user can add a link and i enter it into the db using....

<cfquery name="AddLink" datasource="#DSN#">
INSERT INTO pld_link(
title,
description,
url,
owner_name,
owner_email,
Status,
CATEGORY_ID
)VALUES(
'#FORM.title#',
'#FORM.description#',
'#FORM.url#',
'#FORM.name#',
'#FORM.email#',                  
'0',
'#FORM.category#'
)
</cfquery>

Code (markup):

that inserts the link, and gives it an auto incremented id aswell. I reaslise its in coldfueion but iys the Mysql bit i need help with.

what i need to do, on the same page is get the id that is auto-created and then insert that into a different table,
so in the other table i would have something like

INSERT INTO link_other(
link_id,
)VALUES(
'****the id autocreated for the link above****',
)

Code (markup):

see what i mean?

thanks for any suggestions

Thanks
Mike Sharp

 

try this

$link_id=mysql_insert_id();

Code (markup):

 

im using coldfusion rather than php

 

you might try passing a hidden field to the database, something that will always be unique, i.e. based on timestamp or something like that.

create another field in your database to pass this arbitrary timestamp data to, then retrieve the link_id by doing a select query based on that timestamp.

once you've retrieved the record, simply insert the link_id out of your recordset into the other table.

you could also retrieve the link_id by simply doing a "select * from pld_link order by link_ID desc" and pull the link_ID out of the first record it retrieves, which should work fine, so long as there's not alot of simultaneous queries running... i.e. you may run into issues if 2 seperate queries are run at the same time. The first way would be guaranteed to work, but would be a bit more effort.

 

if i put that select statement on the same page would it do the first query first?

 

Yes, you can have multiple select queries on a single page, no problem. It should read through your code sequentially and run the codes in the order you put them in.

P.S.... when i read your posts, you always have the "family guy" voice in my head

 

hmm i tried

<cfquery name="AddLink" datasource="#DSN#">
	INSERT INTO pld_link(
                    title,
                    description,
                    url,
		    owner_name,
		    owner_email,
		    Status,
		    CATEGORY_ID 
                    
                    )
              VALUES(
                    '#FORM.title#',
                    '#FORM.description#',
                    '#FORM.url#',
                    '#FORM.name#',
		    '#FORM.email#',                   
                    '0',
		    '#FORM.category#'
			)
</cfquery>

<cfquery name="getlastid" datasource="#DSN#">
select * 
from pld_link 
order by link_ID desc
limit 1
</cfquery>

<cfquery name="addlinkvote" datasource="#DSN#">
	INSERT INTO link_vote(
                    link_id 
                    )
              VALUES(
		    '#getlastid.id#'
			)
</cfquery>

Code (markup):

no joy

adds the link but not the second bit

any ideas? im getting desperate

 

not for sure, can you write the addlinkvote query to the browser for debugging, to see what the compiled query looks like? might not be getting the variable properly or something -

 

Your incrementing id is: link_ID ? If so, then '#getlastid.id#' should be '#getlastid.link_ID#'

I'd also change the query you use to find the last id to:

"select max(link_ID) from pld_link"

-Erik

 

no the auto incrementing field is just ID,

ill give that a try,

thanks for all the help

 

aaaargh, *headbutting wall*

still no joy

 

sorted it, and for anyone with the same issue here was my solution

<!--- need to add link and voting stuff------------------------------------------------------------------------>

<cflock name="insertrecord" timeout="10">
<cfquery name="AddLink" datasource="#DSN#">
INSERT INTO pld_link(
title,
description,
url,
owner_name,
owner_email,
Status,
CATEGORY_ID

)
VALUES(
'#FORM.title#',
'#FORM.description#',
'#FORM.url#',
'#FORM.name#',
'#FORM.email#',
'0',
'#FORM.category#'
)
</cfquery>

<cfquery datasource="#dsn#" name="getnewID">
SELECT MAX(ID) AS NewID FROM pld_link
</cfquery>

<cfset newID = getnewID.NewID>
<cfquery name="AddLinkvote" datasource="#dsn#">
INSERT INTO link_vote(
link_id
)
VALUES
(
'#newID#'
)
</cfquery>

</cflock>
<!--- need to add link and voting stuff------------------------------------------------------------------------>

 

glad you sorted it out. I was starting to run out of ideas