Page 1 of 1

table join printing information twice

Posted: Wed Mar 23, 2005 5:29 pm
by 2bran
Hows it going guys i have a slight problem with the script below. I'm trying to table join from a my_sql database and then echo out the result in a while statment for three different tables in PHP. The problem is its print all the information in the tables and even printing some information twice, BIZZARE!!

I'm table joining through the customerID in both sql tables and when user enters the customerID in the html form it should on;y display that information.

If any use the customer ID in the customer is the only primay key, its not a primary key in the jobs table becuase one already exists.

If anyone has any suggestions that would be great.

Cheers
William

Code: Select all

<html>
<head>


<link href="Call%20Center.css" rel="stylesheet" type="text/css">
</head>
<body> 

<table width="195" height="49" border="1" class="H1">
  <tr>
    <td>Customer Enquiry </td>
  </tr>
</table>
<table width="60%" height="20%" border="1" class="Search">
<form action="<?php $_SERVER['PHP_SELF'] ?>" method="post" name="Search">


  <tr>
    <td width="173">Search Customer ID </td>
    <td width="259"><input name="CustomerID" type="text"></td>
	<td width="259"><input name="Search" type="submit" value="Search"></td>
  </tr>
  </form>
</table>


<?php

if (isset ($_POST['CustomerID'])) 
{

$CutomerID= $_POST['CustomerID'];


$connection= mysql_connect("", "", "");

mysql_select_db ("");

$result = mysql_query("SELECT * from customer,jobs WHERE customer.CustomerID=jobs.CustomerID")or die(mysql_error());
 
echo "<font face=verdana size=10pt>";
echo "<table border=1 cellspacing=0 class=enquiry><tr><td>CustomerInfo.</td></tr><tr><td>CustomerID</td><td>Surname</td><td>Firstname</td><td>Address1</td><td>Address2</td><td>Address3</td><td>Postcode</td><td>HomeNo</td><td>MobileNo</td><td>E-mail</td>";
  


while ($row = mysql_fetch_array($result))
{
	echo "<tr>";
	echo "<td>" . $row['CustomerID'] . "</td>";
	echo "<td>" . $row['Surname'] . "</td>";
    echo "<td>" . $row['Firstname'] . "</td>";
    echo "<td>" . $row['Address1'] . "</td>";
    echo "<td>" . $row['Address2'] . "</td>";
    echo "<td>" . $row['Address3'] . "</td>";
    echo "<td>" . $row['Postcode'] . "</td>";
	echo "<td>" . $row['HomeNo'] . "</td>";
	echo "<td>" . $row['MobileNo'] . "</td>";
	echo "<td>" . $row['Email'] . "</td>";
	echo "</tr>";
	echo "</table>";
	
		
	echo "<font face=verdana size=10pt>";
	echo "<table border=1 cellspacing=0 class=enquiry1><tr><td>Appliance Info</td><tr><td>JobNo</td><td>ModelNo</td><td>Mancode</td><td>SerialNo</td><td>ProductNo</td><td>ExpiryDate</td><td>PurchaseDate</td>";

	echo "<tr>";
	echo "<td>" . $row['JobNo'] . "</td>"; 
	echo "<td>" . $row['ModelNo'] . "</td>";
    echo "<td>" . $row['SerialNo'] . "</td>";
    echo "<td>" . $row['ProductNo'] . "</td>";
    echo "<td>" . $row['ExpiryDate'] . "</td>";
    echo "<td>" . $row['PurchaseDate'] . "</td>";
	echo "</tr>";
	echo "</table>";

	echo "<font face=verdana size=10pt>";
	echo "<table border=1 cellspacing=0 class=enquiry2><tr><td>Job Info</td></tr><tr><td>JobID</td><td>Fault</td><td>Instructions</td><td>Parts1</td><td>Parts2</td><td>Parts3</td><td>Calldate</td><td>Calltime</td><td>Postcode</td><td>Engineer</td><td>EngineerNo.</td>";

    echo "<tr>";
	echo "<td>" . $row['Fault'] . "</td>";
    echo "<td>" . $row['Instructions'] . "</td>";
    echo "<td>" . $row['CallDate'] . "</td>";
    echo "<td>" . $row['CallTime'] . "</td>";
    echo "<td>" . $row['Postcode'] . "</td>";
    echo "<td>" . $row['Engineer'] . "</td>";
    echo "<td>" . $row['EngineerNo'] . "</td>";
	echo "</tr>";
	echo "</table>";
	


}
	
	





}




mysql_close($connection);	



?>

</body>
</html>

feyd | Please review how to post code using

Code: Select all

and

Code: Select all

tags. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Wed Mar 23, 2005 5:36 pm
by feyd

Code: Select all

$result = mysql_query('SELECT * FROM `customer` c INNER JOIN `jobs` j ON c.CustomerID = j.CustomerID WHERE c.CustomerID = \'' . $CutomerID . '\'') or die(mysql_error());
note: the variable is misspelled from line 32.

nothin

Posted: Wed Mar 23, 2005 5:38 pm
by method_man
how did u catch that mistake? lol
its a very small one

matt

Posted: Wed Mar 23, 2005 5:58 pm
by feyd
2bran wrote:Subject: Dude you're a genius
Message:
Cheers Man

I stuck it in to the code and it worked straight away. Thanks Dude

:D
I believe 2bran meant to post this here. ;)

Posted: Wed Mar 23, 2005 6:31 pm
by feyd
2bran wrote:Subject:
Table join displaying multiple records
Message:
Hi again guys

Sorry to be back so soon but i have another small problem. The information that was given to me earlier was great and it totally worked but when i went to pull out multiple records like the customer having more than one job, so one record from the jobs table and a number of records from the jobs table how would i go about doing that.

Any suggestions super
another mis-post..

Posted: Wed Mar 23, 2005 6:34 pm
by feyd
provided the Customer ID doesn't change, I don't see why it wouldn't deliver multiple jobs for them. If you want the selection based on the Job ID, then you need to adjust how you make the query such that you can switch to using a Job ID restriction instead of a Customer ID restriction.

Posted: Wed Mar 23, 2005 6:41 pm
by 2bran
Sorry i made a mistake, it is pulling out mulitple records but there being displayed very well. there overlapping on each other and i was wondering if u had any advice for displaying the information accordingly.

Cheers

William

Posted: Wed Mar 23, 2005 7:11 pm
by feyd
move line 42 into the loop ?