Help with querying a database using a date field
Hi there I am trying to do a query of a table in a database using a date field and I am getting no records returned. The date field in the database (MySQL database) is set as a datetime field. So I am guessing that querying just using the date '2008-10-01' is not correct. My query is this SELECT tsessiontracking.siteID, tsessiontracking.userid, tsessiontracking.entered FROM tsessiontracking WHERE tsessiontracking.entered = '2008-10-01' Any help would really be appreciated ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:313312 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Re: Help with querying a database using a date field
try this: SELECT tsessiontracking.siteID, tsessiontracking.userid, tsessiontracking.entered FROM tsessiontracking WHERE DATE(tsessiontracking.entered) = cfqueryparam cfsqltype=cf_sql_date value=#createodbcdate(createdate(2008,10,1))# DATE() is a mysql functions that returns just the date part of a datetime/timestamp field. Azadi Saryev Sabai-dee.com http://www.sabai-dee.com/ Toby King wrote: Hi there I am trying to do a query of a table in a database using a date field and I am getting no records returned. The date field in the database (MySQL database) is set as a datetime field. So I am guessing that querying just using the date '2008-10-01' is not correct. My query is this SELECT tsessiontracking.siteID, tsessiontracking.userid, tsessiontracking.entered FROM tsessiontracking WHERE tsessiontracking.entered = '2008-10-01' Any help would really be appreciated ~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:313313 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
Querying a database
Hi, Can the below be easily done from a database? The idea would be to add/remove directories from the database which would be inserted in the code below. This is just a snippet from my cfdirectory cfloop index=LineNo from=#i# to=#i# cfif Session.stDirFileType[i] eq Dir OR cfif ListFindNoCase(cdfiles,listLast(#Session.stDirFileName[i]#, .)) OR ListFindNoCase(eps,listLast(#Session.stDirFileName[i]#, .)) OR ListFindNoCase(inetpub,listLast(#Session.stDirFileName[i]#, .)) OR ListFindNoCase(mywork,listLast(#Session.stDirFileName[i]#, .)) OR ListFindNoCase(restored files,listLast(#Session.stDirFileName[i]#, .)) OR ListFindNoCase(restored,listLast(#Session.stDirFileName[i]#, .)) OR ListFindNoCase(system volume information,listLast(#Session.stDirFileName[i]#, .)) OR ListFindNoCase(usr,listLast(#Session.stDirFileName[i]#, .)) cfelse cfinclude template=/intincludes/directory-list.cfm /cfif /cfif /cfloop --- Colonel Nathan R. Jessop Commanding Officer Marine Ground Forces Guatanamo Bay, Cuba --- ~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribeforumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting.
Re: querying a database for events in a resource scheduler (like a calendar)
How about this?
1. Initialize the value of each cell to null:
cfloop from="1" to="168" index="iCell"
cfset tmp = SetVariable("cell#iCell#", "")
/cfloop
2. Do one query to get all events.
3. Loop through the events. For each event, determine what cell it
belongs in, and update the value of that cell.
4. Build the table in a loop, putting the value #cell#iCell# into each
cell.
-David
On Fri, 12 Jan 2001 19:00:10 -0800 "Brent Goldman" [EMAIL PROTECTED]
writes:
Hi,
I am currently developing a resource scheduler. The table that
outputs each
schedule has 24 rows on the left: 12 hours with half-hour
increments. There
are 7 columns on the top: one for each day of the week. Total,
there will
be 24*7=168 different cells which need to be populated with events
if there
are any.
There are two different ways to accomplish this. One way is to do a
query
to the database in each and every cell, but 168 queries is a
tremendous
amount of queries for one page load. If there are 10 events in the
database, the page load is about 600 ms with SQL Server.
The second way to accomplish this is do have a master query at the
top of
the page that retrieves each and every element in the database
relevant to
the current page. Then, within each cell, do a CFOUTPUT or CFLOOP
through
the query, and if the data is the one we want, output the results.
This
way, if there are 10 events in the database, and there are 168 loops
(one
loop for each cell), there is a total of 168*10=1680 times the if
statement
(and possibly the code within the if statement) is executed.
Overall, the
page load of this way is about 700 ms with SQL Server, with about
10-20 ms
added for each event added into the database.
The first way is definately better for page load and scalability,
but it is
not very efficient to have 168 queries for a single page load. Does
anyone
have any comments about the two different ways or new ideas on how
to
accomplish this project?
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: querying a database for events in a resource scheduler (like a calendar)
Hi,
That is a really good idea, but the table is dynamically generated. The
cells are not generated through a loop of 1 through 168; they are generated
through a loop of how many rows there are (24; each half-hour for 12 hours)
within an interior loop of how many columns there are (7). How can I check
within each cell against the variables? Maybe storing all of the variables
in a single array or structure would be an easier way to organize the
variables, but what number (or a key in structures) would be checked upon
during the generation of the table?
-Brent
From: David Shadovitz [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: Re: querying a database for events in a resource scheduler (like a
calendar)
Date: Sat, 13 Jan 2001 19:53:33 -0800
How about this?
1. Initialize the value of each cell to null:
cfloop from="1" to="168" index="iCell"
cfset tmp = SetVariable("cell#iCell#", "")
/cfloop
2. Do one query to get all events.
3. Loop through the events. For each event, determine what cell it
belongs in, and update the value of that cell.
4. Build the table in a loop, putting the value #cell#iCell# into each
cell.
-David
On Fri, 12 Jan 2001 19:00:10 -0800 "Brent Goldman" [EMAIL PROTECTED]
writes:
Hi,
I am currently developing a resource scheduler. The table that
outputs each
schedule has 24 rows on the left: 12 hours with half-hour
increments. There
are 7 columns on the top: one for each day of the week. Total,
there will
be 24*7=168 different cells which need to be populated with events
if there
are any.
There are two different ways to accomplish this. One way is to do a
query
to the database in each and every cell, but 168 queries is a
tremendous
amount of queries for one page load. If there are 10 events in the
database, the page load is about 600 ms with SQL Server.
The second way to accomplish this is do have a master query at the
top of
the page that retrieves each and every element in the database
relevant to
the current page. Then, within each cell, do a CFOUTPUT or CFLOOP
through
the query, and if the data is the one we want, output the results.
This
way, if there are 10 events in the database, and there are 168 loops
(one
loop for each cell), there is a total of 168*10=1680 times the if
statement
(and possibly the code within the if statement) is executed.
Overall, the
page load of this way is about 700 ms with SQL Server, with about
10-20 ms
added for each event added into the database.
The first way is definately better for page load and scalability,
but it is
not very efficient to have 168 queries for a single page load. Does
anyone
have any comments about the two different ways or new ideas on how
to
accomplish this project?
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: querying a database for events in a resource scheduler (like a calendar)
Brent,
Still doable. The cell number of each cell is simply
col num + (row number-1)*24
You can easily compute the correct cell number for each event.
Then, inside your inner loop, compute the cell number as above, and then
stick the value of cell#iCell# into it.
-David
On Sat, 13 Jan 2001 20:30:16 -0800 "Brent Goldman" [EMAIL PROTECTED]
writes:
Hi,
That is a really good idea, but the table is dynamically generated.
The
cells are not generated through a loop of 1 through 168; they are
generated
through a loop of how many rows there are (24; each half-hour for 12
hours)
within an interior loop of how many columns there are (7). How can
I check
within each cell against the variables? Maybe storing all of the
variables
in a single array or structure would be an easier way to organize
the
variables, but what number (or a key in structures) would be checked
upon
during the generation of the table?
-Brent
From: David Shadovitz [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: Re: querying a database for events in a resource scheduler
(like a
calendar)
Date: Sat, 13 Jan 2001 19:53:33 -0800
How about this?
1. Initialize the value of each cell to null:
cfloop from="1" to="168" index="iCell"
cfset tmp = SetVariable("cell#iCell#", "")
/cfloop
2. Do one query to get all events.
3. Loop through the events. For each event, determine what cell it
belongs in, and update the value of that cell.
4. Build the table in a loop, putting the value #cell#iCell# into
each
cell.
-David
On Fri, 12 Jan 2001 19:00:10 -0800 "Brent Goldman"
[EMAIL PROTECTED]
writes:
Hi,
I am currently developing a resource scheduler. The table that
outputs each
schedule has 24 rows on the left: 12 hours with half-hour
increments. There
are 7 columns on the top: one for each day of the week. Total,
there will
be 24*7=168 different cells which need to be populated with
events
if there
are any.
There are two different ways to accomplish this. One way is to
do a
query
to the database in each and every cell, but 168 queries is a
tremendous
amount of queries for one page load. If there are 10 events in
the
database, the page load is about 600 ms with SQL Server.
The second way to accomplish this is do have a master query at
the
top of
the page that retrieves each and every element in the database
relevant to
the current page. Then, within each cell, do a CFOUTPUT or
CFLOOP
through
the query, and if the data is the one we want, output the
results.
This
way, if there are 10 events in the database, and there are 168
loops
(one
loop for each cell), there is a total of 168*10=1680 times the
if
statement
(and possibly the code within the if statement) is executed.
Overall, the
page load of this way is about 700 ms with SQL Server, with
about
10-20 ms
added for each event added into the database.
The first way is definately better for page load and
scalability,
but it is
not very efficient to have 168 queries for a single page load.
Does
anyone
have any comments about the two different ways or new ideas on
how
to
accomplish this project?
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Re: querying a database for events in a resource scheduler (like a calendar)
I am not totally understading the purpose, mostly because of my short
attention span, but remember that queries can be referenced as arrays. You
say this is a dynamically generated "table", so I am assuming this data
comes from a query. If so all of your data is already stored in an array for
you.
You can reference each cell like so:
Queryname.Columnname[row of query]
hth (because it might not ;-))
jon
- Original Message -
From: "Brent Goldman" [EMAIL PROTECTED]
To: "CF-Talk" [EMAIL PROTECTED]
Sent: Saturday, January 13, 2001 11:30 PM
Subject: Re: querying a database for events in a resource scheduler (like a
calendar)
Hi,
That is a really good idea, but the table is dynamically generated. The
cells are not generated through a loop of 1 through 168; they are
generated
through a loop of how many rows there are (24; each half-hour for 12
hours)
within an interior loop of how many columns there are (7). How can I
check
within each cell against the variables? Maybe storing all of the
variables
in a single array or structure would be an easier way to organize the
variables, but what number (or a key in structures) would be checked upon
during the generation of the table?
-Brent
From: David Shadovitz [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: Re: querying a database for events in a resource scheduler (like
a
calendar)
Date: Sat, 13 Jan 2001 19:53:33 -0800
How about this?
1. Initialize the value of each cell to null:
cfloop from="1" to="168" index="iCell"
cfset tmp = SetVariable("cell#iCell#", "")
/cfloop
2. Do one query to get all events.
3. Loop through the events. For each event, determine what cell it
belongs in, and update the value of that cell.
4. Build the table in a loop, putting the value #cell#iCell# into each
cell.
-David
On Fri, 12 Jan 2001 19:00:10 -0800 "Brent Goldman" [EMAIL PROTECTED]
writes:
Hi,
I am currently developing a resource scheduler. The table that
outputs each
schedule has 24 rows on the left: 12 hours with half-hour
increments. There
are 7 columns on the top: one for each day of the week. Total,
there will
be 24*7=168 different cells which need to be populated with events
if there
are any.
There are two different ways to accomplish this. One way is to do a
query
to the database in each and every cell, but 168 queries is a
tremendous
amount of queries for one page load. If there are 10 events in the
database, the page load is about 600 ms with SQL Server.
The second way to accomplish this is do have a master query at the
top of
the page that retrieves each and every element in the database
relevant to
the current page. Then, within each cell, do a CFOUTPUT or CFLOOP
through
the query, and if the data is the one we want, output the results.
This
way, if there are 10 events in the database, and there are 168 loops
(one
loop for each cell), there is a total of 168*10=1680 times the if
statement
(and possibly the code within the if statement) is executed.
Overall, the
page load of this way is about 700 ms with SQL Server, with about
10-20 ms
added for each event added into the database.
The first way is definately better for page load and scalability,
but it is
not very efficient to have 168 queries for a single page load. Does
anyone
have any comments about the two different ways or new ideas on how
to
accomplish this project?
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
Fw: Re: querying a database for events in a resource scheduler (like a calendar)
Make that
col num + (row number-1)*7
-David
- Forwarded message --
From: David Shadovitz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Date: Sat, 13 Jan 2001 20:45:58 -0800
Subject: Re: querying a database for events in a resource scheduler (like
a calendar)
Message-ID: [EMAIL PROTECTED]
Brent,
Still doable. The cell number of each cell is simply
col num + (row number-1)*24
You can easily compute the correct cell number for each event.
Then, inside your inner loop, compute the cell number as above, and then
stick the value of cell#iCell# into it.
-David
On Sat, 13 Jan 2001 20:30:16 -0800 "Brent Goldman" [EMAIL PROTECTED]
writes:
Hi,
That is a really good idea, but the table is dynamically generated.
The
cells are not generated through a loop of 1 through 168; they are
generated
through a loop of how many rows there are (24; each half-hour for 12
hours)
within an interior loop of how many columns there are (7). How can
I check
within each cell against the variables? Maybe storing all of the
variables
in a single array or structure would be an easier way to organize
the
variables, but what number (or a key in structures) would be checked
upon
during the generation of the table?
-Brent
From: David Shadovitz [EMAIL PROTECTED]
Reply-To: [EMAIL PROTECTED]
To: CF-Talk [EMAIL PROTECTED]
Subject: Re: querying a database for events in a resource scheduler
(like a
calendar)
Date: Sat, 13 Jan 2001 19:53:33 -0800
How about this?
1. Initialize the value of each cell to null:
cfloop from="1" to="168" index="iCell"
cfset tmp = SetVariable("cell#iCell#", "")
/cfloop
2. Do one query to get all events.
3. Loop through the events. For each event, determine what cell it
belongs in, and update the value of that cell.
4. Build the table in a loop, putting the value #cell#iCell# into
each
cell.
-David
On Fri, 12 Jan 2001 19:00:10 -0800 "Brent Goldman"
[EMAIL PROTECTED]
writes:
Hi,
I am currently developing a resource scheduler. The table that
outputs each
schedule has 24 rows on the left: 12 hours with half-hour
increments. There
are 7 columns on the top: one for each day of the week. Total,
there will
be 24*7=168 different cells which need to be populated with
events
if there
are any.
There are two different ways to accomplish this. One way is to
do a
query
to the database in each and every cell, but 168 queries is a
tremendous
amount of queries for one page load. If there are 10 events in
the
database, the page load is about 600 ms with SQL Server.
The second way to accomplish this is do have a master query at
the
top of
the page that retrieves each and every element in the database
relevant to
the current page. Then, within each cell, do a CFOUTPUT or
CFLOOP
through
the query, and if the data is the one we want, output the
results.
This
way, if there are 10 events in the database, and there are 168
loops
(one
loop for each cell), there is a total of 168*10=1680 times the
if
statement
(and possibly the code within the if statement) is executed.
Overall, the
page load of this way is about 700 ms with SQL Server, with
about
10-20 ms
added for each event added into the database.
The first way is definately better for page load and
scalability,
but it is
not very efficient to have 168 queries for a single page load.
Does
anyone
have any comments about the two different ways or new ideas on
how
to
accomplish this project?
~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
querying a database for events in a resource scheduler (like a calendar)
Hi, I am currently developing a resource scheduler. The table that outputs each schedule has 24 rows on the left: 12 hours with half-hour increments. There are 7 columns on the top: one for each day of the week. Total, there will be 24*7=168 different cells which need to be populated with events if there are any. There are two different ways to accomplish this. One way is to do a query to the database in each and every cell, but 168 queries is a tremendous amount of queries for one page load. If there are 10 events in the database, the page load is about 600 ms with SQL Server. The second way to accomplish this is do have a master query at the top of the page that retrieves each and every element in the database relevant to the current page. Then, within each cell, do a CFOUTPUT or CFLOOP through the query, and if the data is the one we want, output the results. This way, if there are 10 events in the database, and there are 168 loops (one loop for each cell), there is a total of 168*10=1680 times the if statement (and possibly the code within the if statement) is executed. Overall, the page load of this way is about 700 ms with SQL Server, with about 10-20 ms added for each event added into the database. The first way is definately better for page load and scalability, but it is not very efficient to have 168 queries for a single page load. Does anyone have any comments about the two different ways or new ideas on how to accomplish this project? Thanks -Brent ~~ Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
