Insert and Joins

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
smithjasonuk
Forum Newbie
Posts: 13
Joined: Tue Mar 23, 2004 6:57 am
Location: Glasgow, Scotland

Insert and Joins

Post by smithjasonuk »

Yes I am new to this and it might quite possibly be quoted somewhere else but lets spin the wheel bob...!

I am looking for some kind of answer to inserting data into two tables.

Say I have two tables and one is called customers:

customer_id
name
surname
address

then i have an invoice table:

invoice_id
invoice_description
amount
customer_id

Now how do I, when inserting a new record into the invoice table, take the customer id from the customer table so that I can match and display the invoice table and the customers table based on these two being joined

I must sound like and idiot, but still...
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post by CoderGoblin »

You should have the customer_id already when you create the invoice so you can just feed the value in as part of your insert statement. For instance in a PHP variable $customer_id.

INSERT INTO invoice (invoice_description,amount,customer_id) VALUES ('description','10.00',$customer_id);

This statement assumes the default invoice_id is set to an automatic sequence.

A lot will also depend on your table definitions such as the use of PRIMARY KEYS and REFERENCES declarations. If you do not know these I would recommend you read up on them, as well as sequences (POSTGRES) I don't know what other DB's use.
smithjasonuk
Forum Newbie
Posts: 13
Joined: Tue Mar 23, 2004 6:57 am
Location: Glasgow, Scotland

Post by smithjasonuk »

Ah so what you are sayin is call the customer_id from the customer table and store it into a var then take the var and store it into the invoice table

Say in the instance of this forum, I start a new topic which will have a unique id then when i post a new message to the topic it takes the topic id from the topic table and stores it into the post message id so hence how to keep the posts grouped correctly to the topic

Please tell me if im talking <span style='color:blue' title='I&#39;m naughty, are you naughty?'>smurf</span> or not oh great ones
Post Reply