Jump to content

[SOLVED] Unique key and primary key


jmr3460

Recommended Posts

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?

Link to comment
https://forums.phpfreaks.com/topic/164999-solved-unique-key-and-primary-key/
Share on other sites

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?

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?

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?

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.

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.

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.