Pulling related data from relational dB re: thread: Problems with a many-to-many inse - Page 3

[filch]

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

[dickersonka]

well it doesn't make sense as far as the user won't have any services, from the database side it just means there is a user, just no services saved yet

yes users -> uas 1..x

it means a user has been created, but no services have been created

[filch]

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 delete the uas entries for that user as well.

[filch]

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?

[dickersonka]

constraints will do what you are meaning, delete from the user table then it will propagate to other tables that reference that user

lol did i put delete *, just use delete from without the *

[filch]

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 = $usid

else insert

sound about right?

[dickersonka]

no need to do a check, just run the delete, it won't matter if there is a match or isn't

then just run inserts

[filch]

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 is about right.

[dickersonka]

actually no need to check the uas table at all, just delete, then insert

you are correct, check user table i guess from a login or whatever that is, then no need to check anything against uas table except when displaying the form, on submit delete and insert

also i normally wouldn't suggest this, but i think it is appropriate here, we don't need a uas_id on uas table, it is always being deleted and never referenced, you can keep it, no big deal, its just not of use i see yet, but maybe keep it if you think you will need it later

[filch]

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 have a night full of beer if you ever get here to Toronto. If you have a book wish list just let me know. You have been a huge help, a patient man and an all around great guy.

Seriously ... thank you very very much for your kind help.

Dave

[dickersonka]

sure thing man, finally got up to veteran poster now along the way :-)

glad it all works for you, and i'm sure you understand relational databases much better

TORONTO here i come!!!

[filch]

Yeah I have learned a lot thanks to you.

Any time man!