Printable Version of Topic

Click here to view this topic in its original format

HTMLHelp Forums _ Databases _ variables in a select statement

Posted by: seattletype Aug 12 2015, 12:22 PM

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??

Posted by: CharlesEF Aug 14 2015, 04: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

Posted by: seattletype Aug 14 2015, 04:20 PM

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.

Posted by: CharlesEF Aug 14 2015, 04:52 PM

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?

Posted by: CharlesEF Aug 14 2015, 06:02 PM

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.