PHP Datagrid :: SQL Server 2000

Talk about working with different database types

Moderators: ne_moj, zewa, vadimako, ne_moj, zewa, vadimako

mcouche
Newbie
Newbie
Posts: 2
Joined: Nov 2nd, '09, 19:06

PHP Datagrid :: SQL Server 2000

Postby mcouche » Nov 2nd, '09, 19:14

Hello,


I am brand new in the PHP arena coming from the ASP.Net world.
I am trying to generate my first PHP Datagrid in the following environment:
    1. Windows XP
    2. Zend Studio 7.0.2
    3. SQL Server 2000
The issue I am facing is that the SQL query generated by the PHP Datagrid has a wrong order of the SELECT, WHERE and ORDER BY parts.
I saw a post in the forum (viewtopic.php?f=24&t=47&p=1283&hilit=sql+server#p1283) discussing the use of the SQL Server 2005 ROW_NUMBER() function. As this function is not available in SQL Server 2000, I use a column named RowNumber as primary field in the source table
The code and the error message as shown below:

Code: Select all

define ("DATAGRID_DIR", "datagrid/");           
define ("PEAR_DIR", "datagrid/pear/");
//
require_once(DATAGRID_DIR.'datagrid.class.php');
require_once(PEAR_DIR.'PEAR.php');
require_once(PEAR_DIR.'DB.php');
##
##  *** creating variables that we need for database connection
$DB_USER='xxxx';
$DB_PASS='xxxx';
$DB_HOST='xxxx.com';
$DB_NAME='xxxx';

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()); }

##  *** put a primary key on the first place
$sql="SELECT RowNumber, ItemID, Item_Name, Item_Description FROM BS_MenuItems"; 

##  *** set needed options
$debug_mode = true;
$messaging = true;
$unique_prefix = "f_"; 
$dgrid = new DataGrid($debug_mode, $messaging, $unique_prefix, DATAGRID_DIR);
     
     
##  *** set data source with needed options
$default_order_field = "Item_Name";
$default_order_type = "ASC";
$dgrid->DataSource($db_conn, $sql, $default_order_field, $default_order_type);      
     
##  *** set columns in view mode
$dgrid->SetAutoColumnsInViewMode(true); 
     
?>

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

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


/////////////////////////////////////////////////////////////////////////
Result of DEBUG …..
/////////////////////////////////////////////////////////////////////////
search sql (total: 0) SELECT RowNumber, ItemID, Item_Name, Item_Description FROM BS_MenuItems WHERE 1=1 ORDER BY Item_Name ASC AND RowNumber > 0 AND RowNumber < 10)

SELECT RowNumber, ItemID, Item_Name, Item_Description FROM BS_MenuItems

search sql (total: 0) SELECT RowNumber, ItemID, Item_Name, Item_Description FROM BS_MenuItems WHERE 1=1 ORDER BY Item_Name ASC AND RowNumber > 0 AND RowNumber < 10)

Errors:

1) 0
[nativecode=170 - Line 1: Incorrect syntax near '0'.]

2) 0
[nativecode=170 - Line 1: Incorrect syntax near '0'.]

3) 0
[nativecode=170 - Line 1: Incorrect syntax near '0'.]

Warnings:

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

/////////////////////////////////////////////////////////////////////////
ISSUE …..
/////////////////////////////////////////////////////////////////////////
The syntax of the “search sql” is not correct and no records are retuned. If I modify the syntax (ORDER BY after the last part of the WHERE clause) the query returns records as expected. Unfortunately, I have no idea of how to solve the syntax issue in the PHP code.


I hope that you will put me on the right track.
Thank you,

Michel

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

Re: PHP Datagrid :: SQL Server 2000

Postby administrator » Nov 5th, '09, 14:20

PHP DataGrid doesn't works with MSSQL 2000.

Try to play with SetSqlByDbType() and SetSqlLimitByDbType() to make the syntax for your needs.
If you work with small amount of records, you can try to turn off paging feature.

mcouche
Newbie
Newbie
Posts: 2
Joined: Nov 2nd, '09, 19:06

Re: PHP Datagrid :: SQL Server 2000

Postby mcouche » Nov 5th, '09, 18:51

Thank you for your prompt feedback.

Michel


Return to “Databases: different types implementation”