run query within if statement

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
sectionLeader123
Forum Commoner
Posts: 31
Joined: Fri Oct 11, 2013 8:46 am

run query within if statement

Post by sectionLeader123 »

I have a query that executes when the users submits their criterion entered to the form. The first criterion they enter is customer forename and the other is that they select the nature of their query which is either Business or Consumer through two radio buttons(one for each). When the criterion is submitted the value for nature is also passed to the form which will either be Business or Consumer.

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
?>
Post Reply