Help with RDBMS and PHP.

[shachi]

Hello all!

I am building an application and I've got some problems with MYSQL(I am newbie in it). Basically I am trying to build an application whose basic database structure looks like this:

Table jokes:
field id
field name
field email
field author_id

Table authors
field author_id
field author_name

Now what I want is to be the author_id field relational to both tables. How can I do it without the php script to write the id on both tables? How do I set up my database?

I hope I was able to clear myself.

Thanks!

[boogyman]

author_id = primary key in authors category
author_id = foreign key in jokes category

[shachi]

boogyman: Thanks for the reply but how do I do that?

[boogyman]

DROP TABLE IF EXISTS Author {
author_id INT NOT NULL AUTO_INCREMENT;
author_name VARCHAR(50);
PRIMARY KEY(author_id);
}
DROP TABLE IF EXISTS Joke {
joke_id INT NOT NULL AUTO_INCREMENT;
joke_title VARCHAR(20);
joke_message VARCHAR(1024);
PRIMARY KEY(joke_id);
FOREIGN KEY(author_id);
INDEX(joke_title);
}


that will create both of your tables and make the primary keys and it will index the joke_title, which if you choose to set up the site with a "search" bar will allow the user to search for a title.
ceiling of 20 characters - title 1024 characters - message


INSERT INTO table name VALUES(values);

NOTE: seperate values by commas( , )

that is all good and dandy if you want to insert a whole row into the database, but if you want to only insert a specific field in a record use


INSERT INTO table name(col1,col2) VALUES(value1, value2);


also, I suggest you take a look at the Official MySQL Manual (http://dev.mysql.com/doc/refman/5.0/en/)

[shachi]

But ... wait a minute, don't I need a author_id column in table Joke too?

I've seen the manual but couldn't get anything out of it. :(

[boogyman]

oops sorry I made a couple errors, should be



DROP TABLE IF EXISTS Author {
author_id INTEGER NOT NULL AUTO_INCREMENT;
author_name VARCHAR(50);
PRIMARY KEY(author_id);
}
DROP TABLE IF EXISTS Joke {
joke_id INTEGER NOT NULL AUTO_INCREMENT;
joke_title VARCHAR(20);
joke_message VARCHAR(1024);
author_id INTEGER NOT NULL;
PRIMARY KEY(joke_id);
FOREIGN KEY(author_id) REFERENCES Author(author_id);
INDEX(joke_title);
}


sry

[shachi]

nevermind, it's not your fault. :)

So, now if I insert the id into the Author table, will it automagically add that tho the Joke table?

[shachi]

By the way, I am getting lots of errors and the sql doesn't seem to work.

[boogyman]

So, now if I insert the id into the Author table, will it automagically add that tho the Joke table?
the author_id field will automatically increment itself, so you should be able to just leave that blank, and I am not entirely certain how it manages itself, but yes your author_id will be updated as well.




By the way, I am getting lots of errors and the sql doesn't seem to work.

Yah I made more errors :/ change the curly braces { } on the tables to parenthesis ( )



[code]
DROP TABLE IF EXISTS Author (
author_id INTEGER NOT NULL UNIQUE AUTO_INCREMENT;
author_name VARCHAR(50);
PRIMARY KEY(author_id);
) ENGINE=InnoDB;
[B][COLOR="Green"])
DROP TABLE IF EXISTS Joke (
joke_id INTEGER NOT NULL UNIQUE AUTO_INCREMENT;
joke_title VARCHAR(20);
joke_message VARCHAR(1024);
author_id INTEGER NOT NULL;
PRIMARY KEY(joke_id);
FOREIGN KEY(author_id) REFERENCES Author(author_id);
INDEX(joke_title);
) ENGINE=InnoDB;


and also notice that I added the engine type. I didnt know this either, but I guess that is the only engine that supports foreign keys

Let me know if you got any other problems

[shachi]

Yep, I fixed it(I made the dbs manually).

There's one thing I am confused about now, which database do I put the data in? The Author or the Joke?

Thanks again.

[boogyman]

Yep, I fixed it(I made the dbs manually).

There's one thing I am confused about now, which database do I put the data in? The Author or the Joke?

Thanks again.

WHOAAAAAAAAAAAAA HOLD ON A SECOND
they should be in the same database, but they should just be different tables

[shachi]

Oh yea, sorry that was a typo, they are in different tables and not in different databases;