Page 1 of 1

how to retreive data from multiple tables...

Posted: Thu Aug 22, 2002 4:44 pm
by gilbertwang
How can I retreive data from multiple tables?

I have three tables to use in this script.

Table A - Topic_id, Topic_title, Topic_view etc...
Table B - Post_id, Topic_id etc...
Table C - Post_id, Post_text

Now my script gets the topic_id, topic_title, topic_view in a page called pagea.php

Code: Select all

if (!empty($_GETї'start'])) $start = $_GETї'start'];

echo "<table class=justtext bgcolor=#000000 border=0 cellpadding=1 cellspacing=1>\n";
echo "<TR>";
echo "<TD width=460 height=45 bgcolor=#009900>&nbsp;&nbsp;<b>Topic Title</b></TD>";
echo "<TD width=60 bgcolor=#009900>&nbsp;&nbsp;<b>View</b></TD>";
echo "</TR>"; 

for ($i = $start; $i < ($num_items + $start); $i++) 
&#123; 
   echo "<TR>\n" 
   ."<TD width=460 height=30 bgcolor=".row_color($i).">&nbsp;&nbsp;<a href="pageb.php?topic=$i&start=$start">".$array&#1111;$i]&#1111;"topic_title"]."</a></TD>\n" 
   ."<TD width=60 bgcolor=".row_color($i).">&nbsp;&nbsp;".$array&#1111;$i]&#1111;"topic_views"]."</TD>\n"  
   ."</TR>"; 
&#125; 

echo "</TABLE>";
And I need to retreive the post_text from table from the link

Code: Select all

;<a href="view_gossips.php?gossip=$i&start=$start">".$array&#1111;$i]&#1111;"topic_title"]."</a>
.

In order to do that, I need to use the topic_id to find post_id in table b and then used post_id to retreive the post_text.

Thanks in advance for sharing your knowledge.

Posted: Thu Aug 22, 2002 6:31 pm
by riley
Table A - Topic_id, Topic_title, Topic_view etc...
Table B - Post_id, Topic_id etc...
Table C - Post_id, Post_text

This assumes that Topic_id, Post_id are unique and has a corrosponding record in each table

Code: Select all

$query = "Select a.Topic_titld, a.Topic_view, c.Post_text
From TableA a Inner Join TableB b on a.Topic_id = b.Topic_id
Inner Join TableC c on b.Post_id = c.Post_id
Where Topic_id = 'somevalue'"
Then run this query against your database to return a recordset.

Posted: Fri Aug 23, 2002 10:02 pm
by gilbertwang
Do you mean that put the query into mysql database and called it from the page. What do I need to set the topic_id.
I am a newbie and is not sure how to put this together.

Code: Select all

Select a.Topic_title, a.Topic_views, c.post_text From phpbb_topics a Inner Join phpbb_posts b on phpbb_topics.Topic_id = b.Topic_id Inner Join phpbb_posts_text c on b.Post_id = c.Post_id Where Topic_id = 'somewhere'
what do you mean by running this against the database to return a recordset.