Tricky Table Join

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

Tricky Table Join

Post by 2bran »

Hi Guys

This problem is a bit tricky to explain so bear with me but basically i have to php scripts which involve the use of query strings between the two.

The first script selects information from the database on the basis of two variables matching the data in the Mysqltable(EngineerNo and CallDate)and displays reguarly through an echo. At the end of the echo i'm using a link to the next script containg the variables in a query string.

On the second script i request the variables and then run another query asking for the job information in the jobs table. This works fine but the problem i have is that i need to table join the cusotmer and job table but have no variable to join it with becasue no variables(CustomerID's and JobNo's) are being requested from the previous script.

I've managed to accomplish table joining with some help from u guys before so any sugestions would be super.

Here r the scripts


P.S i know the table join work because i haven't declared any variables

First

Code: Select all

<table width="200" border="1" class="Search">
 <form action="" method="post" name="Workload">
  <tr>
    <td>EngineerNo</td>
    <td>CallDate</td>
  </tr>
  <tr>
    <td><input name="EngineerNo" type="text"></td>
    <td><input name="CallDate" type="text"></td>
    <td><input name="View" type="submit" value="View"></td>
  </tr>
  </form>
</table>
<?php

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

$EngineerNo= $_POST['EngineerNo'];
$CallDate= $_POST['CallDate'];


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

mysql_select_db ('');

$result = mysql_query("select * from jobs WHERE EngineerNo='$EngineerNo' and CallDate='$CallDate'");

echo "<font face=verdana size=10pt>";
echo "<table border=1 cellspacing=0 class='enquiry'>";

while ($row = mysql_fetch_array($result))
{
   
   echo "<tr><td>JobNo</td><td>Calldate</td><td>Calltime</td><td>Postcode</td><td>Engineer</td></tr>";

   echo "<tr>";
    echo "<td>" . $row['JobNo'] . "</td>";
    echo "<td>" . $row['CallDate'] . "</td>";
    echo "<td>" . $row['CallTime'] . "</td>";
    echo "<td>" . $row['Postcode'] . "</td>";
    echo "<td>" . $row['Engineer'] . "</td>";
	echo '<td><a href="WorkLoad.php?EngineerNo='.$EngineerNo.'&CallDate='.$CallDate.'">Full Job Desiption</a></td>'; 	
   	echo "</tr>";
	
}
	echo "</table>";
mysql_close($connection);





}







?>
Second

Code: Select all

<?PHP

$EngineerNo = $_REQUEST['EngineerNo'];
$CallDate = $_REQUEST['CallDate'];

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

mysql_select_db ('');

$result = mysql_query("select * from jobs WHERE EngineerNo='$EngineerNo' and CallDate='$CallDate'");
$result = mysql_query('SELECT * FROM `customer` c INNER JOIN `jobs` j ON c.CustomerID = j.CustomerID WHERE c.CustomerID = \'' . $CustomerID . '\'') or die(mysql_error());

  
	echo "<table border=1 cellspacing=0 class=enquiry>";


while ($row = mysql_fetch_array($result))
{
	echo "<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></tr>";
	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 "<tr>";
	echo "</tr>";
	echo "<tr>";
	echo "</tr>";
	echo "<tr>";
	echo "</tr>";
	echo "<tr><td>Appliance Info</td><tr><td>JobNo</td><td>ModelNo</td><td>SerialNo</td><td>ProductNo</td><td>ExpiryDate</td><td>PurchaseDate</td></tr>";
	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 "<tr>";
	echo "</tr>";
	echo "<tr>";
	echo "</tr>";
	echo "<tr>";
	echo "</tr>";
    echo "<tr>";
	echo "<tr><td>Job Info</td></tr><tr><td>Fault</td><td>Instructions</td><td>Calldate</td><td>Calltime</td><td>Postcode</td><td>Engineer</td><td>EngineerNo.</td></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);	


?>

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 »

I'm getting pretty tired of fixing your posts to use

Code: Select all

tags.. 

Maybe your "full job description" link should pass 'JobNo' instead of the other things?
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post by 2bran »

Sorry about keep forgeting to use the php tags i use the normal code ones, sorry.

About ur reply i see what u mean but discover the Job No. in the echo and i dont unfortuantly know how to pass information from the result query into the job description link query string.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

well, you got JobNo on line 38, so pull the url variable stuff out of line 43, and swap in a reference to JobNo.
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post by 2bran »

I see what u mean, but how could i pull the jobNo from the result and replace it in the url? a bit tricky
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

why would it be tricky? You already know how to access JobNo in the record. Remove the current query string you have going to workload.php and add JobNo. :?
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post by 2bran »

So the query sting will become like this amd should kind of work,

Code: Select all

echo "<tr>";
    echo "<td>" . $row['JobNo'] . "</td>";
	echo "<td>" . $row['CustomerID'] . "</td>";
    echo "<td>" . $row['CallDate'] . "</td>";
    echo "<td>" . $row['CallTime'] . "</td>";
    echo "<td>" . $row['Postcode'] . "</td>";
    echo "<td>" . $row['Engineer'] . "</td>";
	echo '<td><a href="WorkLoad.php?EngineerNo='.$EngineerNo.'&CallDate='.$CallDate.'&CustomerID='.$row['CustomerID'].'">Full Job Desiption</a></td>'; 	
   	echo "</tr>";
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

"Full Job Description" tells me you just need to pass the JobNo field.
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post by 2bran »

yeah but i can't table join the job NO its only preset in the jobs table but the customerID is will so querystring makes sense then yeah.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

JobNo will get you the record from the jobs table, which will get you the customer id involved.. seems like all you need..
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post by 2bran »

So i'll put this in the query string to get the jobNo on the frist script

Code: Select all

echo '<td><a href="WorkLoad.php?EngineerNo='.$EngineerNo.'&CallDate='.$CallDate.'&JobNo='.$row['JobNo'].'">Full Job Desiption</a></td>';
Then run these two queries on the second script.

Code: Select all

$result = mysql_query("select * from jobs WHERE JobNo='$JobNo'");

$result = mysql_query('SELECT * FROM `customer` c INNER JOIN `jobs` j ON c.CustomerID = j.CustomerID WHERE c.CustomerID = \'' . $CustomerID . '\'') or die(mysql_error());
I'll need two queries because the jobNo isn't present in the two tables.

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

Post by feyd »

uhh....

Code: Select all

SELECT * FROM `customers` c INNER JOIN `jobs` j ON c.CustomerID = j.CustomerID WHERE j.JobNo = '5'
should work just fine..
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post by 2bran »

Cheers for all the help by the way and i understand the query you wrote for the second script. BUt why the number 5 shouldn't it be $JobNo from the previous script in the passing query string.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

it's an example.. you would switch out the value to the variable.
2bran
Forum Commoner
Posts: 38
Joined: Fri Mar 04, 2005 7:03 pm

Post by 2bran »

yeah i know, cheers for the help again mate. I'm having trouble with my web server right now so i'll test the script with your ideas soon as nice one!!!!!.

Cheers

William



:D
Post Reply