how to retreive data from multiple tables...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
gilbertwang
Forum Commoner
Posts: 32
Joined: Sun Jun 30, 2002 11:08 pm
Location: Calgary

how to retreive data from multiple tables...

Post 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.
User avatar
riley
Forum Commoner
Posts: 45
Joined: Thu May 02, 2002 6:31 pm

Post 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.
gilbertwang
Forum Commoner
Posts: 32
Joined: Sun Jun 30, 2002 11:08 pm
Location: Calgary

Post 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.
Post Reply