Execute array of mysql query using PHP

<?php class MySQLDB {
   private $connection;          // The MySQL database connection

   /* Class constructor */
   function MySQLDB(){
      /* Make connection to database */
      $this->connection = mysql_connect(DB_SERVER, DB_USER, DB_PASS) or die(mysql_error());
      mysql_select_db(DB_NAME, $this->connection) or die(mysql_error());
   }

   /* Transactions functions */

   function begin(){
         $null = mysql_query("START TRANSACTION", $this->connection);
      return mysql_query("BEGIN", $this->connection);
   }

   function commit(){
      return mysql_query("COMMIT", $this->connection);
   }
 
   function rollback(){
      return mysql_query("ROLLBACK", $this->connection);
   }

   function transaction($q_array){
         $retval = 1;

      $this->begin();

         foreach($q_array as $qa){
            $result = mysql_query($qa['query'], $this->connection);
            if(mysql_affected_rows() == 0){ $retval = 0; }
         }

      if($retval == 0){
         $this->rollback();
         return false;
      }else{
         $this->commit();
         return true;
      }
   }

};
/* Create database connection object */ $database = new MySQLDB;
// then from anywhere else simply put the transaction queries in an array or arrays like this:

   function function(){
      global $database;

      $q = array (
         array("query" => "UPDATE table WHERE something = 'something'"),
         array("query" => "UPDATE table WHERE something_else = 'something_else'"),
         array("query" => "DELETE FROM table WHERE something_else2 = 'something_else2'"),
      );

      $database->transaction($q);

   } ?>

PHP debug function

function debug($value) {
    $btr=debug_backtrace();
    $line=$btr[0]['line'];
    $file=basename($btr[0]['file']);
    print"<pre>$file:$line</pre>\n";
    if(is_array($value)) {
        print"<pre>";
        print_r($value);
        print"</pre>\n";
    } elseif (is_object($value)) {
        $value.dump();
    } else {
        print("<p>&gt;${value}&lt;</p>");
    }
}

Call Store Proccedure

        //FIRST WAY
        
        $dbConf = new DBConnection();
        $databaseURL = $dbConf->get_databaseURL();
        $databaseUName = $dbConf->get_databaseUName();
        $databasePWord = $dbConf->get_databasePWord();
        $databaseName = $dbConf->get_databaseName();
     
        $connection = mysql_connect($databaseURL, $databaseUName, $databasePWord);
     
        $db = mysql_select_db($databaseName, $connection);


        //$stmt = mssql_init("sp_GetObjectMasterData", $connection);
        $rs = mysql_query("CALL sp_GetObjectMasterData($p_user_id, $p_org_id, $p_ret_messaage, $p_ret_code)");
     
        if($rs) {
                while($row = mysql_fetch_assoc($rs))
                {
                        echo $row;
                }
                return "Object ".$p_ret_messaage;
        } else {
                return "FALSE DATA";
        }
        $mysqli = new mysqli(  "XXXXXXXXX", "XXXXXXXX", "XXXXXXXX", "XXXXXXXXXXX" );
        $res = $mysqli->multi_query( "CALL sp_GetObjectMasterData($p_user_id, $p_org_id, @message, @code)" );
        if($res) {
                        $tableObjectArray = array();
                    $tableArray = array();
                        $results = 0;
                        $countObj = 0;
                        do {
                                  if ($result = $mysqli->store_result()) {
                                                            while( $row = $result->fetch_row() ) {
                                                                $tableObjectArray[$countObj] = $row[0];
                                                                        $tableArray[$countObj] = $row[1];
                                                                        $countObj++;
                                                            }
                                                            $result->close();
                                                            if( $mysqli->more_results() ) ;
                                  }
                        } while( $mysqli->next_result() );
                     
                        if(count($tableObjectArray)) {
                                $count = 0;
                                $tempTableArray = array();
                                foreach($tableArray as $selectionTable) {
                                        $selectionDataArray = explode('~', $selectionTable);
                                        $tempSelectionDataArray = array();
                                        for($i = 0; $i < count($selectionDataArray) - 1; $i++) {
                                                $selectionData  = explode('|', $selectionDataArray[$i]);
                                                $objectContainer = array();
                                                $objectContainer[0] = $selectionData[0];
                                                $objectContainer[1] = $selectionData[1].'-'.$selectionData[2];
                                                $tempSelectionDataArray[$i] = $objectContainer;
                                        }
                                        $tempTableArray[$tableObjectArray[$count]] = $tempSelectionDataArray;
                                        $count++;
                                 }
                                 $mysqli->close();
                                 return json_encode($tempTableArray);
                        } else {
                                $mysqli->close();
                                return "false";
                        }
                               
          } else {
                $mysqli->close();
                return "false";
          }
     
     
                             
        //SECOND WAY
        /*
        $mysql = mysql_connect('174.122.129.141', 'henrik_mcgm', 'mcgm8899', false, 65536);
        mysql_select_db('henrik_mcgm', $mysql);
        $rs = mysql_query("CALL henrik_mcgm.sp_GetObjectMasterData(212233385,6335572, @message, @code)");
        echo $rs;
        while($row = mysql_fetch_assoc($rs))
        {
                echo $row;
        }
        */
        //THIRD WAY
        /* prepare the statement resource
     
        $dbConf = new DBConnection();
        $databaseURL = $dbConf->get_databaseURL();
        $databaseUName = $dbConf->get_databaseUName();
        $databasePWord = $dbConf->get_databasePWord();
        $databaseName = $dbConf->get_databaseName();
     
        $connection = mysql_connect($databaseURL, $databaseUName, $databasePWord);
     
        $db = mysql_select_db($databaseName, $connection);
     
        $stmt = mssql_init("sp_GetObjectMasterData", $connection);
     
        $p_ret_messaage = "";
        $p_ret_code = 0;
     
     
        mssql_bind($stmt, "@p_user_id", $p_user_id, SQLINT4);
        mssql_bind($stmt, "@p_org_id", $p_org_id, SQLINT4); //SQLVARCHAR
        mssql_bind($stmt, "@p_ret_messaage", $p_ret_messaage, SQLVARCHAR);
        mssql_bind($stmt, "@p_ret_code", $p_ret_code, SQLINT4); //SQLVARCHAR
     
        $result = mssql_execute($stmt);
     
        echo $p_ret_messaage;
        echo $p_ret_code;
        // Fetch Return Data And Place Into Array For Access
        $procedureResponseData =  mssql_fetch_object($result);
     
        mysql_close($connection);
     
        return 'Response: '.$procedureResponseData; */
     
        //LOCAL TEST
        /*
        $mysql = mysql_connect('localhost', '', '', false, 65536);  //,false,65536 // add this to fix issue with recordsets
        mysql_select_db('test', $mysql);
        print '<h3>MYSQL: simple select</h3>';
        $rs = mysql_query('SELECT * FROM users;');
        while($row = mysql_fetch_assoc($rs))
        {
                echo $row['users_id'];
                echo $row['first_name'];
                echo $row['last_name'];
        }
        print '<h3>MYSQL: calling store procedure with out variables</h3>';
        $rs = mysql_query('CALL get_user(1, @first, @last)');
        $rs = mysql_query('SELECT @first, @last');
        while($row = mysql_fetch_assoc($rs))
        {
                echo $row;
        }
        print '<h3>MYSQL: calling store procedure  returning a recordset – doesn\'t work</h3>';
        $rs = mysql_query('CALL get_users()');
        while($row = mysql_fetch_assoc($rs))
        {
                echo $row;
        }
        */

        /*
// MYSQLI
$mysqli = new mysqli('localhost', '', '', 'test');

$rs = $mysqli->query( 'SELECT * FROM users;' );
while($row = $rs->fetch_object())
{
        print_r($row);
}

$rs = $mysqli->query( 'CALL get_user(1, @first, @last)'  );
$rs = $mysqli->query( 'SELECT @first, @last' );
while($row = $rs->fetch_object())
{
        print_r($row);
}

$rs = $mysqli->query( 'CALL get_users()' );
while($row = $rs->fetch_object())
{
        print_r($row);
}
*/

