from:"Stuart Kidd"

Yep, that worked a treat.  I did a little changing to take in to  
account for time as my site is hosted in the US and i'm in the UK:


SELECT articleID, COUNT(topRankedID) AS MyHits
FROM tbl_020articleTopRanked
WHERE DateViewed BETWEEN #CreateODBCDateTime(DateAdd("h", -17, now())) 
# AND #CreateODBCDateTime(DateAdd("h", 5, now()))#
GROUP BY articleID
ORDER BY COUNT(articleID) DESC


Thanks for your help.


On 3 Nov 2005, at 14:51, Alan Rother wrote:

> Sorry, it's early, not enough caffeine and I didn't test the  
> code I
> forgot to remove the ticks from around the date/time entries in the  
> query...
>  
> SELECT articleID, COUNT(ID) AS MyHits
> FROM tbl_020articleTopRanked
> WHERE DateCreated BETWEEN #CreateODBCDateTime(Now())# AND
> #CreateODBCDateTime(DateAdd("h", -14, Now()))#
> GROUP BY articleID
> ORDER BY COUNT(ID) DESC
> 
> --
> Alan Rother
> Macromedia Certified Advanced ColdFusion MX 7 Developer
>
>
> --
> Alan Rother
> Macromedia Certified Advanced ColdFusion MX 7 Developer
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223039
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Finding top ranked articles

Brian,

The main problem with that however is that i have no way of knowing  
when somebody visited the site so I can pick out the top ranked  
articles from the previous 12 hours.

Thanks,

Saturday


On 3 Nov 2005, at 14:39, Brian Peddle wrote:

