OK .. so this is the last part of this ( and I bet you thought you were done with me didn't you? ).

If I wanted to modify the SQL statement you gave me for an update form in which I wanted to show the data just for that user (based in the usr_id passed in the URL string) and for that user, I need to show all of the airports and services available but with the users selections for airports and services checked and the others not?

I have been trying it with multiple recordsets but I am thinking there is a more efficient way?

Dave

not quite sure what you are meaning, show me what you want your result set to be and what your current query is

Well, I have attached an image of what I need the layout to be and how it shows ALL of the airports and all three services for each. It also then shows the airports that are selected with their respective services by means of a checkbox.

At this point, while I know I need a more flexible query, I am basically using three separate querys to make this work so far. The problem with it is that all the airports and services get displayed OK ... but they are all checked.

First Query to get the user data:


Then I am using two queries to get all of the airports and all of the services. The reason I am going this is that I am building a repeat region for the airports, and a nested repeat region for the services is each row that displays the airport current record (see attached image).



Then I am getting the records from the userairportservices table where the usr_id_usr matches the usr_id passed in the URL string


I suspect I need to do a JOIN on these. As far as the layout goes, I am not necessarily stuck on having to do the nested repeat so if the query combines the data properly that I can do a single repeat region and then put the service select boxes in a row with the airport name, code and select box, that will work as well.

Thanks

Dave

OK . so here is the beginning of a revised query:


This pulls up the results shown in the attached image. What I need to do now is combine this query somehow with another that selects all of the airports, all of the services -> airports (services for each airport), which would show the user id, the airport id, a 1 or 0 for the airport checked or unchecked, the services for that airport and a 1 or 0 for selected or unselected checkbox.

I thought I was getting the hang of this, and I suppose to a certain degree I am but either I am over-complicating again or this IS complicated. Not sure which. ;-)

Dave

Wondering why this does not work?


Throws the error shown in the attached image. Grrrr!!!!

Dave

you need to specify inner join on (columnname)
maybe i just don't get it, lol but i cannot understand what you are trying to do here

OK .. I am sure I am not explaining this clearly ??

I need to dynamically list all the airport names and their codes plus a checkbox (which needs to be checked if the user has access to the airport) as well as, in the same row, all the services with a checkbox for each, that are checked or unchecked. depending on whether the database shows the user as having access to the service at this particular airport. Sort of what you gave me before but I need to show all the airports > services, not just the ones that the user has access to. This is because this is part of an update form where the admin may be adding or removing access to an airport that was not previously selected.

I might be able to use PHP and the previous query you gave me to get what I need but I am thinking that there is a way to do this completely in a query. This would be quicker and more efficient I would think.

Dave

i don't have a mysql database in front of me right now, but trying changing that statement i gave you, to a right join instead of inner

Looks to be the same results.

Dave

reverse the statement and use a left