abbymsmith
Posts: 23
|
| Posted: 01/01/2009, 8:52 AM |
|
Hi Everyone,
I'm having a 'tear my hair out moment' here, hoping someone can help me.
MySQL 5.0 | PHP | CCS 4.1.00.027
I have a page I designed awhile ago with a search & grid based on a table (actualy a view, but behaves just like a table), v_Reservation, in a MySQL 5.0 database. The grid displays all the reservations in the table by default, and the search lets you find a specific reservation (by searching on last name for example). It has always worked fine before.
I'm upgrading the system to include some row-level security: meaning, I want certain users who have logged in to only have access to the locations they are allowed to access. To achieve this, I designed a user access table in my database that has a user id on it and specifies which locations that user has access to. Then I use the UserID session variable codecharge stores at login to restrict the query on the table as follows:
select
r.*
from
v_Reservation r,
Event_Location el,
Event e,
User_Event_Location_Access ua
where
r.Event_Location_ID = el.Event_Location_ID and
el.Event_Location_ID = ua.Event_Location_ID and
e.Event_ID = el.Event_Location_ID and
e.Active_Flag = 'Y' and
ua.User_ID = {UserID}
This also works great (on this and other pages), but to do it, I had to change the Data Source of the grid from Table to SQL. When I did this, while the security worked correctly (when users log in they can only see the reservations they are assigned to) the search functionality stopped working (all search attempts returned all rows).
At first I thought it was because I simply needed to add the search parameters returned from the search form into the sql of the grid. So I did this - adding the parameters as URL parameters and then modifying the SQL statement to look like this:
select
r.*
from
v_Reservation r,
Event_Location el,
Event e,
User_Event_Location_Access ua
where
r.Event_Location_ID = el.Event_Location_ID and
el.Event_Location_ID = ua.Event_Location_ID and
e.Event_ID = el.Event_Location_ID and
e.Active_Flag = 'Y' and
ua.User_ID = {UserID} and
r.Last_Name like '%{s_Last_Name}%' and
r.First_Name like '%{s_First_Name}%' and
r.Email_Address like '%{s_Email_Address}%' and
r.Event_Name like '%{s_Event_Name}%' and
r.Location_Name like '%{s_Location_Name}%' and
r.Date_Desc like '%{s_Date_Desc}%' and
r.Session_Desc like '%{s_Session_Desc}%' and
r.Reservation_Status_Desc like '{s_Reservation_Status_Desc}%' and
r.Create_User_Name like '%{s_Create_User_Name}%' and
r.Reservation_Comment like '%{s_Reservation_Comment}%'
BUT - it still doesn't work. Now it shows no reservations at all for anybody. I've tried a million different configurations (using ORs instead of ANDs, etc) and I simply can't get it to work correctly. Either all reservations are returned all the time, or no reservations are returned. What frustrates me, is it seems like it *should* work... 
Does anyone have any ideas? Is it possible to create a search form for a grid that's based on SQL instead of single table? I'm at the point where my next step was to completely recreate the page -- but I noticed the grid builder wouldn't let me add a search form if the grid was based on sql -- so I thought maybe I'm running up against something that can't be done? Any help would be much appreciated.
Thanks,
Abby
|
 |
 |
asawyer13
Posts: 46
|
| Posted: 01/01/2009, 9:46 AM |
|
I wonder if the date search is the problem.
I would try removing that part and see if you get results.
Alan
|
|
|
|
abbymsmith
Posts: 23
|
| Posted: 01/01/2009, 11:46 AM |
|
Just tried it without the Date Search and I have the same problem. The date field is actually just a text field that stores a descriptive date label - it's not an actual date field. So I don't think that's the problem. All the search items are text fields...
|
|
|
|
damian
Posts: 838
|
| Posted: 01/01/2009, 1:54 PM |
|
firstly - does your return string include all of your search strings in the url?
second - have you setup your SQL parameters correctly?
eg
name s_Last_Name s_Last_Name
type Text URL
next inside your visual query builder use the Show Data button to test your query
when you click it you will be prompted for all of your search values
do you get data?
try removing all of your search parameters and adding 1 only at a time until you find your problem parameter
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
abbymsmith
Posts: 23
|
| Posted: 01/01/2009, 2:30 PM |
|
damian: thank you, thank you, thank you! You solved my problem. I had removed a search parameter from the form and forgot to remove it from the sql query! I took it out of the query and now everything's working again. I can't believe how much time I wasted on something so simple.  Thank you so much for your help, you are my guardian angel this evening!
|
|
|
|
damian
Posts: 838
|
| Posted: 01/01/2009, 2:35 PM |
|
possibly your session syntax is wrong -
instead of
ua.User_ID = {UserID}
try
ua.User_ID = CCGetSession("UserLogin"))
or define it as a SQL parameter with
name ua.User_ID UserID
type Integer Session
although after re-reading your post it looks like your syntax does work in the first grid so its more likely a problme with one of your other search parameters...
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
damian
Posts: 838
|
| Posted: 01/01/2009, 2:36 PM |
|
doh!
just finished typing another response and you solved it in the interim
well done - now back to something more productive
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
|