$procedureResponseData = "|1|M|Manhole~|2|L|Lamp hole~|3|G|Ghost Node~|4|O|Outfall~|5|S|Storm Overflow~|6|P|Pumping Station~|7|W|WWTF~|8|Z|Manhole of RM~|9|J|Junction Box~|10|B|Balancing Chamber~|11|V|Vent Shaft~#

|1|M|Manhole~|2|L|Lamp hole~|3|G|Ghost Node~|4|O|Outfall~|5|S|Storm Overflow~|6|P|Pumping Station~|7|W|WWTF~|8|Z|Manhole of RM~|9|J|Junction Box~|10|B|Balancing Chamber~|11|V|Vent Shaft~#

|1|M|Manhole~|2|L|Lamp hole~|3|G|Ghost Node~|4|O|Outfall~|5|S|Storm Overflow~|6|P|Pumping Station~|7|W|WWTF~|8|Z|Manhole of RM~|9|J|Junction Box~|10|B|Balancing Chamber~|11|V|Vent Shaft~#

|1|M|Manhole~|2|L|Lamp hole~|3|G|Ghost Node~|4|O|Outfall~|5|S|Storm Overflow~|6|P|Pumping Station~|7|W|WWTF~|8|Z|Manhole of RM~|9|J|Junction Box~|10|B|Balancing Chamber~|11|V|Vent Shaft~#

|1|M|Manhole~|2|L|Lamp hole~|3|G|Ghost Node~|4|O|Outfall~|5|S|Storm Overflow~|6|P|Pumping Station~|7|W|WWTF~|8|Z|Manhole of RM~|9|J|Junction Box~|10|B|Balancing Chamber~|11|V|Vent Shaft~#

|1|M|Manhole~|2|L|Lamp hole~|3|G|Ghost Node~|4|O|Outfall~|5|S|Storm Overflow~|6|P|Pumping Station~|7|W|WWTF~|8|Z|Manhole of RM~|9|J|Junction Box~|10|B|Balancing Chamber~|11|V|Vent Shaft~#

