bluskyGuy
asked on
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!
The field in the database that holds the date stamp is called datestampid.
Any idea on how to achieve this?
Thanks!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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 :-)
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 :-)
ASKER
Thanks all. I'll give your suggestions a shot and see what works.
Thanks!
Thanks!
ASKER
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">Salesperso n 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.da te1#</stro ng> and <strong>#Form.date2#</stro ng></cfout put>: <span class="style14"><cfoutput> #getDateIn fo.RecordC ount#</cfo utput></sp an></p>
</td>
</tr>
<tr bgcolor="#FFFFFF">
<td height="20" bgcolor="#CCCCCC" class="style2 style13"><div align="center"><strong>Bre akdown 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><spa n class="style3">Salesperson </span></c foutput></ 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>#Recordse t1.SalesUs ername#</s trong></sp an></td>
<td><cfoutput><span class="bottomborder"><span class="style8">#GetTotal.R ecordCount #</span></ span></cfo utput></td >
</tr>
</cfoutput>
</table></td>
</tr>
<tr bgcolor="#FFFFFF">
<td class="style3 style2"> </td>
</tr>
<tr bgcolor="#FFFFFF">
<td class="style3 style2"> </td>
</tr>
</table>
</body>
</html>
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">Salesperso
</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.da
</td>
</tr>
<tr bgcolor="#FFFFFF">
<td height="20" bgcolor="#CCCCCC" class="style2 style13"><div align="center"><strong>Bre
</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><spa
<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>#Recordse
<td><cfoutput><span class="bottomborder"><span
</tr>
</cfoutput>
</table></td>
</tr>
<tr bgcolor="#FFFFFF">
<td class="style3 style2"> </td>
</tr>
<tr bgcolor="#FFFFFF">
<td class="style3 style2"> </td>
</tr>
</table>
</body>
</html>
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>#Recordse t1.SalesUs ername#</s trong></sp an></td>
<td><span class="bottomborder"><span class="style8">#GetTotal.R ecordCount #</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 :-)
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>#Recordse
<td><span class="bottomborder"><span
</tr>
</cfoutput>
Then you can check it by adding this code :
<cfoutput>The total sales is : #TotalSales#</cfoutput>
Just try.
Goodluck!
eNTRANCE2002 :-)
ASKER
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!
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!
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 :-)