Connecting to 2 Mysql Tables on a single php page
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
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
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
You have to select your table in query:
So, say I wanted data from the food table, and data from the drinks table:
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:
Which is the Table in the databases name. Thus, the tables for the two above queries are food and drinks.
- Monkey
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>";
}
?>food part of this query:
Code: Select all
mysql_query("SELECT * FROM food");- Monkey
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK
You can do simple joins like:
Mac
Code: Select all
SELECT t1.ID, t1.name, t2.town_name FROM table1 AS t1, table2 AS t2 WHERE t2.ID = t1.town_id-
devilgrendall
- Forum Newbie
- Posts: 17
- Joined: Fri Sep 10, 2004 10:29 am
- Location: Warwickshire
- Contact:
-
devilgrendall
- Forum Newbie
- Posts: 17
- Joined: Fri Sep 10, 2004 10:29 am
- Location: Warwickshire
- Contact:
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.
any suggestions???
Thanks Jay
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
-
devilgrendall
- Forum Newbie
- Posts: 17
- Joined: Fri Sep 10, 2004 10:29 am
- Location: Warwickshire
- Contact:
-
devilgrendall
- Forum Newbie
- Posts: 17
- Joined: Fri Sep 10, 2004 10:29 am
- Location: Warwickshire
- Contact:
-
devilgrendall
- Forum Newbie
- Posts: 17
- Joined: Fri Sep 10, 2004 10:29 am
- Location: Warwickshire
- Contact:
-
The Monkey
- Forum Contributor
- Posts: 168
- Joined: Tue Mar 09, 2004 9:05 am
- Location: Arkansas, USA
That would mean that you did not query the database correctly. You might try this addition to your queries: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
Code: Select all
<?php
$sql = mysql_query("SELECT * FROM food ORDER BY food_name") OR die("Error selecting food: " . mysql_error());
?>- Monkey
-
devilgrendall
- Forum Newbie
- Posts: 17
- Joined: Fri Sep 10, 2004 10:29 am
- Location: Warwickshire
- Contact:
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.
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>