PDA

View Full Version : Help with RDBMS and PHP.



shachi
04-27-2007, 02:10 PM
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
04-27-2007, 02:13 PM
author_id = primary key in authors category
author_id = foreign key in jokes category

shachi
04-27-2007, 02:25 PM
boogyman: Thanks for the reply but how do I do that?

boogyman
04-27-2007, 03:07 PM
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
04-27-2007, 03:20 PM
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
04-27-2007, 03:36 PM
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
04-27-2007, 03:39 PM
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
04-27-2007, 03:49 PM
By the way, I am getting lots of errors and the sql doesn't seem to work.

boogyman
04-27-2007, 04:37 PM
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
04-27-2007, 05:51 PM
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
04-27-2007, 06:25 PM
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
04-27-2007, 06:30 PM
Oh yea, sorry that was a typo, they are in different tables and not in different databases;