SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    pagination doesnot working with mssql server 2012

    this is my controller.
    public function test_unique()
    {
    $id = (isset($_GET[‘id’]) ? $_GET[‘id’] : ‘’);
    $type = (isset($_GET[‘type’]) ? $_GET[‘type’] : ‘’);
    $flag = (isset($_GET[‘flag’]) ? $_GET[‘flag’] : ‘’);

    $this->load->library(‘Mysession’);
    $this->load->helper(“url”);
    $this->load->library(“pagination”);
    $this->load->model(‘adds_model’);
    $config = array();

    $config[“base_url”]=.base_url().”?id=”.$id.”&type;=”.$type.”&flag;=”.$flag;
    $config[“total_rows”] = $this->adds_model->record_count();
    $config[“per_page”] = 10;
    $config[‘num_links’] = 10;
    $config[“uri_segment”] = 3;
    $config[‘full_tag_open’] = ‘<div id=“pagination”>’;
    $config[‘full_tag_close’] = ‘</div>’;

    $this->pagination->initialize($config);

    $this->load->view(‘common/header’);
    $this->load->view(‘common/topmenu’);
    $this->load->view(‘common/leftmenu’);
    $this->load->model(‘adds_model’);


    $page = ($this->uri->segment( 3)) ? $this->uri->segment(3) : 0;



    $data[‘get_all_impressions’] = $this->adds_model->get_unique_impressions($config[“per_page”],$page,$id,$type,$flag);


    $this->load->view(‘adds/testing’,$data );

    $this->load->view(‘common/footer’);

    }


    and model is,

    //record_count()

    public function record_count() {


    $query = “SELECT COUNT(*) as count_all FROM impressions where delete_flag = ‘N’ “;

    if ($qh = @odbc_exec($this->conn, $query))
    {
    while ( $row = @odbc_fetch_object($qh) )
    {


    $data = $row->count_all;


    }
    @odbc_free_result($qh);

    return $data;

    }
    else
    {
    return 0;
    }
    @odbc_close($this->conn);
    }


    //get_unique_impressions

    function get_unique_impressions($id,$type,$flag)
    {
    $data = array();

    if($type == ‘add’ && $flag=‘u’)
    {


    $page = 0;
    $limit = ’ OFFSET ’ . (0 + $page);

    $query = “SELECT min(id) as id, min(add_id) as add_id, min(website_id) as website_id, max(page_url) as page_url, max(date_created) as date_created, remote_ip, min(country) as country, min(campaign_id) as campaign_id, count(remote_ip) as cnt from impressions where add_id = ‘“.$id.”’ group by remote_ip ORDER BY cnt desc “. $limit.” ROWS FETCH NEXT 10 ROWS ONLY”;

    }
    elseif($type == ‘camp’ && $flag=‘u’)
    {

    //$query = “SELECT * FROM impressions where delete_flag = ‘N’ and campaign_id = ‘“.$id.”’ order by id desc “;

    $query = “SELECT min(id) as id ,min(add_id) as add_id, min(website_id) as website_id, max(page_url) as page_url, max(date_created) as date_created, remote_ip, min(country) as country, min(campaign_id) as campaign_id, count(remote_ip) as cnt from impressions where campaign_id = ‘“.$id.”’ group by remote_ip”;
    }

    if ($qh = @odbc_exec($this->conn, $query))
    {
    while ( $row = @odbc_fetch_object($qh) )
    {

    $data[] = array(“id”=>$row->id,“add_id”=>$row->add_id,“website_id”=>$row->website_id,“page_url”=>$row->page_url,“date_created”=>$row->date_created,“remote_ip”=>$row->remote_ip,“country”=>$row->country,“campaign_id”=>$row->campaign_id,“cnt”=>$row->cnt);

    }

    @odbc_free_result($qh);



    return $data;

    }
    else
    {
    return 0;
    }
    @odbc_close($this->conn);

    }


    here my problem is “. $limit.” ROWS FETCH NEXT 10 ROWS ONLY-> it shows only first 10 rows from database. and also pagination links are doesnot work. then how can i create pagination for mssql server 2012? :-(

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,401
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    I don't understand this part:

    $page = 0;
    $limit = ’ OFFSET ’ . (0 + $page);

    This way, the offset will always be 0, no? Shouldn't you take the $page number from the querystring, and then do something like
    $limit = ' OFFSET ' . ($page - 1) * 10;

  3. #3
    SitePoint Member
    Join Date
    Oct 2013
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi guido. i dont know how to create pagination for mssql server 2012. but i created pagination for mysql. please help me. i dont know what to do.

    My controller:

    public function unique_impressions()
    {
    $id = (isset($_GET['id']) ? $_GET['id'] : '');
    $type = (isset($_GET['type']) ? $_GET['type'] : '');
    $flag = (isset($_GET['flag']) ? $_GET['flag'] : '');

    $this->load->library('Mysession');
    $this->load->view('common/header');
    $this->load->view('common/topmenu');
    $this->load->view('common/leftmenu');
    $this->load->model('adds_model');
    $data['get_all_impressions'] = $this->adds_model->get_unique_impressions($id,$type,$flag);



    $this->load->view('adds/impressions-list',$data );
    $this->load->view('common/footer');

    }


    Model:


    function get_unique_impressions($id, $type, $flag)
    {
    $data = array();
    $id=1; $type='add'; $flag='u';
    if($type == 'add' && $flag='u')
    {


    $query = "SELECT min(id) as id, min(add_id) as add_id, min(website_id) as website_id, max(page_url) as page_url, max(date_created) as date_created, remote_ip, min(country) as country, min(campaign_id) as campaign_id, count(remote_ip) as cnt from impressions where add_id = '".$id."' group by remote_ip ORDER BY cnt desc ";

    }
    elseif($type == 'camp' && $flag='u')
    {

    //$query = "SELECT * FROM impressions where delete_flag = 'N' and campaign_id = '".$id."' order by id desc ";

    $query = "SELECT min(id) as id ,min(add_id) as add_id, min(website_id) as website_id, max(page_url) as page_url, max(date_created) as date_created, remote_ip, min(country) as country, min(campaign_id) as campaign_id, count(remote_ip) as cnt from impressions where campaign_id = '".$id."' group by remote_ip";
    }

    if ($qh = @odbc_exec($this->conn, $query))
    {
    while ( $row = @odbc_fetch_object($qh) )
    {

    $data[] = array("id"=>$row->id,"add_id"=>$row->add_id,"website_id"=>$row->website_id,"page_url"=>$row->page_url,"date_created"=>$row->date_created,"remote_ip"=>$row->remote_ip,"country"=>$row->country,"campaign_id"=>$row->campaign_id,"cnt"=>$row->cnt);

    }

    @odbc_free_result($qh);



    return $data;

    }
    else
    {
    return 0;
    }
    @odbc_close($this->conn);

    }

    and my base_url id: adds/unique_impressions?id=1&type=add&flag=a;


    i'm just spending two days for this. but still i cant get an answer.

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,747
    Mentioned
    64 Post(s)
    Tagged
    0 Thread(s)
    Well for starters, it doesnt matter what the variables are on your URL, because of this line:

    $id=1; $type='add'; $flag='u';

    You've now defined an ID, type, and flag, regardless of what is put in the URL.

    A quick glance at google for MSSQL paging made my head hurt; MSSQL doesn't really do a 'LIMIT' clause very well it appears.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.


Bookmarks

Posting Permissions

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