Paging using PHP & MSSQL

[multichild]

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

[multichild]

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

[multichild]

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.

[cpradio]

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.

[multichild]

Hi cpradio,

Do you mean the code below:

$search="";

if(isset($_POST['btnsubmit']))
{
$search=$_POST['Search'];
} 

[cpradio]

Yep,

Change it to

$search=""; 

if(isset($_POST['btnsubmit'])) 
{ 
$search=$_POST['Search']; 
}  
else if(isset($_GET['Search'])) 
{ 
$search=$_GET['Search']; 
} 

[multichild]

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

[cpradio]

Not a problem, glad I could help out.

[multichild]

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>&nbsp;<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";
        }
} 

[multichild]

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

[multichild]

OK I think i got it working with the code below, but what I noticed is that the fields I searched are the only ones that output the data, when I wanted all the details to show from the contract but only search those fields.


[php]
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 {
$sql = "SELECT COUNT(*) FROM Intranet WHERE (Contract_Number like '%{$trimmed}%' or Site_Name like '%{$trimmed}%' or Street like '%{$trimmed}%' or City like '%{$trimmed}%' or State like '%{$trimmed}%' or Country like '%{$trimmed}%' or Region like '%{$trimmed}%' or Group_Name like '%{$trimmed}%' or Type_of_Establishment like '%{$trimmed}%' or Currency_of_Invoice like '%{$trimmed}%')";
$query = "WITH LIMIT AS( SELECT *, ROW_NUMBER() OVER (ORDER BY ID) AS 'RowNumber' FROM Intranet WHERE (Contract_Number like '%{$trimmed}%' or Site_Name like '%{$trimmed}%' or Street like '%{$trimmed}%' or City like '%{$trimmed}%' or State like '%{$trimmed}%' or Country like '%{$trimmed}%' or Region like '%{$trimmed}%' or Group_Name like '%{$trimmed}%' or Type_of_Establishment like '%{$trimmed}%' or Currency_of_Invoice like '%{$trimmed}%'))
select * from LIMIT WHERE {limit clause} order by ID DESC";
break;
}
[php]

No sorry seems to be fine i think