getting results between two seperate dates

Greetings...I have two html form fields on a page. Each field requires a date in the format of yyyy-mm-dd to be entered. I want results to be retrieved from my mySQL db that are between these two user defined dates.

The field in the database that holds the date stamp is called datestampid.

Any idea on how to achieve this?

Thanks!
bluskyGuyAsked:
Who is Participating?
 
umbraeConnect With a Mentor Commented:
<cfquery name="getDateInfo" datasource="yourdb">
SELECT *
from dateDB
where datestampid BETWEEN <cfqueryparam value="#form.date1#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#form.date2#" cfsqltype="cf_sql_date">
</cfquery>

will return a query with the required data.
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hello bluskyGuy!

You can simply have your query like this :

<cfquery name="GetRecord" datasource="DSN">
SELECT *
FROM TableName
WHERE datestampid BETWEEN <cfqueryparam cfsqltype="cf_sql_date" value="#defined_date1#">
  AND <cfqueryparam cfsqltype="cf_sql_date" value="#defined_date2#">
</cfquery>

Hope this helps!

Goodluck!
eNTRANCE2002 :-)
0
 
Renante EnteraSenior PHP DeveloperCommented:
Ooops!  

I think you should ignore my comment since umbrae posted it first.  We have the same solution being provided for you.

I apologize...  I hope you will consider it umbrae.

I must have to reload the question first before submitting my comment.  I thought, nobody has posted a comment.


Regards!
eNTRANCE2002 :-)
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
bluskyGuyAuthor Commented:
Thanks all. I'll give your suggestions a shot and see what works.

Thanks!
0
 
bluskyGuyAuthor Commented:
Great, that worked!
Now, I have one other task to perform. Basically I have two queries. One query that gets a list of salespeople from the database. Another query that is supposed to generate how ever many sales a perspective salesperson had.

Here's the format I'm going after:

Salesperson             Total # of sales
Sam                        1
Mark                       4
etc                          etc

Basically I can get the output query for the salespeople to function fine. It's the issue with getting their total sales. Here's the script I have so far...

//this works perfect!
 <cfquery name="getDateInfo" datasource="bsc_db">
SELECT *
from memberinfo
where timestampid BETWEEN <cfqueryparam value="#form.date1#" cfsqltype="cf_sql_date"> AND <cfqueryparam value="#form.date2#" cfsqltype="cf_sql_date">
</cfquery>

//this is getting the list of salespeople correctly as well...
<cfquery name="Recordset1" datasource="BSC_DB">
SELECT *
FROM admintbl WHERE
salesName != ''
ORDER BY admintbl.salesName ASC</cfquery>

<body>

 

  <table width="66%" border="0" align="center" cellpadding="4" cellspacing="0" class="border">
    <tr bgcolor="#CCCCCC">
      <td width="100%" class="style3 style2"><div align="center"><span class="style12">Salesperson Reporting Module</span></div></td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td class="style3 style2"></td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td height="47" class="style3 style2"><p class="style10">Total Sales Between <cfoutput><strong>#Form.date1#</strong> and <strong>#Form.date2#</strong></cfoutput>: <span class="style14"><cfoutput>#getDateInfo.RecordCount#</cfoutput></span></p>
      </td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td height="20" bgcolor="#CCCCCC" class="style2 style13"><div align="center"><strong>Breakdown by Salesperson </strong></div></td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td height="21" class="style2 style13"><table width="100%" height="41" border="0" align="center" cellpadding="0" cellspacing="0">
        <tr bgcolor="#003366">
          <td width="42%"><cfoutput><span class="style3">Salesperson</span></cfoutput></td>
          <td width="58%"><span class="style3">Total for this Period </span></td>
        </tr>
        <cfoutput query="Recordset1">
            <cfquery name="GetTotal" datasource="BSC_DB">
    SELECT * FROM memberinfo WHERE memberinfo.salesid = '#Recordset1.salesusername#'
    </cfquery>
          <tr>
            <td><span class="style3 style13"><strong>#Recordset1.SalesUsername#</strong></span></td>
            <td><cfoutput><span class="bottomborder"><span class="style8">#GetTotal.RecordCount#</span></span></cfoutput></td>
          </tr>
        </cfoutput>
            </table></td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td class="style3 style2">&nbsp;</td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td class="style3 style2">&nbsp;</td>
    </tr>

   
  </table>

</body>
</html>
0
 
Renante EnteraSenior PHP DeveloperCommented:
Hello bluskyGuy!

I think you need something like this :

<cfparam name="TotalSales" default="0"> <!--- Put this on top of your page --->

<!--- On the part of looping your query --->
        <cfoutput query="Recordset1">
          <cfquery name="GetTotal" datasource="BSC_DB">
          SELECT * FROM memberinfo WHERE memberinfo.salesid = '#Recordset1.salesusername#'
          </cfquery>
          <cfset TotalSales = TotalSales + GetTotal.recordcount>
          <tr>
            <td><span class="style3 style13"><strong>#Recordset1.SalesUsername#</strong></span></td>
            <td><span class="bottomborder"><span class="style8">#GetTotal.RecordCount#</span></span></td>
          </tr>
        </cfoutput>

Then you can check it by adding this code :
        <cfoutput>The total sales is : #TotalSales#</cfoutput>

Just try.


Goodluck!
eNTRANCE2002 :-)
0
 
bluskyGuyAuthor Commented:
That worked perfectly!
Since this was a two question post with umbrae providing the first answer the points will be given to him.
I'm going to post another question that you provided the answer too. Post the response again and I'll give you the full 500 points.

Thanks again!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.