Free Web Hosting Forum
(#1)
Old
cbarg cbarg is offline
Member
cbarg is on a distinguished road
 
Posts: 54
Join Date: Sep 2011
Default select more than one table - 09-23-2011, 08:11 PM

Hello everyone! I am calling data from two different tables this way:


$sql = "SELECT
topics.topic_id,
topics.topic_subject,
topics.topic_date,
topics.topic_by,
posts.post_topic,
posts.post_content,
posts.post_img
FROM
topics
LEFT JOIN
posts
ON
posts.post_topic = topics.topic_id

ORDER BY
topics.topic_id DESC";

But for some weird reason I am not getting anything from the table posts. No errors in the mysql_query either :-/ Any ideas??
Reply With Quote
(#2)
Old
Passionless Passionless is offline
Member
Passionless is on a distinguished road
 
Posts: 48
Join Date: Sep 2011
Default 09-23-2011, 09:34 PM

Hi,
It could be that you're assigning a wrong post_topic ID. That way you won't get anything from the post table, unless you RIGHT JOIN.

Cya.
Reply With Quote
(#3)
Old
cbarg cbarg is offline
Member
cbarg is on a distinguished road
 
Posts: 54
Join Date: Sep 2011
Default 09-23-2011, 10:30 PM

I really though about that, but exporting the two tables to a spreadsheet made me see more clearly that I am not wrong :-(
http://imageshack.us/photo/my-images/695/topicpost.jpg
Reply With Quote
(#4)
Old
cbarg cbarg is offline
Member
cbarg is on a distinguished road
 
Posts: 54
Join Date: Sep 2011
Default 09-23-2011, 10:35 PM

I don't know how to feel now :-/ RIGHT JOIN works fine, but I don't know why :-? I never studied right join...
Thanks Passionless!!

What I found is that I am storing the date in tho different tables. Should I erase one of them?
Reply With Quote
(#5)
Old
Passionless Passionless is offline
Member
Passionless is on a distinguished road
 
Posts: 48
Join Date: Sep 2011
Default 09-24-2011, 12:17 AM

Hi,
Given your schema you should delete the 'topic_date' row and may be also 'topic_com', which I suppose hold the number of comments on a given topic (that info is already on the tables, just COUNT(*) it) and 'topic_by'.

I think there's at least a post row for each topic row (the topic's subject on the topic table and the topic's content on the post table) so just JOINing should work, since there is no topic without a post (LEFT JOIN) neither a post without a topic it belongs to (RIGHT JOIN).
I sincerely don't know why it behaves this way.

http://img268.imageshack.us/img268/3922/unledzdt.png
Reply With Quote
(#6)
Old
grace1004 grace1004 is offline
Senior Member
grace1004 is on a distinguished road
 
Posts: 890
Join Date: Dec 2010
Default 09-24-2011, 12:46 AM

Left join is used to find rows that don't match. According to the image in your link, the data in topics.topic_id
and posts.post_topic columns are exactly same, thus matching one by one. That's why you could not get any
result. As Passionless pointed out, you can get the result by just joining two tables as follows:

$sql = "SELECT
topics.topic_id,
topics.topic_subject,
topics.topic_date,
topics.topic_by,
from topics, posts,
posts.post_topic,
posts.post_content,
posts.post_img
where posts.post_topic=topics.topic_id //This might not be needed if the data in two columns are exactly same.
order by topics.topic_id DESC";

Last edited by grace1004; 09-24-2011 at 01:24 AM.
Reply With Quote
(#7)
Old
cbarg cbarg is offline
Member
cbarg is on a distinguished road
 
Posts: 54
Join Date: Sep 2011
Default 09-24-2011, 03:32 AM

you guys are amazing!!! THANKS.
BTW I'm really happy the way my page is coming up. You can see it (almost done) but there is still stuff to be done so i'm gonna be bothering for a while more :-)
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump



Powered by vBulletin® Version 3.8.2
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
vBulletin Skin developed by: vBStyles.com