Solved

MySQL Query by date range

Posted on 2011-03-08
19
507 Views
Last Modified: 2012-05-11
The bulk of my new portal is to query data based on users submissions by date range.  I have all the information in 2 tables in my database.  a table for the church information and another table for the weekly submitted information.  The table setup is:

tblChurch
churchID
churchName
etc.

tblChurchInfo
churchInfoID
weeklyGiving
date
churchID

i can figure out how to write mySQL query to get all the data input by church, and church name, what i'd like is to add a filter where the data could be filtered by date.  any suggestions?
0
Comment
Question by:axessJosh
  • 9
  • 9
19 Comments
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35074022
Hi again axessJosh,

How exactly is the workflow going to happen?  The users will submit a date or a date range or do you just need to know how to construct the query?
0
 
LVL 2

Author Comment

by:axessJosh
ID: 35074122
both.

at the user level, they will login and submit their data and select a date (i.e. this past sunday) for the data.

then;

at the admin level, i need for that data to be queried by the date selected by the admin.  I'd like for them to be able select date ranges and see data trends and changes.  For right now, if i can just get the data queried by date i'd be happy.

make sense?
0
 
LVL 70

Accepted Solution

by:
Jason C. Levine earned 250 total points
ID: 35074356
You need to set an advanced recordset and then write the where clause using the field submitted from the form.  The SQL would look something like this:

SELECT tblChurch.*, tblChurchInfo.* FROM tblChurch INNER JOIN tblChurchInfo ON tblChurch.churchID = tblChurchInfo.churchID WHERE tblChurchInfo.date Between YYYY-MM-DD And YYYY-MM-DD;

The above would select a range of dates if you are using two date fields.  In the Advanced View, you would set these as variables using $_POST['datefieldinformname1'] and $_POST['datefieldinformname2'] as the run-time variables.  To do a single date, the SQL would be:

SELECT tblChurch.*, tblChurchInfo.* FROM tblChurch INNER JOIN tblChurchInfo ON tblChurch.churchID = tblChurchInfo.churchID WHERE tblChurchInfo.date = YYYY-MM-DD;
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 40

Expert Comment

by:Sharath
ID: 35074375
Are you looking for something like this?
select *
  from tblChurch c
  join tblChurchInfo ci on c.churchID = ci.churchID
 where ci.date between date1 and date2

Open in new window

0
 
LVL 2

Author Comment

by:axessJosh
ID: 35074383
In both examples would i change yyyy-mm-dd to the form variables or leave it as is?
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35074457
Change it to the form values always. The other stuff is just placeholder data
0
 
LVL 2

Author Comment

by:axessJosh
ID: 35100501
OK, here is what i landed on that works for my purposes.

SELECT tblchurch.churchID, tblchurch.churchName, tblchurchinfo.churchAttendance, tblchurchinfo.churchSSAttendance, tblchurchinfo.churchResponsibility, tblchurchinfo.dateID, tblchurchinfo.churchID FROM tblchurch JOIN tblchurchinfo ON tblchurch.churchID = tblchurchinfo.churchID WHERE tblchurchinfo.dateID = dateVar

dateVar is a dreamweaver variable i made up and is using $_POST['dateID'] to capture the user's input.

now i need have populated a drop down menu with the potential dates to select using DISTINCT in my SQL statement.

how can i pass that variable to my original SQL statement to retrieve the results by dateVar?
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35100556
Just have the form field use dateID as the field name and on post, it will submit $_POST['dateID']
0
 
LVL 2

Author Comment

by:axessJosh
ID: 35100590
it is sending the variable data to the URL, how can i pass just to the page?
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35100603
<form method="post">

You have to choose...post or get.  Can't have it both ways in the same form.  You might have to use two different forms...
0
 
LVL 2

Author Comment

by:axessJosh
ID: 35100621
should i have anything for <form action="" >
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35100638
You don't have to set an action.  Forms will post to themselves in absence of an action parameter.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 35100647
when I select a field, it tries to send me to a new page.  this is the error code i get as a 404 not found.

http://localhost/district_contentDB/2011-02-27

how can i have the jump menu not send to another page?
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35100693
Without seeing your code I have no idea, but jump menus by definition try to send the user to the selected value.  So if you built a dynamic jump menu, it's doing what you told it to do.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 35100722
I have attached the code below.  Currently, i am using a list to populated from a recordset using distinct to pull the date.  I then want that date to be passed and retrieved from a second recordset as the filter to display.

i added a php echo field just to check what data was being passed.  Currently, it shows the same date no matter what.
weeklySummary.php
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35100829
Try putting action="" back in the form tag and see if that fixes it.
0
 
LVL 2

Author Comment

by:axessJosh
ID: 35100873
Tried that.

for some reason it doesn't seem like the form variable is being sent.  I am wondering if it is a problem with the dreamweaver variable code in one recordset

$dateVar_rsChurchInfo = "-1";
if (isset($_POST['dateID'])) {
  $dateVar_rsChurchInfo = $_POST['dateID'];
}

and the form is pulling the data from a different recordset?  

seems as if the data I am trying to retrieve is not making a connection that dateID is present.  (i realize that is laymans terms)
0
 
LVL 70

Expert Comment

by:Jason C. Levine
ID: 35101097
It's not recognizing because your recordset code (that I'm looking at from the download) is wrong:

$dateVar_rsChurchInfo = "-1";
if (isset($_GET['dateID'])) {
  $dateVar_rsChurchInfo = $_GET['dateID'];
}

0
 
LVL 2

Author Comment

by:axessJosh
ID: 35109007
I tried changing to both variations (GET and POST) and still get the same issue.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MSSQL 2008 with mySQL webservers 7 51
SQL inner join confusion 15 52
How do I call MySQL Stored Procedure from oracle using HS link ? 5 41
when to use sequences in mysql 4 27
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Creating and Managing Databases with phpMyAdmin in cPanel.
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

775 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question