Tricky Table Join
Posted: Thu Mar 24, 2005 8:51 pm
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
Second
feyd | Please review how to post code using
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);
}
?>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
andCode: Select all
tags. Read: [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]