Jump to content


Photo

Is it a stored procedure?


  • Please log in to reply
2 replies to this topic

#1 multidata

multidata
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 26 May 2006 - 01:15 AM

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

#2 poirot

poirot
  • Members
  • PipPipPip
  • Advanced Member
  • 646 posts
  • LocationAustin, TX

Posted 26 May 2006 - 05:28 AM

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]
~ D Kuang

#3 multidata

multidata
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 26 May 2006 - 03:21 PM

[!--quoteo(post=377231:date=May 26 2006, 06:28 AM:name=poirot)--][div class=\'quotetop\']QUOTE(poirot @ May 26 2006, 06:28 AM) View Post[/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.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users