The query works great but the problem is that the idea behind the user selecting either Business or Consumer is that the query will only return results which are Business or Consumer. For example I know that there are two people called john in the database. One john is part of a company and the other isn't so therefore when the user queries by the name john and selects Business the query should only return the john that is part of a company, but what it is actually doing is returning both johns anyway. and if the user selects Consumer the query should only return the john that is not part of a company. But what it is actually doing is returning nothing. The only thing you get on the form is the echoed out value for nature which in this case would be Consumer.
I have tried putting the queries into if statements where if nature is equal to Consumer run the query. I have put a condition in the query which is WHERE Company.COMP_Postcode IS NULL so it will return records where that postcode field has nothing in it therefore the record will be a consumer. For Business I have used an if statement where nature is equal to Business run the query. I have also put a condition in the query which is WHERE Company.COMP_Postcode IS NOT NULL.
Also I have pagination applied to the results:
Anyways here is my code so far:
Code: Select all
include 'connect.php';
$criteria = $_GET['sCriteria2'];
$nature = $_GET['nature'];
$sql = "SELECT COUNT(*) FROM Customers
INNER JOIN Jobs ON (Jobs.J_RefNum = Customers.CUST_ID)
INNER JOIN Manufacturers ON (Manufacturers.MANU_ID = Jobs.J_RefNum)
INNER JOIN OperatingSystems ON (OperatingSystems.OS_ID = Jobs.J_RefNum)
INNER JOIN JobStatus ON (JobStatus.JS_ID = Jobs.J_RefNum)
INNER JOIN dataRecSpec ON (dataRecSpec.DRS_ID = Jobs.J_RefNum)
INNER JOIN MediaSpec ON (MediaSpec.MediaSpec_ID = Jobs.J_RefNum)
INNER JOIN Company ON (Company.COMP_ID = Jobs.J_RefNum)
WHERE (Customers.CUST_Forename LIKE '%$criteria%')
ORDER BY '$criteria'
";
$result=mysql_query($sql) or die(mysql_error());
$result2=mysql_fetch_row($result);
$numrows = $result2[0];
$rowsperpage = 1;
$totalpages = ceil($numrows / $rowsperpage);
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
// cast var as int
$currentpage = (int) $_GET['currentpage'];
} else {
// default page num
$currentpage = 1;
} // end if
if ($currentpage > $totalpages) {
// set current page to last page
$currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
// set current page to first page
$currentpage = 1;
} // end if
$offset = ($currentpage - 1) * $rowsperpage;
if($nature=='Business')
{
$sql=" SELECT Customers.CUST_ID, Jobs.J_RefNum, Customers.CUST_Forename,
Manufacturers.MANU_ID, Customers.CUST_Surname, Manufacturers.MANU_Name,
Customers.CUST_Email, Jobs.J_Model, Customers.CUST_Mobile, OperatingSystems.OS_ID,
Customers.CUST_HomeNum, OperatingSystems.OS_Name, Customers.CUST_AddressL1,
Jobs.J_ReceivedBy, Customers.CUST_AddressL2,
DATE_FORMAT(Jobs.J_DateRec, '%d/%m/%Y') AS J_DateRec,
Customers.CUST_AddressL3,
Jobs.J_FaultDesc, Customers.CUST_Postcode, Jobs.J_PassWinAdmin,
Jobs.J_DataRecYN, Jobs.J_PowerSuppYN, Jobs.J_MediaYN, Jobs.J_BagYN, Jobs.J_Conditions,
Jobs.J_ServiceTag, JobStatus.JS_ID, JobStatus.JS_Status, Jobs.J_Engineer, Jobs.J_EngComments,
Jobs.J_AntivirusYN, Jobs.J_ServicePackYN, Jobs.J_PDFYN, Jobs.J_FlashYN, Jobs.J_VLCYN,
Jobs.J_ValidatedYN, Jobs.J_DataRestoredYN, Jobs.J_Quote, Jobs.J_Comms,
DATE_FORMAT(Jobs.J_DateWorkComm, '%d/%m/%Y') AS J_DateWorkComm,
DATE_FORMAT(Jobs.J_DateCollec, '%d/%m/%Y') AS J_DateCollec,
Jobs.J_ProductKey,
MediaSpec.MediaSpec_Spec, dataRecSpec.DRS_Name, Company.COMP_ID, Company.COMP_Name,
Company.COMP_Email, Company.COMP_PrimaryNum, Company.COMP_SecondaryNum, Company.COMP_AddressL1,
Company.COMP_AddressL2, Company.COMP_AddressL3, Company.COMP_Postcode
FROM Customers, Jobs, Manufacturers, OperatingSystems, JobStatus, MediaSpec, dataRecSpec, Company
WHERE (Customers.CUST_Forename LIKE '%$criteria%' AND Jobs.J_RefNum = Customers.CUST_ID)
AND (JobStatus.JS_ID = Jobs.J_RefNum) AND (Manufacturers.MANU_ID = Jobs.J_RefNum)
AND (OperatingSystems.OS_ID = Jobs.J_RefNum) AND (MediaSpec.MediaSpec_ID = Jobs.J_RefNum)
AND (dataRecSpec.DRS_ID = Jobs.J_RefNum) AND (Company.COMP_ID = Jobs.J_RefNum)
AND (Company.COMP_Postcode IS NOT NULL)
ORDER BY '$criteria'
LIMIT $offset, $rowsperpage
";
}elseif($nature=='Consumer')
{
$sql=" SELECT Customers.CUST_ID, Jobs.J_RefNum, Customers.CUST_Forename,
Manufacturers.MANU_ID, Customers.CUST_Surname, Manufacturers.MANU_Name,
Customers.CUST_Email, Jobs.J_Model, Customers.CUST_Mobile, OperatingSystems.OS_ID,
Customers.CUST_HomeNum, OperatingSystems.OS_Name, Customers.CUST_AddressL1,
Jobs.J_ReceivedBy, Customers.CUST_AddressL2,
DATE_FORMAT(Jobs.J_DateRec, '%d/%m/%Y') AS J_DateRec,
Customers.CUST_AddressL3,
Jobs.J_FaultDesc, Customers.CUST_Postcode, Jobs.J_PassWinAdmin,
Jobs.J_DataRecYN, Jobs.J_PowerSuppYN, Jobs.J_MediaYN, Jobs.J_BagYN, Jobs.J_Conditions,
Jobs.J_ServiceTag, JobStatus.JS_ID, JobStatus.JS_Status, Jobs.J_Engineer, Jobs.J_EngComments,
Jobs.J_AntivirusYN, Jobs.J_ServicePackYN, Jobs.J_PDFYN, Jobs.J_FlashYN, Jobs.J_VLCYN,
Jobs.J_ValidatedYN, Jobs.J_DataRestoredYN, Jobs.J_Quote, Jobs.J_Comms,
DATE_FORMAT(Jobs.J_DateWorkComm, '%d/%m/%Y') AS J_DateWorkComm,
DATE_FORMAT(Jobs.J_DateCollec, '%d/%m/%Y') AS J_DateCollec,
Jobs.J_ProductKey,
MediaSpec.MediaSpec_Spec, dataRecSpec.DRS_Name, Company.COMP_ID, Company.COMP_Name,
Company.COMP_Email, Company.COMP_PrimaryNum, Company.COMP_SecondaryNum, Company.COMP_AddressL1,
Company.COMP_AddressL2, Company.COMP_AddressL3, Company.COMP_Postcode
FROM Customers, Jobs, Manufacturers, OperatingSystems, JobStatus, MediaSpec, dataRecSpec, Company
WHERE (Customers.CUST_Forename LIKE '%$criteria%' AND Jobs.J_RefNum = Customers.CUST_ID)
AND (JobStatus.JS_ID = Jobs.J_RefNum) AND (Manufacturers.MANU_ID = Jobs.J_RefNum)
AND (OperatingSystems.OS_ID = Jobs.J_RefNum) AND (MediaSpec.MediaSpec_ID = Jobs.J_RefNum)
AND (dataRecSpec.DRS_ID = Jobs.J_RefNum) AND (Company.COMP_ID = Jobs.J_RefNum)
AND (Company.COMP_Postcode IS NULL)
ORDER BY '$criteria'
LIMIT $offset, $rowsperpage
";
}
$result=mysql_query($sql) or die(mysql_error());
while($result2=mysql_fetch_assoc($result))
{
$CID = $result2['CUST_ID'];
$cuEmail = $result2['CUST_Email'];
$forename = $result2['CUST_Forename'];
$surname = $result2['CUST_Surname'];
$mobile = $result2['CUST_Mobile'];
$homenum = $result2['CUST_HomeNum'];
$add1 = $result2['CUST_AddressL1'];
$add2 = $result2['CUST_AddressL2'];
$add3 = $result2['CUST_AddressL3'];
$pCode = $result2['CUST_Postcode'];
$compID = $result2['COMP_ID'];
$compName = $result2['COMP_Name'];
$compEmail = $result2['COMP_Email'];
$pNumber = $result2['COMP_PrimaryNum'];
$sNumber = $result2['COMP_SecondaryNum'];
$compAdd1 = $result2['COMP_AddressL1'];
$compAdd2 = $result2['COMP_AddressL2'];
$compAdd3 = $result2['COMP_AddressL3'];
$compPostcode = $result2['COMP_Postcode'];
$jrefnum = $result2['J_RefNum'];
$manuID = $result2['MANU_ID'];
$manufacturer = $result2['MANU_Name'];
$model = $result2['J_Model'];
$osID = $result2['OS_ID'];
$os = $result2['OS_Name'];
$prokey = $result2['J_ProductKey'];
$recBy = $result2['J_ReceivedBy'];
$dateRec = $result2['J_DateRec'];
$faultDesc = $result2['J_FaultDesc'];
$passwin = $result2['J_PassWinAdmin'];
$dataRecYN = $result2['J_DataRecYN'];
$dataRecSpec = $result2['DRS_Name'];
$powerSupp = $result2['J_PowerSuppYN'];
$media = $result2['J_MediaYN'];
$mediaSpec = $result2['MediaSpec_Spec'];
$bag = $result2['J_BagYN'];
$conditions = $result2['J_Conditions'];
$servTag = $result2['J_ServiceTag'];
$jstatID = $result2['JS_ID'];
$jstatus = $result2['JS_Status'];
$engineer = $result2['J_Engineer'];
$engComments = $result2['J_EngComments'];
$antivirus = $result2['J_AntivirusYN'];
$servicepack = $result2['J_ServicePackYN'];
$pdf = $result2['J_PDFYN'];
$flash = $result2['J_FlashYN'];
$vlc = $result2['J_VLCYN'];
$validated = $result2['J_ValidatedYN'];
$datarest = $result2['J_DataRestoredYN'];
$quote = $result2['J_Quote'];
$comms = $result2['J_Comms'];
$dateworkcomm = $result2['J_DateWorkComm'];
$datecollec = $result2['J_DateCollec'];
}//end while
$range = 3;
// if not on page 1, don't show back links
if ($currentpage > 1) {
// show << link to go back to page 1
echo "<a href=\"{$_SERVER['PHP_SELF']}?currentpage={1}&sCriteria2={$criteria}&nature={$nature}\">First </a>";
// get previous page num
$prevpage = $currentpage - 1;
// show < link to go back to 1 page
echo "<a href=\"{$_SERVER['PHP_SELF']}?currentpage={$prevpage}&sCriteria2={$criteria}&nature={$nature}\">Previous</a>";
} // end if
// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
// if it's a valid page number...
if (($x > 0) && ($x <= $totalpages)) {
// if we're on current page...
if ($x == $currentpage) {
// 'highlight' it but don't make a link
echo " [<b>$x</b>] ";
// if not current page...
} else {
// make it a link
echo "<a href=\"{$_SERVER['PHP_SELF']}?currentpage={$x}&sCriteria2={$criteria}&nature={$nature}\">$x</a>";
} // end else
} // end if
} // end for
// if not on last page, show forward and last page links
if ($currentpage != $totalpages) {
// get next page
$nextpage = $currentpage + 1;
// echo forward link for next page
echo "<a href=\"{$_SERVER['PHP_SELF']}?currentpage={$nextpage}&sCriteria2={$criteria}&nature={$nature}\">Next </a>";
// echo forward link for lastpage
echo "<a href=\"{$_SERVER['PHP_SELF']}?currentpage={$totalpages}&sCriteria2={$criteria}&nature={$nature}\">Last</a>";
} // end if
?>