Page 1 of 1

Joining tables

Posted: Sun Oct 01, 2006 11:40 am
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,

Posted: Sun Oct 01, 2006 12:33 pm
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.

Posted: Sun Oct 01, 2006 12:42 pm
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>

Posted: Sun Oct 01, 2006 12:55 pm
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.

Posted: Sun Oct 01, 2006 12:59 pm
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.

:)