select will not return more than 9 rows

Talk about installation process and problems

Moderator: alexandrleonenko

leean1221
Newbie
Newbie
Posts: 2
Joined: Apr 2nd, '10, 18:57

select will not return more than 9 rows

Postby leean1221 » Apr 2nd, '10, 19:23

Hi,

Please help! I think I am missing a configuration setting somewhere.

I just purchased and installed the Datagrid 6.0.7 Pro version. The example scripts in /datagrid/examples/ run fine against the test db that I created on mySQL. However, our production environment runs on MS SQL Server 2008. In that instance, I can't get back more than 9 rows of data in a table that has 13 rows. Here are the messages I'm getting (debug and message turned on):
______________________________________________________________________________

search sql (total: 9) SELECT * FROM ( SELECT TOP 10 ROW_NUMBER() OVER ( ORDER BY id ASC) AS RowNumber, id, f1, f2, f3 FROM dgtest ) as foo WHERE 1 = 1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY id ASC

search sql (total: 9) SELECT * FROM ( SELECT TOP 10 ROW_NUMBER() OVER ( ORDER BY id ASC) AS RowNumber, id, f1, f2, f3 FROM dgtest ) as foo WHERE 1 = 1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY id ASC


POST: Array ( )

GET: Array ( )

Warnings:

1) Wrong parameter in [id]: Check this field carefully, it may be not a Primary Key!
_______________________________________________________________________________

Also, the pagination controls beneath the table are not displaying..

Data comes from a simple table created for the purpose of testing the installation. I tried using our existing tables and I was getting a lot of errors so I thought I would start out simply. Note that I am getting 9 rows back from all our other tables as well.

dgtest
id int, not null, PK, auto increment
f1 nchar(10) null
f2 nchar(10) null
f3 varchar(10) null

The code is modified from your example 1_1. The only three changes were 1) db_conn to point to mssql 2) select statement 3) comment out section 7 (Add/Edit/...).

Code: Select all

<?php
## database connection variables
include_once($_SERVER['DOCUMENT_ROOT']."/lib/variables.php");

    ################################################################################   
    ## +---------------------------------------------------------------------------+
    ## | 1. Creating & Calling:                                                    |
    ## +---------------------------------------------------------------------------+
    ##  *** define a relative (virtual) path to datagrid.class.php file
    ##  *** (relatively to the current file)
    ##  *** RELATIVE PATH ONLY ***     
      define ("DATAGRID_DIR", "../");                     /* Ex.: "datagrid/" */
      define ("PEAR_DIR", "../pear/");                    /* Ex.: "datagrid/pear/" */
   
      require_once(DATAGRID_DIR.'datagrid.class.php');
      require_once(PEAR_DIR.'PEAR.php');
      require_once(PEAR_DIR.'DB.php');
   
   
    ob_start();
      $db_conn = DB::factory('mssql');  /* don't forget to change on appropriate db type */
      $result_conn = $db_conn->connect(DB::parseDSN('mssql://'.$DB_USER.':'.$DB_PASS.'@'.$DB_HOST.'/'.$DB_NAME));
      if(DB::isError($result_conn)){ die($result_conn->getDebugInfo()); }
     
    ##  *** put a primary key on the first place
 //     $sql=" SELECT " 
  //     ." demo_countries.id, "
 //      ." demo_countries.name, "
 //      ." demo_countries.description, "
 //      ." demo_countries.picture_url, "
 //      ." FORMAT(demo_countries.population, 0) as population, "   
 //      ." CASE WHEN demo_countries.is_democracy = 1 THEN 'Yes' ELSE 'No' END as is_democracy "
 //      ."FROM demo_countries ";

      $sql = "SELECT id, f1, f2, f3 FROM dgtest ";   
    ##  *** set needed options
      $debug_mode = true;
      $messaging = true;
      $unique_prefix = "abc_"; 
      $dgrid = new DataGrid($debug_mode, $messaging, $unique_prefix, DATAGRID_DIR);

    ##  *** set data source with needed options
      $default_order_field = "id";
      $default_order_type = "ASC";
      $dgrid->DataSource($db_conn, $sql, $default_order_field, $default_order_type);      
      $dg_caption = '<b>Simplest PHP DataGrid</b> - <a href=index.php>Back to Index</a>';
      $dgrid->SetCaption($dg_caption);
   
    ## +---------------------------------------------------------------------------+
    ## | 6. View Mode Settings:                                                    |
    ## +---------------------------------------------------------------------------+
    ##  *** set columns in view mode
       $dgrid->SetAutoColumnsInViewMode(true); 
   
    ## +---------------------------------------------------------------------------+
    ## | 7. Add/Edit/Details Mode settings:                                        |
    ## +---------------------------------------------------------------------------+
    ##  ***  set settings for edit/details mode
//      $table_name = "summary_talent";
//      $primary_key = "opid";
//      $condition = "";
//      $dgrid->SetTableEdit($table_name, $primary_key, $condition);
//      $dgrid->SetAutoColumnsInEditMode(false);
     
    ## +---------------------------------------------------------------------------+
    ## | 8. Bind the DataGrid:                                                     |
    ## +---------------------------------------------------------------------------+
    ##  *** set debug mode & messaging options
        $dgrid->Bind();       
        ob_end_flush();
    ################################################################################   
?>

administrator
Site Admin
Site Admin
Posts: 6001
Joined: Jan 7th, '09, 23:18
Contact:

Re: select will not return more than 9 rows

Postby administrator » Apr 4th, '10, 08:43

1. Try to un-comment Edit Mode section, define table and PK, just leave Edit Mode fields array empty (or define auto-generated fields).
2. Try to define page size 25 or something else to see what happens.

leean1221
Newbie
Newbie
Posts: 2
Joined: Apr 2nd, '10, 18:57

Re: select will not return more than 9 rows

Postby leean1221 » Apr 5th, '10, 14:58

As you've recommended, I changed the following code:

Code: Select all

    ## +---------------------------------------------------------------------------+
    ## | 4. Sorting & Paging Settings:                                             |
    ## +---------------------------------------------------------------------------+
    ##  *** set sorting option: true(default) or false
     $sorting_option = true;
     $dgrid->AllowSorting($sorting_option);               
    ##  *** set paging option: true(default) or false
     $paging_option = true;
     $rows_numeration = true;
     $numeration_sign = "N #";
     $dropdown_paging = true;
     $dgrid->AllowPaging($paging_option, $rows_numeration, $numeration_sign, $dropdown_paging);
    ##  *** set paging settings
     $bottom_paging = array("results"=>true, "results_align"=>"left", "pages"=>true, "pages_align"=>"center", "page_size"=>true, "page_size_align"=>"right");
     $top_paging = array();
     $pages_array = array("5"=>"5", "10"=>"10", "15"=>"15", "25"=>"25", "50"=>"50", "100"=>"100");
     $default_page_size = 15;
     $paging_arrows = array("first"=>"|&lt;&lt;", "previous"=>"&lt;&lt;", "next"=>"&gt;&gt;", "last"=>"&gt;&gt;|");
     $dgrid->SetPagingSettings($bottom_paging, $top_paging, $pages_array, $default_page_size, $paging_arrows);


And:

Code: Select all

    ## +---------------------------------------------------------------------------+
    ## | 7. Add/Edit/Details Mode settings:                                        |
    ## +---------------------------------------------------------------------------+
    ##  ***  set settings for edit/details mode
      $table_name = "dgtest";
      $primary_key = "id";
      $condition = "";
      $dgrid->SetTableEdit($table_name, $primary_key, $condition);
      $dgrid->SetAutoColumnsInEditMode(false);


The results are that I get one less row than what is specified in $default_page_size. Also, there are no pagination controls.

If I set $dgrid->SetAutoColumnsInEditMode(true); instead of false, no data is returned, and I get the following errors:

Notice: Object of class DB_Error could not be converted to int in /home/epongo/html/datagrid/datagrid.class.php on line 4176

search sql (total: 9) SELECT * FROM ( SELECT TOP 10 ROW_NUMBER() OVER ( ORDER BY id ASC) AS RowNumber, id, f1, f2, f3 FROM dgtest ) as foo WHERE 1 = 1 AND (RowNumber > 0 AND RowNumber < 10) ORDER BY id ASC

search sql (total: 14) SELECT * FROM ( SELECT TOP 15 ROW_NUMBER() OVER ( ORDER BY id ASC) AS RowNumber, id, f1, f2, f3 FROM dgtest ) as foo WHERE 1 = 1 AND (RowNumber > 0 AND RowNumber < 15) ORDER BY id ASC

POST: Array ( )

GET: Array ( )

Errors:

1) SELECT * FROM `dgtest`
[nativecode=102 - Incorrect syntax near '`'.]

administrator
Site Admin
Site Admin
Posts: 6001
Joined: Jan 7th, '09, 23:18
Contact:

Re: select will not return more than 9 rows

Postby administrator » Apr 6th, '10, 07:36

It seems to be a problem of ` sing, try to remove it from whole the datagrid.class.php
We'll check it too.


Return to “Installation”