Page 1 of 1

Tricky Table Join

Posted: Thu Mar 24, 2005 8:51 pm
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]

Posted: Thu Mar 24, 2005 9:14 pm
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?

Posted: Thu Mar 24, 2005 9:22 pm
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.

Posted: Thu Mar 24, 2005 9:29 pm
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.

Posted: Thu Mar 24, 2005 9:40 pm
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

Posted: Thu Mar 24, 2005 9:47 pm
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. :?

Posted: Fri Mar 25, 2005 3:57 pm
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>";

Posted: Fri Mar 25, 2005 4:08 pm
by feyd
"Full Job Description" tells me you just need to pass the JobNo field.

Posted: Fri Mar 25, 2005 4:17 pm
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.

Posted: Fri Mar 25, 2005 4:19 pm
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..

Posted: Fri Mar 25, 2005 4:38 pm
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

Posted: Fri Mar 25, 2005 4:50 pm
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..

Posted: Fri Mar 25, 2005 4:58 pm
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.

Posted: Fri Mar 25, 2005 5:00 pm
by feyd
it's an example.. you would switch out the value to the variable.

Posted: Fri Mar 25, 2005 5:13 pm
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