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

[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.