table join printing information twice

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

table join printing information twice

Post 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]
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
method_man
Forum Contributor
Posts: 257
Joined: Sat Mar 19, 2005 1:38 am

nothin

Post by method_man »

how did u catch that mistake? lol
its a very small one

matt
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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. ;)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

move line 42 into the loop ?
Post Reply