By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,707 Members | 1,640 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,707 IT Pros & Developers. It's quick & easy.

how to search using php and sql server as database

P: 11
how does one search using date "from to" while still selecting a user from a drop down list
Feb 2 '10 #1
Share this Question
Share on Google+
7 Replies


Atli
Expert 5K+
P: 5,058
Hey.

You should be able to just do something like:
Expand|Select|Wrap|Line Numbers
  1. SELECT [stuff]
  2. FROM [tablename]
  3. WHERE [datefield]
  4.     BETWEEN '2010-01-01' AND '2010-02-01';
I'm no expert on Microsoft SQL Server in particular, but the basic syntax is the same for most types of SQL based databases.


If that is not what you are looking for, please elaborate on your question. Perhaps show us a sample of the code you are having trouble with.
Feb 2 '10 #2

code green
Expert 100+
P: 1,726
In addition SQL Server does not have a DATE data type, only DATETIME so to capture a complete two days you will need
Expand|Select|Wrap|Line Numbers
  1. SELECT [stuff] 
  2. FROM [tablename] 
  3. WHERE [datefield] 
  4.     BETWEEN 
  5. '2010-01-01 00:00:00.000' AND '2010-02-01 23.59.59.999'; 
Feb 2 '10 #3

Atli
Expert 5K+
P: 5,058
It doesn't? Seems like somewhat of an oversight, to be honest.

... barely managed to refrain from taking a cheap-shot at M$ just now xD

@code green
That would be two months, would it not?
It would be in MySQL, but I don't really know SQL Server.
Feb 2 '10 #4

code green
Expert 100+
P: 1,726
Sorry, yes two months.
Dates are a pain. Have to use CAST or CONVERT to strip off time segment.
Need to be careful with comparisons, which is why I rather rudely interupted, Atli
Feb 2 '10 #5

P: 11
this is the sample code

Expand|Select|Wrap|Line Numbers
  1. $vUserActQuery = '    SELECT ds_description,ua_busscompid, ua_type, ua_actionid , ua_startdate, ua_enddate, user_comments 
  2.                                 FROM dms_useraction,status
  3.                                 WHERE ua_userid = \''.$vUserId.'\' AND ua_actionid=ds_id' from this part its wrong help me out AND ua_startdate >= \''.$vStartDate.'\' AND ua_startdate <= \''.$vToDate.'\';    
thnx
Feb 3 '10 #6

code green
Expert 100+
P: 1,726
I prefer to wrap queries in double quotes.
It looks cleaner than escaping, so makes spotting errors easier
Expand|Select|Wrap|Line Numbers
  1. $vUserActQuery = "SELECT ds_description, ua_busscompid, 
  2. ua_type, ua_actionid , ua_startdate, ua_enddate, user_comments 
  3. FROM dms_useraction,status
  4. WHERE ua_userid = $vUserId AND ua_actionid = ds_id
  5. from this part its wrong help me out 
  6. AND ua_startdate >= $vStartDate AND ua_startdate <= $vToDate; 
  7.  
If I have cleaned this correctly, I can see straight away there are no single quotes around the dates.
I used to make that error.
Expand|Select|Wrap|Line Numbers
  1. AND ua_startdate >= '$vStartDate' AND ua_startdate <= '$vToDate'
But as Atli suggested, BETWEEN is simpler
Expand|Select|Wrap|Line Numbers
  1. AND ua_startdate BETWEEN '$vStartDate' AND '$vToDate'
If $vUserId is a string that needs wrapping in quotes.
DON'T overlook the time element as in my first post.
And may I suggest studying the JOIN command
Feb 3 '10 #7

P: 11
thanx a million guyz
Feb 3 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.