From: "David Stoltz" on
Wow - thanks for the code, but it's over my head at this point.

I'm a PHP newbie....I typically use ASP Classic, but I realize I need to learn PHP for ongoing development. Problem is, we don't have MySQL here, so I have to fumble my way through with MS SQL.

Thanks!


-----Original Message-----
From: hack988 hack988 [mailto:hack988(a)dev.htwap.com]
Sent: Wednesday, August 26, 2009 10:13 AM
To: ash(a)ashleysheridan.co.uk
Cc: David Stoltz; php-general(a)lists.php.net
Subject: Re: [PHP] How to output a NULL field?

My code for mssql
please enable the php's mssql extentions.
it used like so many mysql class that you can find by google
------------------------------------------------------------------------------------------------------------------
<?php
if(!defined('IN_WEB')) {
exit('Access Denied');
}
ini_set('mssql.datetimeconvert',0);//php>4.2.0 disable php's automatic
datetime convert
Class DB {
var $querynum=0;
var $mssql_link;
var $conn_link;
var $sp_link;
var $sp_name='';
var $error_stop=0;
var $show_error=0;
var $dbhost;
var $dbuser;
var $dbpw;
var $dbname;
var $pconnect;
var $var_type=array();
var $fields_name=array();
var $last_error_msg='';
var $phprunversion='';
function DB() {
//define type for sp
$this->var_type['sp_bit']=SQLBIT;
$this->var_type['sp_tinyint']=SQLINT1;
$this->var_type['sp_smallint']=SQLINT2;
$this->var_type['sp_int']=SQLINT4;
$this->var_type['sp_bigint']=SQLVARCHAR;
$this->var_type['sp_real']=SQLFLT4;
$this->var_type['sp_float']=SQLFLT8;
$this->var_type['sp_float-null']=SQLFLTN;
$this->var_type['sp_smallmoney']=SQLFLT8;
$this->var_type['sp_money']=SQLFLT8;
$this->var_type['sp_money-null']=SQLFLT8;
$this->var_type['sp_char']=SQLCHAR;
$this->var_type['sp_varchar']=SQLVARCHAR;
$this->var_type['sp_text']=SQLTEXT;
$this->var_type['sp_datetime']=SQLINT4;
$this->phprunversion=phpversion();
//end
}
/*>=php4.4.1,>=php5.1.1
a new paramate for if use newlink for connect,pconnect
*/
function rconnect($newlink=false){//2007.03.01 by hack988 fix phpversion check
if($this->phprunversion >= '4.4.1' && $this->phprunversion < '5.0.0'
|| $this->phprunversion >= '5.1.1'){
return $this->rconnect4p($newlink);
}else{
return $this->rconnect3p();
}
}
function rconnect3p(){
$this->mssql_link = $this->pconnect==0 ?
mssql_connect($this->dbhost, $this->dbuser, $this->dbpw) :
mssql_pconnect($this->dbhost, $this->dbuser, $this->dbpw);
if(!$this->mssql_link){
$this->halt("connect
($this->pconnect)MSSQL($this->dbhost,$this->dbuser)failed!");
return false;
}else{
$this->conn_link=$this->mssql_link;
if($this->dbname) {
if (!@$this->select_db($this->dbname,$this->conn_link)){
$this->halt('can not use database '.$this->dbname);
return false;
}else{
return true;
}
}else{
return true;
}
}
}
function rconnect4p($newlink=false){
$this->mssql_link = $this->pconnect==0 ?
mssql_connect($this->dbhost, $this->dbuser, $this->dbpw , $newlink) :
mssql_pconnect($this->dbhost, $this->dbuser, $this->dbpw, $newlink);
if(!$this->mssql_link){
$this->halt("reconect($this->pconnect)MSSQL($this->dbhost,$this->dbuser)failed");
return false;
}else{
$this->conn_link=$this->mssql_link;
if($this->dbname) {
if (!@$this->select_db($this->dbname,$this->conn_link)){
$this->halt('can not use database '.$this->dbname);
return false;
}else{
return true;
}
}else{
return true;
}
}
}

function connect($dbhost, $dbuser, $dbpw, $dbname, $pconnect =
0,$auto_conn=0 ,$newlink=false) {
$this->dbhost=$dbhost;
$this->dbuser=$dbuser;
$this->dbpw=$dbpw;
$this->dbname=$dbname;
$this->pconnect=$pconnect;
if($auto_conn){
return $this->rconnect($newlink);
}else{
return true;
}
}

function close() {
if($this->conn_link){
$result=mssql_close($this->conn_link);
}else{
$result=true;
}
$this->mssql_link='';
$this->sp_link='';
$this->conn_link='';
return $result;
}

function select_db($dbname){
$this->mssql_link=mssql_select_db("[".$dbname."]");
return $this->mssql_link;
}

function query($SQL,$method='') {
if($method=='UNBUFFERED'){
mssql_query("SET NOCOUNT ON",$this->conn_link);
$this->mssql_link = mssql_query($SQL,$this->conn_link);
mssql_query("SET NOCOUNT OFF",$this->conn_link);
}else{
$this->mssql_link = mssql_query($SQL,$this->conn_link);
}

if (!$this->mssql_link) $this->halt('SQL query error: ' . $SQL);
$this->querynum++;
return $this->mssql_link;
}

function get_one($sql,$prefix=""){
$query=$this->query($sql,'UNBUFFERED');
if(strlen($prefix)>0){
$this->get_fields_name();
$rs=$this->fetch_duplicate_array($query);
}else{
$rs = $this->fetch_array($query, MSSQL_ASSOC);
}
return $rs;
}

function seek($num,$link=''){
$link = empty($link) ? $this->mssql_link : $link;
return mssql_data_seek($link,$num);
}

function fetch_array($query, $result_type = MSSQL_ASSOC) {
return mssql_fetch_array($query, $result_type);
}

function fetch_duplicate_array($query, $prefix="dup_") {
if(count($this->fields_name)<1) return false;
$fields=$this->fetch_array($query, MYSQL_NUM);
if(!$fields) return false;
$reternfields=array();
foreach($fields AS $key=>$value){
if(isset($reternfields[$this->fields_name[$key]]))
$reternfields[$prefix.$this->fields_name[$key]]=$value;
else
$reternfields[$this->fields_name[$key]]=$fields[$key];
}
return $reternfields;
}

function affected_rows($link='') {
$link= empty($link) ? $this->conn_link :$link;
return mssql_rows_affected($link);
}

function get_fields_name($link=''){
$link= empty($link) ? $this->mssql_link :$link;
$fieldscount=$this->num_fields($link);
for($i=0;$i<$fieldscount;$i++){
$field[$i]=mssql_field_name($this->mssql_link,$i);
}
$this->fields_name=$field;
}

function num_rows($link='') {
$link = empty($link) ? $this->mssql_link : $link;
$rows = mssql_num_rows($link);
return $rows;
}

function num_fields($query) {
return mssql_num_fields($query);
}

function sp_init($sp_name){
$this->sp_link=mssql_init($sp_name,$this->conn_link);
!$this->sp_link && $this->halt('Init PROCEDURE Failed :' . $sp_name);
$this->sp_name=$sp_name;
return $this->sp_link;
}

function sp_bind($parameter,&$var,$type,$if_out=false,$if_null=false){
if(!$this->sp_link){
$this->halt('Can not bind var for PROCEDURE' . $parameter);
return false;
}else{
if(!mssql_bind($this->sp_link,$parameter,$var,$this->var_type[$type],$if_out,$if_null)){
$this->halt('PROCEDURE var binding failed' .
$parameter.$this->var_type[$type]);
return false;
}else{
return true;
}
}
}

function sp_execute($skipout,$sp_link=''){
$this->sp_link=$sp_link? $sp_link : $this->sp_link;
$this->mssql_link=mssql_execute($this->sp_link,$skipout);
if (!$this->mssql_link) $this->halt('exec PROCEDURE failed: ' . $SQL);
$this->querynum++;
return $this->mssql_link;
}

function sp_free_statement($sp_link=''){
$this->sp_link=$sp_link? $sp_link : $this->sp_link;
if(!mssql_free_statement($this->sp_link)){
$this->halt('free memory failed (PROCEDURE)$B!'(J'.$this->sp_name);
return false;
}else{
$this->sp_link='';
return true;
}
}

function free_result($query) {
return mssql_free_result($query);
}

function insert_id() {
$rs = $this->get_one("SELECT @@IDENTITY AS [insertid]");
if($rs)
return $rs['insertid'];
else
return false;
}

function halt($msg='') {
if($this->show_error){
echo date("Y-m-d H:i:s",time())."<br/>\n";
echo $msg."<br/>\n";
$this->last_error_msg="SQL Server
Msg:".nl2br(mssql_get_last_message())."<br/>\n";
echo $this->last_error_msg;
$this->last_error_msg = $msg.$this->last_error_msg;
}
$this->error_stop && exit;
}
}
?>
From: Ashley Sheridan on
On Wed, 2009-08-26 at 10:50 -0400, David Stoltz wrote:
> Wow - thanks for the code, but it's over my head at this point.
>
> I'm a PHP newbie....I typically use ASP Classic, but I realize I need to learn PHP for ongoing development. Problem is, we don't have MySQL here, so I have to fumble my way through with MS SQL.
>
> Thanks!
>
>
> -----Original Message-----
> From: hack988 hack988 [mailto:hack988(a)dev.htwap.com]
> Sent: Wednesday, August 26, 2009 10:13 AM
> To: ash(a)ashleysheridan.co.uk
> Cc: David Stoltz; php-general(a)lists.php.net
> Subject: Re: [PHP] How to output a NULL field?
>
> My code for mssql
> please enable the php's mssql extentions.
> it used like so many mysql class that you can find by google
> ------------------------------------------------------------------------------------------------------------------
> <?php
> if(!defined('IN_WEB')) {
> exit('Access Denied');
> }
> ini_set('mssql.datetimeconvert',0);//php>4.2.0 disable php's automatic
> datetime convert
> Class DB {
> var $querynum=0;
> var $mssql_link;
> var $conn_link;
> var $sp_link;
> var $sp_name='';
> var $error_stop=0;
> var $show_error=0;
> var $dbhost;
> var $dbuser;
> var $dbpw;
> var $dbname;
> var $pconnect;
> var $var_type=array();
> var $fields_name=array();
> var $last_error_msg='';
> var $phprunversion='';
> function DB() {
> //define type for sp
> $this->var_type['sp_bit']=SQLBIT;
> $this->var_type['sp_tinyint']=SQLINT1;
> $this->var_type['sp_smallint']=SQLINT2;
> $this->var_type['sp_int']=SQLINT4;
> $this->var_type['sp_bigint']=SQLVARCHAR;
> $this->var_type['sp_real']=SQLFLT4;
> $this->var_type['sp_float']=SQLFLT8;
> $this->var_type['sp_float-null']=SQLFLTN;
> $this->var_type['sp_smallmoney']=SQLFLT8;
> $this->var_type['sp_money']=SQLFLT8;
> $this->var_type['sp_money-null']=SQLFLT8;
> $this->var_type['sp_char']=SQLCHAR;
> $this->var_type['sp_varchar']=SQLVARCHAR;
> $this->var_type['sp_text']=SQLTEXT;
> $this->var_type['sp_datetime']=SQLINT4;
> $this->phprunversion=phpversion();
> //end
> }
> /*>=php4.4.1,>=php5.1.1
> a new paramate for if use newlink for connect,pconnect
> */
> function rconnect($newlink=false){//2007.03.01 by hack988 fix phpversion check
> if($this->phprunversion >= '4.4.1' && $this->phprunversion < '5.0.0'
> || $this->phprunversion >= '5.1.1'){
> return $this->rconnect4p($newlink);
> }else{
> return $this->rconnect3p();
> }
> }
> function rconnect3p(){
> $this->mssql_link = $this->pconnect==0 ?
> mssql_connect($this->dbhost, $this->dbuser, $this->dbpw) :
> mssql_pconnect($this->dbhost, $this->dbuser, $this->dbpw);
> if(!$this->mssql_link){
> $this->halt("connect
> ($this->pconnect)MSSQL($this->dbhost,$this->dbuser)failed!");
> return false;
> }else{
> $this->conn_link=$this->mssql_link;
> if($this->dbname) {
> if (!@$this->select_db($this->dbname,$this->conn_link)){
> $this->halt('can not use database '.$this->dbname);
> return false;
> }else{
> return true;
> }
> }else{
> return true;
> }
> }
> }
> function rconnect4p($newlink=false){
> $this->mssql_link = $this->pconnect==0 ?
> mssql_connect($this->dbhost, $this->dbuser, $this->dbpw , $newlink) :
> mssql_pconnect($this->dbhost, $this->dbuser, $this->dbpw, $newlink);
> if(!$this->mssql_link){
> $this->halt("reconect($this->pconnect)MSSQL($this->dbhost,$this->dbuser)failed");
> return false;
> }else{
> $this->conn_link=$this->mssql_link;
> if($this->dbname) {
> if (!@$this->select_db($this->dbname,$this->conn_link)){
> $this->halt('can not use database '.$this->dbname);
> return false;
> }else{
> return true;
> }
> }else{
> return true;
> }
> }
> }
>
> function connect($dbhost, $dbuser, $dbpw, $dbname, $pconnect =
> 0,$auto_conn=0 ,$newlink=false) {
> $this->dbhost=$dbhost;
> $this->dbuser=$dbuser;
> $this->dbpw=$dbpw;
> $this->dbname=$dbname;
> $this->pconnect=$pconnect;
> if($auto_conn){
> return $this->rconnect($newlink);
> }else{
> return true;
> }
> }
>
> function close() {
> if($this->conn_link){
> $result=mssql_close($this->conn_link);
> }else{
> $result=true;
> }
> $this->mssql_link='';
> $this->sp_link='';
> $this->conn_link='';
> return $result;
> }
>
> function select_db($dbname){
> $this->mssql_link=mssql_select_db("[".$dbname."]");
> return $this->mssql_link;
> }
>
> function query($SQL,$method='') {
> if($method=='UNBUFFERED'){
> mssql_query("SET NOCOUNT ON",$this->conn_link);
> $this->mssql_link = mssql_query($SQL,$this->conn_link);
> mssql_query("SET NOCOUNT OFF",$this->conn_link);
> }else{
> $this->mssql_link = mssql_query($SQL,$this->conn_link);
> }
>
> if (!$this->mssql_link) $this->halt('SQL query error: ' . $SQL);
> $this->querynum++;
> return $this->mssql_link;
> }
>
> function get_one($sql,$prefix=""){
> $query=$this->query($sql,'UNBUFFERED');
> if(strlen($prefix)>0){
> $this->get_fields_name();
> $rs=$this->fetch_duplicate_array($query);
> }else{
> $rs = $this->fetch_array($query, MSSQL_ASSOC);
> }
> return $rs;
> }
>
> function seek($num,$link=''){
> $link = empty($link) ? $this->mssql_link : $link;
> return mssql_data_seek($link,$num);
> }
>
> function fetch_array($query, $result_type = MSSQL_ASSOC) {
> return mssql_fetch_array($query, $result_type);
> }
>
> function fetch_duplicate_array($query, $prefix="dup_") {
> if(count($this->fields_name)<1) return false;
> $fields=$this->fetch_array($query, MYSQL_NUM);
> if(!$fields) return false;
> $reternfields=array();
> foreach($fields AS $key=>$value){
> if(isset($reternfields[$this->fields_name[$key]]))
> $reternfields[$prefix.$this->fields_name[$key]]=$value;
> else
> $reternfields[$this->fields_name[$key]]=$fields[$key];
> }
> return $reternfields;
> }
>
> function affected_rows($link='') {
> $link= empty($link) ? $this->conn_link :$link;
> return mssql_rows_affected($link);
> }
>
> function get_fields_name($link=''){
> $link= empty($link) ? $this->mssql_link :$link;
> $fieldscount=$this->num_fields($link);
> for($i=0;$i<$fieldscount;$i++){
> $field[$i]=mssql_field_name($this->mssql_link,$i);
> }
> $this->fields_name=$field;
> }
>
> function num_rows($link='') {
> $link = empty($link) ? $this->mssql_link : $link;
> $rows = mssql_num_rows($link);
> return $rows;
> }
>
> function num_fields($query) {
> return mssql_num_fields($query);
> }
>
> function sp_init($sp_name){
> $this->sp_link=mssql_init($sp_name,$this->conn_link);
> !$this->sp_link && $this->halt('Init PROCEDURE Failed :' . $sp_name);
> $this->sp_name=$sp_name;
> return $this->sp_link;
> }
>
> function sp_bind($parameter,&$var,$type,$if_out=false,$if_null=false){
> if(!$this->sp_link){
> $this->halt('Can not bind var for PROCEDURE' . $parameter);
> return false;
> }else{
> if(!mssql_bind($this->sp_link,$parameter,$var,$this->var_type[$type],$if_out,$if_null)){
> $this->halt('PROCEDURE var binding failed' .
> $parameter.$this->var_type[$type]);
> return false;
> }else{
> return true;
> }
> }
> }
>
> function sp_execute($skipout,$sp_link=''){
> $this->sp_link=$sp_link? $sp_link : $this->sp_link;
> $this->mssql_link=mssql_execute($this->sp_link,$skipout);
> if (!$this->mssql_link) $this->halt('exec PROCEDURE failed: ' . $SQL);
> $this->querynum++;
> return $this->mssql_link;
> }
>
> function sp_free_statement($sp_link=''){
> $this->sp_link=$sp_link? $sp_link : $this->sp_link;
> if(!mssql_free_statement($this->sp_link)){
> $this->halt('free memory failed (PROCEDURE):'.$this->sp_name);
> return false;
> }else{
> $this->sp_link='';
> return true;
> }
> }
>
> function free_result($query) {
> return mssql_free_result($query);
> }
>
> function insert_id() {
> $rs = $this->get_one("SELECT @@IDENTITY AS [insertid]");
> if($rs)
> return $rs['insertid'];
> else
> return false;
> }
>
> function halt($msg='') {
> if($this->show_error){
> echo date("Y-m-d H:i:s",time())."<br/>Â¥n";
> echo $msg."<br/>Â¥n";
> $this->last_error_msg="SQL Server
> Msg:".nl2br(mssql_get_last_message())."<br/>Â¥n";
> echo $this->last_error_msg;
> $this->last_error_msg = $msg.$this->last_error_msg;
> }
> $this->error_stop && exit;
> }
> }
> ?>
>
You should try and see if you can get it installed there, as it will
work on Windows servers. I've found it generally to be faster than MS
SQL, and the choice of different database engines for each table gives
you a LOT of flexibility for the future too. Also, MySQL offers a bit
more functionality I've found.

Thanks,
Ash
http://www.ashleysheridan.co.uk



From: Andrew Ballard on
On Wed, Aug 26, 2009 at 10:52 AM, Ashley
Sheridan<ash(a)ashleysheridan.co.uk> wrote:
> You should try and see if you can get it installed there, as it will
> work on Windows servers. I've found it generally to be faster than MS
> SQL, and the choice of different database engines for each table gives
> you a LOT of flexibility for the future too. Also, MySQL offers a bit
> more functionality I've found.
>
> Thanks,
> Ash
> http://www.ashleysheridan.co.uk

To be fair, I've not had found any performance problems when using SQL
Server. And, while there is one main feature MySQL has that I really
miss in SQL Server (the LIMIT clause), I miss enforcement of CHECK
constraints in MySQL. (I suppose I could implement them via triggers,
but that just seems messy to me.)

I'm all for flexibility, though.

Andrew
From: Andrew Ballard on
On Tue, Aug 25, 2009 at 3:22 PM, Shawn McKenzie<nospam(a)mckenzies.net> wrote:
> First off, if the value is NULL in the database then in PHP it will be
> the string "NULL" and not a null value as far as I remember.

I've not seen this happen. I've found, depending on the database and
the data access library used to interface with it that NULL usually
comes back as either the PHP NULL value or an empty string.

Andrew
From: hack988 hack988 on
Mysql,mssql has its own feature,you can't say Mysql is better than
Mssql or Mssql it better than Mysql,Is'nt is?
My the Way ,Mssql support Top n,m form mssql 2005 :)

2009/8/27 Andrew Ballard <aballard(a)gmail.com>:
> On Wed, Aug 26, 2009 at 10:52 AM, Ashley
> Sheridan<ash(a)ashleysheridan.co.uk> wrote:
>> You should try and see if you can get it installed there, as it will
>> work on Windows servers. I've found it generally to be faster than MS
>> SQL, and the choice of different database engines for each table gives
>> you a LOT of flexibility for the future too. Also, MySQL offers a bit
>> more functionality I've found.
>>
>> Thanks,
>> Ash
>> http://www.ashleysheridan.co.uk
>
> To be fair, I've not had found any performance problems when using SQL
> Server. And, while there is one main feature MySQL has that I really
> miss in SQL Server (the LIMIT clause), I miss enforcement of CHECK
> constraints in MySQL. (I suppose I could implement them via triggers,
> but that just seems messy to me.)
>
> I'm all for flexibility, though.
>
> Andrew
>