Join Help....

crawfd

I have the following table structure:

Basically, each user has the ability to choose 3 separate color choices for their templates, and also can post entries and comments. How the heck do I join these fields and relate it to one id? and which id do I reference for the join? the users incremental id?....thanks..........


CREATE TABLE users (
  id int(10) NOT NULL auto_increment,
  username varchar(40) default NULL,
  password varchar(50) default NULL,
  regdate date default NULL,
  email varchar(100) default NULL,
  website varchar(150) default NULL,
  location varchar(150) default NULL,
  last_login date default NULL,
  age char(2) default NULL,
  gender varchar(15) default NULL,
  bio longblob,
  yahooim varchar(70) default NULL,
  msnim varchar(70) default NULL,
  Icqnum varchar(20) default NULL,
  aolim varchar(70) default NULL,
  status varchar(7) default NULL,
  photo char(1) default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY username (username)
) TYPE=MyISAM;

CREATE TABLE entries (
  entries_id int(10) default NULL,
  colors_id int(10) default NULL,
  date date(40) default NULL,
  username varchar(50) default NULL,
  title varchar(80) default NULL,
  dreamstatus varchar(6) default NULL,
  entry longblob,
  awareness varchar(20) default NULL,
  PRIMARY KEY  (entries_id),
  UNIQUE KEY id (colors_id),
) TYPE=MyISAM;

CREATE TABLE colors (
  colors_id (20) int(10),
  bgclr varchar(70),
  fontclr varchar(70),
  headerclr varchar(70),
  PRIMARY KEY  (colors_id),
  UNIQUE KEY id (colors_id),
  KEY username (colors_id)
) TYPE=MyISAM;

CREATE TABLE comments (
  comments_id int(10) default NULL,
  entries_id int(10) default NULL,
  name varchar(40) default NULL,
  date date(25) default NULL,
  email varchar(100) default NULL,
  message longblob,
  total varchar(15) default NULL,
  PRIMARY KEY  (entries_id),
  UNIQUE KEY id (entries_id)
) TYPE=MyISAM;

kmunk

Last thing first.

In your structure you make the default value of username null, that means you can't use that in your join nor make it a key.
To use it for the join it needs to be unique in your users table, that means you can use the id.

each table then needs this id. So i'd recomend rename it user_id.
Then add it to the each table.

then you can do following statement:

SELECT * FROM users as U, entries as E, colors as C1, comments as C2 WHERE U.user_id=E.user_id AND U.user_id=C1.user_id AND U.user_id=C2.user_id

to join all the tables.

beside I'd recomend to split up some of the tables. Eg. making a table with online-no:

online_no
user_id
service
address

eg.

user_id = 10
service = ICQ
address = 86093748

then you will not take up space not needed. to be square you can move all those extras into that table. also e-mail and www etc. just a thought.

crawfd

I really still don't understand the logistics of how joins work nor do I know if I ever will. So if my users table I should leave the id filed set on incremental? Also when another users profile is added to my db, how does this find the correct matching rows in other tables with multiple users?

For I.e.

Let's say I have user A that createsin a profile, later on enters a few entries and comments...

Then later a User B creates a profile, and later on enters entries and comments..

How are the entries, comments, and colors related to a specific user when searching through a join? I understand how the physical portion of a join works, but not with multiple users I guess..........

kmunk

First off - yes your user_id should be autoincrement.
Secondly since this is a unique value you can use it as a Foreign Key in other tables, ex.:

users
user_id
fname
lname
address
zip

city
zip
city

comments
id
user_id
title
comment

If you make following select you will join these three tables. I'd then show you the result afterwards:

$query = "SELECT user_id, fname, lname, address, zip, city, title,
                 comment FROM users, city, comment WHERE 
                 users.zip = city.zip AND 
                 users.user_id=comments.user_id ORDER BY user_id";
$result = mysql_query($query);

while($resarr = mysql_fetch_array($result){
    echo $resarr[0]." - ".$resarr[1]." - ".$resarr[2]." - ".
    $resarr[3]." - ".$resarr[4]." - ".$resarr[5]." - ".$resarr[6].
    " - ".$resarr[7];
}

Given following data the result would be:

user(1, "john", "Doe", "some street", 12345)
user(2, "jane", "doe", "some other street", 54321)

city(12345, "some big city")
city(54321, "some small city")

comment (1, 1, "first comment", "by John Doe")
comment(2, 2, "first comment", "by jane doe")
comment(3, 1, "another comment", "by john doe")

the result:

1 - john - doe - some street - 12345 - some big city - first comment - by john doe
1 - john - doe - some street - 12345 - some big city - another comment - by john doe
2 - jane - doe - some other street - 54321 - some small city - first comment - by jane doe

Hope this clarifies it a bit.

crawfd

How does this id structure look?

I don't see why I have to add another id field when I can just equal everything to the id field in users table?. All I have to do is equal the id in the users table with the other id's in other tables?

CREATE TABLE users (
  id int(10) NOT NULL auto_increment,
  username varchar(40) Not NULL,
  password varchar(50) default NULL,
  regdate date default NULL,
  email varchar(100) default NULL,
  website varchar(150) default NULL,
  location varchar(150) default NULL,
  last_login date default NULL,
  age char(2) default NULL,
  gender varchar(15) default NULL,
  bio longblob,
  status varchar(7) default NULL,
  photo char(1) default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id),
  KEY username (username)
) TYPE=MyISAM;

CREATE TABLE services (
  services_id int(10) default NULL,
  yahooim varchar(70) default NULL,
  msnim varchar(70) default NULL,
  Icqnum varchar(20) default NULL,
  aolim varchar(70) default NULL,
  PRIMARY KEY  (messaging_id),
  UNIQUE KEY id (messaging_id),
) TYPE=MyISAM;

CREATE TABLE entries (
  entries_id int(10) default NULL,
  colors_id int(10) default NULL,
  date date(40) default NULL,
  username varchar(50) default NULL,
  title varchar(80) default NULL,
  dreamstatus varchar(6) default NULL,
  entry longblob,
  awareness varchar(20) default NULL,
  PRIMARY KEY  (entries_id),
  UNIQUE KEY id (colors_id),
) TYPE=MyISAM;

CREATE TABLE colors (
  colors_id (20) int(10),
  bgclr varchar(70),
  fontclr varchar(70),
  headerclr varchar(70),
  PRIMARY KEY  (colors_id),
  UNIQUE KEY id (colors_id),
  KEY username (colors_id)
) TYPE=MyISAM;

CREATE TABLE comments (
  comments_id int(10) default NULL,
  entries_id int(10) default NULL,
  name varchar(40) default NULL,
  date date(25) default NULL,
  email varchar(100) default NULL,
  message longblob,
  total varchar(15) default NULL,
  PRIMARY KEY  (entries_id),
  UNIQUE KEY id (entries_id)
) TYPE=MyISAM;

ultraslacker

I'd recommend using user_id as your foreign key instead of username.

Add a unique constraint to username.

crawfd

How would I add a unique contraint to username

ultraslacker

CREATE UNIQUE INDEX blah ON users(username)

DROP INDEX username ON users

crawfd

Is this correct?, in regards to CREATE UNIQUE INDEX blah ON users(username) and DROP INDEX username ON users.

CREATE TABLE users (
id int(10) NOT NULL auto_increment,
username varchar(40) Not NULL,
password varchar(50) default NULL,
regdate date default NULL,
email varchar(100) default NULL,
website varchar(150) default NULL,
location varchar(150) default NULL,
last_login date default NULL,
age char(2) default NULL,
gender varchar(15) default NULL,
bio longblob,
status varchar(7) default NULL,
photo char(1) default NULL,
PRIMARY KEY (id),
UNIQUE KEY username (username),
) TYPE=MyISAM;