Page 1 of 2
Connecting to 2 Mysql Tables on a single php page
Posted: Wed Oct 13, 2004 12:42 pm
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
Posted: Wed Oct 13, 2004 12:48 pm
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
Posted: Wed Oct 13, 2004 1:33 pm
by qads
look into [mysql_man]join[/mysql_man].
Posted: Thu Oct 14, 2004 3:58 am
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
Posted: Thu Oct 14, 2004 8:35 am
by devilgrendall
Thanks for all your help!!!!!
Pretty much on the right track now.
Jay
Posted: Wed Oct 20, 2004 11:12 am
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
Posted: Wed Oct 20, 2004 11:14 am
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]
Posted: Wed Oct 20, 2004 11:20 am
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
Posted: Wed Oct 20, 2004 11:29 am
by feyd
thats how unions work... this sounds more like you need 2 seperate queries...

Posted: Wed Oct 20, 2004 11:37 am
by devilgrendall
any suggestions as to structure??
Posted: Wed Oct 20, 2004 11:46 am
by feyd
structure of the 2 seperate queries? The Monkey's post details that.
Posted: Wed Oct 20, 2004 12:16 pm
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
Posted: Wed Oct 20, 2004 12:18 pm
by feyd
post the code you tried and an export of the table structure you are using.
Posted: Wed Oct 20, 2004 12:25 pm
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
Posted: Wed Oct 20, 2004 12:35 pm
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"> </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"> </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>