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
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
