variables in a select statement |
variables in a select statement |
seattletype |
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?? |
CharlesEF |
Aug 14 2015, 04:11 PM
Post
#2
|
Programming Fanatic Group: Members Posts: 1,984 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 |
seattletype |
Aug 14 2015, 04:20 PM
Post
#3
|
Group: Members Posts: 2 Joined: 12-August 15 Member No.: 23,284 |
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. |
CharlesEF |
Aug 14 2015, 04:52 PM
Post
#4
|
Programming Fanatic Group: Members Posts: 1,984 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?
|
CharlesEF |
Aug 14 2015, 06:02 PM
Post
#5
|
Programming Fanatic Group: Members Posts: 1,984 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.
|
Lo-Fi Version | Time is now: 25th September 2024 - 07:07 AM |