oks
i have 6 groups that have to submit a report on there day eg. group one submits day 1
and so on however where it gets to day 7 it should read group 1 = day 7
what i needed was to find the last record to calculate the last record against the current day. and also this
<cfset group_id = #last_result.group_id# + 1>
<cfif #group_id# gt #group_max.group_max#>
<cfset #group_id# = #group_min.group_min#>
</cfif>
<cfif #group_id# eq #user_groupid#>
Unlucky Dude You have to do a report today
<cfelse>
You don't have to do a report today!
</cfif>
</cfif>so all good!
IT WORKED!
thanks guys
rob.
On 02/04/2004, at 4:22 PM, Barry Beattie wrote:
Mike,
Rob (original poster) hasn't actually said it was after an insert and the MySQL V4.1 he is using does support SELECT @@identity
Rob, can we ask why you need the last record? it might help..
-----Original Message----- From: Michael Kear [mailto:[EMAIL PROTECTED] Sent: Friday, 2 April 2004 4:11 PM To: CFAussie Mailing List Subject: [cfaussie] RE: how can i last result in a table (mysql) ???
If there's a possibility of there being another record added after the insert, another way to find out the recordnumber of the record just added is to do a SELECT with enough of the just-inserted parameters to guarantee it's unique. For example:
<cfquery name="insert" datasource="#request.DSN#"> INSERT into TABLE (username, password, firstname, lastname, address1, address2, town, state, country, phone, etc etc )VALUES (#trim(form. username)#, #trim(form.password)#, #trim(form.firstname)# etc - all the form.stuff) </cfquery>
Then do a query on these values.
<cfquery name="getvalue" datasource="#request.DSN#"> SELECT from TABLE UserID WHERE username= (#trim(form. username)# AND password=#trim(form.password)# AND firstname= #trim(form. firstname)# AND lastname= #trim(form. lastname)# AND etc etc until you are certain you will not get any duplicates. </cfquery>
It's not as neat and tidy as the MSSQL @@IDENTITY method or the Oracle
equivalent, but unless you have a high volume of signups it works. It
has
the advantage that it doesn't have to be done immediately after the
insert.
It can be done any time. You could include it in a general-purpose CFC
as
one of the library of routines you use for all sorts of things.
Oh and you have to have a check in the errortrapping area to make sure
they
can't add a record where those already exist, thereby guaranteeing there
can
never be more than one with the same combination.
Cheers Mike Kear Windsor, NSW, Australia AFP Webworks http://afpwebworks.com
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Kevin Faulkner Sent: Friday, 2 April 2004 3:40 PM To: CFAussie Mailing List Subject: [cfaussie] RE: how can i last result in a table (mysql) ???
Ah, you spotted that, I'll revoke my rant about large or poorly indexed tables then.
However I do think you should use what the DB offers....
select * from myTable order by id desc limit 1
Is obviously there for a reason!
----- Original Message ----- From: "Aaron DC" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Friday, April 02, 2004 3:40 PM Subject: [cfaussie] RE: how can i last result in a table (mysql) ???
Ah I see... no I meantthe
<CFQUERY ... MAXROWS =1>
etc.
Aaron
----- Original Message ----- From: "Aaron DC" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Friday, April 02, 2004 3:38 PM Subject: [cfaussie] RE: how can i last result in a table (mysql) ???
The question was how to grab the last result in a table.
Why do you ask if it would be a waste of time ?
Aaron
----- Original Message ----- From: "Gareth Edwards" <[EMAIL PROTECTED]> To: "CFAussie Mailing List" <[EMAIL PROTECTED]> Sent: Friday, April 02, 2004 3:05 PM Subject: [cfaussie] RE: how can i last result in a table (mysql) ???
As in <cfoutput query="query" maxrows="1"> ???
If you were to do this wouldnt you be getting all of the rows from[EMAIL PROTECTED]table. Wouldnt this be a waste of time?[EMAIL PROTECTED]
Gareth.
--- You are currently subscribed to cfaussie as:To unsubscribe send a blank email to[EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
--- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
--- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
--- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
Robert Shaw Online Learning [EMAIL PROTECTED] MLC School Rowley Street Burwood 2134 Phone: 97471266 Mobile: 0422222515 Fax: 97453254
--- You are currently subscribed to cfaussie as: [EMAIL PROTECTED] To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia http://www.mxdu.com/ + 24-25 February, 2004
