How can I 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 can I 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:

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>  <b>Topic Title</b></TD>"; 
echo "<TD width=60 bgcolor=#009900>  <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).">  <a href="pageb.php?topic=$i&start=$start">".$array&#1111;$i]&#1111;"topic_title"]."</a></TD>\n" 
   ."<TD width=60 bgcolor=".row_color($i).">  ".$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:
;<a href=\"view_gossips.php?gossip=$i&start=$start\">".$array[$i]["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.
MacDaddy
Forum Newbie
Posts: 12
Joined: Sun May 05, 2002 9:36 pm
Location: Atlanta, GA

Post by MacDaddy »

gilbertwang,

Just have multiple quieries
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

maybe something like

Code: Select all

$query = 'SELECT TableC.Post_text from TableC left join TableB on TableC.Post_id = TableB.Post_id WHERE TableB.Topic_id = '.$topicId;
this queries the Post_text-field of all records in TableC that are related to the specified Topic_id in TableB
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

Volka's way will be quicked as if you use different queries it will take time executing it fetching row etc. As MySQL is faster than PHP I think you should select it all at once.

I've got question here:-

Code: Select all

SELECT table1.column1,table2.column1
How am I going to access table1's column and table2's column?
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Takuma wrote:I've got question here:-

Code: Select all

SELECT table1.column1,table2.column1
How am I going to access table1's column and table2's column?
You could just do:

Code: Select all

SELECT table1.column1 AS t1col1, table2.column1 AS t2col1
which would give them unique names.

Mac
User avatar
mikeq
Forum Regular
Posts: 512
Joined: Fri May 03, 2002 3:33 am
Location: Edinburgh, Scotland

Post by mikeq »

and the short version is to miss out the AS keyword

select table1.column1 T1COL1....
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

Which isn't quite as clear to read, it can look like (if you're reading someone else's code) a comma or other character has been missed out. The AS keyword makes it very explicit what you are trying to do.

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

Post by gilbertwang »

do I get the results from the url, like 1, and how can I set the number to $topicId
http://www.url.com/view_page.php?source=1&start=0

how can I assign the $topicId to the source number

Code: Select all

$query = 'SELECT TableC.Post_text from TableC left join TableB on TableC.Post_id = TableB.Post_id WHERE TableB.Topic_id = '.$topicId;
print "$query<br><br>\n"

I am new to php, thanks for teaching me.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

<uri>?key1=value1&key2=value2 is called 'GET'-method.
in newer version of php (>=4.1.x ?) you should get these values from an array called $_GET, i.e. $_GET['key1'] or in your case $_GET['source']
The simplest way is to alter the query-string

Code: Select all

$query = 'SELECT TableC.Post_text from TableC left join TableB on TableC.Post_id = TableB.Post_id WHERE TableB.Topic_id = '.$_GET&#1111;'source'];
and please read:
Sticky: Before Post Read: Concerning Passing Variables in PHP 4.2+
(if you didn't already ;) )
gilbertwang
Forum Commoner
Posts: 32
Joined: Sun Jun 30, 2002 11:08 pm
Location: Calgary

Post by gilbertwang »

ok..I know what you mean, but when I print the $query it give me

SELECT TableC.Post_text from TableC left join TableB on TableC.Post_id = TableB.Post_id WHERE TableB.Topic_id = 4

Code: Select all

$query = 'SELECT TableC.Post_text from TableC left join TableB on TableC.Post_id = TableB.Post_id WHERE TableB.Topic_id = '.$_GET&#1111;'source'];

print "$query<br><br>\n";
How can I print the result instead of the query. thanks
User avatar
phice
Moderator
Posts: 1416
Joined: Sat Apr 20, 2002 3:14 pm
Location: Dallas, TX
Contact:

Post by phice »

I suggest have multiple queries. That way, you wouldnt get confuzed in your code. But whatever is easiest for you, then do it that way! :D
Image Image
gilbertwang
Forum Commoner
Posts: 32
Joined: Sun Jun 30, 2002 11:08 pm
Location: Calgary

Post by gilbertwang »

thanks but I run the query against mysql database and it works great.

but my problem is it doesn't display on my web page, why is that?

should I do write anything after the query, I can't get the results showing on my website, but the query works in mysql database.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

Code: Select all

&lt;?php
$dbConn = mysql_connect('host', 'user', 'password') or die(mysql_error());
mysql_select_db('db') or die(mysql_error());

$query = 'SELECT TableC.Post_text from TableC left join TableB on TableC.Post_id = TableB.Post_id WHERE TableB.Topic_id = '.$_GET&#1111;'source'];
$result = mysql_query($query, $dbConn) or die(mysql_error());

print('&lt;table border="1"&gt;');
while( $row = mysql_fetch_row($result) )
	print('&lt;tr&gt;&lt;td&gt;'.$row&#1111;0].'&lt;/td&gt;&lt;/tr&gt;');
print('&lt;table border="1"&gt;');

mysql_free_result($result);
mysql_close($dbConn);
?&gt;
prints each queried text. No other field is selected, so you have to modifiy the code to be more useful ;)

PHP online manual - LXII. MySQL Functions
gilbertwang
Forum Commoner
Posts: 32
Joined: Sun Jun 30, 2002 11:08 pm
Location: Calgary

Post by gilbertwang »

it works like a charm.

Thank YOU so much....

You are great :lol:
Post Reply