1 year ago

Laravel Pagination - changing the page makes my query vanish?

Posted 1 year ago by uccdev

So I'm writing a page using Laravel that calls data from my database. First, lemme describe & show you my code, with the problem stated below it.

The database returns all its data in the form of a paginated table.

The user is given five fields and a submit box. Every field is optional, so they can be as specific as they like in their query. If no values at all are called, then it is a general search of the database.

My Controller's query looks like so:

 $postmen = DB::table('postmen')
   ->join('cats', 'postmen.cat_id', '=', 'cats.cat_id')
   ->join('reg_status', 'postmen.status_id', '=', 'reg_status.status_id')
   ->select('postmen.postmen_id', 'postmen.forename', 'postmen.surname',
    'cats.description AS catdesc', 'reg_status.description AS regdesc')
    ->where(function ($query) use ($request) {
      if (!empty($request->forename)) {
        $query->where('postmen.forename', $request->forename);
      if (!empty($request->surname)) {
        $query->where('postmen.surname', $request->surname);
      if (!empty($request->cat_id)) {
        $query->where('postmen.cat_id', $request->cat_id);
      if (!empty($request->status_id)) {
        $query->where('postmen.status_id', $request->status_id);
      if (!empty($request->postmen_id)) {
        $query->where('postmen.postmen_id', $request->postmen_id);

Now, the above code works, and returns for me a query with the values I want.

So, on the view side? I have a table that gives me the following:

    <form action="searchCriteria" method="get">
           <label for="forename">Forename: </label>
           <input type="text" name="forename">

           <label for="surname">Surname: </label>
           <input type="text" name="surname">

          <label for="cat_id">Cat: </label>
            <select name="cat_id">
              <option name="---" value=0>---</option>
              <option name="Black" value=1>Black Cat</option>
              <option name=”White” value=2>White Cat</option>
              <option name=”BlackWhite” value=3>Black and White Cat</option>
          <label for="status">Status: </label>
            <select name="status_id">
              <option name="---" value=0>---</option>
              <option name="Injured" value=1>Injured</option>
              <option name="Working" value=2>Working</option>
              <option name="Holiday" value=3>Holiday</option> 

           <label for="postman_id">Postman ID: </label>
           <input type="number" name="postman_id">
      <input type="submit" name="submit" value="Submit">

      <div class="form-group">
        <table id="postmanTable" data-page-length='5' cellspacing="0"
class="table table-bordered table-striped table-hover table-condensed"
        <!--Default table headers-->
          <thead class="thead-dark">
              <th scope="col">Postman ID:</th>
              <th scope="col">Name:</th>
              <th scope="col">Surname:</th>
              <th scope="col">Cat:</th>
              <th scope="col">Status:</th>
    <!--Display all the data from the query-->  
            @if( !empty($postmen))
              @foreach ($postmen as $postman)
                    <input type='submit' name='submit' value={{$postman->postman_id}}></input>
                  @if( !empty($course))
                <td>{{$postman->postman_id}} <input type='submit' name='submit' value={{$postman->postman_id}} ></input> </td>

        @if(!empty($postmen) )
          @if ($postmen->hasMorePages())
          <h4>No postmen available under this query</h4>

This also gives me the table I want, fully paginated, with paginated links to match.

So what is the problem?

It's when I try to go to a different page in the query other than the first. It thinks I should be clicking the Submit button thanks to my controller's validation (unshown) requiring the submit button be clicked.

And when I remove the validation so I can click the button as normal? Well the query completely changes, from a specific query to one that has no queries at all. If I query for all postmen with the forename "Tom", then look at page 2 of the results, I instead get every postman with any name at all, not just Tom. That shouldn't be the case.

Any advice? Such would be greatly appreciated if you could.

Please sign in or create an account to participate in this conversation.