This has it. It is weird because this is more-or-less what I had and the page just came up blank. turns out that it has nothing to do with the query but something else on the page is cause the page...

Hello all,

I have the following SQL statement.

SELECT a.airport_code AS AIRPORTCODE, a.airport_name AS AIRPORT, u.usr_id AS USERID, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME,...

Cheers ... I had a quick glance at this and the scarey thing is, for the most part, I understood it. Not perfectly clear but certainly not overly cloudy either.

Dave

OK .. I was migrating an application from a staging server where the MySQL version is 4.1.10a to a live server where the MySQL version is 5.0.45. There apparently is quite a difference between query...

Yeah I have learned a lot thanks to you.

Any time man!

OK ;-)))))))

That's got it!!!!

Now all I have to do is figure out how to disable the services checkboxes until the user clicks on an airport checkbox ... but that is another issue ;-)

You...

Actually, this will no longer be an if/else statement will it? We just need to test if there is a record matching the user, airport etc and then delete all of those. Then do an INSERT. I think that...

OK .... you are correct ;-) I guess the problem was the * all along because I used the same SQL when I was trying it before.

So, if there is a match, delete from userairportservices where usr_id...

So, I think you are still saying that we should be able to delete directly from the UAS table ... yes? So, what is the purpose of constraints?

Do I have to alter the tables/database or something?

OK .. I used the following:

DELETE * FROM `userairportservices` WHERE `usr_id_users` = 5

and it says I have an error in my syntax. I don't think I do. But I bet if I delete the user it will...

OK I will try it but the relationship is a one-to-many from users to uas ... yes? It would not make sense that a user can exist and not have a link to an airport or service?? I will try deleting...

Yeah I have the usr_id no problem but the uas table will NOT allow deletes if a matching user exists in the users table because a user cannot exist in the users table without a related id in the uas...

Well I had been working along the line of deleting all the records belonging to the usr_id before INSERT or UPDATE. However, this is where I ran into trouble with the constraints. I would have to...

Ok ... I am back. I had a minor freak out because I am under considerable pressure to get this done and then my machine started to crash and then my page code got out of control etc ...etc. On and on...

There is something here that does not seem quite right (God I want to be done with this ;-))

Here is the SQL to find all matches:

SELECT uas.userairportservices_id FROM userairportservices uas...

Well it is still not updating and is still pretty weird to me. Here is what I have so far:

foreach ($a_id as $aid => $chosen) {
foreach ($chosen as $sid => $v) {
if...

Sorry ... let me make sure I understand what is going on here:

1. I have a multidimensional array here created from the combination of the airport_id checkboxes and the service_id checkboxes.
2....

RE: $aid vrs $a_id: yes I am pretty sure that is correct. It works perfectly in my INSERT page.

The error, however, seems to be because I have no $db object. This is OOP and there is no object...

I am using the following:

$userid = $_GET['usr_id'];
echo "USER ID: " . $userid . "<br />";
$a_id = $_POST['airport_id'];
/*echo print_r($linkID);*/
echo print_r($a_id);

...

Right .. OK. Thanks. ;-)

OK well then, I understand that if something on the user table, or the service or airport tables gets updated, the uas table must be updated as well. However, if I delete an airport from the airport...

Thanks for this. I will give this a try. It seems right to me.

However, I am wondering if some of the problem I was having did not have to do with the key constraints. I tried deleting from the...

OK .. seriously .. if you are ever in Toronto, the beer is on me. I am pulling my hair out (what is left of it) because I am so close but have run smack into what seems to be a cement wall.

I can...

OK I have managed to get this to work. I use a:
SELECT * FROM airport and a SELECT * FROM service to set up a parent
<?php do { } while ?> loop and another as a nested loop inside of this parent...

I'm sorry but you have lost me here ;-(

LEFT JOIN users ON userairportservices.usr_id_users = user.usr_id ???

Sorry to be dull here.

Dave

Still looks the same. Is this what you mean?

SELECT userairportservices.usr_id_users, userairportservices.airport_id_airport, airport.airport_code, airport.airport_name,...

Looks to be the same results.

Dave

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

Wondering why this does not work?

SELECT u.usr_id, a.airport_name, a.airport_id, s.service_id, uas.service_id_service as chosen FROM airport a INNER JOIN service s INNER JOIN users u USING...

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

SELECT u.usr_id AS ID, u.usr_fname AS FIRSTNAME, u.usr_lname AS LASTNAME, uas.airport_id_airport AS AIRPORTID, uas.service_id_service AS SERVICEID...

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

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

Hey ... just wanted to let you know that I got this working .. and now understand this quite a bit more, thanks to you. I really do appreciate your help.

Cheers

Dave

Thanks for this. I will not have a chance to get to it until Friday hopefully but will let you hnow how it goes. Can you explain this statement to me?

max(if(service_id_service=1, 1, 0)) as JET...

Of course ... whenever you can. I appreciate it.

Dave

Here you go:

--
-- Database: `fsmgroup3`
--

-- --------------------------------------------------------

--
-- Table structure for table `access`

This may seem like an idiot response but can you clarify what you mean by structure so I don't send you the wrong thing?

D

To be truthful I am not sure. I understand some of that but not enough to know if it would work with my situation. It seems like it would but I am not sure.

Dave

Ahh yes .. I mean in PHP. I guess, as I have an SQL query that gives me what I want, this question would not be appropriate here? I think I was wondering if there was a way to write an SQL query that...

Yeah I want there to be one row per airport and then, inside of that row, I want to list the related services, either as columns or as rows.

I have attached what my current SQL query is giving me....