...

View Full Version : How to interact with two tables with mysql & PHP



Jon W
01-17-2008, 05:47 AM
Hello!

I was wondering if you guys could give me a good idea on how I can connect two tables together in mysql. And also with mysql_query how I would insert in the id number for those two tables. I'm just really kinda confused really on how it works.. But I'll tell you want I do know. Basically I know that in mysql database the tables that you want interact with each has to have one column thats the same. What I don't know is how it really works.


Example: Say that I have two tables in mysql database, and one table is called 'users' and the other is called 'user_post' I know pretty much that say for example in each table I would have a column called something like user_id, ok says that the user_id is a primary_key, so when a person goes to register them self and I use a mysql_query such as the follow: mysql_query("INSERT INTO users (``, `username`, `password`) VALUES('', '$username', '$password')") or die('Datebase error: ' . mysql_error()); that would only bring the value in the user table to 1, seeing that the value was at 0. So my question is how do I connect the two together if when I go: mysql_query("SELECT user_id FROM user_post WHERE id = '" . $_GET['id'] . "'") or die('Database errror: ' . mysql_error()); When the user_post id is still at 0.

I'm sorry if I didn't example this good enough, and if you have any question then just ask me. Thanks for you help.

Jon W

StupidRalph
01-17-2008, 06:43 AM
So my question is how do I connect the two together if when I go: mysql_query("SELECT user_id FROM user_post WHERE id = '" . $_GET['id'] . "'") or die('Database error: ' . mysql_error()); When the user_post id is still at 0.
There is nothing in the post table to select. The user has only registered, they have not actually created any posts. When the person logs in, you would pull their credentials (user_id, username, etc.) and probably store them in a cookie or session depending on your preferences. When that person actually decides to make a post, you would then INSERT that persons user_id into the post table so you will no who that post belongs to.

users table

CREATE TABLE `users` (
`user_id` int(10) unsigned NOT NULL auto_increment COMMENT 'authorization identifier',
`username` varchar(128) collate utf8_unicode_ci NOT NULL COMMENT 'authentication id (plain text)',
`email` varchar(128) collate utf8_unicode_ci NOT NULL COMMENT 'email address',
`password` varchar(128) collate utf8_unicode_ci NOT NULL COMMENT 'authentication credentials (plain text)',
PRIMARY KEY (`user_id`),
UNIQUE KEY `username` (`username`,`email`)
) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='List of all known users and information about them'
posts table


CREATE TABLE `posts` (
`post_id` int(10) unsigned NOT NULL auto_increment,
`modification_time` timestamp NOT NULL default CURRENT_TIMESTAMP COMMENT 'time of last edit',
`creation_time` timestamp NULL default NULL COMMENT 'time when row was first inserted',
`user_id` int(10) unsigned NOT NULL COMMENT 'authorization identifier; foreign key for user table',
`topic_id` int(10) unsigned NOT NULL COMMENT 'uniquely identifies forum topic',
`is_visible` tinyint(3) unsigned NOT NULL default '1' COMMENT 'Is the post visible to anonymous users?',
`subject` varchar(128) collate utf8_unicode_ci NOT NULL COMMENT 'subject of this post',
`content` text collate utf8_unicode_ci NOT NULL COMMENT 'body of the forum post',
PRIMARY KEY (`post_id`),
KEY `user_id` (`user_id`),
KEY `visible` (`is_visible`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='List of all forum posts'


If you wanted to join two related tables together you could do so by joining them on the primary/foreign key pairs.


SELECT * FROM `users`,`posts` WHERE `users.user_id` = `posts.users_id`; /*the join happens in the WHERE statement here */
Or you could perform a JOIN on the two tables. If you go to the MySQL forum here and click on the links in the Moderator's (Guelphdad) signature then they'll help you out with all of your SQL primer needs as well as show you the best practices.

Jon W
01-17-2008, 07:24 AM
Ohh... Now that makes sense. Thanks for your help, I'll take a look at that other post as well.

Jon W



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum