Need Help

I am trying to make a counter using SQL server...   if it is not the system date to insert the date plus a 1, but then if it is the date to update based on the date... but when it updates it adds  an extra line with the same date starting a one again... why is that???


<cfquery name="getdate" datasource="nassausports" dbtype="ODBC">
select *
from counter
</cfquery>

<cfoutput query="getdate">
<cfif #date# eq  #dateformat(now())#>
      <cfquery name="insert" datasource="NassauSports" dbtype="ODBC">
            update counter
            set hits = #hits# + 1
            where id = #id#
      </cfquery>
</cfif>

<cfif #date# is NOT #dateformat(now())#>
            <cfquery name="insert1" datasource="NassauSports" dbtype="ODBC">
            insert into counter(date, hits)
            values('#dateformat(now())#', 1)
            </cfquery>
</cfif>
</cfoutput>
LVL 2
cookmysterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

anushaCommented:
For the update,
<cfif #getdate.date# eq  #dateformat(now())#>  
.....
</CFIF>
after this line, the script gets into the next block of code. which is
<cfif #getdate.date# is NOT #dateformat(now())#>
....
</CFIF>

Are you redefining the #date# variable before it test for loop2 (insert1)?


0
cookmysterAuthor Commented:
No.. I am not redefining the #date# variable??  How would I do that, and why???
0
punkerCommented:
You say when it updates it "adds an extra line", do you mean another record in the database?

0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

cookmysterAuthor Commented:
Yeah it adds an extra into the database....
0
anushaCommented:
cookmyster,

I asked a question to clarify the question you originally asked. As for redefining a variable ...  here goes ..<CFSET #date#='12/12/99'>

punker, I think he/she means that it adds a new record, which means that the program gets into the loop where you do the insert statement.


0
anushaCommented:
cookmyster,

I asked a question to clarify the question you originally asked. As for redefining a variable ...  here goes ..<CFSET #date#='12/12/99'>

punker, I think he/she means that it adds a new record, which means that the program gets into the loop where you do the insert statement.


0
punkerCommented:
Out of curiosity, when you put <cfoutput>#DateFormat(Now())#</cfoutput> on a CF page, what do you see?

On mine, it comes up 12-Dec-99, but I'm thinking that might be dependent on how I've set up my CF administrator?

One of the things that popped into my head, was if #DateFormate(Now())# outputs the exact time (i.e., minutes and seconds), theoretically while in the midst of your cfif statement, #DateFormat(Now())# could change, so it would never match in the second cfif statement.

On another note, to streamline your code, use a CFIF-CFELSE statement instead of two CFIFs: (Since a date is either going to be equal (eq) or not equal (neq) but never anywhere in between.)

<cfoutput query="getdate">
<cfquery name="insert" datasource="NassauSports" dbtype="ODBC">
<cfif #date# eq #dateformat(now())#>
update counter
set hits = #hits# + 1
where id = #id#
<cfelse>
insert into counter(date, hits)
values('#dateformat(now())#', 1)
</cfif>
</cfquery>
</cfoutput>
0
cookmysterAuthor Commented:
How do I get the dateformat to just show the date and not the time???
0
punkerCommented:
With an input mask:

#DateFormat(Now(), "DD-MMM-YY")#

Was that the problem?
0
anushaCommented:
Date Format options to show date only.

DateFormat("8/19/96")                                  "19-Aug-96"

 DateFormat("8/19/96", "mmm-dd-yyyy")        "Aug-19-1996"

 DateFormat("8/19/96", "mmmm d, yyyy")       "August 19, 1996"

 DateFormat("8/19/96", "mm/dd/yyyy")           "08/19/1996"

 DateFormat("8/19/96", "d-mmm-yyyy")          "19-Aug-1996"

 DateFormat("8/19/96", "ddd, mmmm dd, yyyy")    "Mon, August 19,1996"

 DateFormat("8/19/96", "d/m/yy")        "19/8/96"




Anusha
0
punkerCommented:
Actually make that YYYY, to make it Y2K compliant. =)

Make sure when you input the dates into your database, you also use the input mask in the same format.
0
Nathan Stanford SrSenior ProgrammerCommented:
Have you solved this problem or Are you still needing help?
0
rod_nolanCommented:
cookmyster,

Your question was a little difficult to understand at first but I took a look at your code and the problem became evident as I started testing. If I have interpreted your question properly, the answer is simply that your page does exactly what you've programmed it to do! In other words, it's just a problem with the logic of the code, rather than the syntax of the code.

As an aside, I had to change the column name "date" to "myDate" and I'm surprised that SQL Server let you give a column a name like "date", which is a reserved word, I believe.

Initially, I noticed the same things that the others have already mentioned regarding streamlining your code (use <cfelse> instead of two <cfif>'s)

Also, when comparing two values, make sure that you don't inadvertently mess up the comparison with bad code. If you're applying the dateFormat() function to one side of the expression, make sure you apply it to the other side as well.

Take a look at the output from the following two pieces of code:

<!--- dateFormat() used on the right side only --->
<cfoutput query="getDate">
Is #getDate.myDate# equal to #DateFormat(now())#?<br>
</cfoutput>
<hr>

<!--- dateFormat() used on both sides --->
<cfoutput query="getDate">
Is #DateFormat(getDate.myDate)# equal to #DateFormat(now())#?<br>
</cfoutput>



OK, the whole problem, as I perceive it, is related to the value of the myDate column in the records that you start with.

Each record that contains a date that is NOT today's date, will create a new record with a date value that IS today's date. Each of those newly created records will be incremented upon subsequent requests on that page but those original records, the ones that 'gave birth' (for lack of a better phrase) to the new records in the first place will continue to give birth to more new records with a hits value of 1 while the records that were created on previous accesses will simply be incremented.

I came to this realization with the template below. Take a look at it and let us know if it solves the problem. It's heavily commented so it should be self explanatory. The only things you'll need to change to make this example work on your machine is the myDate column name and the datasource name.

Good Luck,
Rod


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<html>
<head>
      <title>Date Counter</title>
</head>

<body>

<!--- go get all the data to show what we're starting with before the page is executed --->
<cfquery name="getStartdata" datasource="#application.dsn#">
      select *
      from counter
</cfquery>

<h3>This is what we're starting with</h3>
<table border="1">
<tr>
      <th>ID</th>
      <th>Date</th>
      <th>Hits</th>
</tr>
<cfoutput query="getStartData">
<tr>
      <td>#getStartData.ID#</td>
      <td>#DateFormat(getStartData.myDate)#</td>
      <td>#getStartData.Hits#</td>
</tr>
</cfoutput>
</table>

<hr size=5 noshade color=Red>


<!--- now go get all the data to work on,
this is an unnecessary step because we just did the query
but the first one is just for demonstration purposes
and will be deleted from the final page --->
<cfquery name="getdate" datasource="#application.dsn#">
      select *
      from counter
      order by id
</cfquery>


<!---
Just so you know, this is the data that I started with: yesterday, today and tomorrow
That should cover all possible situations. Modify according to today's date as necessary.

id      hits      myDate
2      22      12/20/99
3      24      12/21/99
4      26      12/22/99
 --->


<!---
start a loop to check each record in the database
and do an insert or an update depending on the
value of the date field in the current row.

date IS today? update current row: increment hits column
date NOT today? insert a new row: today's date and 1 hit
--->
<cfoutput query="getdate">
      <cfif #DateFormat(getDate.myDate)# EQ #DateFormat(now())#>
      <!--- if the date in the current row IS today's date,
                  increment the hits column by 1 --->

            <b>Row #getDate.id# was updated</b>. The hits column was incremented by 1.<br>
            The next time this page is reloaded the hits column in <b>row #getDate.id#</b> will be incremented again.<p>
            
            <cfquery name="update" datasource="#application.DSN#">
                  update counter
                  set hits = #getDate.hits# + 1
                  where id = #getDate.id#
            </cfquery>
      <cfelse>
      <!--- if the date in the current row is NOT today's date,
                  insert a new record with today's date and 1 hit --->

            <i>Row #getDate.id# caused a new record to be inserted with today's date and 1 hit.</i><br>
            The hits column in this newly created row will be updated (incremented by 1) the next time you reload.<br>
            The next time this page is reloaded, <b>row #getDate.id#</b> will create another new row with <b>today's date</b> and <b>1 hit</b>.<p>
            <cfquery name="insert" datasource="#application.DSN#">
                  insert into counter (hits, myDate)
                  values(1, #CreateODBCDate(now())#)
            </cfquery>
      </cfif>
</cfoutput>

<hr size=5 noshade color=Red>

The table now contains the following data.<br>

<cfquery name="getnewdata" datasource="#application.dsn#">
      select *
      from counter
</cfquery>

<table border="1">
<tr>
      <th>ID</th>
      <th>Date</th>
      <th>Hits</th>
</tr>
<cfoutput query="getNewData">
<tr>
      <td>#getNewData.ID#</td>
      <td>#DateFormat(getNewData.myDate)#</td>
      <td>#getNewData.Hits#</td>
</tr>
</cfoutput>
</table>

</body>
</html>

0
cookmysterAuthor Commented:
hey rod... your code does is doing the same thing my code was doing.. it adds an extra record to the database when the page is reloaded... if I reload the page for today's date  12/22/99.. say three times.. I get three new records with that date???
0
Nathan Stanford SrSenior ProgrammerCommented:
Why didn't you reject it then?  I think I can give you an answer.

If you are saying that you want a counter that will count the number of hits per day I can do that for you and if not help me understand the final goal and I can give you the answer you are wanting.
0
cookmysterAuthor Commented:
yeah I need a counter that will count the number of hits per day
0
anushaCommented:
Try using CFCOOKIE to get a correct count for number of distinct hits on a page.

Anusha
0
anushaCommented:
Are you reading this thread?
0
Nathan Stanford SrSenior ProgrammerCommented:
Below is a working example of what you are looking for if you need more help let me know...

Nathan

ps. I setup a sample database and table to test this out before posting.
==================================


<cfset NeedNewDate =0>

<cfquery name="getdate" datasource="nassausports" dbtype="ODBC">
select *
from counter
</cfquery>

<cfoutput query="getdate">
<cfset today = #DateFormat("#now()#",'mm/dd/yyyy')#>
<cfset CheckDate = #DateFormat("#date#",'mm/dd/yyyy')#>

<cfif #today# eq #CheckDate#>
<cfquery name="insert" datasource="NassauSports" dbtype="ODBC">
update counter
set hits = #hits# + 1
where id = #id#
</cfquery>
<cfset NeedNewDate = 1>
</cfif>
</cfoutput>

<cfif NeedNewDate eq 0>
<cfset newdate = #CreateOdbcDateTime(#today#)#>
<cfquery name="insert1" datasource="NassauSports" dbtype="ODBC">
insert into counter(hits)
values( 1)
</cfquery>
</cfif>

<cfquery name="GetMaxDate" datasource="nassausports" dbtype="ODBC">
select max(id) As maxid
from counter
</cfquery>
<cfset NewMaxID = #GetMaxDate.maxid#>
<cfquery name="displaydate" datasource="nassausports" dbtype="ODBC">
select *
from counter
where id = #NewMaxID#
</cfquery>

<html>
<head>
      <title></title>
</head>
<body bgcolor="ffffff">
<br><br><br><br>

<cfoutput query="displaydate">
#hits#
</cfoutput>

</body>
</html>
0
Nathan Stanford SrSenior ProgrammerCommented:
Can you comment on WHY you rejected this code it works???
0
cookmysterAuthor Commented:
sorry man.. didn't mean to reject it... post an answer again... and I will accept your answer
0
bigbadbCommented:
Below is a working example of what you are looking for if you need more help let me know...

Nathan

ps. I setup a sample database and table to test this out before posting.
==================================


<cfset NeedNewDate =0>

<cfquery name="getdate" datasource="nassausports" dbtype="ODBC">
select *
from counter
</cfquery>

<cfoutput query="getdate">
<cfset today = #DateFormat("#now()#",'mm/dd/yyyy')#>
<cfset CheckDate = #DateFormat("#date#",'mm/dd/yyyy')#>

<cfif #today# eq #CheckDate#>
<cfquery name="insert" datasource="NassauSports" dbtype="ODBC">
update counter
set hits = #hits# + 1
where id = #id#
</cfquery>
<cfset NeedNewDate = 1>
</cfif>
</cfoutput>

<cfif NeedNewDate eq 0>
<cfset newdate = #CreateOdbcDateTime(#today#)#>
<cfquery name="insert1" datasource="NassauSports" dbtype="ODBC">
insert into counter(hits)
values( 1)
</cfquery>
</cfif>

<cfquery name="GetMaxDate" datasource="nassausports" dbtype="ODBC">
select max(id) As maxid
from counter
</cfquery>
<cfset NewMaxID = #GetMaxDate.maxid#>
<cfquery name="displaydate" datasource="nassausports" dbtype="ODBC">
select *
from counter
where id = #NewMaxID#
</cfquery>

<html>
<head>
<title></title>
</head>
<body bgcolor="ffffff">
<br><br><br><br>

<cfoutput query="displaydate">
#hits#
</cfoutput>

</body>
</html>
0
cookmysterAuthor Commented:
nice try bigbadb....  nathans is supposed to answer the question.....
0
Nathan Stanford SrSenior ProgrammerCommented:
Thanks you, If you need anymore help let me know.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
cookmysterAuthor Commented:
no worrries....
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Web Servers

From novice to tech pro — start learning today.