The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> variables in a select statement
seattletype
post Aug 12 2015, 12:22 PM
Post #1





Group: Members
Posts: 2
Joined: 12-August 15
Member No.: 23,284



I've built a search form in essence, where the form has several variables to be passed to the select statement. There's a uniqueID, a typeID and two date fields where a user can search between the two dates. THe form works fine if only searching on either of the IDs, and it works fine if searching between two dates, but when I put them all together in the select statement, none of them work. I'm sure it's just a grammar thing, but I don't know the answer. I was hoping it was going to be something like this: (where $from and $to were assigned values from the form - this works, by the way)

SELECT * FROM Res WHERE (RID LIKE ? OR Type LIKE ? OR (DateField BETWEEN '$from' AND '$to')) ORDER BY RID

but.. that doesn't work. Help??
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Aug 14 2015, 04:11 PM
Post #2


This is My Life
*******

Group: Members
Posts: 1,436
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



I haven't used LIKE in any of my SQL but I think your problem lies with this line of code:
CODE
SELECT * FROM Res WHERE (RID LIKE ? OR Type LIKE ? OR (DateField BETWEEN '$from' AND '$to')) ORDER BY RID
If you want to search by either RID or Type and have a date range then you should use the AND keyword instead of the OR you are using, like this:
CODE
SELECT * FROM Res WHERE (RID LIKE ? OR Type LIKE ?) AND (DateField BETWEEN '$from' AND '$to') ORDER BY RID
I should say this SELECT will require your users to search for RID or Type and a date range. If you want to allow them to search by either RID or Type or a date range then try this:
CODE
SELECT * FROM Res WHERE (RID LIKE ? OR Type LIKE ?) OR (DateField BETWEEN '$from' AND '$to') ORDER BY RID


This post has been edited by CharlesEF: Aug 14 2015, 04:15 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
seattletype
post Aug 14 2015, 04:20 PM
Post #3





Group: Members
Posts: 2
Joined: 12-August 15
Member No.: 23,284



QUOTE(CharlesEF @ Aug 14 2015, 02:11 PM) *

I haven't used LIKE in any of my SQL but I think your problem lies with this line of code:
CODE
SELECT * FROM Res WHERE (RID LIKE ? OR Type LIKE ? OR (DateField BETWEEN '$from' AND '$to')) ORDER BY RID
If you want to search by either RID or Type and have a date range then you should use the AND keyword instead of the OR you are using, like this:
CODE
SELECT * FROM Res WHERE (RID LIKE ? OR Type LIKE ?) AND (DateField BETWEEN '$from' AND '$to') ORDER BY RID
I should say this SELECT will require your users to search for RID or Type and a date range. If you want to allow them to search by either RID or Type or a date range then try this:
CODE
SELECT * FROM Res WHERE (RID LIKE ? OR Type LIKE ?) OR (DateField BETWEEN '$from' AND '$to') ORDER BY RID



Yeah, I think that last one is what I'm trying to use, however, I fear it is requiring that ALL fields have SOME value in them or else the select fails. Trying to figure out how to work around that.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Aug 14 2015, 04:52 PM
Post #4


This is My Life
*******

Group: Members
Posts: 1,436
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



I don't think that is true but the only way to know for sure is to try it out. Your script would need to make sure that some data is supplied either a RID or Type or if those are blank then make sure $from and $to is supplied. I see you use ? in the RID or Type but why not for the DateField?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
CharlesEF
post Aug 14 2015, 06:02 PM
Post #5


This is My Life
*******

Group: Members
Posts: 1,436
Joined: 27-April 13
From: Edinburg, Texas
Member No.: 19,088



If you have to I guess you could build the SQL query that you need. Use if statements to test which values are passed and build the query as needed, based on those values.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 20th November 2017 - 08:19 AM