table join help

[doddsey_65]

im using the following query to pull all rows from forum_posts and the signature row from a table called details.

 

Only problem is that the signature doesnt show up and the posts are repeated several times. What have i done wrong?

 

$sql = mysql_query("
SELECT *
FROM forum_posts p, details d
WHERE p.topicid = '$threadid'") or die (mysql_error());
while ($row = mysql_fetch_object($sql)) {

$topicname = $row->topicname;
$topicnamesafe = str_replace(" ", "-", $topicname);
$sig = $row->signature;

echo '<tr><td class="posts" style="width:80%;">' .$row->content . '<hr />'.$sig.'</td><td class="forum">';
echo 'By ' . $row->postedby . '<br /><h5 style="font-size: 12px;">On ';

echo date("F j, Y, g:i a", strtotime($row->date));

echo '</h5></td></tr><tr>';

}

[gevik]

The problem is that you do not state how the database should join the tables. What you are doing here is joining allrecords->with all records.

 

A join is typically:

 

SELECT
     a.field1,
     b.fieldX
FROM
     table1 a
     INNER JOIN table2 b on a.id = b.id    

 

I hope this helps.

[doddsey_65]

so how would i change mine to match yours? sorry but this is the first time i have used join. I tried and i get an error not unique table

[doddsey_65]

i tried this but i dont get any results:

 

$sql = mysql_query("
SELECT forum_posts.content, forum_posts.postedby, forum_posts.postedby, details.signature 
FROM forum_posts
INNER JOIN details on forum_posts.content = details.signature 
WHERE forum_posts.topicid = '$threadid'") or die (mysql_error());

[ChemicalBliss]

Ah see you need to get how the join works.

 

Joins match records based on matching specified fields, eg:

forum_posts.content = details.signature 

 

Should be something like:

forum_posts.authorid = details.authorid 

 

So in your details table, you would use the field that has the ID numbers that the signature belongs to,

And in your Posts table you would have the same - the ID number of the user who's posted the content.

 

This way you can match authorid's with as many tables that have an authorid to get lots and lots of seperated, normalized information (with multiple JOIN statements).

 

Hope this clears things up a bit.

-cb-

[doddsey_65]

thanks, now i understand that this isnt what i need lol.

 

More info:

 

I have 2 tables: forum_posts and details

 

i need to pull everything from forum_posts and i need to pull signature row from details. How would i do this? it cant be a join because there are no identical tables.

[ChemicalBliss]

So how would you possibly know which signature belongs to which user?

And which user posted which content?

 

I think you need to rethink your database design.

 

-cb-

[doddsey_65]

the post is in forum_posts.content

the author is in posts.author

 

the user signature is in details.signature

 

i echo the post by pulling all posts from the database

i then echo who the author is

i then want to echo the sig by doing something like

 

echo $post

 

echo $sig WHERE author= $_SESSION['usr']

 

something like that?

[ChemicalBliss]

Well there you go, the author fields should all contain the author id, you can use those fields as the ID's.

 

-cb-

[doddsey_65]

but the id in the details table is different from the id in the post table since the details id is for the users and the post id is the id for the posts. So a join wouldnt work with 2 different id values would it?

[ChemicalBliss]

no you dont just join the ID fields, you choose which fields join to which.

 

Example;

Table "forum_posts":
   id   |   content   |   date   |   author_name

Table "forum_users":
   id   |   name   |   datejoined

Table "user_details":
   id   |   name   |   value   |   user_name

 

You could choose to left join `forum_post`.`author_name` = `forum_users`.`name`

and another left join for `forum_post`.`author_name` = `user_details`.`user_name`

 

so your query would look like:

 

SELECT 
   `forum_post`.`id` As post_id,
   `forum_post`.`content` As post_content,
   `forum_post`.`date` As post_date,
   `forum_post`.`author_name` As post_author,
   `forum_users`.`id` As user_id,
   `forum_users`.`name` As user_name,
   `forum_users`.`datejoined` As user_date,
   `forum_details`.`id` As details_id,
   `forum_details`.`name` details_name,
   `forum_details`.`value` As details_value,
   `forum_details`.`user_name` As details_user_name
   FROM `forum_post`
   LEFT JOIN `forum_users` on `forum_post`.`author_name`=`forum_users`.`name`
   LEFT JOIN `forum_details` on `forum_post`.`author_name`=`forum_details`.`user_name`
   WHERE `forum_post`.`post_id`='1'

 

In fact you dont need the join to the forum_users table since i think your storing the actual username inside the posts_table, so you already have the username, if you were using the ID (which is faster, better practice), then you would need this join to get the name of the author (rather than display the ID number of the user).

 

Also, the reason i would highly reccommend using "Aliases" (the As ... keyword), lets you define what the key names will be in the resulting row, for ex.

Normally, selecting `forum_post`.`id` would result in the id key being.. id, where as the alias now renames the key "post_id". eg $row['post_id'];

This is important when using joins as table fields can have the same name (the ID fields for example).

 

Experiement with different joins to get a better understand of what a left/right and INNER join is.

Generally, LEFT join will match every result on the left table/field.

Whereas the RIGHT join will only give a row if there is a match on the Right Table/field.

 

Hope this helps,

-cb-