11 Replies - 7499 Views - Last Post: 24 November 2008 - 12:17 PM Rate Topic: -----

#1 chaminda7245  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 17
  • Joined: 09-October 08

insert data into many to many relational table

Post icon  Posted 15 October 2008 - 11:02 PM

Hi,

I have 3 tables.

01)users(user_id,user_name,user_password)
02)doctors(doc_id,doc_name)
03)users_to_doctors(user_id,doc_id)

I need users to select doctors by multiple list/menu and insert into users_to_doctors table when he fill the registration form.Also need insert data to the users table.How i can do it?

Thanks & Regards,

Chaminda
Is This A Good Question/Topic? 0
  • +

Replies To: insert data into many to many relational table

#2 chaminda7245  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 17
  • Joined: 09-October 08

Re: insert data into many to many relational table

Posted 15 October 2008 - 11:06 PM

Hi,

I have 3 tables.

01)users(user_id,user_name,user_password)
02)doctors(doc_id,doc_name)
03)users_to_doctors(user_id,doc_id)

I need users to select doctors by multiple list/menu and insert into users_to_doctors table when he fill the registration form.Also need insert data to the users table.How i can do it?

Thanks & Regards,

Chaminda
Was This Post Helpful? 1

#3 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4332
  • View blog
  • Posts: 12,126
  • Joined: 18-April 07

Re: insert data into many to many relational table

Posted 15 October 2008 - 11:32 PM

When you select multiple items from a list those selected items are submitted (when you press the submit button) as an array that PHP can get at. It is just a matter of looping through the choices and creating an insert statement into the table users_to_doctors using the id of the user that submitted the form.

Simple. Just make sure the name you give the select list has square brackets on it so that the form submits it as an array.

May I suggest you take a look around the net with the keywords "php" and "multiple select list"

You will get a search result like this...

Using multiple select boxes in PHP

Wasn't too hard now was it? ;)
Was This Post Helpful? 0
  • +
  • -

#4 chaminda7245  Icon User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 17
  • Joined: 09-October 08

Re: insert data into many to many relational table

Posted 15 October 2008 - 11:45 PM

Thanks for the information Martyr2.I will search.
Was This Post Helpful? 0
  • +
  • -

#5 Guest_localhot*


Reputation:

Re: insert data into many to many relational table

Posted 24 November 2008 - 08:27 AM

A burning issue for a novice )
I have 3 MySQL tables just of the kind chaminda7245 has:

01)users(user_id,user_name,user_password)
02)doctors(doc_id,doc_name)
03)users_to_doctors(user_id,doc_id)

Filling out an HTML form the user enters his info (to be stored in table 1) and chooses a doctor (choice to be stored in table 3).
As I see it, I can't insert anything in table 3 until (1) I've inserted new user's info in table 1, (2) MySQL generated user_id, (3) I querried MySQL and got the user_id that's just been generated/stored.
And only then, armed with the user_id, I insert it in table 3 along with doc_id.
Is this approach correct or am I complicating things and there is a special procedure that handles situations like this?
Thanks!
Was This Post Helpful? 0

#6 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2910
  • View blog
  • Posts: 10,075
  • Joined: 08-August 08

Re: insert data into many to many relational table

Posted 24 November 2008 - 08:47 AM

At the point where you have user_id, if you also know the doc_id for that user, you could insert that in table 3. There is no need to wait for the next post submit to read it from the table 2 unless you don't know the correct doc_id.

Even if you do need to wait for a submit to get the doc_id, you don't need to read from table 2 if you use SESSION variables.

This post has been edited by CTphpnwb: 24 November 2008 - 08:48 AM

Was This Post Helpful? 0
  • +
  • -

#7 Guest_localhot*


Reputation:

Re: insert data into many to many relational table

Posted 24 November 2008 - 09:28 AM

Well, doc_id isn't a problem. The idea is I get it from the form or set as a default value.

As I understand, my 3-step approach to storing data in many-to-many ralationship tables is essentially correct and there are no Magic Handlers that get data from a single PHP-embedded INSERT query, sort everything out, put everything on appropriate 'shelves' in different tables, and make sure that the mapping table (table 3 -- users_to_doctors in our example) gets the same user_id as table 1 (users)?
Was This Post Helpful? 0

