Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.
Moderator: General Moderators
impulse()
Forum Regular
Posts: 748 Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:
Post
by impulse() » Sun Oct 01, 2006 11:40 am
I've started working on joining certain data from certain tables but I'm struggling a tiny bit in outputting the data. At the moment 1 table contains a company name with a unique ID that relates to the company. The other table contains a customer name which has an ID that correspondes to the ID from the first table, a first name, a surname and a product. This is the code I have at the moment:
Code: Select all
mysql_connect("x", "x", "x");
mysql_select_db("resellers");
$query = mysql_query("SELECT r.ResellerName, c.fName, c.sName, c.package FROM resellerID r JOIN customers c");
?><table border="1" cellpadding="1" cellspacing="1">
<tr>
<th> Reseller Name </th>
<th> First Name </th>
<th> Last Name </th>
<th> Package </th>
</tr><?php
while ($results = mysql_fetch_array($query)) {
?><tr>
<td> <?php echo $results['ResellerName']; ?> </td>
<td> <?php echo $results['fName']; ?> </td>
<td> <?php echo $results['sName']; ?> </td>
<td> <?php echo $results['package']; ?> </td>
</tr><?php
}
?></table><?php
But it outputs every customer entry 5 times in the table, each one with each of the companys names.
http://stesbox.co.uk/php/test/join.php
Can anyone help me out with this?
Ste,
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098 Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia
Post
by Chris Corbyn » Sun Oct 01, 2006 12:33 pm
That query looks a bit off. What are the two tables called and in each table, what is the name of the ID you need to join on? resellerID is not the name of a table is it? It sounds more like the name of a column.
volka
DevNet Evangelist
Posts: 8391 Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger
Post
by volka » Sun Oct 01, 2006 12:42 pm
try
Code: Select all
<?php
mysql_connect("x", "x", "x");
mysql_select_db("resellers");
$query = "SELECT
r.ResellerName, c.fName, c.sName, c.package
FROM
resellerID as r
LEFT JOIN
customers as c
ON
r.id=c.id"
$query = mysql_query($query) or die(mysql_error());
?>
<table border="1" cellpadding="1" cellspacing="1">
<tr>
<th> Reseller Name </th>
<th> First Name </th>
<th> Last Name </th>
<th> Package </th>
</tr>
<?php while ($results = mysql_fetch_array($query)) { ?>
<tr>
<td> <?php echo $results['ResellerName']; ?> </td>
<td> <?php echo $results['fName']; ?> </td>
<td> <?php echo $results['sName']; ?> </td>
<td> <?php echo $results['package']; ?> </td>
</tr>
<?php } ?>
</table>
impulse()
Forum Regular
Posts: 748 Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:
Post
by impulse() » Sun Oct 01, 2006 12:55 pm
The names seem OK. Here's my layout:
Table 1
Name of table : customers
Contains : id, resellerID, fName, sName, package
Table 2
Name of table : resellerID
Contains : ResellerName, id
I want it to loops through and display each fName, sName, package and also which reseller they belong to by identifying what their resellerID is.
As you can see at the moment it goes through every name and prints out every resellers name for each customer.
impulse()
Forum Regular
Posts: 748 Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:
Post
by impulse() » Sun Oct 01, 2006 12:59 pm
That worked great Volka, besides I had to use
Not your fault thought, you had little information about my tables.