Zendframework 2. ID parameter in SQL query

I kind of understand what is needed but I am new to ZF2 so just need pushing in the right direction.

I currently have a route set up, for example, viewsystem/1, which has the form [action][id].

When a person clicks on a link, they change their id, for example, viewsystem/5.

In the model where I run the SQL, I wish the id to change for the SQL statement:

->where('system.Id = "'.$id.'" ')

Can anyone explain where I can "get" the parameter and use this as a variable in the SQL? Do I need to do something in the controller? Can I not just use a $_GET or something?

I have updated this, as it is quite clear to see what is happening. The route for viewsystemAction() is different to the route of ajaxviewsystemAction().

When I use $id = (int) $this->params()->fromRoute('id', 0); inside the viewsystemAction(), it echoes back the page link id route, for example viewsystem/220

When I use $id = (int) $this->params()->fromRoute('id', 0); inside the ajaxviewsystemAction(), it echoes back the 0 as the route id.

I need the route to be passed through this function

private function getSourceViewAllSystems($id)
{   
    return $this->getSystemsTable()->fetchViewAllSystems($id);
}

public function viewsystemAction()
{
    $id = (int) $this->params()->fromRoute('id', 0);
    echo $id; //i see the correct id for example 220 from the route in the browser
}

public function ajaxviewsystemAction()
{
    $id = (int) $this->params()->fromRoute('id', 0);
    echo $id; //to see the id of the route with the ajax page
    //displays 0 and not the route id from the viewsystemAction

    $table = new TableExample\Advance();
    $table->setAdapter($this->getDbAdapter())
            ->setSource($this->getSourceViewAllSystems($id))
            ->setParamAdapter($this->getRequest()->getPost());
    return $this->htmlResponse($table->render('custom' , 'custom-b2'));
}

To try explain a bit better here is my issue.

As you can see i am passing a param as you suggested into fetchViewAllSystems($id = 1); fetchViewAllSystems is in my model and works perfectly, with the 1 there, it displays the system1.

however, the 1 needs to be the url id.

$id = (int) $this->params()->fromRoute('id', 0);

This gets the ID in the viewaction, but viewaction does not control the fetchViewAllSystems so it is quite tricky to pass this value from the url.

   private function getSourceViewAllSystems()
    {    
         return $this->getSystemsTable()->fetchViewAllSystems($id = 1);
    }

   public function viewsystemAction()
    {

         $id = (int) $this->params()->fromRoute('id', 0);
         /*if (!$id) {
             return $this->redirect()->toRoute('systems', array(
                 'action' => 'activesystems'
             ));
         }*/

         echo $id;

    }

   public function ajaxviewsystemAction()

    {
        /*$table = new TableExample\Base();
        $table->setAdapter($this->getDbAdapter())
                ->setSource($this->getSourceViewAllSystems())
                ->setParamAdapter($this->getRequest()->getPost())
        ;
        return $this->htmlResponse($table->render());*/
        $table = new TableExample\Advance();
        $table->setAdapter($this->getDbAdapter())
                ->setSource($this->getSourceViewAllSystems())
                ->setParamAdapter($this->getRequest()->getPost())
        ;
        return $this->htmlResponse($table->render('custom' , 'custom-b2'));
        echo $id;
    }

To get a $_GET params in your controller, do like this:

// your IndexController.php
public function indexAction(){
   $viewmodel = new ViewModel();

   // get the ID
   $id = $this->params('id', null); // null is my default value

   // ...

   return $viewmodel;
}

I highly recommend you to check this great example : https://github.com/akrabat/zf2-tutorial - http://zf2.readthedocs.org/en/latest/ref/overview.html

Check this line https://github.com/akrabat/zf2-tutorial/blob/master/module/Album/src/Album/Controller/AlbumController.php#L43

Get params

$id = $this->params('id', null); // null is my default value

$id = $request->query()->get('foo', 'default value');

Ref: http://zend-framework-community.634137.n4.nabble.com/ZF2-How-to-set-get-params-in-url-td4076050.html

controller.php

I don't know what getSystemsTable() returns and also fetchViewAllSystems but it should be like this

private function getSourceViewAllSystems($id = 1)
{    
     return $this->getSystemsTable()->fetchViewAllSystems($id);
}

public function viewsystemAction()
{

     $id = $this->params()->fromRoute('id', null);
     if (!$id) {
         return $this->redirect()->toRoute('systems', array(
             'action' => 'activesystems'
         ));
     }

     echo $id;

}

public function ajaxviewsystemAction()
{
    $id = $this->params()->fromRoute('id', null);
    $id = $this->params()->fromQuery()['id']; // if from ajax GET param

    $table = new TableExample\Advance();
    $table->setAdapter($this->getDbAdapter())
            ->setSource($this->getSourceViewAllSystems($id)) // send the current id
            ->setParamAdapter($this->getRequest()->getPost())
    ;
    return $this->htmlResponse($table->render('custom' , 'custom-b2'));
}