|1|M|Manhole~|2|L|Lamp hole~|3|G|Ghost Node~|4|O|Outfall~|5|S|Storm Overflow~|6|P|Pumping Station~|7|W|WWTF~|8|Z|Manhole of RM~|9|J|Junction Box~|10|B|Balancing Chamber~|11|V|Vent Shaft~#

|1|M|Manhole~|2|L|Lamp hole~|3|G|Ghost Node~|4|O|Outfall~|5|S|Storm Overflow~|6|P|Pumping Station~|7|W|WWTF~|8|Z|Manhole of RM~|9|J|Junction Box~|10|B|Balancing Chamber~|11|V|Vent Shaft~";

$tableObjectArray = array(0 => 'object_node_status_master', 1 => 'object_node_type_master', 2 => 'object_ward_master', 3 => 'object_manhole_shape_master',

4 => 'object_soffit_master', 5 => 'object_cover_shape_master', 6 => 'object_connection_code_master', 7 => 'object_lining_material_code_master',

8 => 'object_pipe_material_code_master', 9 => 'object_pipe_shape_master');

$tableArray = explode('#', $procedureResponseData);

$tempTableArray = array();

$count = 0;

foreach($tableArray as $selectionTable) {

$selectionDataArray = explode('~', $selectionTable);

$tempSelectionDataArray = array();

for($i = 0; $i < count($selectionDataArray) - 1; $i++) {

$selectionData = explode('|', $selectionDataArray[$i]);

$objectContainer = array();

$objectContainer[0] = $selectionData[1];

$objectContainer[1] = $selectionData[2].'-'.$selectionData[3];

$tempSelectionDataArray[$i] = $objectContainer;

}

$tempTableArray[$tableObjectArray[$count]] = $tempSelectionDataArray; 

$count++;

}

return json_encode($tempTableArray); 

Execute Store Procedure Using PHP

<?php
function &get_conn()
{
   global $conn;
   $myServer = "localhost";
   $myUser   = "markuser";
   $myPass   = "markpass";
   $myDB     = "markdb";

   if(!$conn)
   {
      $conn = mssql_connect($myServer, $myUser, $myPass)
         or die("Couldn't connect to SQL Server on $myServer");
      mssql_select_db($myDB, $conn)
         or die("Couldn't select database $myDB");
   }
   return $conn;
}

/* get a connection */
$conn =& get_conn();
?>

(Some of you may frown at the use of global $conn. I don’t normally use this, 
but it was a quick and dirty hack, not for production use….)

So far, all pretty standard stuff.

When executing a stored procedure, you have two choices.

1. (BAD) Execute it as a standard SQL statement:

EXEC myprocedure param1, param2, param3
or

EXEC myprocedure @id=param1, @name=param2, @email=param3
This is great from the command line when using isql, or when using SQL query analyzer (damn the Americans and their Z’s!).


2. (GOOD) A far more robust solution is to use the mssql_init, mssql_bind and 
mssql_execute methods, and bind named (and typed) parameters to your stored procedure object. 
The following snippet example gives you an idea of how to do this:

<?php
/* prepare the statement resource */
$stmt=mssql_init("myprocedure", $conn);

/* now bind the parameters to it */
mssql_bind($stmt, "@id",    $id,    SQLINT4,    FALSE);
mssql_bind($stmt, "@name",  $name,  SQLVARCHAR, FALSE);
mssql_bind($stmt, "@email", $email, SQLVARCHAR, FALSE);  

/* now execute the procedure */
$result = mssql_execute($stmt);
?>
There are two great benefits to using this approach:

Security - You don’t have to worry about escaping characters (like apostrophes) in your variable names. This makes nasty things like SQL injection impossible.
Typed variables - If you try and put text in a field that should be an integer you will get an error. While nobody likes errors, it’s much better to get 
them here than at the database end.
So that’s it in a nutshell! I needed my stored procedure to return stuff to me,
but I was having problems getting my output parameters to work. I’ve just seen the following in the online PHP documentation,
posted by a chap called Matt, that may help here:

1) you need to pass your php variable by reference. mssql_bind($stmt, "@outParam", &$outParam, true)

2) php-4.3.1-win32 had a binding bug and output parameters do not bind. 4.1 and 4.3.2rc1 do not have the bug. I have not tested other versions.

PHP Manual Reference :

Example #4 Calling a stored procedure with an output parameter
<?php
$stmt = $dbh->prepare("CALL sp_returns_string(?)");
$stmt->bindParam(1, $return_value, PDO::PARAM_STR, 4000);

// call the stored procedure
$stmt->execute();

print "procedure returned $return_value\n";
?>
Developers may also specify parameters that hold values both input and output; the syntax is similar to output parameters. In this next example, the string 'hello' is passed into the stored procedure, and when it returns, hello is replaced with the return value of the procedure.

Example #5 Calling a stored procedure with an input/output parameter
<?php
$stmt = $dbh->prepare("CALL sp_takes_string_returns_string(?)");
$value = 'hello';
$stmt->bindParam(1, $value, PDO::PARAM_STR|PDO::PARAM_INPUT_OUTPUT, 4000); 

// call the stored procedure
$stmt->execute();

print "procedure returned $value\n";
?>