and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
Hello for all..
I'm doing my first database to something important like an company
I had been trying to learn some sql, and i think i'm going right but now i'm with some problems(newbie problems).
I Have 2 tables..
1 - Autor
2 - Book
i know that i will have to have another table because is * to *.
But i done this code.
[syntax="sql"]CREATE TABLE Autor(
ID_Autor INTEGER(5) NOT NULL AUTO_INCREMENT,
NAME VARCHAR(30),
PRIMARY KEY(ID_Autor));
CREATE TABLE Book(
ID_Book INTEGER(5) NOT NUL AUTO_INCREMENT,
NAME VARCHAR(30),
PRIMARY KEY(ID_Book));
CREATE TABLE Autoria(
ID_Autoria INTEGER(5) NOT NULL AUTO_INCREMENT;
ID_Autor INTEGER(5) NOT NULL,
ID_Book INTEGER(5) NOT NULL,
PRIMARY KEY(ID_Autoria));
ALTER TABLE Autoria ADD constraint Fk_Autoria_Autor FOREIGN KEY(ID_Autor) REFERENCES Autor(ID_Autor);
ALTER TABLE Autoria ADD constraint FK_Autoria_Book FOREIGN KEY(ID_Book) REFERENCES Book(ID_Book);
and [syntax="..."] tags where appropriate when posting code. Your post has been edited to reflect how we'd like it posted. Please read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url] to learn how to do it too.[/color]
$query= "INSERT INTO Autor(NAME) VALUES('$autor')";
$do_query = mysql_query($query);
$query2= "INSERT INTO Book(NAME) VALUES('$tittle')";
$do_query2= mysql_query($query2);
It registed the values on the table author and book but still empty on autoria
What would you expect to be in autoria? You have to insert it first... Thus after the first two inserts, you have to tell the two items are related to each other:
CREATE PROCEDURE insert_bookauthor
(
P_AUTHOR IN AUTHOR.TITLE%Type,
P_BOOK IN BOOK.TITLE%Type
)
AS
V_AUTHOR_ID INT := 0;
V_BOOK_ID INT := 0;
BEGIN
-- generate ids for the author and book
SELECT SEQ_AUTHOR.NEXTVAL, SEQ_BOOK.NEXTVAL INTO V_AUTHOR_ID, V_BOOK_ID FROM DUAL;
INSERT INTO author (id, title) VALUES (V_AUTHOR_ID, P_AUTHOR);
INSERT INTO book (id, title) VALUES (V_BOOK_ID, P_BOOK);
INSERT INTO bookauthor (author_id, book_id) VALUES (V_AUTHOR_ID, V_BOOK_ID);
END;
And now you can use this stored procedure... CALL insert_bookauthor('author', 'book');";