#8 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2910
  • View blog
  • Posts: 10,075
  • Joined: 08-August 08

Re: insert data into many to many relational table

Posted 24 November 2008 - 09:42 AM

I don't understand. You have 3 tables containing data:

Quote

01)Users(User_id,User_name,User_password)
02)Doctors(Doc_id,Doc_name)
03)Users_to_doctors(User_id,Doc_id)

Anytime you have the correct data, you can store it in the appropriate table with a simple query. If you're working on the Users table, you must have User_id, and the only other thing necessary for the Users_to_doctors table is Doc_id. If you have that, you can update that table too, but if you don't, you can't. It all depends on what you've got in your code. Since we've seen no code, that's about all we can say.
Was This Post Helpful? 0
  • +
  • -

#9 Guest_localhot*


Reputation:

Re: insert data into many to many relational table

Posted 24 November 2008 - 10:41 AM

Here's the tricky thing. I do work with Users table but at the beginning I have no idea what the user_id is.

It's supposed to work like this (unless someone has a better solution) :
1. An HTML registration form gets user info and doc_id
2. Data from (1) is passed to a PHP file.
3. PHP deals with the data and prepares an MySQL query based on it.

At this point it'd be nice of PHP to send a single INSERT query and store the data in the MySQL-- end of story. Not so.
Right now we don't know the user_id because it doesn't exist yet. To store a record for the new user (in table 1) we send user_id as NULL, and MySQL generates user_id at the moment the user record is created.
MySQL knows user_id for this new user, I don't. But I need user_id so I could insert it in table 3.
The logic of a novice dictates that :

4. PHP script sends a query to MySQL to find out what this user's user_id is
5. Now that the PHP script has the user_id, it sends an INSERT query to store user_id and doc_id in table 3.

Seems too complicated to me though I expect it will work. But is there a better solution?
Was This Post Helpful? 0

#10 CTphpnwb  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 2910
  • View blog
  • Posts: 10,075
  • Joined: 08-August 08

Re: insert data into many to many relational table

Posted 24 November 2008 - 11:44 AM

View Postlocalhot, on 24 Nov, 2008 - 09:41 AM, said:

4. PHP script sends a query to MySQL to find out what this user's user_id is
5. Now that the PHP script has the user_id, it sends an INSERT query to store user_id and doc_id in table 3.

There is no reason you can't do steps 4 and 5 before the next submit.

This post has been edited by CTphpnwb: 24 November 2008 - 11:45 AM

Was This Post Helpful? 0
  • +
  • -

#11 Guest_localhot*


Reputation:

Re: insert data into many to many relational table

Posted 24 November 2008 - 12:17 PM

Sorry, I don't understand what 'submit' you mean by:

View PostCTphpnwb, on 24 Nov, 2008 - 10:44 AM, said:

There is no reason you can't do steps 4 and 5 before the next submit.

And sorry, I messed up the numbers in my list. I implied in the paragraph between (3) and (4) that:

3.1. PHP sends the query to MySQL and MySQL stores user info in table 1, thus creating user_id for this new record. (This record will be queried in (4) to get user_id).

This post has been edited by localhot: 24 November 2008 - 12:18 PM

Was This Post Helpful? 0

#12 AdaHacker  Icon User is offline

  • Resident Curmudgeon

Reputation: 452
  • View blog
  • Posts: 811
  • Joined: 17-June 08

Re: insert data into many to many relational table

Posted 24 November 2008 - 12:17 PM

View Postlocalhot, on 24 Nov, 2008 - 11:41 AM, said:

At this point it'd be nice of PHP to send a single INSERT query and store the data in the MySQL-- end of story.

Yeah, that would be nice. But SQL doesn't work that way. You can only INSERT into one table at a time. Period. If you really want to send just one request from your script to the database, you could always write a stored procedure that encapsulates the multiple inserts. And if you don't want to do that, you'll just have to do each of the inserts from code as you described. It might be a little tedious, but it's really not that complicated.
Was This Post Helpful? 1
  • +
  • -

Page 1 of 1