echo out the query for each page and post it here, that will likely lead to the problem.
I just checked it with a ‘Terminated’ contract ordering as there only 1 and the contract didnt show, so looked into it and as I have set it so that 2 contracts are to be displayed per page if anything that would have fallen onto the second page has to now appear on the first page, it doesnt happen.
They are still odering by how the are in the database if you can see what I mean.
case “AllContractsOld-New”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} Order by ID ASC
case “AllContractsNew-Old”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} Order by ID DESC
case “AllContractsOnHold”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Contract_Status=‘On Hold’) Order by Contract_Number
case “AllContractsActive”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Contract_Status=‘Active’) Order by Contract_Number
case “AllContractsTerminated”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Contract_Status=‘Terminated’) Order by Contract_Number
case “ByCountryA-Z”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} Order by Country ASC
case “ByCountryZ-A”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} Order by Country DESC
case “ByRegionA-Z”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} Order by Region ASC
case “ByRegionZ-A”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} Order by Region DESC
case “ByNumberModulesO-X”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} Order by No_of_Modules ASC
case “ByNumberModulesX-O”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} Order by No_of_Modules DESC
case “ByContractValueLow-High”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value ASC
case “ByContractValueHigh-Low”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value DESC
case “ByFoodCheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Food_Safety=‘1’) Order by Contract_Number ASC
case “ByPoolCheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Swimming_Pool_Safety=‘1’) Order by Contract_Number ASC
case “ByAquaCheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Legionella_Safety=‘1’) Order by Contract_Number ASC
case “ByFireCheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Fire_Safety=‘1’) Order by Contract_Number ASC
case “ByCrisisCheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Hurricane=‘1’) Order by Contract_Number ASC
case “ByEcoCheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Green=‘1’) Order by Contract_Number ASC
case “BySafetyCheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Guest=‘1’) Order by Contract_Number ASC
case “ByRoomCheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Room_Safety=‘1’) Order by Contract_Number ASC
case “ByQualityCheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Quality_Safety=‘1’) Order by Contract_Number ASC
case “BySupplyCheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Supply_Safety=‘1’) Order by Contract_Number ASC
case “ByTourcheckYes”:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} AND (Tourcheck=‘1’) Order by Contract_Number ASC
Default:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE {limit clause} order by ID DESC
Sorry, probably should have been a bit more specific on location and what I would need to see.
Change
$stmt = sqlsrv_query($conn, $query);
To
echo $query;
$stmt = sqlsrv_query($conn, $query);
For example, just pick one search type (one that filters the results), then visit page 1, copy the query and paste it here, visit page 2, copy the query, and paste it here.
Sorry mate, OK have choosen the ‘Contract Value Low High’
Here is the echo of Page 1:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 Order by Annual_Contract_Value DESC
Page 2:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE RowNumber BETWEEN 3 AND 4 order by ID DESC
Just to point it out, the way they are order in the database is:
DOM00001 - 16800
DOM00002 - 1000
DOM00003 - 3000
DOM00004 - 0
EGY00001 - 2000
And this is how they appear in the pageing
Page 1:
DOM00002 - 1000
DOM00001 - 16800
Page 2:
DOM00004 - 0
DOM00003 - 3000
Okay, first thing you should notice is that the ORDER BY clause is different for both pages.
Page 1 orders by Annual_Contract_Value and Page 2 orders by ID.
So what that means, is you need to some how persist the $search value across each page so it knows to sort it the correct way.
One way would be to pass it along with each page
Find
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
Replace with
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1&Search=$search'><<</a> ";
Find
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
Replace with
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage&Search=$search'><</a> ";
Find
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
Replace with
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x&Search=$search'>$x</a> ";
Find
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
Replace with
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage&Search=$search'>></a> ";
Find
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
Replace with
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages&Search=$search'>>></a> ";
Right I see yes.
I did that, and it carried through search, but its didnt appear to re-adjust the ordering for each page.
I tested it with the contract value low high ordering and the results where the same.
Okay, give me the query outputs again, and also records that are being displayed (their Contract_Number and their Annual_Contract_Value)
OK Have gone for High to Low Contract Values again, and here is the echo for
Page 1:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 Order by Annual_Contract_Value DESC
Page 2:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE RowNumber BETWEEN 3 AND 4 order by ID DESC
The ordering is:
Page 1:
DOM00001 - 16800
DOM00002 - 1000
Page 2:
DOM00004 - 0
DOM00003 - 3000
Okay, here is where it gets complicated again (also note that your $search variable was persisted on Page 2…)
You need to update the WITH LIMIT statement to (ORDER BY Annual_Contract_Value DESC)
Example:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Annual_Contract_Value DESC) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’)
This way it will number the records based on how you want to receive them. Likewise, if you have any where clauses (example: (Quality_Safety=‘1’)), you need to add that to the LIMIT statement too, like so:
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1'))
select * from LIMIT WHERE {limit clause} AND (Quality_Safety='1') Order by Contract_Number ASC";
Nearly there I think.
I changed to your code for ‘case “ByContractValueLow-High”:’
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Annual_Contract_Value ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value ASC";
break;
It seemed to work as the ordering on page 1 changed but when I clicked page 2 it changed to order by ID instead by the looks, and it put one in from the front page and left one out completely
So put the echo back in for contract value low high and this is what i got and the ordering below
Page 1:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Annual_Contract_Value ASC) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE RowNumber BETWEEN 1 AND 2 Order by Annual_Contract_Value ASC
Page 2:
WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS ‘RowNumber’ FROM Intranet WHERE Contract_Number like ‘DOM%’) select * from LIMIT WHERE RowNumber BETWEEN 3 AND 4 order by ID DESC
Order is:
Page 1
DOM00004 - 0
DOM00002 - 1000
Page 2
DOM00004 - 0
DOM00003 - 3000
sorry I think I did it wrong cpradio. I will see if I can sort this out and will leave you be now for the day.
Thanks for everything again
One thing I noticed, was when the page 2 button is clicked it is reading from the default query and not its named query.
It reads this one, thats why page 2 is ordering by id again, but it should go back and read the query of its category shouldnt it.
This one:
default:
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} order by ID DESC";
break;
Good night cpradio, you have been awsome.
When you get to work tomorrow, paste the code where $search is defined, so I can see why going to Page 2 runs the default query.
Hi cpradio,
Do you mean the code below:
$search="";
if(isset($_POST['btnsubmit']))
{
$search=$_POST['Search'];
}
Yep,
Change it to
$search="";
if(isset($_POST['btnsubmit']))
{
$search=$_POST['Search'];
}
else if(isset($_GET['Search']))
{
$search=$_GET['Search'];
}
Ah, i was walking the dog last night and I thought I know this one and i thought it was that I had to use GET, so I tried it this morning and it didnt work, but I didnt put the rest of the code around it such as isset, so I nearly didnt have to bother you with it.
It worked thugh cpradio and I think thats that bit done. I only changed the code to yours for the contract Low-High query, so will change the others but that one def worked and I have to say you have been awsome, so thank you very much.
All I can do to give back to the forum is keep my eye on the threads and chip in if I think I have an answer to somebodys problem to return the kindness.
Fingers crossed I wont have anymore problems with the pageing, but if I do LOL I will reply.
Thanks again
Not a problem, glad I could help out.
Hi cpradio and others me again, and I’m going to carry on with this thread as it is related.
All the above works perfectly, and so i moved onto the next thing which was to build a keyword table search option for the user.
My issue at this stage isnt how to build it, as as you will see below i think i got that, its how its built into the code so that its read and actioned whilst ignoring everything else on th epage, and if not used the page displays the categories as normal.
Here is what I got so far:
$searchKeyword="";
// Get the search variable from URL
if(isset($_GET['q']))
{
$searchKeyword = @$_GET['q'];
$trimmed = $searchKeyword; //trim whitespace from the stored variable
//$trimmed = trim($var); //trim whitespace from the stored variable
//$trimmed = preg_replace('~[^a-z]~i', null, $trimmed);
//echo $trimmed;
} else {
$trimmed = trim($searchKeyword); //trim whitespace from the stored variable
$trimmed = preg_replace('~[^a-z]~i', null, $trimmed);
}
<form name="form2" action="contracts.php" method="get" style="position:relative; height:20px;" >
<input type="text" name="q" ></input> <input type="submit" name="searchButton" class="btn-std" value="Go">
</form>
And in honesty all is fine, so far as when I echo out the variable it picks up the search keyword fine.
Then i tried to put the code that outputs the contracts inline with my other code and I’m not getting it right.
I tried to see it as that it would go through the cases first and then pick up my searchKeyword bit and then if nothing there either output the default contract view, but its not right, and cant seem to work out where to put it and how to order it.
My new bit is at the bottom
switch($search)
{
case "AllContractsOld-New"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by ID ASC";
break;
case "AllContractsNew-Old"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by ID DESC";
break;
case "AllContractsOnHold"://complete but not tested with contracts
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='On Hold')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='On Hold'))
select * from LIMIT WHERE {limit clause} AND (Contract_Status='On Hold') Order by Contract_Number";
break;
case "AllContractsActive"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Active')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Active'))
select * from LIMIT WHERE {limit clause} AND (Contract_Status='Active') Order by Contract_Number";
break;
case "AllContractsTerminated"://complete but not tested with pageing
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Terminated')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Contract_Status='Terminated'))
select * from LIMIT WHERE {limit clause} AND (Contract_Status='Terminated') Order by Contract_Number";
break;
case "ByCountryA-Z"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by Country ASC";
break;
case "ByCountryZ-A"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Country DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by Country DESC";
break;
case "ByRegionA-Z"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Region ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by Region ASC";
break;
case "ByRegionZ-A"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Region DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by Region DESC";
break;
case "ByNumberModulesO-X"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY No_of_Modules ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by No_of_Modules ASC";
break;
case "ByNumberModulesX-O"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY No_of_Modules DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by No_of_Modules DESC";
break;
case "ByContractValueLow-High"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Annual_Contract_Value ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value ASC";
break;
case "ByContractValueHigh-Low"://complete
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Annual_Contract_Value DESC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} Order by Annual_Contract_Value DESC";
break;
case "ByFoodCheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Food_Safety='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Food_Safety='1'))
select * from LIMIT WHERE {limit clause} AND (Food_Safety='1') Order by Contract_Number ASC";
break;
case "ByPoolCheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Swimming_Pool_Safety='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Swimming_Pool_Safety='1'))
select * from LIMIT WHERE {limit clause} AND (Swimming_Pool_Safety='1') Order by Contract_Number ASC";
break;
case "ByAquaCheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Legionella_Safety='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Legionella_Safety='1'))
select * from LIMIT WHERE {limit clause} AND (Legionella_Safety='1') Order by Contract_Number ASC";
break;
case "ByFireCheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Fire_Safety='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Fire_Safety='1'))
select * from LIMIT WHERE {limit clause} AND (Fire_Safety='1') Order by Contract_Number ASC";
break;
case "ByCrisisCheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Hurricane='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Hurricane='1'))
select * from LIMIT WHERE {limit clause} AND (Hurricane='1') Order by Contract_Number ASC";
break;
case "ByEcoCheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Green='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Green='1'))
select * from LIMIT WHERE {limit clause} AND (Green='1') Order by Contract_Number ASC";
break;
case "BySafetyCheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Guest='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Guest='1'))
select * from LIMIT WHERE {limit clause} AND (Guest='1') Order by Contract_Number ASC";
break;
case "ByRoomCheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Room_Safety='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Room_Safety='1'))
select * from LIMIT WHERE {limit clause} AND (Room_Safety='1') Order by Contract_Number ASC";
break;
case "ByQualityCheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Quality_Safety='1'))
select * from LIMIT WHERE {limit clause} AND (Quality_Safety='1') Order by Contract_Number ASC";
break;
case "BySupplyCheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Supply_Safety='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Supply_Safety='1'))
select * from LIMIT WHERE {limit clause} AND (Supply_Safety='1') Order by Contract_Number ASC";
break;
case "ByTourcheckYes"://complete but not by pageing as not enough in database
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Tourcheck='1')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY Contract_Number ASC) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%' AND (Tourcheck='1'))
select * from LIMIT WHERE {limit clause} AND (Tourcheck='1') Order by Contract_Number ASC";
break;
//default:
//$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
//$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
//select * from LIMIT WHERE {limit clause} order by ID DESC";
//break;
if ($searchKeyword == "") {
//default:
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} order by ID DESC";
//break;
break;
} else {
echo "2";
}
}
OK got one bit working by adding the default: before the if statement, and that seemed to allow the default contract to display if no search has been done.
default:
if ($searchKeyword == "") {
$sql = "SELECT COUNT(*) FROM Intranet WHERE Contract_Number like '{$contractLike}%'";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE Contract_Number like '{$contractLike}%')
select * from LIMIT WHERE {limit clause} order by ID DESC";
break;
} else {
}
so if i can get the keyword search to work, is this the right way to deal with contracts being displayed either by using the case, or display depending if there is or isnt a keyword search being done