Select multiple fields and increment an id

crawfd

I want to successfully create one id field that collocates different fields from each table and then traverses records incrementally. I have a feeling I'll need to create another table that's based soley on id fields? This is very confusing to me........

I need to grab:

From comments table:

commends.id, comments.message, comments.total, comments.date, comments.name

From entries table:

entries.id, entries.date, entries.username, entries.title, entries.dreamstatus, entries.entry, entries.awareness

From users table:

users.id, users.bgclr, users.headerclr, users.fontclr

Perhaps someone could help me form a select statement/query, thanks.


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


CREATE TABLE entries (
  id int(10) NOT NULL auto_increment,
  date varchar(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,
  pagination varchar(10) default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
) TYPE=MyISAM;


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,
  msnim varchar(70) default NULL,
  Icqnum varchar(20) default NULL,
  aolim varchar(70) default NULL,
  bgclr varchar(70) default NULL,
  headerclr varchar(70) default NULL,
  fontclr varchar(70) default NULL,
  status varchar(7) default NULL,
  PRIMARY KEY  (id),
  UNIQUE KEY id (id)
) TYPE=MyISAM;

rachel2004

It looks like you want to use a JOIN, but I'm not clear on how your tables are related to help you with a SELECT statement.

Is the "id" column of each table what you are using to link the tables? In other words, would you want to grab records where:

comments.id = entries.id = users.id ?

If not, what is the column that links all of these tables?

crawfd

Yeah I'd like to use JOIN...

comments.id = entries.id = users.id

I want to link all three id's to one id ...but don't I have to create another table with some sort of id assocation?.......

rachel2004

It's up to you decide whether you want to create a linking table or if you want to just create a column in each table (or use an existing column) to link the tables.

Based on how your tables look right now, you might be better off using the id from your first table, and creating a column in the other two tables and plugging in those id values.

Then for your join:

$sql = "SELECT FROM
        t1.col1, 
        t1.col2,
        t1.col3,
        t2.col1,
        t2.col2,
        t2.col3,
        t3.col1,
        t3.col2,
        t3.col3
        FROM Table1 AS t1
        LEFT JOIN Table2 AS t2
        ON (t1.col1 = t2.col2)
        LEFT JOIN Table3 AS t3
        ON (t2.col1 = t3.col1)
        WHERE t1.col1 = '$someNumber'";

I think you get the idea. Pay attention to the ON clauses. It matters what order the tables are used with the = operator. Just follow my model with you r table names and columns and you shuould be okay.

Post back with your results.

crawfd

Which would function faster? Your method or create a linking table and associating id's.

rachel2004

Wow, I know the answer to that, but I don't know if I want to type it all out. ;-)

It depends on one-to-one, one-to-many, or many-to-many relationships between your tables.

Depending on what table you consider your "primary" (so to speak), you can determine what kind of relationship it has with the other tables.

DB design is a bigger topic than I can cover in a post and I cannot answer your question without info on what kind of relationship your tables have. BUT don't give me that info, because I don't have time for that right now.

Make a determination on what you want to do, and maybe I can help you with the JOIN clauses, but I don't want to get bogged down too heavily in one thread.

crawfd

Well I'll work with the information you've given me thus far. Perhaps later on I can try and fetch more infromation from you about indexing certain columns to increase the performace on the database in relationship to my db structure, for now though this is beginning to be a crash course in relational databases. Thanks...

rachel2004

Yeah, no sweat. DB design will require some study time, but you'll enjoy it if you're a true geek at heart (or wannabe geek) . ;-)

crawfd

Yeah, maybe I got in a little over my head with this project...but I'm learning a lot.

Based on my field names in my tables, do you feel I organized it fairly well at a glance?

rachel2004

If you're just starting out, it's not bad. For commercial or high-volume sites, though, you'd definitely want to normalize even further. Don't be discouraged by my remarks, however. Just research on your own.

crawfd

Well I'd like to do this the most efficient way, what do you mean by normalization? You mean break up the tables even further? Thanks

rachel2004

Normalization is the term used for breaking up a database into tables and then breaking those tables into only the fields that should be associated with that topic. The idea is to prevent redundant data from being stored in more than one location, if possible. You can find a more scholarly definition in a book, but that's mainly it.

There are probably good resources on the Web to explain it further. A pretty good, easy-to-understand book, though:

http://www.amazon.com/exec/obidos/tg/detail/-/0201752840/qid=1086705136/sr=8-1/ref=sr_8_xs_ap_i1_xgl14/102-0657998-2562516?v=glance&s=books&n=507846

crawfd

I'm a bit confused, but I guess I'll have to read into it. Could you provide a specific example from my table structure(s) above and let me know what direction to follow in terms of normalization? Thanks

crawfd

Would it be faster to join field names in tables by using 'join', or would it be faster to just put all the fields I want to grab into one table and query through those fields????


$sql = "SELECT FROM 
        t1.col1, 
        t1.col2, 
        t1.col3, 
        t2.col1, 
        t2.col2, 
        t2.col3, 
        t3.col1, 
        t3.col2, 
        t3.col3 
        FROM Table1 AS t1 
        LEFT JOIN Table2 AS t2 
        ON (t1.col1 = t2.col2) 
        LEFT JOIN Table3 AS t3 
        ON (t2.col1 = t3.col1) 
        WHERE t1.col1 = '$someNumber'";