PHP Datagrid and SQL Express

Talk about working with different database types

Moderator: alexandrleonenko

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

Re: PHP Datagrid and SQL Express

Postby administrator » Sep 8th, '09, 19:07

Sure! :)

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

Re: PHP Datagrid and SQL Express

Postby administrator » Sep 8th, '09, 22:11

Try this:

Go to SetSqlByDbType() method and replace
case "mssql" with this one:

Code: Select all

case "mssql":   // mssql
        $select_top = isset($matches[0][1]) ? $matches[0][1] : "";
        if($select_top != "" && $over_order_by != ""){
            $select_index = strpos(Helper::ConvertCase($sql,"lower",$this->langName), "select ");
            $from_index   = strpos(Helper::ConvertCase($sql,"lower",$this->langName), "from ");
            $where_index  = strpos(Helper::ConvertCase($sql,"lower",$this->langName), "where ");
            $table_name   = substr($sql, $from_index+4, $where_index-$from_index-4);
            $fields_list  = substr($sql, $select_index+6, $from_index-$select_index-6);
                   
            $sql_string = "SELECT * FROM ( ";
            $sql_string .= " SELECT TOP ".$select_top." ROW_NUMBER() OVER (".$order_by.") AS RowNumber, ".$fields_list." FROM ".$table_name;
            $sql_string .= ") as foo ";
            $sql_string .= "WHERE 1 = 1 ";
            $sql_string .= $limit." ".$order_by;
        }
break; 

exodus
Newbie
Newbie
Posts: 7
Joined: Sep 5th, '09, 16:27

Re: PHP Datagrid and SQL Express

Postby exodus » Sep 9th, '09, 18:58

hey admin!

thx for the quick reply...

unfortunately i've been too held up at work to try this out... i will give this a shot when i can and let you know how this turns out...

peace!

exodus
Newbie
Newbie
Posts: 7
Joined: Sep 5th, '09, 16:27

Re: PHP Datagrid and SQL Express

Postby exodus » Sep 10th, '09, 16:07

Hey admin!

I did try out what you said, but, I'm still having error messages!

Here is the result of the little experiment:
Errors:
1)
[nativecode=0 - Changed database context to 'monroe'.]

2)
[nativecode=0 - Changed database context to 'monroe'.]

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


I've double checked this carefully, even tried it on 2 or 3 tables. For my table that particular key is the primary key, so something else is off!

Here is the code again for reference:

Code: Select all

<?php
    // we need this if we want to prevent FF sending double request
    header("content-type: text/html; charset=utf-8");
       
   
    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');
   
    $DB_USER='user';            /* usually like this: prefix_name             */
    $DB_PASS='pass';                /* must be already encrypted (recommended)    */
    $DB_HOST='xxx.xxx.xxx.xxx';       /* usually localhost                          */
    $DB_NAME='monroe';          /* usually like this: prefix_dbName           */
   
    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()); } 
   
    $sql = "SELECT CallsID, RunID, ParseDate, Phone FROM Calls;";
     
    $debug_mode = true;        /* display SQL statements while processing */   
    $meessaging = true;          /* display system messages on a screen */
    $unique_prefix = "abc_";    /* prevent overlays - must be started with a letter */
    $dgrid = new DataGrid($debug_mode, $messaging, $unique_prefix, DATAGRID_DIR);
   
    $default_order_field = "ParseDate"; /* Ex.: field_name_1 [, field_name_2...] */
    $default_order_type = "ASC";           /* Ex.: ASC|DESC [, ASC|DESC...] */
    $dgrid->DataSource($db_conn, $sql, $default_order_field, $default_order_type);      
   
    $ajax_option = true;
    $dgrid->AllowAjax($ajax_option);
   
    $dg_caption = "ITG Test File";
    $dgrid->SetCaption($dg_caption);
   
    $scrolling_option = true;
    $dgrid->AllowScrollingSettings($scrolling_option); 
   
    $scrolling_width = "90%";
    $scrolling_height = "100%";
    $dgrid->SetScrollingSettings($scrolling_width, $scrolling_height);
   
    $multirow_option = false;
    $dgrid->AllowMultirowOperations($multirow_option);
   
    $printing_option = true;
    $dgrid->AllowPrinting($printing_option);
    $exporting_option = true;
    $exporting_directory = "";               
    $export_all = false;
    $dgrid->AllowExporting($exporting_option, $exporting_directory, $export_all);
    $exporting_types = array("excel"=>"true", "pdf"=>"true", "xml"=>"true");
    $dgrid->AllowExportingTypes($exporting_types);
   
    $sorting_option = true;
    $dgrid->AllowSorting($sorting_option);
                   
    $filtering_option = true;
    $show_search_type = true;
    $dgrid->AllowFiltering($filtering_option, $show_search_type);
   
    $filtering_fields = array(
    "CallsID"=>array("type"=>"textbox", "table"=>"Calls", "field"=>"CallsID", "filter_condition"=>"", "show_operator"=>"false", "default_operator"=>"=", "case_sensitive"=>"false", "comparison_type"=>"string|numeric|binary", "width"=>"", "on_js_event"=>""),
    );
    $dgrid->SetFieldsFiltering($filtering_fields);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
    <title>Site :: Home</title>
    <meta http-equiv=Content-Type content="text/html; charset=utf-8">
    <?php
        $dgrid->WriteCssClass();
    ?>
