Page 1 of 1

Newbie Doubt

Posted: Sat Oct 28, 2006 10:04 pm
by Gurzi
feyd | Please use

Code: Select all

,

Code: Select all

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);
But it doesnt works... why ?

He didnt regist nothing on the table Autoria

thnks


feyd | Please use[/syntax]

Code: Select all

,

Code: Select all

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]

Posted: Sat Oct 28, 2006 10:07 pm
by feyd
Do the tables you've created support foreign keys?

Posted: Sat Oct 28, 2006 10:09 pm
by Gurzi
how i can know that ?

i'm using mysql with the help of phpmyadmin in a cpanel.

i think that i can use foreign keys, i payed for that :P

Posted: Sat Oct 28, 2006 10:14 pm
by feyd
If the storage engine used for the table is InnoDB, it supports foreign keys. If not, it does not.

Posted: Sat Oct 28, 2006 10:19 pm
by Gurzi
hmmm... i'm really reading one site about it, i didn't know about it...

http://uk.builder.com/architecture/db/0 ... 897,00.htm

So when i create one table i have to put Engine=INNODB;

if i do what i said and i put the rest same code it will work ?

or you found another bugs ?

thanks for your pacient!;)

I'm in Portugal , everyone is sleeping here(3:20 am) :P

greetings :wink:

Posted: Sat Oct 28, 2006 10:34 pm
by John Cartwright
Yes you have to specify the table type.. and no it's not Engine=INNODB, its TYPE=INNODB;

Posted: Sat Oct 28, 2006 10:41 pm
by Gurzi
weird, in the site i said, it says to use ENGINE, what is the difference ?

after i insert some values on the table books and autor the table autoria still empty :(

8)

Posted: Sat Oct 28, 2006 10:43 pm
by John Cartwright
My bad, I was having a brain fart.. it is ENGINE

Posted: Sat Oct 28, 2006 10:49 pm
by Gurzi
ehehe that happens to all ;)

So i'm posting here all i'm doing ..


Creating the tables. ..

Code: Select all

CREATE TABLE Book( IDLivro INT(5) NOT NULL AUTO_INCREMENT,  NAME VARCHAR(50) NOT NULL,
PRIMARY KEY(IDLivro)) ENGINE=INNODB;

CREATE TABLE Autor( IDAutor INT(5) NOT NULL AUTO_INCREMENT, NAME VARCHAR(50) NOT NULL,
PRIMARY KEY(IDAutor)) ENGINE=INNODB;

CREATE TABLE Autoria(
IDAutoria INT(5) NOT NULL,
FK_Autor INT(5) NOT NULL,
FK_Livro INT(5) NOT NULL,
INDEX(FK_Autor),
INDEX(FK_Livro),
FOREIGN KEY(FK_Autor) REFERENCES Autor(IDAutor),
FOREIGN KEY(FK_Livro) REFERENCES Book(IDLivro),
PRIMARY KEY(IDAutoria)) ENGINE=INNODB;
On my php code i did

Code: Select all


    $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 :(

Posted: Sun Oct 29, 2006 3:16 am
by timvw
Gurzi wrote: On my php code i did

Code: Select all


    $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:

Code: Select all

mysql_query("insert into author (name) values ('author')");
$author_id = mysql_last_insert_id();
mysql_query("insert into book (name) values ('title')");
$book_id = mysql_last_insert_id();
insert into autoria (fk_author, fk_livro) values ($author_id, $book_id);
(notice that you might want to make idautoria auto_increment too... otherwise you'll have to come up with your own values....

Posted: Sun Oct 29, 2006 4:23 am
by Gurzi
That is the point.. i thought that mysql is like acess , it saves the 2 ids automatically!

:roll:

So , i read that sometimes people use stored procedure to save the ids and after it put the ids on the three tables, how i do that ?

I will continue searching !

Thnks a lot :D

Posted: Sun Oct 29, 2006 4:48 am
by timvw
I'm not familiar with MySQL stored procedures... But you can look it up at: http://dev.mysql.com/doc/refman/5.0/en/ ... edure.html

Here's how it would look like with PL/SQL:

Code: Select all

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');";

Posted: Sun Oct 29, 2006 5:31 am
by Gurzi
Thnks, i'm searching for tutorials explaining SQL Procedures to i understand the syntax you used.

With no procedures but using the mysql_insert_id() ; mysql return this error :

Cannot add or update a child row: a foreign key constraint fails :roll:

Code: Select all

if($_POST){

    $autor = $_POST['autor'];
    $apelido =$_POST['apelido'];
    $titulo = $_POST['titulo'];
    $editora = $_POST['editora'];
    $edicao = $_POST['edicao'];
    $ano =$_POST['ano'];
    $paginas = $_POST['paginas'];
    $descricao = $_POST['descricao'];
    
    include("settings.php");
    $connect = mysql_connect($server,$user,$pass) or die("Falhou a Ligacao");
    $select_db = mysql_select_db($db);
    $query= "INSERT INTO Autor(Nome,Apelido) VALUES('$autor','$apelido')";
    $do_query = mysql_query($query);
    $autor_id = mysql_insert_id();
    $query2= "INSERT INTO Livro(Titulo,Editora,Edicao,Ano,Paginas,Descricao) VALUES('$titulo','$editora','$edicao','$ano','$paginas','$descricao')";
    $do_query2= mysql_query($query2);
    $book_id = mysql_insert_id();
    $j= mysql_query("INSERT INTO Autoria(Autoria_Livro, Autoria_Autor) VALUES('$autor_id','$book_id')");
    if($j) echo "sim"; else echo "nao";
    if($do_query2) echo "true"; else echo"false";
    }
    echo mysql_error();


Posted: Sun Oct 29, 2006 6:05 am
by Gurzi
i found the bug, it seems that mysql_insert_id() isnt returning what i want..

i tried to do INSERT VALUES('8','9') and works!

Thnks all :D

Posted: Sun Oct 29, 2006 2:08 pm
by timvw
I just had a quick look at the mysql manual, here is my first (untested) attempt:

Code: Select all

CREATE PROCEDURE insert_bookauthor
(
 IN p_author VARCHAR(20),
 IN p_book VARCHAR(20)
)

BEGIN

DECLARE v_author_id INT;
DECLARE v_book_id INT;

INSERT INTO authors (author) VALUES (p_author);
SELECT LAST_INSERT_ID() INTO v_author_id;
INSERT INTO books (book) VALUES (p_book);
SELECT LAST_INSERT_ID() INTO v_book_id;
INSERT INTO bookauthors (author_id, book_id) VALUES (v_author_id, v_book_id); 

END;
(As you may notice i prefer to prepend p_ to parameters and v_ to variables...)