Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    Regular Coder
    Join Date
    Jan 2008
    Posts
    334
    Thanks
    9
    Thanked 0 Times in 0 Posts

    How to interact with two tables with mysql & PHP

    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

  • #2
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts
    Quote Originally Posted by Jon W
    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
    Code:
    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
    Code:
    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.

    Code:
    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.
    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • Users who have thanked StupidRalph for this post:

    Jon W (01-17-2008)

  • #3
    Regular Coder
    Join Date
    Jan 2008
    Posts
    334
    Thanks
    9
    Thanked 0 Times in 0 Posts
    Ohh... Now that makes sense. Thanks for your help, I'll take a look at that other post as well.

    Jon W


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •