Php > Databases

A set of functions sitting on top of the abstraction layer that makes it a little

A set of functions sitting on top of the abstraction layer that makes it a little
easier to do SQL stuff

<?php /*  -*-  C++  -*-  */ 
/* 
  *  $Id:  sql.phl,v  1.5  1998/07/02  09:52:13  ssb  Exp  $ 
  */ 

/* 
  *  ABOUT: 
  * 
  *  CONFIGURATION: 
  * 
  *  You  must  set  some  global  variables  prior  to  including  this  library: 
  *  $db_type                    which  database  abstraction  layer  to  use. 
  *                                      If  your  layer  is  defined  in  "db-odbc.phl", 
  *                                      set  this  variable  to  "odbc". 
  *  $db_dsn                      Which  database  to  connect  to  (dsn=data  source  name) 
  *  $db_user                    Which  user  to  connect  as 
  *  $db_pass                    Password  used  when  connecting 
  * 
  *  Some  optional  global  variables  can  be  set: 
  *  $sql_debug                true/false,  toggles  debug  information 
  *  $sql_log_facility  how  to  log  debug  information 
  *  $sql_log_dest          debug  information  destination 
  * 
  *  $sql_log_facility  and  $sql_log_dest  are  passed  as  the  second 
  *  and  third  args  to  PHP's  error_log()  function,  respectively. 
  *  The  default  is  debugging  through  the  PHP  TCP  debugger  to 
  *  port  1400  on  localhost.    Debug  information  is  also  disabled 
  *  by  default. 
  * 
  *  The  following  global  variables  will  be  defined: 
  *  $db_connection    The  opened  connection. 
  * 
  * 
  *  USAGE: 
  * 
  *  All  you  have  to  do  is  require("sql.phl"),  either  explicitly  in  the 
  *  pages  where  you  want  to  use  it,  or  through  auto_prepend_file  somehow. 
  *  Being  a  lazy  programmer,  I  always  include  everything  I  need  with 
  *  auto-prepend  from  a  file  I  call  "config.phl"  looking  something  like 
  *  this: 
  * 
  *  <?php 
  *  $db_type  =  "odbc"; 
  *  $db_user  =  "myuser"; 
  *  $db_pass  =  "mypassword"; 
  *  $db_dsn  =  "SVT=Solid;  DSN=MyDB" 
  *  require("sql.phl"); 
  *  ?> 
  * 
  *  Once  you  have  included  this  file,  a  database  connection  will 
  *  be  opened  for  you  once  it  is  needed,  and  this  connection  will 
  *  be  used  for  all  queries.    Memory  is  freed  automatically  for 
  *  all  functions  except  sql(). 
  * 
  *  Example  use  of  all  functions:   
  * 
  *  //  returns  a  result  identifier 
  *  $res  =  sql("SELECT  *  FROM  table"); 
  * 
  *  //  returns  one  column: 
  *  $name  =  sql_one("SELECT  name  FROM  table  WHERE  id  =  $id"); 
  * 
  *  //  returns  one  row  in  an  array 
  *  $row  =  sql_onerow("SELECT  *  FROM  table  WHERE  id  =  $id"); 
  * 
  *  //  returns  an  array  of  arrays  with  all  the  result  data 
  *  $data  =  sql_all("SELECT  *  FROM  table"); 
  * 
  *  //  returns  an  array  with  data  from  column  0  only: 
  *  $names  =  sql_col("SELECT  name  FROM  table",  0); 
  * 
  *  //  returns  an  associative  array  with  the  first  column  as  the 
  *  //  key  and  the  second  column  as  the  value: 
  *  $name_by_id  =  sql_assoc("SELECT  id,  name  FROM  table"); 
  * 
  *  //  enable  auto-commit 
  *  sql_autocommit(true); 
  * 
  *  //  disable  auto-commit 
  *  sql_autocommit(false); 
  * 
  *  //  commit  transaction 
  *  sql_commit(); 
  * 
  *  //  roll  back  transaction 
  *  sql_rollback(); 
  * 
  */ 

require( "db-$db_type.phl"); 

$db_connection  =  0; 

/*  debugging  defaults  */ 
if  (empty($sql_debug))  { 
        $sql_debug  =  false; 
} 
if  (empty($sql_log_facility))  { 
        $sql_log_facility  =  2;  /*  debug  through  TCP  */ 
} 
if  (empty($sql_log_dest))  { 
        $sql_log_dest  =  "127.0.0.1:1400";  /*  destination  */ 
} 

/* 
  *  Function:  sql_debug 
  *  Description:  sends  debug  information  somewhere  if 
  *  the  global  variable  $sql_debug  is  true. 
  */ 
function  sql_debug($message)  { 
        global  $sql_debug,  $sql_log_facility,  $sql_log_dest; 
        if  ($sql_debug)  { 
        error_log( "[SQL]  $message",  $sql_log_facility,  $sql_log_dest); 
        } 
} 

/* 
  *  Function:  assert_db_connection 
  *  Description:  makes  sure  we  have  a  database  connection 
  */ 
function  assert_db_connection() 
{ 
        global  $db_connection; 
        if  ($db_connection)  { 
        return; 
        } 
        global  $db_dsn,  $db_user,  $db_pass; 
        $db_connection  =  db_connect($db_dsn,  $db_user,  $db_pass); 
        if  (!$db_connection)  { 
        die( "Failed  to  connect  to  database."); 
        } 
} 

/* 
  *  Function:  sql 
  *  Arguments:  $query  (string)      -  SQL  statement 
  *  Description:  executes  an  SQL  statement 
  *  Returns:  (int)  result  identifier 
  *                    returns  0  upon  error 
  */ 
function  sql($query)  { 
        global  $db_connection,  $PHP_SELF; 
        assert_db_connection(); 
        sql_debug($query); 
        return  db_query($db_connection,  $query); 
} 

/* 
  *  Function:  sql_onerow 
  *  Arguments:  $query  (string)      -  SQL  statement 
  *  Description:  executes  an  SQL  statement  and  returns  the  first 
  *                            row  of  the  result 
  *  Returns:  (array)  the  first  row 
  *                    returns  0  upon  error 
  */ 
function  sql_onerow($query)  { 
        $res  =  sql($query); 
        $row  =  db_fetch_row($res); 
        db_free_result($res); 
        return  $row; 
} 

/* 
  *  Function:  sql_one 
  *  Arguments:  $query  (string)      -  SQL  statement 
  *  Description:  executes  an  SQL  statement  and  returns  the  first 
  *                            column  of  the  first  row  of  the  result 
  *  Returns:  (mixed)  the  first  column  of  the  first  row 
  *                    returns  false  upon  error 
  */ 
function  sql_one($query)  { 
        $row  =  sql_onerow($query); 
        if  (gettype($row)  ==  "array")  { 
        return  $row[0]; 
        }  else  { 
        return  false; 
        } 
} 

/* 
  *  Function:  sql_all 
  *  Arguments:  $query  (string)      -  SQL  statement 
  *  Description:  executes  an  SQL  statement  and  returns  an  array 
  *                            of  arrays  with  the  rows  and  columns  of  all  the 
  *                            result  data  from  the  query. 
  *  Returns:  array  of  arrays  or  false  on  error 
  */ 
function  sql_all($query)  { 
        $res  =  sql($query); 
        if  ($res)  { 
        $all  =  array(); 
        while  ($row  =  db_fetch_row($res))  { 
                $all[]  =  $row; 
        } 
        db_free_result($res); 
        return  $all; 
        } 
        return  false; 
} 


/* 
  *  Function:  sql_col 
  *  Arguments:  $query  (string)      -  SQL  statement 
  *                        $column  (int)          -  returned  result  column  number 
  *  Description:  executes  an  SQL  statement  and  returns  an  array 
  *                            with  the  results  from  a  specific  column  in  the  result. 
  *  Returns:  array  or  false  on  error 
  */ 
function  sql_col($query,  $column)  { 
        $res  =  sql($query); 
        if  ($res)  { 
        $all  =  array(); 
        while  ($row  =  db_fetch_row($res))  { 
                $all[]  =  $row[$column]; 
        } 
        db_free_result($res); 
        return  $all; 
        } 
        return  false; 
} 


/* 
  *  Function:  sql_assoc 
  *  Arguments:  $query  (string)      -  SQL  statement 
  *  Description:  executes  an  SQL  statement  and  returns  an  associative 
  *                            array.    The  indices  of  this  array  are  taken  from  the 
  *                            first  column  of  the  result,  while  the  values  are  taken 
  *                            from  the  second  column.    If  there  are  more  than  two 
  *                            columns  the  remaining  ones  are  ignored. 
  *  Returns:  associative  array  or  false  on  error 
  */ 
function  sql_assoc($query)  { 
        $res  =  sql($query); 
        if  ($res)  { 
        $row  =  db_fetch_row($res); 
        if  (!is_array($row)  ||  count($row)  <  2)  { 
                db_free_result($res); 
                return  false; 
        } 
        while  ($row)  { 
                $assoc[$row[0]]  =  $row[1]; 
                $row  =  db_fetch_row($res); 
        } 
        db_free_result($res); 
        return  $assoc; 
        } 
        return  false; 
} 


function  sql_autocommit($enabled) 
{ 
        global  $db_connection; 
        if  (!$enabled)  { 
        sql_debug( "transaction  starting"); 
        } 
        return  db_autocommit($db_connection,  $enabled); 
} 


function  sql_commit() 
{ 
        global  $db_connection; 
        sql_debug( "transaction  complete"); 
        return  db_commit($db_connection); 
} 


function  sql_rollback() 
{ 
        global  $db_connection; 
        sql_debug( "transaction  aborted"); 
        return  db_rollback($db_connection); 
} 

?>