> Why does it matter if someone already viewed the article?  If you  
> are going
> to remove historical data anyway, after 14 hours, what does it  
> matter if the
> person visited 2 hours later or 15 hours later.  What about people  
> using
> proxy servers, you may be blocking out unique views.
>
> I think I would axe that, add a column to the article table called  
> views.
> Create a stored proc or query that selects the article and then  
> does an
> update.  Update article_table set views = views + 1.  Or something  
> like
> that.
>
> Then no need to worry about space and scheduling a delete.
>
> -Original Message-
> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
> Sent: Thursday, November 03, 2005 8:48 AM
> To: CF-Talk
> Subject: Finding top ranked articles
>
> Hi guys,
>
> Late last night in a matter of moments I found myself creating a
> table called tbl_020articleTopRanked .  The idea was to do something
> similar to news stories where they display their 5 top-viewed
> articles (like at SMH: http://www.smh.com.au - at the bottom end of
> the page).
>
> So in my creation I created this table and put 4 fields there,
> topRankedID (PK - incrementing), articleID (ID of the article which
> has just been viewed), dateViewed (when it was viewed) and lastly
> ipAddress (client's IP address).
>
> Each time the page gets viewed a check is made to see whether this IP
> has already viewed the page in the last few hours.  If they have then
> an insert doesn't happen but if they haven't then a record is  
> inserted.
>
> It all works fine and I'm very happy, but i'm just trying to work out
> the best way to work out what are the top viewed pages in the last 12
> hours.  I also will create a script to delete, say, all inserts over
> 14 hours old so that i'm not using too much MS-SQL space (this i'll
> do as a CFSCHEDULE or get the hosting company to set up a schedule to
> run that script.
>
> Any advice or help would be greatly appreciated.
>
> Thanks,
>
> Saturday
>
>
>
>
>
> 

~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223019
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Xmlformat() ... well not formatting

I'm forever having problems with that type of thing.  On my site club  
owners can enter their club details, venues etc and I feed it out via  
XML.  I have stuff like this in it:

event = replace(myQuery.eventName[ctr], "<", "<", "ALL");
event = replace(event, "&", "&", "ALL");
event = replace(event, '"', """, "ALL");
event = replace(event, '', "'", "ALL");
event = replace(event, '', "'", "ALL");
event = replace(event, '', "'", "ALL");
event = replace(event, '', "'", "ALL");
event = replace(event, '', "-", "ALL");

It's really annoying as I can never be 100% sure the feed will always  
be error free as I don't control what club owners put into their  
details.

On 3 Nov 2005, at 13:33, gabriel l smallman wrote:

> Yup, your right, it is, blast. Any ideas if something pre-built 
> (function)
> that might be of use here?
>
> Thanks
>
> gabe
>
> -Original Message-
> From: Thomas Chiverton [mailto:[EMAIL PROTECTED]
> Sent: Thursday, November 03, 2005 8:26 AM
> To: CF-Talk
> Subject: Re: Xmlformat() ... well not formatting
>
> On Thursday 03 November 2005 13:23, gabriel l smallman wrote:
>> fields. The documentation tells me that xmlformat should replace this
>> value ASCII 241 with its escape. But no joy. Documentation shows that
>> it should
>
> Are you sure it isn't a unicode character ?
>
> -- 
>
> Tom Chiverton
> Advanced ColdFusion Programmer
>
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223007
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Finding top ranked articles

Hi guys,

Late last night in a matter of moments I found myself creating a  
table called tbl_020articleTopRanked .  The idea was to do something  
similar to news stories where they display their 5 top-viewed  
articles (like at SMH: http://www.smh.com.au - at the bottom end of  
the page).

So in my creation I created this table and put 4 fields there,  
topRankedID (PK - incrementing), articleID (ID of the article which  
has just been viewed), dateViewed (when it was viewed) and lastly  
ipAddress (client's IP address).

Each time the page gets viewed a check is made to see whether this IP  
has already viewed the page in the last few hours.  If they have then  
an insert doesn't happen but if they haven't then a record is inserted.

It all works fine and I'm very happy, but i'm just trying to work out  
the best way to work out what are the top viewed pages in the last 12  
hours.  I also will create a script to delete, say, all inserts over  
14 hours old so that i'm not using too much MS-SQL space (this i'll  
do as a CFSCHEDULE or get the hosting company to set up a schedule to  
run that script.

Any advice or help would be greatly appreciated.

Thanks,

Saturday



~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223005
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Find no records

Thanks for your help everyone, definitely got it working now.

Thanks,

Saturday


On 2 Nov 2005, at 22:54, Brian Peddle wrote:

> SELECT
>  u.userID,
>  u.firstName,
>  u.lastName,
>  u.emailAddress,
>  u.password
> FROM
>  tbl_020publicUsers u
> WHERE
>   not exists (select * from tbl_020eventDetails e where u.userID =
> e.userID)
>
>
> May perform a bit better than NOT IN
>
>
> -Original Message-
> From: Charlie Griefer [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 02, 2005 4:56 PM
> To: CF-Talk
> Subject: Re: Find no records
>
> SELECT
>  u.userID,
>  u.firstName,
>  u.lastName,
>  u.emailAddress,
>  u.password
> FROM
>  tbl_020publicUsers u
> WHERE
>  u.userID NOT IN (SELECT DISTINCT userID FROM tbl_020eventDetails)
>
>
> On 11/2/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>> Hi guys,
>>
>> Since getting help on my last problem which was finding records of
>> users (from user table) which have events in the events table I am
>> now trying to list all the users in the users table which don't have
>> any rows which match in the events table.
>>
>> 
>> SELECT
>>u.userID,
>>u.firstName,
>>u.lastName,
>>u.emailAddress,
>>u.password,
>>e.userID
>> FROM
>>tbl_020publicUsers u INNER JOIN tbl_020eventDetails e
>>ON u.userID <> e.userID
>> 
>>
>> I thought something like the above might work but doesn't seem to.
>>
>> Any help would be great, thanks.
>>
>> Saturday
>>
>>
>>
>>
>>
>
>
>
> 

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222985
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Find no records

Hi guys,

Since getting help on my last problem which was finding records of  
users (from user table) which have events in the events table I am  
now trying to list all the users in the users table which don't have  
any rows which match in the events table.


SELECT
u.userID,
u.firstName,
u.lastName,
u.emailAddress,
u.password,
e.userID
FROM
tbl_020publicUsers u INNER JOIN tbl_020eventDetails e
ON u.userID <> e.userID


I thought something like the above might work but doesn't seem to.

Any help would be great, thanks.

Saturday




~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222974
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: SQL distinct

Thanks for all your help on that one guys :)  All working now.


On 2 Nov 2005, at 19:36, Hassan Arteaga wrote:

> Hi Stuart
>
> Try "Inner Join"  with the key in each table
>
> IE:
>
> Imagine u have two tables Basket and basketDetails in an ecommerce
> application
>
> SELECT*
> FROM_C_Basket INNER JOIN
>  _R_BasketDetails ON _C_Basket.idBasket =
> _R_BasketDetails.idBasket
>
> In this case idbasket is primary key in _C_Basket ...
> Regards
> __
> M.Sc. Hassan Arteaga Rodríguez
> Microsoft Certified System Engineer
> IT Specialist
> DIGI Grupo de Desarrollo. COPEXTEL, S.A.
>
> Este email y sus adjuntos está dirigido solamente a los destinatarios
> consignados en el mismo y debe ser considerado confidencial. Si Ud.  
> no es el
> destinatario consignado o la persona responsable de entregar/enviar el
> presente, no podrá copiarlo o entregarlo/enviarlo a ninguna otra  
> persona ni
> utilizar el mismo en forma no autorizada. Dichas acciones están  
> prohibidas y
> pueden ser consideradas ilegales. Si Ud. recibiese este email por  
> error, por
> favor comuníquelo de inmediato al emisor del mismo.
>
> -Original Message-
> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, November 02, 2005 12:56 PM
> To: CF-Talk
> Subject: SQL distinct
>
>
> Hi guys,
>
> I'm trying to do something simple but having a bit of a bother.
>
>  SELECT u.userID,  
> u.firstName,
> u.lastName, e.eventID, e.userID FROM tbl_020publicUsers u,
> tbl_020eventDetails e WHERE u.userID = e.userID 
>
> I'm trying to get a list of all the users in table tbl) 
> 020publicUsers which
> have a record in the table tbl_020eventDetails.  However, I keep get
> multiple listings for the users who have multiple rows in
> tbl_020eventDetails.  I did trying adding the word DISTINCT after  
> SELECT but
> it didn't do anything.
>
> Any help would be gratefully appreciated.
>
> Thanks,
>
> Stuart Kidd
>
>
>
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222959
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: [SPAM : 8.1] Re: ColdFusion Podcast Series | Tips, Tricks and News

Snake, here's a link to what Podcasting is:

http://www.020.com/webs/02005/showArticle.cfm?id=234

On 2 Nov 2005, at 18:15, Snake wrote:

> What is a podcast anyway?
>
> -Original Message-
> From: Paul Stewart [mailto:[EMAIL PROTECTED]
> Sent: 02 November 2005 16:22
> To: CF-Talk
> Subject: [SPAM : 8.1] Re: ColdFusion Podcast Series | Tips, Tricks  
> and News
>
> My first podcast ever. Great. Very well done.
>
> Paul Stewart
> Site Developer
> [EMAIL PROTECTED]
> www.whichfranchise.com
>
> - Original Message -
> From: "Michael Haynie" <[EMAIL PROTECTED]>
> To: "CF-Talk" 
> Sent: Monday, October 31, 2005 11:56 PM
> Subject: Re: ColdFusion Podcast Series | Tips, Tricks and News
>
>
>
>> Episode II is up now, lots of good stuff including Ajax and Google  
>> Maps
>> API. If you are subscribed via iTunes, it seems to be lagging on  
>> the new
>> episode, but ipodder and the rest pulled it as we published. So,  
>> episode
>> II is there as well. Thanks for the kind words and we hope to do one
>> weekly and incorporate code, examples, and news. Thanks for  
>> listening!
>>
>> Michael
>>
>>
>>
>>
>
>
>
> 

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222933
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

SQL distinct

Hi guys,

I'm trying to do something simple but having a bit of a bother.


SELECT u.userID, u.firstName, u.lastName, e.eventID, e.userID
FROM tbl_020publicUsers u, tbl_020eventDetails e
WHERE u.userID = e.userID


I'm trying to get a list of all the users in table tbl)020publicUsers  
which have a record in the table tbl_020eventDetails.  However, I  
keep get multiple listings for the users who have multiple rows in  
tbl_020eventDetails.  I did trying adding the word DISTINCT after  
SELECT but it didn't do anything.

Any help would be gratefully appreciated.

Thanks,

Stuart Kidd



~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222920
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Simple SQL

2005-10-26 Thread Saturday (Stuart Kidd)

Thanks for that JP, that helped me too.  I'll try and make the  
gradual change-over to the standard sql-92.


On 26 Oct 2005, at 15:47, John Paul Ashenfelter wrote:

> On 10/26/05, Charlie Hanlon <[EMAIL PROTECTED]> wrote:
>
>> John Paul,
>> You stated
>>
>> "I'd *always* use the SQL standard of explictly stating the joins  
>> instead of
>> just letting the db figure it out"
>>
>> Is this a best practice that I should adopt.  I am self-taught and  
>> am not
>> aware that explictly joining the tables is more efficient than  
>> joining the
>> tables in the 'Where' clause'.
>>
>
> Short answer is yes, you should. While most databases accept the older
> format of just listing the tables in the FROM clause, you have a lot
> more flexibility with the SQL-92 syntax of
> {INNER|LEFT|RIGHT|FULL|OUTER|CROSS} JOIN that specifically sets what
> columns/criteria you're joining *on*. That standard's been around
> since, well, 1992, so its rare to find a db that doesn't support it.
>
> Not only is it easier to read what's going on (for example, you'd have
> to explicitly do a CROSS JOIN to get the results you were getting --
> which would be a dead giveaway that somethings wrong) but you can also
> do non-equi joins if you need to and the db supports it (non-equijoins
> allow you to join using two columns and an expression, like <10
> instead of just the = sign).
>
> Under the hood, the SQL parser in your database will translate two
> equivalent queries to the same execution plan, so it's not "more
> efficient" to use this syntax per se, but it is more standard, more
> readable, and more flexible -- and less prone to errors as your
> problems shows :)
>
>
>> thanks in advance for your clarification/confirmation...
>>
>> rgds,
>> Charlie Hanlon
>>
>>
>>
>> - Original Message -
>> From: "John Paul Ashenfelter" <[EMAIL PROTECTED]>
>> To: "CF-Talk" 
>> Sent: Wednesday, October 26, 2005 9:59 AM
>> Subject: Re: Simple SQL
>>
>>
>>
>>> On 10/26/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>>>
>>>> Hi guys,
>>>>
>>>> I'm trying to do a simple query but am having a blank and pulling
>>>> multiples.
>>>>
>>>>  
>>>>  
>>>>  SELECT e.eventID, e.eventName, e.eventTeaser,
>>>> e.eventDate, m.musicGenreID, m.musicGenreName
>>>>  FROM tbl_020eventDetails e, tbl_020musicGenres m
>>>>  WHERE e.eventDate >= #DateAdd("d", -1, UKtodayDate)#
>>>>  ORDER BY e.eventDate ASC
>>>>  
>>>>
>>>> What i'd like it to do is pull each event and the corresponding  
>>>> music
>>>> genre but instead each record is getting pulled multiple times all
>>>> with the same musicGenreID details.
>>>>
>>>
>>> Are you getting a cross join? Sounds like it -- I don't see what
>>> relates the tables in your query.
>>>
>>> I'd *always* use the SQL standard of explictly stating the joins
>>> instead of just letting the db figure it out
>>>
>>> SELECT e.eventID, e.eventName, e.eventTeaser, e.eventDate,
>>> m.musicGenreID, m.musicGenreName
>>> FROM tbl_020eventDetails e,
>>>  INNER JOIN tbl_020musicGenres m ON (whatever relates the tables)
>>> WHERE e.eventDate >= #DateAdd("d", -1, UKtodayDate)#
>>> ORDER BY e.eventDate ASC
>>>
>>> --
>>> John Paul Ashenfelter
>>> CTO/Transitionpoint
>>> (blog) http://www.ashenfelter.com
>>> (email) [EMAIL PROTECTED]
>>>
>>>
>>>
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222330
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Simple SQL

2005-10-26 Thread Saturday (Stuart Kidd)

Yes, I forgot that - doh!  Can't believe I forgot that.

Thanks Charlie! :)


> 
>  SELECT e.eventID, e.eventName, e.eventTeaser,
> e.eventDate, m.musicGenreID, m.musicGenreName
>  FROM tbl_020eventDetails e, tbl_020musicGenres m
>  WHERE e.eventDate >= #DateAdd("d", -1, UKtodayDate)#
>  
> AND e.musicGenreID = m.musicgenreid
>
>  ORDER BY e.eventDate ASC
>  
>
> Try this.  I think you are getting mulitple records because you're  
> original
> query did not join the two tables in you're 'From' clause.
>
> rgds,
> Charlie Hanlon
>
>
>
> - Original Message -
> From: "Saturday (Stuart Kidd)" <[EMAIL PROTECTED]>
> To: "CF-Talk" 
> Sent: Wednesday, October 26, 2005 9:49 AM
> Subject: Simple SQL
>
>
>
>> Hi guys,
>>
>> I'm trying to do a simple query but am having a blank and pulling
>> multiples.
>>
>> 
>> 
>> SELECT e.eventID, e.eventName, e.eventTeaser,
>> e.eventDate, m.musicGenreID, m.musicGenreName
>> FROM tbl_020eventDetails e, tbl_020musicGenres m
>> WHERE e.eventDate >= #DateAdd("d", -1, UKtodayDate)#
>> ORDER BY e.eventDate ASC
>> 
>>
>> What i'd like it to do is pull each event and the corresponding music
>> genre but instead each record is getting pulled multiple times all
>> with the same musicGenreID details.
>>
>> Any help I'd be grateful,
>>
>> Thanks,
>>
>> Saturday
>>
>>
>>
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222301
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Simple SQL

2005-10-26 Thread Saturday (Stuart Kidd)

Hi guys,

I'm trying to do a simple query but am having a blank and pulling  
multiples.

 
 
 SELECT e.eventID, e.eventName, e.eventTeaser,  
e.eventDate, m.musicGenreID, m.musicGenreName
 FROM tbl_020eventDetails e, tbl_020musicGenres m
 WHERE e.eventDate >= #DateAdd("d", -1, UKtodayDate)#
 ORDER BY e.eventDate ASC
 

What i'd like it to do is pull each event and the corresponding music  
genre but instead each record is getting pulled multiple times all  
with the same musicGenreID details.

Any help I'd be grateful,

Thanks,

Saturday




~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:96
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: stop: SHOUTING

2005-10-24 Thread Saturday (Stuart Kidd)

Thanks to everyone for their help on this.

Via everyone's help i've now got a solution.

:)

On 24 Oct 2005, at 19:28, Kerry wrote:

> 
> str="i HAVE MY CAPS LOCK ON BY MISTAKE!";
> ucasecount = len(rereplace(str,"[^A-Z]","","ALL"));
> lcasecount = len(rereplace(str,"[^a-z]","","ALL"));
> if(ucasecount gt lcasecount){
> writeoutput("More uppercase ("&ucasecount&") than lowercase  
> ("&lcasecount&")
> characters found. you might be SHOUTING.");
> }
> 

Stu

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222125
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

stop: SHOUTING

2005-10-24 Thread Saturday (Stuart Kidd)

Hi guys,

Just wondering if anyone knows a good way to recognise when all the  
characters are capitalised?  Some users love putting text in CAPS and  
it just looks awful when it's displayed on screen.

Any help I'd be grateful.

Thanks,

Saturday


~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:222100
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Moving directories via CF code

2005-10-19 Thread Saturday (Stuart Kidd)

Hi guys,

Does anyone know whether it's possible to move a directory and its  
contents to a different place without actually physically moving it  
via a shell command or dragging and dropping (on Windows).
Is there a CF command to do it in my script or would that be a  
security problem?

I have a directory structure like:

venues/venueID/events/eventID/image.jpg

For instance I may have 3 files under the directory

  /venues/12/events/8/

mypic.jpg, mypic2.jpg and mypic3.jpg.

It's possible an event will move to a different venue therefore it  
would be good if i could move the eventID directory and its contents  
to a new venueID directory.

Any ideas or is it just plain stupid what i'm doing?

Thanks,

Saturday

ps: i know i could have all the images one directory but it just  
keeps them in good order this way.




~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:221576
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Coldfusion - i'm moving on

2005-10-08 Thread Saturday (Stuart Kidd)

Hi guys,

I'm quite happy with myself because after so long i've been using CF
i haven't always been using it in 'rapid development'... but today
instead of keep re-writing code, usually in a copy & paste style then
a re-hash of variable names etc I decided I was going to create an
include file so I could reuse a form. In this case it was a 'contact
us form'.

I created an include file that had a form in it and also a type thingy so that if the form had been submitted then it
would action that.

So I have on my main page:

AND ON my include_formContactUs.cfm I have:

Blah blah

My form

However, I know this is probably the right time for me to do it
another way, but i'm just not sure how to do it. It works fine the
way it is but i know that for instance i have set the actionPage but
i'm sure there is a different way of doing that? It's not a
CFFUNCTION i have to make is it?

Is it worth doing it for this? I'm sure it would come handy because
so often I have written 'contact us' type action forms.

Thanks for your help,

Saturday

~|
Logware (www.logware.us): a new and convenient web-based time tracking
application. Start tracking and documenting hours spent on a project or with a
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220488
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Client variables

2005-10-08 Thread Saturday (Stuart Kidd)

Hi guys,

I have a competition on my site.  When a user enters the competition  
I set a client variable:




I tested the competition a few days ago and now when i come to look  
at it I can see that the Client variable  
client.competitionEntry#url.ID# must still be equalling 1 as this  
line is in effect:

 
 

 Show competition form here.

 
 

 You have entered this competition.

I automatically thought that client variables would end once the  
browser has been closed?  I have had my Mac switched off overnight,  
how could this be?

Anyone who can shed some light on this I'd be very grateful.

Thanks,

Saturday


~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220477
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

cftextarea and

2005-10-06 Thread Saturday (Stuart Kidd)

Hi guys,

I have a textarea in my form (Flash form) and I've noticed that if I  
make a paragraph break in my textarea the actual html comes out as  
two 's.

The code i'm using to output my textarea is:

#Replace(GetArticle.articleMain,"#chr(13)#","","all")#

Does anyone know of any better code to format the paragraph so that  
it is XHTML complaint, ie  and  always having an end tag .

Other parts of my site i have used Tiny MCE textarea, but this is a  
Flash Form so I can't use that.

Thanks for any input.

Saturday


~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220249
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Getting a variable

2005-10-04 Thread Saturday (Stuart Kidd)

Certainly lots of ways to skin a cat.

Thanks everyone! :)


On 4 Oct 2005, at 20:35, Bryan Stevenson wrote:

> 
>
> not tested...should fly
>
> Cheers
>
> Bryan Stevenson B.Comm.
> VP & Director of E-Commerce Development
> Electric Edge Systems Group Inc.
> phone: 250.480.0642
> fax: 250.480.1264
> cell: 250.920.8830
> e-mail: [EMAIL PROTECTED]
> web: www.electricedgesystems.com
>
> 

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220063
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Getting a variable

2005-10-04 Thread Stuart Kidd

Hi guys,

I've got a simple question just can't find my answer.

I want to dynamically define a variable.



So in the above case i would want to define, say 

I'm sure this can be done easily i just can't remember.

Thanks,

Saturday









~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:220057
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re:

2005-10-03 Thread Saturday (Stuart Kidd)

oh! easy as that, thanks everyone for your replies :)

Thanks!


On 3 Oct 2005, at 22:23, Adrian Lynch wrote:

> Wack some #'s in there.
>
> list="#incorrentList#"
>
> Ade
>
> -Original Message-
> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
> Sent: 03 October 2005 22:15
> To: CF-Talk
> Subject: 
>
>
> Hi guys,
>
> I've appended a list and on this occasion my list outputs 1,3,5 (for
> instance).
>
>  
>  #GetQuizQuestions.questionText[thisItem]# this
> wrong
>  
>
> The problem i'm coming across an error which says:
>
> The value "incorrectList" cannot be converted to a number
>
> Does anyone know a way around this?
>
> Thanks,
>
> Saturday
>
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219979
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

cf-talk@houseoffusion.com

2005-10-03 Thread Saturday (Stuart Kidd)

Hi guys,

I've appended a list and on this occasion my list outputs 1,3,5 (for  
instance).

 
 #GetQuizQuestions.questionText[thisItem]# this  
wrong
 

The problem i'm coming across an error which says:

The value "incorrectList" cannot be converted to a number

Does anyone know a way around this?

Thanks,

Saturday

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219974
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Online quiz

2005-10-03 Thread Saturday (Stuart Kidd)

Hi guys,

I have 5 questions in my MS-SQL database and when i want to display  
them in an online quiz I loop them through like so:


  
   
  


When these get submitted i look through my DB again to pull the  
answers.  And do something like:

 
 
 

The problem i'm having is somehow looping through the  
#form.questionID#'s as I don't want to hard-code each one like below  
as in some quizzes they'll be less than 5 questions and some more:







Also, will it be a problem that my form names which are ID's will be  
numbers, ie 1 or 2 or 3 etc?

Any help on this would be great,

Thanks,

Saturday


~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219920
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Now more query problems... boo hoo

2005-09-29 Thread Stuart Kidd

Thanks Jenny, I'd never thought of that idea - i'll give it a go and  
see what i come up with.


On 29 Sep 2005, at 14:41, Webmaster at FastTrack On Line wrote:




> Hi Stuart,
>
> I'm not the world best at SQL and it helps me to build my queries  
> in MS SQL.
> I keep queries running in ms sql where I can and only do it from a  
> cfm file
> when absolutely necessary.  Even if it needs to be done from the  
> cfm file, I
> still create it in ms sql first, then copy across the sql code.
>
> Sorry I can't help with your specific problem, but I hope you find  
> this
> works for you.
>
> Jenny
>
>
> - Original Message -
> From: "Saturday (Stuart Kidd)" <[EMAIL PROTECTED]>
> To: "CF-Talk" 
> Sent: Thursday, September 29, 2005 1:19 PM
> Subject: Now more query problems... boo hoo
>
>
>
>
>
>
>> Jumping from one query problem to another... just when it was safe to
>> to run a query another problem leapt out of the dungeons.
>>
>> I would like to only pull the top 8 authors who have the most
>> articles (articleID)
>>
>> I have specified only to get the 8 maxrows but the problem i'm having
>> is when i try to add to the ORDER BY COUNT(articleID) DESC
>>
>> My error is:
>>
>> [Macromedia][SQLServer JDBC Driver][SQLServer]Column
>> 'tbl_020authorDetails.authorID' is invalid in the select list because
>> it is not contained in an aggregate function and there is no GROUP BY
>> clause.
>>
>> It can be seen at: http://www.020.com/testsql.cfm
>>
>>
>> 
>> SELECT
>> tbl_020authorDetails.authorID,
>> tbl_020authorDetails.authorFirstName,
>> tbl_020authorDetails.authorSurname,
>> tbl_020authorDetails.authorPhotograph,
>> tbl_020authorDetails.authorAboutMe,
>> tbl_020articleDetails.articleID,
>> tbl_020articleDetails.articleTitle
>> FROM tbl_020articleDetails
>> INNER JOIN tbl_020authorDetails ON
>> tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
>> WHERE tbl_020authorDetails.authorPhotograph <> '' AND
>> tbl_020articleDetails.articleSubTypeID <> 55
>> ORDER BY tbl_020authorDetails.authorID, COUNT(articleID) DESC
>> 
>>
>> 
>> #authorFirstName#
>>
>> my articles:
>> #articleTitle#
>> 
>>
>> Any help would be great.
>>
>> Thanks,
>>
>> Stuart
>>
>>
>>
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219618
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Now more query problems... boo hoo

I'm managed to do the GROUP BY stuff and it's not throwing any errors  
but when I add the COUNT() in the order it isn't doing anything:


 SELECT
 tbl_020authorDetails.authorID,
 tbl_020authorDetails.authorFirstName,
 tbl_020authorDetails.authorSurname,
 tbl_020authorDetails.authorPhotograph,
 tbl_020authorDetails.authorAboutMe,
 tbl_020articleDetails.articleID,
 tbl_020articleDetails.articleTitle
 FROM tbl_020articleDetails
 INNER JOIN tbl_020authorDetails ON  
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
 WHERE tbl_020articleDetails.articleAuthenticated = 1 AND  
tbl_020authorDetails.authorPhotograph <> ''
 GROUP BY
 tbl_020authorDetails.authorID,
 tbl_020authorDetails.authorFirstName,
 tbl_020authorDetails.authorSurname,
 tbl_020authorDetails.authorPhotograph,
 tbl_020authorDetails.authorAboutMe,
 tbl_020articleDetails.articleID,
 tbl_020articleDetails.articleTitle
 ORDER BY tbl_020authorDetails.authorID, COUNT 
(tbl_020articleDetails.articleID) DESC



#authorFirstName#

my articles:
#articleTitle#


Any ideas why not?  It doesn't make any changes whatsoever.  The  
ORDER BY, is that just ordering the main query, what in this case as  
i just want to order the JOIN (tbl_020articleDetails)?

Thanks,

Stuart



On 29 Sep 2005, at 13:38, Taco Fleur wrote:

> You can use it in the order by clause, he just needs to group all the
> columns in his query with GROUP BY.
>
>
> Taco Fleur - Pacific Fox
> an industry leader with commercial IT experience since 1994 .
> http://www.pacificfox.com - Web Design and Development
>
>
>
>


~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219608
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Now more query problems... boo hoo

I did try to use COUNT(tbl_020articleDetails.articleID) AS  
totalArticles but came up with the error:

[Macromedia][SQLServer JDBC Driver][SQLServer]Column  
'tbl_020authorDetails.authorID' is invalid in the select list because  
it is not contained in an aggregate function and there is no GROUP BY  
clause.

 SELECT
 tbl_020authorDetails.authorID,
 tbl_020authorDetails.authorFirstName,
 tbl_020authorDetails.authorSurname,
 tbl_020authorDetails.authorPhotograph,
 tbl_020authorDetails.authorAboutMe,
 COUNT(tbl_020articleDetails.articleID) AS totalArticles,
 tbl_020articleDetails.articleTitle
 FROM tbl_020articleDetails
 INNER JOIN tbl_020authorDetails ON  
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
 WHERE tbl_020authorDetails.authorPhotograph <> '' AND  
tbl_020articleDetails.articleSubTypeID <> 55
 ORDER BY tbl_020authorDetails.authorID

#authorFirstName#

my articles:
#articleTitle#

On 29 Sep 2005, at 13:30, Adrian Lynch wrote:

> You can't use COUNT in the ORDER BY clause. (Please someone back me  
> up here
> else I'm off to spend some time with Book Online!).
>
> Ade

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219587
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Now more query problems... boo hoo

Jumping from one query problem to another... just when it was safe to  
to run a query another problem leapt out of the dungeons.

I would like to only pull the top 8 authors who have the most  
articles (articleID)

I have specified only to get the 8 maxrows but the problem i'm having  
is when i try to add to the ORDER BY COUNT(articleID) DESC

My error is:

[Macromedia][SQLServer JDBC Driver][SQLServer]Column  
'tbl_020authorDetails.authorID' is invalid in the select list because  
it is not contained in an aggregate function and there is no GROUP BY  
clause.

It can be seen at: http://www.020.com/testsql.cfm



 SELECT
 tbl_020authorDetails.authorID,
 tbl_020authorDetails.authorFirstName,
 tbl_020authorDetails.authorSurname,
 tbl_020authorDetails.authorPhotograph,
 tbl_020authorDetails.authorAboutMe,
 tbl_020articleDetails.articleID,
 tbl_020articleDetails.articleTitle
 FROM tbl_020articleDetails
 INNER JOIN tbl_020authorDetails ON  
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
 WHERE tbl_020authorDetails.authorPhotograph <> '' AND  
tbl_020articleDetails.articleSubTypeID <> 55
 ORDER BY tbl_020authorDetails.authorID, COUNT(articleID) DESC



#authorFirstName#

my articles:
#articleTitle#


Any help would be great.

Thanks,

Stuart

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219582
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: INNER JOIN

Thanks Ian... i think i had fried my brain during the time it took me  
to create that query from a few days ago until now... :)

It works now!

Stu


On 29 Sep 2005, at 12:42, Ian Tait wrote:

> You need to use something like ORDER BY authorID
> if you're grouping on it.
>
> Ian
>
>
>> -Original Message-----
>> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
>> Sent: 29 September 2005 12:37
>> To: CF-Talk
>> Subject: Re: INNER JOIN
>>
>> Hi,
>>
>> I've simplified it a bit:
>>
>> 
>>  SELECT
>>  tbl_020authorDetails.authorID,
>>  tbl_020authorDetails.authorFirstName,
>>  tbl_020authorDetails.authorSurname,
>>  tbl_020authorDetails.authorPhotograph,
>>  tbl_020authorDetails.authorAboutMe,
>>  tbl_020articleDetails.articleID,
>>  tbl_020articleDetails.articleTitle
>>  FROM tbl_020articleDetails
>>  INNER JOIN tbl_020authorDetails ON
>> tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
>>  WHERE tbl_020authorDetails.authorPhotograph <> '' AND
>> tbl_020articleDetails.articleSubTypeID <> 55 
>>
>> 
>> #authorFirstName#
>>
>> my articles:
>> #articleTitle# 
>>
>> This is now on a standalone page at: http://www.020.com/testsql.cfm
>>
>> The results are correct but instead of putting all of the
>> articles under the corresponding author it is putting
>> splitting them up and creating duplicate authors.
>>
>> Could it have anything to do with my tables in MS-SQL?  Is
>> there something else I have to do like do actual JOINS in the
>> DB and not just in the code?
>>
>> Thanks,
>>
>> Stuart
>>
>>
>>
>> On 29 Sep 2005, at 12:09, Adrian Lynch wrote:
>>
>>
>>> A quick suggestion, write a test page with the minimum of code in.
>>> It'll
>>> make it easier for you to debug and easier for us to help.
>>>
>>> Ade
>>>
>>> -Original Message-
>>> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
>>> Sent: 29 September 2005 10:50
>>> To: CF-Talk
>>> Subject: INNER JOIN
>>>
>>>
>>> Hi,
>>>
>>> I've been having trouble with JOINS.  My query below works
>>>
>> but when i
>>
>>> try outputting my results I am getting duplicate authors (authorID).
>>>
>>> 
>>>  SELECT
>>>  tbl_020authorDetails.authorID,
>>>  tbl_020authorDetails.authorFirstName,
>>>  tbl_020authorDetails.authorSurname,
>>>  tbl_020authorDetails.authorPhotograph,
>>>  tbl_020authorDetails.authorAboutMe,
>>>  tbl_020articleDetails.articleID,
>>>  tbl_020articleDetails.articleTitle
>>>  FROM tbl_020articleDetails
>>>  INNER JOIN tbl_020authorDetails ON
>>>
>> tbl_020articleDetails.authorID
>>
>>> = tbl_020authorDetails.authorID
>>>  WHERE tbl_020authorDetails.authorPhotograph <> ''
>>> 
>>>
>>>  
>>>  
>>>  >> onclick="javascript:clickarticle(this)">
>>>  
>>>  >> src="#PhotosWritersWeb##GetAuthors.authorPhotograph#">
>>>  
>>>  
>>>  #authorFirstName# #authorSurname#
>>>  I
>>>
>> was born
>>
>>> in the outer reaches of the galaxy and ever since first seeing my
>>> first book had a desire to write one.#ParagraphFormat
>>> (authorAboutMe)#
>>>  
>>>  
>>>  A list of my last articles
>>>  
>>>  
>>>  http://www.020.com/webs/02005/
>>> showArticle.cfm?id=#articleID#">#left
>>> (articleTitle, 35)#...#articleTitle#
>>>  
>>>  
>>>  
>>>  
>>>  
>>>  
>>>
>>>  
>>>  
>>>  #authorFirstName##authorSurname#
>>>  
>>>  
>>>
>>> I tried to make the join a RIGHT JOIN and it seemed to work
>>>
>> but then
>>
>>> will I add extra criteria to the WHERE i end up getting
>>>
>> more duplicate
>>
>>> authorIDs.
>>>
>>> Any help would be grateful,
>>>
>>> Saturday
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219579
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: INNER JOIN

Hi,

I've simplified it a bit:


 SELECT
 tbl_020authorDetails.authorID,
 tbl_020authorDetails.authorFirstName,
 tbl_020authorDetails.authorSurname,
 tbl_020authorDetails.authorPhotograph,
 tbl_020authorDetails.authorAboutMe,
 tbl_020articleDetails.articleID,
 tbl_020articleDetails.articleTitle
 FROM tbl_020articleDetails
 INNER JOIN tbl_020authorDetails ON  
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
 WHERE tbl_020authorDetails.authorPhotograph <> '' AND  
tbl_020articleDetails.articleSubTypeID <> 55



#authorFirstName#

my articles:
#articleTitle#


This is now on a standalone page at: http://www.020.com/testsql.cfm

The results are correct but instead of putting all of the articles  
under the corresponding author it is putting splitting them up and  
creating duplicate authors.

Could it have anything to do with my tables in MS-SQL?  Is there  
something else I have to do like do actual JOINS in the DB and not  
just in the code?

Thanks,

Stuart



On 29 Sep 2005, at 12:09, Adrian Lynch wrote:

> A quick suggestion, write a test page with the minimum of code in.  
> It'll
> make it easier for you to debug and easier for us to help.
>
> Ade
>
> -----Original Message-
> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
> Sent: 29 September 2005 10:50
> To: CF-Talk
> Subject: INNER JOIN
>
>
> Hi,
>
> I've been having trouble with JOINS.  My query below works but when i
> try outputting my results I am getting duplicate authors (authorID).
>
> 
>  SELECT
>  tbl_020authorDetails.authorID,
>  tbl_020authorDetails.authorFirstName,
>  tbl_020authorDetails.authorSurname,
>  tbl_020authorDetails.authorPhotograph,
>  tbl_020authorDetails.authorAboutMe,
>  tbl_020articleDetails.articleID,
>  tbl_020articleDetails.articleTitle
>  FROM tbl_020articleDetails
>  INNER JOIN tbl_020authorDetails ON
> tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
>  WHERE tbl_020authorDetails.authorPhotograph <> ''
> 
>
>  
>  
>   onclick="javascript:clickarticle(this)">
>  
>   src="#PhotosWritersWeb##GetAuthors.authorPhotograph#">
>  
>  
>  #authorFirstName# #authorSurname#
>  I was born
> in the outer reaches of the galaxy and ever since first seeing my
> first book had a desire to write one.#ParagraphFormat
> (authorAboutMe)#
>  
>  
>  A list of my last articles
>  
>  
>  http://www.020.com/webs/02005/
> showArticle.cfm?id=#articleID#">#left
> (articleTitle, 35)#...#articleTitle#
>  
>  
>  
>  
>  
>  
>
>  
>  
>  #authorFirstName##authorSurname#
>  
>  
>
> I tried to make the join a RIGHT JOIN and it seemed to work but then
> will I add extra criteria to the WHERE i end up getting more
> duplicate authorIDs.
>
> Any help would be grateful,
>
> Saturday
>
>
>
>
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219574
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

INNER JOIN

Hi,

I've been having trouble with JOINS.  My query below works but when i  
try outputting my results I am getting duplicate authors (authorID).


 SELECT
 tbl_020authorDetails.authorID,
 tbl_020authorDetails.authorFirstName,
 tbl_020authorDetails.authorSurname,
 tbl_020authorDetails.authorPhotograph,
 tbl_020authorDetails.authorAboutMe,
 tbl_020articleDetails.articleID,
 tbl_020articleDetails.articleTitle
 FROM tbl_020articleDetails
 INNER JOIN tbl_020authorDetails ON  
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
 WHERE tbl_020authorDetails.authorPhotograph <> ''


 
 
 
 
 
 
 
 #authorFirstName# #authorSurname#
 I was born  
in the outer reaches of the galaxy and ever since first seeing my  
first book had a desire to write one.#ParagraphFormat 
(authorAboutMe)#
 
 
 A list of my last articles
 
 
 http://www.020.com/webs/02005/ 
showArticle.cfm?id=#articleID#">#left 
(articleTitle, 35)#...#articleTitle#
 
 
 
 
 
 
   
 
 
 #authorFirstName##authorSurname#
 
 

I tried to make the join a RIGHT JOIN and it seemed to work but then  
will I add extra criteria to the WHERE i end up getting more  
duplicate authorIDs.

Any help would be grateful,

Saturday




~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219571
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Order by number of articles (count?)

Hi Robert,

When i tried your one I had this error coming up:

  Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]Column  
'tbl_020authorDetails.authorID' is invalid in the select list because  
it is not contained in an aggregate function and there is no GROUP BY  
clause.

Thanks,

Stuart

On 28 Sep 2005, at 23:20, Robert Munn wrote:

>  
>  SELECT SELECT COUNT 
> (tbl_020articleDetails.articleID) AS totalArticles,  
> tbl_020authorDetails.authorID,
> authorPhotograph, authorFirstName, authorSurname, authorAboutMe,
> tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,
> articleTitle, articleDatePublishedLast
>  FROM tbl_020authorDetails, tbl_020articleDetails
>  WHERE authorPhotograph <> ' ' AND
> tbl_020articleDetails.authorID = tbl_020authorDetails.authorID AND
> tbl_020articleDetails.articleAuthenticated = 1 AND
> tbl_020articleDetails.articleSubTypeID <> 55 AND
> tbl_020articleDetails.articleSubTypeID <> 68
>  ORDER BY COUNT(tbl_020articleDetails.articleID),  
> tbl_020authorDetails.authorFirstName,
> articleDatePublishedLast DESC
>  

Stu

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219536
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Order by number of articles (count?)

Hi Andy,

I tried this option out but it was coming up with an error saying  
that the query doesn't have a GROUP AS statement.

i then tried going back to your code from yesterday when i was  
getting help on the actual cfquery but still couldn't manage to get  
it to work.

At the moment i have it as:

 
 SELECT a.authorID, a.authorPhotograph,  
a.authorFirstName, a.authorSurname, a.authorAboutMe, b.articleID,  
b.authorID, b.articleTitle, a.articleDatePublishedLast
 FROM tbl_020authorDetails a
 INNER tbl_020articleDetails b
 WHERE authorPhotograph <> ' ' AND a.authorID =  
a.authorID AND b.articleAuthenticated = 1 AND b.articleSubTypeID <>  
55 AND b.articleSubTypeID <> 68
 

The error i am getting is:

  Error Executing Database Query.
[Macromedia][SQLServer JDBC Driver][SQLServer]'tbl_020articleDetails'  
is not a recognized join option.

Any help would be great,

Thanks.


On 28 Sep 2005, at 21:13, Andy Matthews wrote:

> The Count function goes in the SELECT portion of your statement.
>
> SELECT COUNT(tbl_020articleDetails.articleID) AS totalArticles
>
>  andy matthews
> web developer
> ICGLink, Inc.
> [EMAIL PROTECTED]
> 615.370.1530 x737
> --//->
>
> -Original Message-
> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, September 28, 2005 2:34 PM
> To: CF-Talk
> Subject: Order by number of articles (count?)
>
>
> Hi guys,
>
> I'm trying to work out how to order my query by the amount of  
> articles.
>
>  
>  SELECT tbl_020authorDetails.authorID,
> authorPhotograph, authorFirstName, authorSurname, authorAboutMe,
> tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,
> articleTitle, articleDatePublishedLast
>  FROM tbl_020authorDetails, tbl_020articleDetails
>  WHERE authorPhotograph <> ' ' AND
> tbl_020articleDetails.authorID = tbl_020authorDetails.authorID AND
> tbl_020articleDetails.articleAuthenticated = 1 AND
> tbl_020articleDetails.articleSubTypeID <> 55 AND
> tbl_020articleDetails.articleSubTypeID <> 68
>  ORDER BY tbl_020authorDetails.authorFirstName,
> articleDatePublishedLast DESC
>  
>
> How do I count how many articles there are, ie something like ORDER
> BY tbl_020articleDetails.articleID(Count as) or something?
>
> Thanks for any help,
>
> Saturday
>
>
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219526
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Order by number of articles (count?)

Hi guys,

I'm trying to work out how to order my query by the amount of articles.

 
 SELECT tbl_020authorDetails.authorID,  
authorPhotograph, authorFirstName, authorSurname, authorAboutMe,  
tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,  
articleTitle, articleDatePublishedLast
 FROM tbl_020authorDetails, tbl_020articleDetails
 WHERE authorPhotograph <> ' ' AND  
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID AND  
tbl_020articleDetails.articleAuthenticated = 1 AND  
tbl_020articleDetails.articleSubTypeID <> 55 AND  
tbl_020articleDetails.articleSubTypeID <> 68
 ORDER BY tbl_020authorDetails.authorFirstName,  
articleDatePublishedLast DESC
 

How do I count how many articles there are, ie something like ORDER  
BY tbl_020articleDetails.articleID(Count as) or something?

Thanks for any help,

Saturday


~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219505
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: restricting cfoutput character length

Thanks to everyone on that, i certainly got my answer and even more  
than i bargained for (Senor Tangorre)!

:)

On 28 Sep 2005, at 13:53, Rick Faircloth wrote:

> Showoff!  ;o)
>
> PS - Translates as "Put the dots in after your variable as regular  
> text...
>
> Rick
>
>
>
>> -Original Message-
>> From: Tangorre, Michael [mailto:[EMAIL PROTECTED]
>> Sent: Wednesday, September 28, 2005 8:42 AM
>> To: CF-Talk
>> Subject: RE: restricting cfoutput character length
>>
>>
>>
>>> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
>>> I'm trying to do something which i can imagine is very simple but
>>> can't find the function.   When i display a title
>>> #myTitle# i'd like to just display the
>>> first 15 characters and if more then put three dots
>>> (periods).  I know the LEN function but i can't work out how
>>> to replace the text with dots after the 15th character.
>>>
>>
>>
>> Let's see how many people I can piss off this morning with a nice
>> example using IIF and DE. :-)
>>
>> Replace "str" with your string.
>>
>> #IIF(Len(str) gt 15,DE(Left(str,15) & '...'),DE(str))#
>>
>> HTH,
>>
>> Mike
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219407
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

restricting cfoutput character length

2005-09-28 Thread Stuart Kidd

Hi,

I'm trying to do something which i can imagine is very simple but  
can't find the function.   When i display a title  
#myTitle# i'd like to just display the first 15  
characters and if more then put three dots (periods).  I know the LEN  
function but i can't work out how to replace the text with dots after  
the 15th character.

Any help would be great,

Thanks,

Saturday




Stu







~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219400
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Splitting cfoutput of grouped query

That works fantastically, thanks Deanna! :)

Saturday

On 27 Sep 2005, at 21:42, Deanna Schneider wrote:

> You just need to build your own counter to close the div.
>
> 
> 
> blah blah
> 
> 
> title
> 
> 
> 
> 
> 
> 
>
>
> On 9/27/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>
>>
>> Mike,
>>
>> I've tried various ways, like having a cfloop within the simple
>>  - but no luck.
>>
>> The code is:
>>
>> 
>> 
>> Name: #authorFirstName# #authorSurname#
>> About Me:#ParagraphFormat(authorAboutMe)#
>> Articles:
>> #articleTitle#
>> 
>> 
>>
>> What i'm trying to do is split up the article title so that i can
>> more or less create two divs for my html one that will have articles
>> 1-10 and then another which will have 11-20.
>>
>> Thanks,
>>
>> Saturday
>>
>>
>> On 27 Sep 2005, at 20:23, Dawson, Michael wrote:
>>
>>
>>> You have specified two  blocks. In addition, you
>>> need to specify a simple  block to continue the looping.
>>>
>>> You can have as many nested CFOUTPUT GROUPs, however, you usualy
>>> always
>>> need the simple CFOUTPUT to get the "details".
>>>
>>> M!ke
>>>
>>> -Original Message-
>>> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
>>> Sent: Tuesday, September 27, 2005 1:31 PM
>>> To: CF-Talk
>>> Subject: Splitting cfoutput of grouped query
>>>
>>> I'm having some trouble trying to split a query.
>>>
>>> 
>>> SELECT tbl_020authorDetails.authorID,
>>> authorPhotograph,
>>> authorFirstName, authorSurname, authorAboutMe,
>>> tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,
>>> articleTitle
>>> FROM tbl_020authorDetails, tbl_020articleDetails
>>> WHERE authorPhotograph <> ' ' AND
>>> tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
>>> ORDER BY tbl_020authorDetails.authorID
>>> 
>>>
>>>
>>> 
>>> 
>>> Name: #authorFirstName# #authorSurname#
>>> About Me:#ParagraphFormat(authorAboutMe)#
>>> Articles:
>>> >> group="articleTitle">#articleTitle#
>>> 
>>> 
>>>
>>> What i'd like to do is have something like below and repeated for  
>>> each
>>> author:
>>>
>>> AuthorFirstName AuthorSurname
>>>
>>> AboutMe text here.
>>>
>>> 
>>> List of articles 1 - 6
>>> 
>>>
>>> 
>>> List of articles 7-12
>>> 
>>>
>>> I've tried a cfloop but that didn't seem to work.
>>>
>>> Any help i would be truly grateful.
>>>
>>> Thanks,
>>>
>>> Saturday
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219384
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Splitting cfoutput of grouped query

Mike,

I've tried various ways, like having a cfloop within the simple  
 - but no luck.

The code is:

 
 
 Name: #authorFirstName# #authorSurname#
 About Me:#ParagraphFormat(authorAboutMe)#
 Articles:
 #articleTitle#
 
 

What i'm trying to do is split up the article title so that i can  
more or less create two divs for my html one that will have articles  
1-10 and then another which will have 11-20.

Thanks,

Saturday


On 27 Sep 2005, at 20:23, Dawson, Michael wrote:

> You have specified two  blocks.  In addition, you
> need to specify a simple  block to continue the looping.
>
> You can have as many nested CFOUTPUT GROUPs, however, you usualy  
> always
> need the simple CFOUTPUT to get the "details".
>
> M!ke
>
> -----Original Message-
> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, September 27, 2005 1:31 PM
> To: CF-Talk
> Subject: Splitting cfoutput of grouped query
>
> I'm having some trouble trying to split a query.
>
>  
>  SELECT tbl_020authorDetails.authorID,  
> authorPhotograph,
> authorFirstName, authorSurname, authorAboutMe,
> tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,
> articleTitle
>  FROM tbl_020authorDetails, tbl_020articleDetails
>  WHERE authorPhotograph <> ' ' AND
> tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
>  ORDER BY tbl_020authorDetails.authorID
>  
>
>
>  
>  
>  Name: #authorFirstName# #authorSurname#
>  About Me:#ParagraphFormat(authorAboutMe)#
>  Articles:
>   group="articleTitle">#articleTitle#
>  
>  
>
> What i'd like to do is have something like below and repeated for each
> author:
>
> AuthorFirstName AuthorSurname
>
> AboutMe text here.
>
> 
> List of articles 1 - 6
> 
>
> 
> List of articles 7-12
> 
>
> I've tried a cfloop but that didn't seem to work.
>
> Any help i would be truly grateful.
>
> Thanks,
>
> Saturday
>
>
>
>
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219365
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Splitting cfoutput of grouped query

I'm having some trouble trying to split a query.

 
 SELECT tbl_020authorDetails.authorID,  
authorPhotograph, authorFirstName, authorSurname, authorAboutMe,  
tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,  
articleTitle
 FROM tbl_020authorDetails, tbl_020articleDetails
 WHERE authorPhotograph <> ' ' AND  
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
 ORDER BY tbl_020authorDetails.authorID
 


 
 
 Name: #authorFirstName# #authorSurname#
 About Me:#ParagraphFormat(authorAboutMe)#
 Articles:
 #articleTitle#
 
 

What i'd like to do is have something like below and repeated for  
each author:

AuthorFirstName AuthorSurname

AboutMe text here.


List of articles 1 - 6



List of articles 7-12


I've tried a cfloop but that didn't seem to work.

Any help i would be truly grateful.

Thanks,

Saturday




~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219355
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Queries of queries

I'm not sure if it got posted as i got a reply saying there were over  
100 lines in the email so:

Hi,

Thanks guys! I have given all replies a try but managed to get it
done this way better... but it's only outputting 1 articleTitle and
not the whole lot.  Sorry that i didn't state my problem - it was
really because i couldn't work out what i was doing and didn't
realise the possibility of having a  within a  so
was just in total confusion.

I'm re-doing all of my site (this hasn't been released yet): http://
www.020.com/index_020homepage2.cfm?cp=11 and then click on the
CONTACT US link in the text.

In my new site it's all divs instead of tables so it just seems very
new having all of my queries being done all at once even though most
people may not even look at this section.

My code now stands at:

  
  SELECT tbl_020authorDetails.authorID,
authorPhotograph, authorFirstName, authorSurname, authorAboutMe,
tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,
articleTitle
  FROM tbl_020authorDetails, tbl_020articleDetails
  WHERE authorPhotograph <> ' ' AND
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
  

  

  
  
  
  
  
  
  #authorFirstName# #authorSurname#
  I was born
in the outer reaches of the galaxy and ever since first seeing my
first book had a desire to write one.#ParagraphFormat
(authorAboutMe)#
  
  
  #articleTitle#
  
  
  

  


Any help would be great, thanks.

Saturday


~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219348
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Queries oh queries

Hi,

Thanks guys! I have given all replies a try but managed to get it  
done this way better... but it's only outputting 1 articleTitle and  
not the whole lot.  Sorry that i didn't state my problem - it was  
really because i couldn't work out what i was doing and didn't  
realise the possibility of having a  within a  so  
was just in total confusion.

I'm re-doing all of my site (this hasn't been released yet): http:// 
www.020.com/index_020homepage2.cfm?cp=11 and then click on the  
CONTACT US link in the text.

In my new site it's all divs instead of tables so it just seems very  
new having all of my queries being done all at once even though most  
people may not even look at this section.

My code now stands at:

 
 SELECT tbl_020authorDetails.authorID,  
authorPhotograph, authorFirstName, authorSurname, authorAboutMe,  
tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,  
articleTitle
 FROM tbl_020authorDetails, tbl_020articleDetails
 WHERE authorPhotograph <> ' ' AND  
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
 

 

 
 
 
 
 
 
 #authorFirstName# #authorSurname#
 I was born  
in the outer reaches of the galaxy and ever since first seeing my  
first book had a desire to write one.#ParagraphFormat 
(authorAboutMe)#
 
 
 #articleTitle#
 
 
 

 


On 27 Sep 2005, at 16:13, Deanna Schneider wrote:

> You don't explain what your "problems" are. If there are multiple  
> articles
> per author, then you _should_ get multiple rows back per author.  
> What you
> probably want to do is order that joined query by the author's  
> name, and
> then do your output like so:
>
> 
>  onclick="javascript:clickartic
>
>>
>> le(this)">
>> 
>> > src="#PhotosWritersWeb##GetAuthors.authorPhotograph#">
>> 
>> 
>> #authorFirstName# #authorSurname#
>> #ParagraphFormat(authorAboutMe)#
>> 
>> 
>> The titles to go here. 
>> 
>> 
>> 
>>
>
>
>
> On 9/27/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>
>>
>> Hi,
>>
>> I'm trying to retrieve data from two tables in one query, when i was
>> just querying one everything was okay:
>>
>> 
>> SELECT authorID, authorPhotograph, authorFirstName,
>> authorSurname, authorAboutMe
>> FROM tbl_020authorDetails
>> WHERE authorPhotograph <> ''
>> 
>>
>> But my two table query (join) had a problem:
>>
>> 
>> SELECT tbl_020authorDetails.authorID,
>> authorPhotograph, authorFirstName, authorSurname, authorAboutMe,
>> tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,
>> articleTitle
>> FROM tbl_020authorDetails, tbl_020articleDetails
>> WHERE authorPhotograph <> ' ' AND
>> tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
>> 
>>
>> The thing is that I am trying to pull all the authors and their
>> articles - but some authors have more than one article, is this the
>> best way to do it?
>>
>> I'm also worried that if i pull the data like in the join above (if
>> that even works properly) how do i break it down, ie how do i
>> differentiate between getting information for individual authors?
>>
>> 
>> > onclick="javascript:clickarticle(this)">
>> 
>> > src="#PhotosWritersWeb##GetAuthors.authorPhotograph#">
>> 
>> 
>> #authorFirstName# #authorSurname#
>> #ParagraphFormat(authorAboutMe)#
>> 
>> 
>> The titles to go here.
>> 
>> 
>> 
>>
>>
>> Thanks for any help. Should I just keep this in two queries?
>>
>> Saturday
>>
>>
>>
>>
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219347
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Queries oh queries

Hi,

I'm trying to retrieve data from two tables in one query, when i was  
just querying one everything was okay:

 
 SELECT authorID, authorPhotograph, authorFirstName,  
authorSurname, authorAboutMe
 FROM tbl_020authorDetails
 WHERE authorPhotograph <> ''
 

But my two table query (join) had a problem:

 
 SELECT tbl_020authorDetails.authorID,  
authorPhotograph, authorFirstName, authorSurname, authorAboutMe,  
tbl_020articleDetails.articleID, tbl_020articleDetails.authorID,  
articleTitle
 FROM tbl_020authorDetails, tbl_020articleDetails
 WHERE authorPhotograph <> ' ' AND  
tbl_020articleDetails.authorID = tbl_020authorDetails.authorID
 

The thing is that I am trying to pull all the authors and their  
articles - but some authors have more than one article, is this the  
best way to do it?

I'm also worried that if i pull the data like in the join above (if  
that even works properly) how do i break it down, ie how do i  
differentiate between getting information for individual authors?

 
 
 
 
 
 
 #authorFirstName# #authorSurname#
 #ParagraphFormat(authorAboutMe)#
 
 
 The titles to go here.
 
 
 


Thanks for any help.  Should I just keep this in two queries?

Saturday

  



~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219339
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Damn Queries!

2005-09-25 Thread Saturday (Stuart Kidd)

Wow, yours is a lot more simple than my complicated convoluted way!   
I'll definitely give your one the thumbs up!

Thanks Ade,

Saturday


On 25 Sep 2005, at 16:29, Adrian Lynch wrote:

> This should work:
>
> SELECT f.FeatureName
> FROM tbl_020articleFeatures f
> INNER JOIN YourJoiningTable j ON ( f.FeatureID = j.FeatureID )
> INNER JOIN tbl_020authorDetails a ON ( j.AuthorID = a.AuthorID )
> WHERE a.AuthorID = #CLIENT.authorID#
>
> Ade
>
> -Original Message-
> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
> Sent: 25 September 2005 16:07
> To: CF-Talk
> Subject: Damn Queries!
>
>
> Hi,
>
> I have a table named tbl_020articleFeatures which has fields
> featureID and featureName.
>
> I also have  a table named tbl_020authorDetails which has authorID as
> a field (as well as other stuff).
>
> I made a 'cross table' which is called tbl_020authorFeatures - this
> table will have rows to establish which authors are authorised to
> write which particular features.  In here the fields I have are
> authorFeatureID, featureID and authorID.
>
> I am trying to write a query which will pull only the features which
> e particular author can write.
>
> At the moment I have it in 2 queries but i wouldn't be surprised if i
> could just have one:
>
>  
>  
>  SELECT authorFeaturesID, featureID, authorID
>  FROM tbl_020authorFeatures
>  WHERE authorID = #client.authorID#
>  
>
>  
>  
>  SELECT featureID, featureName
>  FROM tbl_020articleFeatures
>  WHERE featureID = '#GetAuthorFeatures.featureID#'
>  ORDER by featureName ASC
>  
>
> And at the moment this is only pulling the first feature ID.
>
> If anyone could be of any help i would be grateful.
>
> Thanks,
>
> Saturday
>
> 

~|
Find out how CFTicket can increase your company's customer support 
efficiency by 100%
http://www.houseoffusion.com/banners/view.cfm?bannerid=49

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219190
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Damn Queries!

2005-09-25 Thread Saturday (Stuart Kidd)

I've got it, sorry!


 
 
 SELECT authorFeaturesID, tbl_020authorFeatures.featureID,  
tbl_020articleFeatures.featureID, authorID, featureName
 FROM tbl_020authorFeatures, tbl_020articleFeatures
 WHERE authorID = #client.authorID# AND  
tbl_020articleFeatures.featureID = tbl_020authorFeatures.featureID
 

It seems to work :)

On 25 Sep 2005, at 16:06, Saturday (Stuart Kidd) wrote:

> Hi,
>
> I have a table named tbl_020articleFeatures which has fields
> featureID and featureName.
>
> I also have  a table named tbl_020authorDetails which has authorID as
> a field (as well as other stuff).
>
> I made a 'cross table' which is called tbl_020authorFeatures - this
> table will have rows to establish which authors are authorised to
> write which particular features.  In here the fields I have are
> authorFeatureID, featureID and authorID.
>
> I am trying to write a query which will pull only the features which
> e particular author can write.
>
> At the moment I have it in 2 queries but i wouldn't be surprised if i
> could just have one:
>
>  
>  
>  SELECT authorFeaturesID, featureID, authorID
>  FROM tbl_020authorFeatures
>  WHERE authorID = #client.authorID#
>  
>
>  
>  
>  SELECT featureID, featureName
>  FROM tbl_020articleFeatures
>  WHERE featureID = '#GetAuthorFeatures.featureID#'
>  ORDER by featureName ASC
>  
>
> And at the moment this is only pulling the first feature ID.
>
> If anyone could be of any help i would be grateful.
>
> Thanks,
>
> Saturday
>
>
>
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219188
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Damn Queries!

2005-09-25 Thread Saturday (Stuart Kidd)

Hi,

I have a table named tbl_020articleFeatures which has fields  
featureID and featureName.

I also have  a table named tbl_020authorDetails which has authorID as  
a field (as well as other stuff).

I made a 'cross table' which is called tbl_020authorFeatures - this  
table will have rows to establish which authors are authorised to  
write which particular features.  In here the fields I have are  
authorFeatureID, featureID and authorID.

I am trying to write a query which will pull only the features which  
e particular author can write.

At the moment I have it in 2 queries but i wouldn't be surprised if i  
could just have one:

 
 
 SELECT authorFeaturesID, featureID, authorID
 FROM tbl_020authorFeatures
 WHERE authorID = #client.authorID#
 

 
 
 SELECT featureID, featureName
 FROM tbl_020articleFeatures
 WHERE featureID = '#GetAuthorFeatures.featureID#'
 ORDER by featureName ASC
 

And at the moment this is only pulling the first feature ID.

If anyone could be of any help i would be grateful.

Thanks,

Saturday





~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:219186
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

loop list

2005-09-22 Thread Saturday (Stuart Kidd)

Hi,

Up until now I had a nice little loop list going.

 
 #myLink#
 

Now i have decided to have another field in my database which is  
called articleLinksNames - inside here will be the names that  
correspond to the list of links each separated by a comma.

I know that this isn't the best practice but i've had these list of  
links in this table for a long time and would be a hefty job changing  
it - so at the moment i'd like to make a fix.

Is there a way I can now roll through 2 lists at the same time so  
that I can have my articleLinks in the href and the articleLinksNames  
in the output to the screen (which users will see)?

Thanks for any help,

Saturday


~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218953
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: DateTime problems

For some reason it won't allow me to do a cfqueryparam within my line:

WHERE articleID = #URL.ID# AND ipAddress = '#cgi.REMOTE_ADDR#' AND  
commentDate > #dateAdd("n", -10, UKTodayDate)#

Is it because it's a dateAdd?

On 21 Sep 2005, at 23:38, Barney Boisvert wrote:

> You'll want to use 'timestamp', not 'date'.  JDBC calls dates 'date',
> calls times 'time', and calls datetimes, 'timestamp'.  So
>  />.
>
> cheers,
> barneyb
>
> On 9/21/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>
>> Hi Charlie,
>>
>> Yes, it holds date and time.
>>
>> Is this how I should do the cfqueryparam?  I noticed there wasn't a
>> cfsqltype called 'datetime' so i put it as a date.
>>
>> WHERE articleID = #URL.ID# AND ipAddress = '#cgi.REMOTE_ADDR#' AND
>> commentDate < #dateAdd("n",
>> -10, UKTodayDate)#
>>
>>
>
> --
> Barney Boisvert
> [EMAIL PROTECTED]
> 360.319.6145
> http://www.barneyb.com/
>
> Got Gmail? I have 100 invites.
>
> 

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218898
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: DateTime problems

Hi Charlie,

Yes, it holds date and time.

Is this how I should do the cfqueryparam?  I noticed there wasn't a  
cfsqltype called 'datetime' so i put it as a date.

WHERE articleID = #URL.ID# AND ipAddress = '#cgi.REMOTE_ADDR#' AND  
commentDate < #dateAdd("n",  
-10, UKTodayDate)#


On 21 Sep 2005, at 23:22, Charlie Griefer wrote:

> commentDate a smallDateTime field (does it hold a time value as  
> well as a date)?
>
> i'd definitely suggest putting a  around that date value
> (well, all of the dynamic values).  at the very, very least, a
> #createODBCDateTime()#.
>
> On 9/21/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>
>> Hi,
>>
>> I'm inserting some user comments into a database.  When done, they
>> insert articleID, ipAddress and commentDate (the date when they were
>> submitted).
>>
>> Before a comment gets added i check the database to see whether there
>> already exists a comment from the same IP Address in the last 10
>> minutes... but unfortunately I can't seem to work out the actual
>> WHERE statement.
>>
>>  
>> SELECT articleID, ipAddress, commentDate
>> FROM tbl_020publicComments
>> WHERE articleID = #URL.ID# AND ipAddress = '#cgi.REMOTE_ADDR#' AND
>> commentDate < #DateAdd('n', 10, UKTodayDate)#
>>  
>>
>> Can anyone help me out?
>>
>> Thanks,
>>
>> Saturday
>>
>> ps: i've already played around with  but this only persists
>> in a browser, but some people (like most of us) have more than one
>> browser.
>>
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218894
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: DateTime problems

Thanks Barney, hopefully that'll be it.

Also, I should have said that when I put UKTodayDate (instead of Now 
()) it's because I'm on a US server but i'm situated in the UK so in  
my Application.cfm file I have:




On 21 Sep 2005, at 23:20, Barney Boisvert wrote:

> It should be commentDate > #dateAdd("n", -10, now())#.
>
> cheers,
> barneyb
>
> On 9/21/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>
>> Hi,
>>
>> I'm inserting some user comments into a database.  When done, they
>> insert articleID, ipAddress and commentDate (the date when they were
>> submitted).
>>
>> Before a comment gets added i check the database to see whether there
>> already exists a comment from the same IP Address in the last 10
>> minutes... but unfortunately I can't seem to work out the actual
>> WHERE statement.
>>
>>   
>> SELECT articleID, ipAddress, commentDate
>> FROM tbl_020publicComments
>> WHERE articleID = #URL.ID# AND ipAddress = '#cgi.REMOTE_ADDR#' AND
>> commentDate < #DateAdd('n', 10, UKTodayDate)#
>>   
>>
>> Can anyone help me out?
>>
>> Thanks,
>>
>> Saturday
>>
>> ps: i've already played around with  but this only persists
>> in a browser, but some people (like most of us) have more than one
>> browser.
>>
>>
>
> --
> Barney Boisvert
> [EMAIL PROTECTED]
> 360.319.6145
> http://www.barneyb.com/
>
> Got Gmail? I have 100 invites.
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218893
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

DateTime problems

Hi,

I'm inserting some user comments into a database.  When done, they  
insert articleID, ipAddress and commentDate (the date when they were  
submitted).

Before a comment gets added i check the database to see whether there  
already exists a comment from the same IP Address in the last 10  
minutes... but unfortunately I can't seem to work out the actual  
WHERE statement.

  
SELECT articleID, ipAddress, commentDate
FROM tbl_020publicComments
WHERE articleID = #URL.ID# AND ipAddress = '#cgi.REMOTE_ADDR#' AND  
commentDate < #DateAdd('n', 10, UKTodayDate)#
  

Can anyone help me out?

Thanks,

Saturday

ps: i've already played around with  but this only persists  
in a browser, but some people (like most of us) have more than one  
browser.


~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:218889
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

SOT: Counties and Towns data for UK

2005-07-20 Thread Stuart Kidd

Hi,

 Does such a place exist where you can download databases of counties and towns 
for the UK?  I'd like to use them for my multiple related selects.

 Thanks,

 Saturday

 



~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212298
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Join queries

Yeah, i remember now why i did that weird thing with the loop - i  
couldn't seem to get it to work.

If I put the below in, the flash form will not load (although no  
error comes up either).

 
 
 

But if i add the _relationshipTypeID to make each name in the loop  
unique it comes up no problem.

Do you know why this is happening by any chance?  Is it a flash form  
problem?

 
 
 

Thanks,

Stuart

ps: i've changed the attributes to relationshipTypes instead.


On 15 Jul 2005, at 18:43, Deanna Schneider wrote:

> I don't use ms-sql, so I'm a bit rusty on the ansi standard left join
> sytax. It'll look something like the below. (Note the change in the
> from close, and the deletion of the one part of the where clause.)
>
> The a and p (and now l) are just aliases, so I don't have to type out
> your tablenames over and over.
>
> As for your generating your checkboxes, there's no need to use
> different checkbox names. Just do this:
>  
>  name="attributes" label="#attributeName#" value="#attributeid#">
> 
>
> On your action page, if any of the checkboxes have been selected, then
> you'll get a comma-delimited list of all the checked ones. (If none
> have been checked, form.attributes won't be there at all, so be
> careful. Come to think of it, attributes is a reserved word, so call
> it something else, too  - maybe "attributesDesired" or something.
>
> 
> SELECT profileID, userID, profileTitle, gender,
> genderSeeking, screenName, location, locationID, locationName, age,
> confirmationID, imageName, imageAuthorisation
>
> FROM (tbl_020publicProfiles p LEFT OUTER JOIN
> tbl_020publicProfilesAttributes a on p.profileid = a.profileid) INNER
> JOIN tbl_020publicLocations l ON p.locationid = l.locationid
>
> WHERE gender = '#form.genderSeeking#' AND genderSeeking =
> '#form.gender#' AND confirmationID = 'yes' AND userID <>
>  cfsqltype="cf_sql_integer" maxlength="4"> AND
> tbl_020publicLocations.locationID =
> tbl_020publicProfiles.location form.location neq 0> AND location = '#form.location#' isDefined('form.minAge') and form.minAge neq 0> AND age >=
> #form.minAge# neq 0> AND age <= #form.maxAge# ('form.hasPhotograph') and form.hasPhotograph eq 1> AND
> imageAuthorisation = 1
> AND a.attributeid IN ( value="#form.attributeid#" list="yes")
>ORDER by confirmationDate DESC
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212026
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Join queries

Thanks Deanna.  Just a couple of questions (and problems).

The form which displays the check boxes does so with a loop getting a  
list of the possible attributes:

 
 
 SELECT attributeID, attributeName
 FROM tbl_020publicAttributes
 

 
 
 

And also, there don't necessarily have to be any attributes in the  
table tbl_020publicProfilesAttributes for every profile as some may  
not have changed their profile accordingly.

Also can you tell me, are the p and the a just references which could  
be any letter?  Oh yes, i'm using MS-SQL.

It was about a week ago when you suggested i should use this middle  
join table instead and i never thought i'd get my head around it but  
managed to create one and have it update (well, delete or create  
rows) when users change their profiles.  Now i'm up to the bit where  
the searching is and this part has just stumped me a bit.

I am learning however... :)  Albeit slowly.

Thanks,


On 15 Jul 2005, at 16:50, Deanna Schneider wrote:

> Try this. I'm not sure which DB, or if all users are requred to select
> the type of match they seek. So, you may need to use the SQL Server
> join syntax or the outer join syntax, if matching that parameter is
> optional.
>
> 
> SELECT profileID, userID, profileTitle, gender,
> genderSeeking, screenName, location, locationID, locationName, age,
> confirmationID, imageName, imageAuthorisation
> FROM tbl_020publicProfiles p,
> tbl_020publicLocations,tbl_020publicProfilesAttributes a
> WHERE gender = '#form.genderSeeking#' AND genderSeeking =
> '#form.gender#' AND confirmationID = 'yes' AND userID <>
>  cfsqltype="cf_sql_integer" maxlength="4"> AND
> tbl_020publicLocations.locationID =
> tbl_020publicProfiles.location form.location neq 0> AND location = '#form.location#' isDefined('form.minAge') and form.minAge neq 0> AND age >=
> #form.minAge# neq 0> AND age <= #form.maxAge# ('form.hasPhotograph') and form.hasPhotograph eq 1> AND
> imageAuthorisation = 1
>
> AND p.profileid = a.profileid
> AND a.attributeid IN ( value="#form.attributeid#" list="yes")
> ORDER by confirmationDate DESC
> 
>
> On 7/15/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>
>> Hi guys,
>>
>> I'm working on the search page of a dating application.
>>
>> In the form where the user gets to choose his/her preferred partner
>> there are a few checkboxes which says:
>>
>> I am searching for:
>>
>> Love
>> Casual
>> Travel mate
>> Friend
>>
>> When displayed as checkboxes i'm pulling from a table called
>> tbl_020publicAttributes (fields: attributeID, attributeName).
>>
>> All users with profiles on the site have their attributes in a join
>> table called tbl_020publicProfilesAttributes (fields profileID,
>> attributeID)
>>
>> So when a visitor comes to the site and fills out a search form i
>> want the results to also include the stated type of attributes they
>> are looking for.
>>
>> So far my form works but i have no idea how i am going to also
>> include this other table tbl_020publicProfilesAttributes:
>>
>>  
>>  SELECT profileID, userID, profileTitle, gender,
>> genderSeeking, screenName, location, locationID, locationName, age,
>> confirmationID, imageName, imageAuthorisation
>>  FROM tbl_020publicProfiles, tbl_020publicLocations
>>  WHERE gender = '#form.genderSeeking#' AND genderSeeking =
>> '#form.gender#' AND confirmationID = 'yes' AND userID <>
>> > cfsqltype="cf_sql_integer" maxlength="4"> AND
>> tbl_020publicLocations.locationID =
>> tbl_020publicProfiles.location> form.location neq 0> AND location = '#form.location#'> isDefined('form.minAge') and form.minAge neq 0> AND age >=
>> #form.minAge#> neq 0> AND age <= #form.maxAge#> ('form.hasPhotograph') and form.hasPhotograph eq 1> AND
>> imageAuthorisation = 1
>>  ORDER by confirmationDate DESC
>>  
>>
>> Any help i'd be very grateful with.
>>
>> Thanks,
>>
>> Saturday
>>
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212014
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Join queries

Hi guys,

I'm working on the search page of a dating application.

In the form where the user gets to choose his/her preferred partner  
there are a few checkboxes which says:

I am searching for:

Love
Casual
Travel mate
Friend

When displayed as checkboxes i'm pulling from a table called  
tbl_020publicAttributes (fields: attributeID, attributeName).

All users with profiles on the site have their attributes in a join  
table called tbl_020publicProfilesAttributes (fields profileID,  
attributeID)

So when a visitor comes to the site and fills out a search form i  
want the results to also include the stated type of attributes they  
are looking for.

So far my form works but i have no idea how i am going to also  
include this other table tbl_020publicProfilesAttributes:

 
 SELECT profileID, userID, profileTitle, gender,  
genderSeeking, screenName, location, locationID, locationName, age,  
confirmationID, imageName, imageAuthorisation
 FROM tbl_020publicProfiles, tbl_020publicLocations
 WHERE gender = '#form.genderSeeking#' AND genderSeeking =  
'#form.gender#' AND confirmationID = 'yes' AND userID <>  
 AND  
tbl_020publicLocations.locationID =  
tbl_020publicProfiles.location AND location = '#form.location#' AND age >=  
#form.minAge# AND age <= #form.maxAge# AND  
imageAuthorisation = 1
 ORDER by confirmationDate DESC
 

Any help i'd be very grateful with.

Thanks,

Saturday


~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:212004
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Catching errors and emailing

Thanks everyone for your help and I like your photo Doug!

I got it working now! :)

I had a few problems because first of all i wasn't using the line  
below, instead i had the cftry/cfcatch on my main page and it wasn't  
cfmailing:



But then i put it on the top of my main page and all is good.

Should the type be 'exception'?  Will that catch all?

The CFERROR, could i put that in my APPLICATION.CFM or is that  
dangerous?

Thanks,

Saturday


Then on my other page, request_err.cfm I have:


On 15 Jul 2005, at 14:50, Raymond Camden wrote:

> It is meant to be use in an error template. Check out the doc for
> , which lets you specify a page to run when an error occurs.
>
> On 7/15/05, Saturday (Stuart Kidd) <[EMAIL PROTECTED]> wrote:
>
>> Thanks for that.
>>
>> Can I ask why it's not a good idea to use CFTRY/CFCATCH?
>>
>> Whereabouts do i put the CFMAIL script below?  It comes up with an
>> error saying that TYPE is undefined in ERROR?
>>
>> Thanks,
>>
>> Saturday
>>
>>
>> On 15 Jul 2005, at 14:06, Michael Dinowitz wrote:
>>
>>
>>> Sorry, forgot to mention that I use this code in a sitewide error
>>> handling template so every error on the site gets emailed to me,
>>> even ones in a CFTRY/CFCATCH. On a related note, don't use CFTRY/
>>> CFCATCH to handle known errors. Handle the error directly!
>>>
>>>
>>>
>>>> 
>>>> 
>>>> 
>>>> 
>>>> 
>>>> >>> label="session">
>>>> >>> label="application">
>>>> 
>>>>
>>>>
>>>
>>>
>>>
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211983
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: Catching errors and emailing

Thanks for that.

Can I ask why it's not a good idea to use CFTRY/CFCATCH?

Whereabouts do i put the CFMAIL script below?  It comes up with an  
error saying that TYPE is undefined in ERROR?

Thanks,

Saturday


On 15 Jul 2005, at 14:06, Michael Dinowitz wrote:

> Sorry, forgot to mention that I use this code in a sitewide error  
> handling template so every error on the site gets emailed to me,  
> even ones in a CFTRY/CFCATCH. On a related note, don't use CFTRY/ 
> CFCATCH to handle known errors. Handle the error directly!
>
>
>> 
>> 
>> 
>> 
>> 
>> > label="session">
>> > label="application">
>> 
>>
>
> 

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211975
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Catching errors and emailing

Hi guys,

Just wondering if anyone knows of a way of emailing any errors which  
are caught?  So that way if there is an error I'll get to find out  
what it is as most users probably wouldn't bother emailing one if found.

Thanks,

Saturday


~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211954
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: 2 Primary Keys

Great, that works fine.  I had tried it earlier i think (maybe  
incorrectly) but that was when i had the third column which had an  
identity with increment.  Maybe that's why i couldn't get it to work.

:)

Right, i'm off to bed (1.14am here). Only so much boring Live Big  
Brother i can stand to watch!

Thanks for everyone's help tonight.

Stuart


On 15 Jul 2005, at 01:00, Ian Skinner wrote:

> In table design, highlight all fields for the key and click the key  
> button.
>
>
> --
> Ian Skinner
> Web Programmer
> BloodSource
> www.BloodSource.org
> Sacramento, CA
>
> "C code. C code run. Run code run. Please!"
> - Cynthia Dunning
>
> -Original Message-
> From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED]
> Sent: Thursday, July 14, 2005 4:51 PM
> To: CF-Talk
> Subject: Re: 2 Primary Keys
> 
> Just one other thing however, how do i create the compound key via
> Enterprise Manager, can I do it through that?  I'm on shared  
> hosting
> with hostmysite.com.
> 
> Thanks,
> 
> Stuart
> 
> 
> On 15 Jul 2005, at 00:52, Dave Watts wrote:
> 
> >> I want to add a row in a join table which i don't want to be
> >> duplicated ever.
> >>
> >> So i have my tableID, table1ID, table2ID - if table1ID = 24
> >> and table2ID = 35 i don't want there to ever be a  
> reoccurence of
> >> them. I guess i have to set both of those fields (table1ID,  
> table2ID)
> >> to primary keys, but how can i do that?  Do i do it in ms- 
> sql or via
> >> Coldfusion somehow?
> >>
> >
> > As a bunch of people have already mentioned, you can create
> > compound keys
> > within your database. A compound key consists of more than one
> > field. If you
> > go that route, you really don't need the "tableID" field any  
> more
> > (assuming
> > that field is currently being used as your primary key) - you  
> can
> > just make
> > the combination of table1ID and table2ID your primary key.
> >
> > One disadvantage of this is that, if you have other tables  
> which are
> > dependent upon this one, those tables will need to contain both
> > fields for
> > use as a foreign key. However, in all likelihood you don't  
> have any
> > other
> > tables which are dependent upon this one, but rather this  
> table is
> > dependent
> > upon two other tables - this kind of table is often called a
> > linking or
> > intersection table. The "middle" table in a many-to-many  
> relationship
> > typically follows this pattern.
> >
> > Another disadvantage of this is that you won't be able to store
> > historical
> > relationship data - either a relationship exists now, or it
> > doesn't. If you
> > do want to store historical data, you'd need to keep the current
> > "tableID"
> > as your primary key, or add a third field to your two-field  
> primary
> > key -
> > the value of that third field would have to be different for any
> > two rows
> > that have the same values for the other two fields.
> >
> >
> >> If i do it via MS-SQL then won't it pop an error up in the  
> code if
> >> it happens?
> >>
> >
> > Yes, like any other invalid query you might run. It's up to  
> you to
> > catch and
> > resolve those errors appropriately within your code.
> >
> > Dave Watts, CTO, Fig Leaf Software
> > http://www.figleaf.com/
> >
> > Fig Leaf Software provides the highest caliber vendor-authorized
> > instruction at our training centers in Washington DC, Atlanta,
> > Chicago, Baltimore, Northern Virginia, or on-site at your  
> location.
> > Visit http://training.figleaf.com/ for more information!
> >
> >
> >
> 
> 
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211947
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: 2 Primary Keys

Just one other thing however, how do i create the compound key via  
Enterprise Manager, can I do it through that?  I'm on shared hosting  
with hostmysite.com.

Thanks,

Stuart


On 15 Jul 2005, at 00:52, Dave Watts wrote:

>> I want to add a row in a join table which i don't want to be
>> duplicated ever.
>>
>> So i have my tableID, table1ID, table2ID - if table1ID = 24
>> and table2ID = 35 i don't want there to ever be a reoccurence of
>> them. I guess i have to set both of those fields (table1ID, table2ID)
>> to primary keys, but how can i do that?  Do i do it in ms-sql or via
>> Coldfusion somehow?
>>
>
> As a bunch of people have already mentioned, you can create  
> compound keys
> within your database. A compound key consists of more than one  
> field. If you
> go that route, you really don't need the "tableID" field any more  
> (assuming
> that field is currently being used as your primary key) - you can  
> just make
> the combination of table1ID and table2ID your primary key.
>
> One disadvantage of this is that, if you have other tables which are
> dependent upon this one, those tables will need to contain both  
> fields for
> use as a foreign key. However, in all likelihood you don't have any  
> other
> tables which are dependent upon this one, but rather this table is  
> dependent
> upon two other tables - this kind of table is often called a  
> linking or
> intersection table. The "middle" table in a many-to-many relationship
> typically follows this pattern.
>
> Another disadvantage of this is that you won't be able to store  
> historical
> relationship data - either a relationship exists now, or it  
> doesn't. If you
> do want to store historical data, you'd need to keep the current  
> "tableID"
> as your primary key, or add a third field to your two-field primary  
> key -
> the value of that third field would have to be different for any  
> two rows
> that have the same values for the other two fields.
>
>
>> If i do it via MS-SQL then won't it pop an error up in the code if
>> it happens?
>>
>
> Yes, like any other invalid query you might run. It's up to you to  
> catch and
> resolve those errors appropriately within your code.
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
>
> Fig Leaf Software provides the highest caliber vendor-authorized
> instruction at our training centers in Washington DC, Atlanta,
> Chicago, Baltimore, Northern Virginia, or on-site at your location.
> Visit http://training.figleaf.com/ for more information!
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211945
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: 2 Primary Keys

Yeah, i was wondering whether i needed that field as i couldn't see  
how i'd use it.  I'll go ahead and delete it.

Thanks,

Stuart


On 15 Jul 2005, at 00:52, Dave Watts wrote:

>> I want to add a row in a join table which i don't want to be
>> duplicated ever.
>>
>> So i have my tableID, table1ID, table2ID - if table1ID = 24
>> and table2ID = 35 i don't want there to ever be a reoccurence of
>> them. I guess i have to set both of those fields (table1ID, table2ID)
>> to primary keys, but how can i do that?  Do i do it in ms-sql or via
>> Coldfusion somehow?
>>
>
> As a bunch of people have already mentioned, you can create  
> compound keys
> within your database. A compound key consists of more than one  
> field. If you
> go that route, you really don't need the "tableID" field any more  
> (assuming
> that field is currently being used as your primary key) - you can  
> just make
> the combination of table1ID and table2ID your primary key.
>
> One disadvantage of this is that, if you have other tables which are
> dependent upon this one, those tables will need to contain both  
> fields for
> use as a foreign key. However, in all likelihood you don't have any  
> other
> tables which are dependent upon this one, but rather this table is  
> dependent
> upon two other tables - this kind of table is often called a  
> linking or
> intersection table. The "middle" table in a many-to-many relationship
> typically follows this pattern.
>
> Another disadvantage of this is that you won't be able to store  
> historical
> relationship data - either a relationship exists now, or it  
> doesn't. If you
> do want to store historical data, you'd need to keep the current  
> "tableID"
> as your primary key, or add a third field to your two-field primary  
> key -
> the value of that third field would have to be different for any  
> two rows
> that have the same values for the other two fields.
>
>
>> If i do it via MS-SQL then won't it pop an error up in the code if
>> it happens?
>>
>
> Yes, like any other invalid query you might run. It's up to you to  
> catch and
> resolve those errors appropriately within your code.
>
> Dave Watts, CTO, Fig Leaf Software
> http://www.figleaf.com/
>
> Fig Leaf Software provides the highest caliber vendor-authorized
> instruction at our training centers in Washington DC, Atlanta,
> Chicago, Baltimore, Northern Virginia, or on-site at your location.
> Visit http://training.figleaf.com/ for more information!
>
>
> 

~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211943
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: 2 Primary Keys

Thanks Ian, that did the trick.  Got it working.

Cheers! :)


On 15 Jul 2005, at 00:31, Ian Skinner wrote:

> Please feel free to fix the typos by removing the extra space after  
> the pound sign on the second insert value and the missing right  
> parenthesis ")" to close the Values Caluse.  Be aware there may be  
> others, my example was created in the e-mail and untested.
>
>
> --
> Ian Skinner
> Web Programmer
> BloodSource
> www.BloodSource.org
> Sacramento, CA
>
> "C code. C code run. Run code run. Please!"
> - Cynthia Dunning
>
> -Original Message-
> From: Ian Skinner
> Sent: Thursday, July 14, 2005 4:27 PM
> To: CF-Talk
> Subject: RE: 2 Primary Keys
> 
> The fastest fix, from the example you provided, is to put  
> pounds around
> the evaluate function otherwise you are trying to put a large  
> string into
> you join column.
> 
> But better yet may be something like this:
> 
>  "true">
> 
> INSERT INTO
> tbl_020publicProfilesAttribes
> (profileID,attributeID)
> VALUES (
> #InsertProfileDetails.profileID#,
> # form["attribute_" &
> FindAttributes.attributeID]#
> 
> 
> 
> 
> While writing this to show how to eliminate the need for the  
> evaluate
> functions by using structure and array notation I noticed that  
> the form
> variable in the If statement was different then the one in the  
> insert
> statement.  One has an underscore "_" and the other does not.   
> Is this
> not an error?
> 
> --
> Ian Skinner
> Web Programmer
> BloodSource
> www.BloodSource.org
> Sacramento, CA
> 
> "C code. C code run. Run code run. Please!"
> - Cynthia Dunning
>
> Confidentiality Notice:  This message including any
> attachments is for the sole use of the intended
> recipient(s) and may contain confidential and privileged
> information. Any unauthorized review, use, disclosure or
> distribution is prohibited. If you are not the
> intended recipient, please contact the sender and
> delete any copies of this message.
>
>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211942
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: 2 Primary Keys

Sorry if this has come through twice but i got a bounce back saying  
the email body was too long (so have deleted some now).

My problem is that i have a form on a dating site where when users
sign up i create a row for them in my profile table.  When collating
their profile I also ask them what type of dates they'd like to meet,
some of which are love, casual, friendship, travel mate.  Each of
these attributes are held in an attributes table.  When i have taken
all the user info i create the rows in the two tables.  For the
profile info it's pretty straight forward, but for the attributes i
am finding it quite hard.

i'll show you what i am doing.

  
  
  SET NOCOUNT ON
  INSERT INTO tbl_020publicProfiles (profileText,
profileTitle, screenName, userID, acceptImages, dateSubmitted,
location, age, gender, genderSeeking, confirmationID, profileIPAddress)
  VALUES ('#Form.profileText#', '#Form.title#',
'#Form.screenName#', #CheckUser.userID#, #Form.acceptImages#,
#UKtodayDate#, #Form.location#, '#Form.age#', '#Form.gender#',
'#Form.genderSeeking#', '#confirmationID#', '#cgi.REMOTE_ADDR#')
  SELECT @@IDENTITY as profileID
  SET NOCOUNT OFF
  

  
  
  SELECT attributeID
  FROM tbl_020publicAttributes
  

  
  
  
  
  INSERT INTO tbl_020publicProfilesAttributes (profileID,
attributeID)
  VALUES (#InsertProfileDetails.profileID#, evaluate
('form.attribute_' & 'FindAttributes.attributeID'))
  
  
  

As you can see i'm having problems with putting the attributes values
in as i am finding that you can't use the CF attribute function in an
SQL statement.

I am also wondering if there is any easier way to all this then the
way i am doing it.  Anyone have any ideas or am i doing it right?

Any help at this point would be really helpful.

Thanks,

Stuart


~|
Discover CFTicket - The leading ColdFusion Help Desk and Trouble 
Ticket application

http://www.houseoffusion.com/banners/view.cfm?bannerid=48

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211936
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: 2 Primary Keys

My problem is that i have a form on a dating site where when users  
sign up i create a row for them in my profile table.  When collating  
their profile I also ask them what type of dates they'd like to meet,  
some of which are love, casual, friendship, travel mate.  Each of  
these attributes are held in an attributes table.  When i have taken  
all the user info i create the rows in the two tables.  For the  
profile info it's pretty straight forward, but for the attributes i  
am finding it quite hard.

i'll show you what i am doing.

 
 
 SET NOCOUNT ON
 INSERT INTO tbl_020publicProfiles (profileText,  
profileTitle, screenName, userID, acceptImages, dateSubmitted,  
location, age, gender, genderSeeking, confirmationID, profileIPAddress)
 VALUES ('#Form.profileText#', '#Form.title#',  
'#Form.screenName#', #CheckUser.userID#, #Form.acceptImages#,  
#UKtodayDate#, #Form.location#, '#Form.age#', '#Form.gender#',  
'#Form.genderSeeking#', '#confirmationID#', '#cgi.REMOTE_ADDR#')
 SELECT @@IDENTITY as profileID
 SET NOCOUNT OFF
 

 
 
 SELECT attributeID
 FROM tbl_020publicAttributes
 

 
 
 
 
 INSERT INTO tbl_020publicProfilesAttributes (profileID,  
attributeID)
 VALUES (#InsertProfileDetails.profileID#, evaluate 
('form.attribute_' & 'FindAttributes.attributeID'))
 
 
 

As you can see i'm having problems with putting the attributes values  
in as i am finding that you can't use the CF attribute function in an  
SQL statement.

I am also wondering if there is any easier way to all this then the  
way i am doing it.  Anyone have any ideas or am i doing it right?

Any help at this point would be really helpful.

Thanks,

Stuart



On 14 Jul 2005, at 23:47, Aaron DC wrote:

> It sounds like you are enforcing a 1 to 1 restriction but storing the
> link in a 1 to many or many to many db design. If table1 can only ever
> be linked to 1 table2 record and vice versa, wouldnt it be easier to
> store the table1ID in the table2 as a field (or vice versa)? All your
> queries would have 1 less join, etc.
>
> Aaron
>
> Saturday (Stuart Kidd) wrote:
>
>
>> Hi Jordan,
>>
>> Yes, more than one field needs to be a primary key.  I'm using MS-SQL
>> (on shared hosting).
>>
>> Stuart
>>
>>
>> On 14 Jul 2005, at 23:07, Jordan Michaels wrote:
>>
>>
>>
>>
>>> Saturday (Stuart Kidd) wrote:
>>>
>>>
>>>
>>>
>>>
>>>> Hi guys,
>>>>
>>>> I want to add a row in a join table which i don't want to be
>>>> duplicated ever.
>>>>
>>>> So i have my tableID, table1ID, table2ID - if table1ID = 24 and
>>>> table2ID = 35 i don't want there to ever be a reoccurence of  
>>>> them.  I
>>>> guess i have to set both of those fields (table1ID, table2ID) to
>>>> primary keys, but how can i do that?  Do i do it in ms-sql or via
>>>> Coldfusion somehow?  If i do it via MS-SQL then won't it pop an  
>>>> error
>>>> up in the code if it happens?
>>>>
>>>> Thanks,
>>>>
>>>> Saturday
>>>>
>>>>
>>>>
>>>>
>>>>
>>>>
>>> Are you talking about compound keys? Where more then one field is  
>>> the
>>> primary key?  This can be done in most databases - even access if I
>>> remember correctly.
>>>
>>> -JM
>>>
>>> -- 
>>> Warm regards,
>>> Jordan Michaels
>>> Vivio Technologies
>>> http://www.viviotech.net/
>>> [EMAIL PROTECTED]
>>>
>>>
>>>
>>>
>>>
>>
>>
>>
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211935
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

Re: 2 Primary Keys

Hi Jordan,

Yes, more than one field needs to be a primary key.  I'm using MS-SQL  
(on shared hosting).

Stuart


On 14 Jul 2005, at 23:07, Jordan Michaels wrote:

> Saturday (Stuart Kidd) wrote:
>
>
>> Hi guys,
>>
>> I want to add a row in a join table which i don't want to be
>> duplicated ever.
>>
>> So i have my tableID, table1ID, table2ID - if table1ID = 24 and
>> table2ID = 35 i don't want there to ever be a reoccurence of them.  I
>> guess i have to set both of those fields (table1ID, table2ID) to
>> primary keys, but how can i do that?  Do i do it in ms-sql or via
>> Coldfusion somehow?  If i do it via MS-SQL then won't it pop an error
>> up in the code if it happens?
>>
>> Thanks,
>>
>> Saturday
>>
>>
>>
> Are you talking about compound keys? Where more then one field is the
> primary key?  This can be done in most databases - even access if I
> remember correctly.
>
> -JM
>
> -- 
> Warm regards,
> Jordan Michaels
> Vivio Technologies
> http://www.viviotech.net/
> [EMAIL PROTECTED]
>
> 

~|
Logware (www.logware.us): a new and convenient web-based time tracking 
application. Start tracking and documenting hours spent on a project or with a 
client with Logware today. Try it for free with a 15 day trial account.
http://www.houseoffusion.com/banners/view.cfm?bannerid=67

Message: http://www.houseoffusion.com/lists.cfm/link=i:4:211931
Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4
Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4
Unsubscribe: 
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
Donations & Support: http://www.houseoffusion.com/tiny.cfm/54

2 Primary Keys