Jump to content

Archived

This topic is now archived and is closed to further replies.

multidata

Is it a stored procedure?

Recommended Posts

I'm new to php and have studied sql, so I thought I'd try a bit of php/mysql.

I've manage to connect to my database, been able to view rows and display on a table with alternating colored rows, however I didn't write the script myself, and as a bad way to learn programming, I'm searching for the 'ideal' script to use, and not doing a very good job of manipulating the scripts, so I've joined this forum to ask a desperately seeking solution to my problem

The way I've organised my db tables is that for each table, there is a column which has a auto incremented key. So, when I add a new row to that table it adds on a new number to the table. My tables are related in such a way that that new number is then inputted into another table to identify itself and more data goes into that table.

For example I've three tables. One table has a 'persons' details, with primary key of the auto incremented number. One table which is related to 'persons' table, containing their 'telephone' number. The primary key is also another auto incremented number. The third table is related to the 'persons' table and holds the 'email' address, which is identified by another auto incremented number.

Therefore when I enter the following details I submit the following details on a form;
persons name.
persons telephone.
persons email.

So far so good.

Lets say this is the 50th person registered. I submit the form and the 'persons' table now has the following data;
person_id = 50.
person_name = name.
person_lastname = lastname.

However, since some people have more than one telephone, this is the 80th telephone in the database and the following data is entered into the database;
telephone_id = 80
person_id = 50.
telephone_number = 123-456-7890.
telephone_type = home.

The same problem exists with email, and not everyone has an email address, so this entry is the 45th email address and the following data is inputted;
email_id =45.
person_id = 50.
email_address = someone@emailaddress.com

I can either enter all the data seperately, taking up much time with new entries into each table seperately in phpmyadmin, or I can devise a script which puts the persons details in the 'person' table, and extract that same auto-incremented number and then put that number into the 'person_id' field in the 'telephone' and 'email' tables. It is this process that I am having much difficulty with.

As I struggle to write objects I was wondering if someone knew of a script I could use that would extract the newly created 'person_id' from 'person' and use that value to go into 'person_id' into the other two tables.

Many thanks

Share this post


Link to post
Share on other sites
Yes there is. It's the LAST_INSERT_ID MySQL statement.

[a href=\"http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/get...-unique-id.html[/a]

Share this post


Link to post
Share on other sites
[!--quoteo(post=377231:date=May 26 2006, 06:28 AM:name=poirot)--][div class=\'quotetop\']QUOTE(poirot @ May 26 2006, 06:28 AM) [snapback]377231[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Yes there is. It's the LAST_INSERT_ID MySQL statement.

[a href=\"http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html\" target=\"_blank\"]http://dev.mysql.com/doc/refman/5.0/en/get...-unique-id.html[/a]
[/quote]

thanks, I'll look at it and see if there is any success with it.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.