Joining tables

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

Moderator: General Moderators

Post Reply
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Joining tables

Post by impulse() »

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,
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

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.
User avatar
volka
DevNet Evangelist
Posts: 8391
Joined: Tue May 07, 2002 9:48 am
Location: Berlin, ger

Post by volka »

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() »

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() »

That worked great Volka, besides I had to use

Code: Select all

ON
             r.id = c.resellerID
Not your fault thought, you had little information about my tables.

:)
Post Reply