</head>

<body>
<?php
    $dgrid->Bind();       
    ob_end_flush();   
?>
</body>
</html>

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

Re: PHP Datagrid and SQL Express

Postby administrator » Sep 10th, '09, 21:08

What do you see with $debug=true; ?

exodus
Newbie
Newbie
Posts: 7
Joined: Sep 5th, '09, 16:27

Re: PHP Datagrid and SQL Express

Postby exodus » Sep 10th, '09, 22:07

That quoted text in my previous post with the errors and warnings were the only things I saw. If you'd like I can take a screenshot and put it up.

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

Re: PHP Datagrid and SQL Express

Postby administrator » Sep 11th, '09, 19:30

ok, we'll check it.

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

Re: PHP Datagrid and SQL Express

Postby administrator » Sep 12th, '09, 10:05

Try this:

Code: Select all

case "mssql":   // mssql
    /// [#0008] - START
    $select_top = isset($matches[0][1]) ? $matches[0][1] : "";
    if($select_top != ""){
        $select_index = strpos(Helper::ConvertCase($sql,"lower",$this->langName), "select ");
        $from_index   = strpos(Helper::ConvertCase($sql,"lower",$this->langName), "from ");
        $where_index  = strpos(Helper::ConvertCase($sql,"lower",$this->langName), "where ");
        $table_name   = substr($sql, $from_index+4, $where_index-$from_index-4);
        $fields_list  = substr($sql, $select_index+6, $from_index-$select_index-6);
       
        $sql_string = "SELECT * FROM ( ";
        $sql_string .= " SELECT TOP ".$select_top." ROW_NUMBER() OVER (".$order_by.") AS RowNumber, ".$fields_list." FROM ".$table_name;
        $sql_string .= ") as foo ";
        $sql_string .= "WHERE 1 = 1 ";
        $sql_string .= $limit." ".$order_by;
    }
    /// [#0008] - END
    break;         

bono056
Newbie
Newbie
Posts: 1
Joined: May 16th, '10, 12:20

Re: PHP Datagrid and SQL Express

Postby bono056 » May 16th, '10, 12:24

i have the same problem with datagrid & mssql 2000. i test ur last fix mentioned here (SetSqlByDbType) but it didnt work out. i trace the code & it doesnt enter in if part. ($select_top="")
& the error has shows is:

Code: Select all

search sql (total: 0)  SELECT * FROM authors WHERE 1=1 ORDER BY au_id ASC AND RowNumber > 0 AND RowNumber < 10)
search sql (total: 0) SELECT * FROM authors WHERE 1=1 ORDER BY au_id ASC AND RowNumber > 0 AND RowNumber < 10)
Errors:
1) [nativecode=0 - Changed database context to 'pubs'.]
2) [nativecode=0 - Changed database context to 'pubs'.]
3) [nativecode=0 - Changed database context to 'pubs'.]
Warnings:
1) Wrong parameter in [SELECT]: Check this field carefully, it may be not a Primary Key!
POST: Array ( )
GET: Array ( )


& if it helps, my sample code is something like this:

Code: Select all

  $DB_USER='sa';           
  $DB_PASS='';           
  $DB_HOST='192.168.0.201';       
  $DB_NAME='pubs';   

ob_start();

  $db_conn = DB::factory('mssql');
  $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()); } 
  $sql=" SELECT * FROM authors ";
   
  $debug_mode = true;
  $messaging = true;
  $unique_prefix = "f_"; 
  $dgrid = new DataGrid($debug_mode, $messaging, $unique_prefix, DATAGRID_DIR);
  $default_order_field = "au_id";
  $default_order_type = "ASC";
  $dgrid->dataSource($db_conn, $sql, $default_order_field, $default_order_type);      

   $dgrid->setAutoColumnsInViewMode(true); 


    $dgrid->bind();       
    ob_end_flush();


AndyL
Junior
Junior
Posts: 22
Joined: May 18th, '10, 12:41

Re: PHP Datagrid and SQL Express

Postby AndyL » May 18th, '10, 12:52

Has this problem been fixed? I'm looking to buy the grid, but as it has to work with SQL Server, this is pretty important to me.

I have downloaded the free edition to evaluate, and made the suggested changes to SetSqlByDbType, but I get the same errors as reported above.

It works beautifully with MySQL, but I don't have the luxury of using that!


Return to “Databases: different types implementation”