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++) 
&#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.

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&#1111;'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&#1111;'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! :D

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

&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

Posted: Thu Sep 05, 2002 11:46 pm
by gilbertwang
it works like a charm.

Thank YOU so much....

You are great :lol: