Connecting to 2 Mysql Tables on a single php page

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

Connecting to 2 Mysql Tables on a single php page

Post by devilgrendall »

Hi all,

I was wondering if anyone could help me. I need to pull records from 2 or more tables in a single Mysql db on a php page. Can anyone start me off on this.

Thanks


Jay
The Monkey
Forum Contributor
Posts: 168
Joined: Tue Mar 09, 2004 9:05 am
Location: Arkansas, USA

Post by The Monkey »

You have to select your table in query:

So, say I wanted data from the food table, and data from the drinks table:

Code: Select all

<?php
$sql = mysql_query("SELECT * FROM food ORDER BY food_name");
while($row = mysql_fetch_array($sql))
{
     $food_name = $row['food_name'];
     echo "<p>$food_name</p>";
}

$sql = mysql_query("SELECT * FROM drinks ORDER BY drink_name");
while($row = mysql_fetch_array($sql))
{
     $drink_name = $row['drink_name'];
     echo "<p>$drink_name</p>";
}
?>
This script would select all of the food from the database, echo each food's name, and then do the exact same thing for the drinks. The key lies in
food part of this query:

Code: Select all

mysql_query("SELECT * FROM food");
Which is the Table in the databases name. Thus, the tables for the two above queries are food and drinks.

- Monkey
qads
DevNet Resident
Posts: 1199
Joined: Tue Apr 23, 2002 10:02 am
Location: Brisbane

Post by qads »

look into [mysql_man]join[/mysql_man].
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post by twigletmac »

You can do simple joins like:

Code: Select all

SELECT t1.ID, t1.name, t2.town_name FROM table1 AS t1, table2 AS t2 WHERE t2.ID = t1.town_id
Mac
devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

Post by devilgrendall »

Thanks for all your help!!!!!

Pretty much on the right track now.


Jay
devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

Post by devilgrendall »

back again im afraid, can any one help me out here.

Im using this statement to join 2 tables. which works fine but it duplicates the entries. I dont want to use any where clauses as i simply want to drawout the last 8 entries in pretty much all the columns in the tables.

Code: Select all

$sql = "SELECT building.*, news.* FROM building, news" ;

any suggestions???


Thanks Jay
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

you have to attach the tables together somehow with a join, or you will always get ~duplicate records.

maybe you should look into [mysql_man]union syntax[/mysql_man]
devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

Post by devilgrendall »

tried union, it only indexes the results under the table in the first select, ie the results from the second select get indexed under the column names from the table in the first select.

back to the drawing board
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

thats how unions work... this sounds more like you need 2 seperate queries... :?
devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

Post by devilgrendall »

any suggestions as to structure??
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

structure of the 2 seperate queries? The Monkey's post details that.
devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

Post by devilgrendall »

thanks, i tried monkey queries but i got Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /
still new to this

jay
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

post the code you tried and an export of the table structure you are using.
The Monkey
Forum Contributor
Posts: 168
Joined: Tue Mar 09, 2004 9:05 am
Location: Arkansas, USA

Post by The Monkey »

devilgrendall wrote:thanks, i tried monkey queries but i got Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /
still new to this

jay
That would mean that you did not query the database correctly. You might try this addition to your queries:

Code: Select all

<?php
$sql = mysql_query("SELECT * FROM food ORDER BY food_name") OR die("Error selecting food: " . mysql_error());
?>
The key there is the mysql_error() function. This gives you error information as to what went wrong with the query.

- Monkey
devilgrendall
Forum Newbie
Posts: 17
Joined: Fri Sep 10, 2004 10:29 am
Location: Warwickshire
Contact:

Post by devilgrendall »

ok here you go. the tables in the db are building and news, both contain about 40 columns. ones im trying to retrive are

buildings
-------------
buildingname
country


News
--------------
category
articletitle
clip


This is the code i tried, probably totally wrong, as yet ive not had to deal with multiple queries.

Code: Select all

<html>
<head>			
<?php
			include ("****");
			$link = mysql_connect($host, $user, $password) or die ("Could not connect.");
			$sql = mysql_query("SELECT * FROM building ORDER BY keyindex desc");
				while($row = mysql_fetch_array($sql))
					{
    				 $buildingname = $row['buildingname'];
						}
			$sql = mysql_query("SELECT * FROM building ORDER BY keyindex desc");
					while($row = mysql_fetch_array($sql))
					{
   				  $country = $row['country'];
						}

			$sql = mysql_query("SELECT * FROM news ORDER BY keyindex desc");
					while($row = mysql_fetch_array($sql))
					{
   				  $category = $row['category'];
						}
			$sql = mysql_query("SELECT * FROM news ORDER BY keyindex desc");
					while($row = mysql_fetch_array($sql))
					{
   				  $articlename = $row['articlename'];
						}
			$sql = mysql_query("SELECT * FROM news ORDER BY keyindex desc");
					while($row = mysql_fetch_array($sql))
					{
   				  $clip = $row['clip'];
						}
			mysql_close($link);
?>
</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0" class="body" >
	<table align="center" cellpadding="0" cellspacing="0" class="txt1">
	<tr align="center"> 
	<td width="900" height="96">&nbsp;</td>
	</tr>
	<tr align="left" valign="top"> 
	<td> <table width="897" cellpadding="0" cellspacing="9" class="txt">
	 <tr> 
	<td width="236" align="left" valign="top" class="mainnews" <? echo "rowspan" . "=" . "8"; ?> rowspan="8"> 
	<table width="100%" border="0" cellpadding="0" cellspacing="0">
	<tr> 
	<? for ($indexcount = 0;$indexcount <=7; $indexcount++) {?>
	<td align="center"><img src= "<? print "images/buildingthumbs/" . mysql_result($result,$indexcount,keyindex) . "_pic1.jpg"" ?>"  alt=""
	width = "100" style="background-color: #FFFFFF" /><br><? echo $row["$buildingname"]; ?></td>
	</tr>
	<? } ?>
 	</table></td>
	<td width="345" align="left" valign="top" <? echo "rowspan" . "=" . "8"; ?> rowspan="8"><p align="center">&nbsp;</p>
	</td>
	<? for ($indexcount = 0;$indexcount <=7; $indexcount++) {?>
	<td width="278" valign="top" class="mainnews"> <b><? echo $row["$category"];?> 
	 > <? echo $row['$articletitle'];?></b> <br><? echo $row["clip"];?><div align="right"> <a href="" class="roll">Read more...</a> </div></td>
	</tr>
	<? } ?>
      </table></td>
  </tr>
	</table>
  </body>
	</html>
Post Reply