Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts

    run query through an if condition

    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:
    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
    ?>

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    3,763
    Thanks
    23
    Thanked 548 Times in 547 Posts
    You don't show us where $nature is set nor the form that selects the name that will set it, and that's where the problem lies. It may also be elsewhere, but the first thing that needs to be looked at is how $nature is selected and then set.
    Evolution - The non-random survival of random variants.

  • #3
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    The user keys in the criteria here, in this case it will be a forename. The user also selects the nature of their query here which is either Consumer or Business. Once the form is submitted criteria and nature get posted over to the form.

    Code:
    <form method="GET" id="form" name="form" action="querytoFormbyname.php">
    <table border="0">
    <tbody>
    <tr>
    <td align="left"><h2>Query by Customer Forename</h2></td>
    </tr>
    <tr>
    <td align="left"><label>Select Nature of this Query:</label></td>
    </tr>
    <tr>
    <td align="left">Business<input type="radio" name="nature" id="Nature1" value="Business"></td>
    </tr>
    <tr>
    <td align="left">Consumer<input type="radio" name="nature" id="Nature2" value="Consumer"></td>
    </tr>
    <tr>
    <td align="left"><label>Query By Forename:</label></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td></td>
    <td><input type="text" name="sCriteria2" id="Scriteria2" value=""/></td>
    <td><input type="submit" name="query" value="Query by Forename"/></td>
    </tr>
    </tbody>
    </table>
    </form>

  • #4
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    3,763
    Thanks
    23
    Thanked 548 Times in 547 Posts
    I verified that the problem is in the query.

    Code:
    Company.COMP_Postcode IS NULL
    This looks like the attribute of the column may not be able to be null.
    And picking IS NOT NULL is also a bad thing.
    Evolution - The non-random survival of random variants.

  • #5
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    Got it thanks, appreciate the help is used Company.COMP_Postcode = ' '
    But in the database I have two people called john. One is part of a company and the other is not. So when I execute a query by the name of John and Pick Consumer it only returns the John that's not part of a company however because there are two results it still returns a second page but it is blank. Is there any way I can put a limit on it so that it does not return the unnecessary blank page?

    Thanks Again

  • #6
    Regular Coder
    Join Date
    Aug 2013
    Posts
    141
    Thanks
    22
    Thanked 0 Times in 0 Posts
    It ok got it thanks, appreciate the guidance


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •