Page 1 of 1
How can I retreive data from multiple tables?
Posted: Mon Sep 02, 2002 10:32 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:
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++)
{
echo "<TR>\n"
."<TD width=460 height=30 bgcolor=".row_color($i)."> <a href="pageb.php?topic=$i&start=$start">".$arrayї$i]ї"topic_title"]."</a></TD>\n"
."<TD width=60 bgcolor=".row_color($i)."> ".$arrayї$i]ї"topic_views"]."</TD>\n"
."</TR>";
}
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.
Posted: Mon Sep 02, 2002 11:28 pm
by MacDaddy
gilbertwang,
Just have multiple quieries
Posted: Mon Sep 02, 2002 11:59 pm
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
Posted: Tue Sep 03, 2002 1:14 am
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?
Posted: Tue Sep 03, 2002 1:59 am
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
Posted: Tue Sep 03, 2002 2:51 am
by mikeq
and the short version is to miss out the AS keyword
select table1.column1 T1COL1....
Posted: Tue Sep 03, 2002 3:00 am
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
Posted: Tue Sep 03, 2002 11:28 pm
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.
Posted: Tue Sep 03, 2002 11:49 pm
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ї'source'];
and please read:
Sticky: Before Post Read: Concerning Passing Variables in PHP 4.2+
(if you didn't already

)
Posted: Wed Sep 04, 2002 9:59 pm
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ї'source'];
print "$query<br><br>\n";
How can I print the result instead of the query. thanks
Posted: Wed Sep 04, 2002 11:36 pm
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!

Posted: Thu Sep 05, 2002 8:06 pm
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.
Posted: Thu Sep 05, 2002 10:07 pm
by volka
Code: Select all
<?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ї'source'];
$result = mysql_query($query, $dbConn) or die(mysql_error());
print('<table border="1">');
while( $row = mysql_fetch_row($result) )
print('<tr><td>'.$rowї0].'</td></tr>');
print('<table border="1">');
mysql_free_result($result);
mysql_close($dbConn);
?>
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
Posted: Thu Sep 05, 2002 11:46 pm
by gilbertwang
it works like a charm.
Thank YOU so much....
You are great
