[SOLVED] Unique key and primary key

[jmr3460]

I am organizing a database that will have several tables. I am still a little lost. I am going to create a table for id purposes. I am hoping it will be the table that makes all the other tables relate to each other. I am going to create a table called auth_users with:

 

[`id` INT( 5 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`username` VARCHAR( 50 ) NOT NULL ,
`password` VARCHAR( 25 ) NOT NULL ,
`groupname` VARCHAR( 50 ) NOT NULL]

 

 

If I create a second table called contact with:

[`groupid` INT( 5 ) NOT NULL UNIQUE KEY ,
`address` VARCHAR( 50 ) NOT NULL ,
`city` VARCHAR( 25 ) NOT NULL ,
`state` VARCHAR( 2 ) NOT NULL]

 

My question is do I relate the two tables with the primary key and unique key?

In other words when a group registers they are assigned an id number with the auto_increment feature. When I add information to the contact table do I assign the groupid in the contact table with the same integer that is assigned to the id of the auth_user table?

[p2grace]

If a user can be associated to more than one group, then I'd recommend having three tables.

 

1.) Users table

2.) Groups table

3.) Group Associations table, with the responsibility of storing a user id, and a group id (therefore creating an association to a group).

 

Make sense?

[jmr3460]

So I have created several tables. I have a user table of which I was going to use this as a table to associate the groups. The id would be identifying the groupid in all the other tables. What you are saying is that I should create one more table to do what I want the user table, yes?

[p2grace]

It depends if a user can be associated to more than one group.  If a user will only be connected to one group, then putting the group id in the user table is fine, otherwise you need to create a table that tracks the table's relationships.

[jmr3460]

I am going to start writing the code tonight. I have 11 tables all of them except the auth_user table has a groupid column in it. Each user will have an id from my auth-user table and I want to use that id number to relate the user to their groupid. Am I on the right track? I have one other question, some of the groups meet more that once a day. the groupid in every table is a unique key and the id is the primary key. Some of the tables will have the same groupid number but with different value in the time column. Is that going to be an issue?

[jmr3460]

This is what I have come up with so far. I think my $sql is bad. this is my warning:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/arscnaor/public_html/banner/index.php on line 43

Line 43 is the $result variable assignment.

 

I have two tables:

The first is called banner with two columns groupid and banner(bool).

The second table is called contact with four columns groupid, gname, gaddress and gcity.

Groupid is the relation between the two tables. I am trying to display the contact information of the group where the banner.banner = 1 or true.

 

This is my code:

<?php
ini_set ("display_errors", "1");
error_reporting(E_ALL);
$host = "localhost";
$user = "arscna";
$password = "password";
$db = "groupinfo";
$sql = "SELECT banner.groupid, *
			FROM banner, contact
			WHERE banner.banner = 1";
$connection = mysql_connect($host, $user, $password) or die(mysql_error());
$database = mysql_select_db($db);
if ($database)
{
$data = mysql_query($sql);
$result = mysql_fetch_array($data);
}
?>

I got this $sql after reading a tutorial on this site on JOINS.

[p2grace]

Try this for your query.

 

<?php
$sql = "SELECT `g`.`groupid`,`g`.`gname`,`g`.`gaddress`,`g`.`gcity`
            FROM `banner` `b`, `contact` `g`
            WHERE `b`.`banner` = '1' AND `b`.`groupid` = `g`.`groupid`";
?>

[fenway]

The comma operator is a bad choice... ALWAYS use proper JOIN syntax instead.  And you don't need the backticks:

 

SELECT g.groupid,g.gname,g.gaddress,g.gcity
FROM banner AS b 
INNER JOIN contact AS g USING ( groupid )
WHERE b.banner = '1'

[jmr3460]

Thanks for the help. I had to change the tables around to make it work but I am able to run the query now. I have two tables the banner just has two columns(groupid and banner[bool]). The second one has the contact information (including groupid) in it. I only wanted to show the contact information if the group whos banner = 1 or true. Fenway your query worked after I changed the table names around. I am not going to say that I totally understand exactly how it works 100% now. Maybe 50%. Thanks I will have to study more on INNER JOIN. Thanks again.