<?php
class classSqlConnection
{
public $trans = null;
public $debug = null;
public $conn  = null;
public $sth   = null;
public $sth_exec = null;

	function __construct($cfg)
	{
	$this->debug = $cfg['debug'];
	$this->trans = isset($cfg['opt'][PDO::ATTR_PERSISTENT]) ? $cfg['opt'][PDO::ATTR_PERSISTENT] : false;
	$this->connect($cfg);
	//$this->query("SET NAMES utf8"); // resi class sql
	}

	function __destruct()
	{
	$this->disConnect();
	}

	function connect($cfg)
	{
	try	{
		$this->conn = new PDO('mysql:host='.$cfg['host'].';dbname='.$cfg['db'], $cfg['user'], $cfg['psw'], $cfg['opt']);
		}
	catch (PDOException $e)
		{
		$this->error($e->getMessage());
		}
	}

	public function disConnect()
	{
	$this->sth      = null;
	$this->sth_exec = null;
	$this->conn     = null;
	}  

	public function lastInsertId()
	{
	return ($this->sth_exec) ? $this->conn->lastInsertId() : null;
	}

	public function querySafe($query)
	{
	$this->sth = $this->conn->prepare($query);
	$this->sth_exec = $this->sth->execute();
	return $this->sth;
	}

//	function free($result)
//	{
//	mysql_free_result($result);
//	}

	public function queryError($sth)
		{
		if (!$sth) {$this->error('Not sth!'); return;}
		$this->error('Error query: ' . $sth->errorCode().' '.implode(" - ",$sth->errorInfo()));
		}

	public function queryDebug($query)
	{
	echo "\n<div class=\"query\">query = ".$query."</div>";
	$this->querySafe($query);
	if (!$this->sth_exec && ($this->trans==false || $this->trans==null))
		{
		$this->queryError($this->sth);
		}
	return $this->sth;
	}

	public function query($query)
	{
	if ($this->debug)
		{
		return $this->queryDebug($query);
		}
	else	{
		return $this->querySafe($query);
		}
	}

/*
	public function fetch($sth=null,$fetch_type=PDO::FETCH_ASSOC)
	{
var_dump(123);
//	var_dump( $this->sth->fetch(PDO::FETCH_ASSOC));	//$sth
	$sth = !$sth ? $this->sth : $sth;
var_dump($fetch_type);
	return $sth ? $sth->fetch($fetch_type) : false;	//$sth
	}
*/

//	public function numRows($sth)
//	{
//	return $sth->query('SELECT FOUND_ROWS()')->fetchColumn();
//	}

	public function error($err_message)
	{
	var_dump($err_message);
	}
}

class classSql
{
private $cfg = null;
public $sql = null;
public $sth  = null;
public $sth_exec = null;

	function __construct($opt)
	{
	$sql = array();
	$cfg = $this->createConfig($opt);
//	$cfg['opt'][PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES \'latin1\'';
	$cfg['opt'][PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES \'utf8\'';
//	$cfg['opt'][PDO::MYSQL_ATTR_INIT_COMMAND] = 'SET NAMES \'cp1250\'';
	$sql[0] = new classSqlConnection($cfg);
$cfg['debug'] = true;
	$cfg['opt'][PDO::ATTR_PERSISTENT] = true;
	$cfg['opt'][PDO::ATTR_ERRMODE]    = PDO::ERRMODE_EXCEPTION;
	$sql[1] = new classSqlConnection($cfg);
	$this->sql = $sql;
	}

	function __destruct()
	{
	$this->sql[0]->disconnect();
	$this->sql[1]->disconnect();
	}

	function createConfig($opt)
	{
	$cfg = array(
		'host'  => '',
		'user'  => '',
		'psw'   => '',
		'db'    => '',
		'opt'   => array(),
		'debug' => false
		);
	foreach($cfg as $key=>$value)
		{
		$cfg[$key] = isset($opt[$key]) ? $opt[$key] : $value;
		}
	return $cfg;
	}

	public function query($query)
	{
	$sql  = $this->sql[0];
	$sth  = $sql->query($query);
	$this->sth      = $sql->sth;
	$this->sth_exec = $sql->sth_exec;
	return $sth;
	}

	public function transQuery($queries=array())
	{
	$sql = $this->sql[1];
	$sth  = null;
	try
		{
		$sql->conn->beginTransaction();
		//   $dbh -> exec ( "select 1;" );
		foreach($queries as $query)
			{
			$sth = $sql->query($query);
			}
		$sql->conn->commit();
		$this->sth      = $sql->sth;
		$this->sth_exec = $sql->sth_exec;
		}
	catch ( Exception $e )
		{
//		$sql->error($e->errorInfo[0].': '.$e->errorInfo[2]);
		echo '<pre>'.$e->xdebug_message.'</pre>';

		if (!$this->sth_exec)
			{
			$sql->queryError($this->sth);
			}
		$sql->conn->rollBack();
		$this->sth      = null;
		$this->sth_exec = null;
		}
	return $sth;
	}

	public function fetch($sth=null,$fetch_type=PDO::FETCH_ASSOC)
	{
//	var_dump( $this->sth->fetch(PDO::FETCH_ASSOC));	//$sth
	$sth = !$sth ? $this->sth : $sth;
	return $sth ? $sth->fetch($fetch_type) : false;	//$sth
	}

	public function createInsert($_table,$_data,$_idname='')
	{
	if ($_idname!='' && isset($_data[$_idname])) {unset($_data[$_idname]);}
	$keys   = array();
	$values = array();
	foreach ($_data as $key=>$value)
		{
		$keys[]   = $this->escapeKey($key);
		$values[] = $this->escapeValue($value);
		}
	return "INSERT INTO ".$this->escapeKey($_table)." (".implode(", ",$keys).") VALUES (".implode(", ",$values).")";
	}

	public function createUpdate($_table,$_data,$_idname)
	{
	$_id    = array($_idname=>$_data[$_idname]);
	unset($_data[$_idname]);
	$values = array();
	$where  = array();
	foreach ($_data as $key=>$value)
		{
		$values[] = $this->escapeKey($key). "=" . $this->escapeValue($value);
		}
	foreach ($_id as $key=>$value)
		{
		$where[] = $this->escapeKey($key). "=" . $this->escapeValue($value);
		}
	return "UPDATE ".$this->escapeKey($_table)." SET ".implode(", ",$values)." WHERE ".implode(' AND ',$where);
	}

	public function createReplace($_table,$_data,$_where=array())
	{
	$keys   = array();
	$values = array();
	foreach ($_data as $key=>$value)
		{
		$keys[]   = $this->escapeKey($key);
		$values[] = $this->escapeValue($value);
		}
	$where = '';
/*
	if (count($_where)>0)
		{
		$where  = array();
		foreach ($_where as $key=>$value)
			{
			$where[] = $this->escapeKey($key). "=" . $this->escape($value);
			}
		$where = count($where)>0 ? " WHERE ".implode(' AND ',$where) : '';
		}
*/
	return "REPLACE INTO ".$this->escapeKey($_table)." (".implode(", ",$keys).") VALUES (".implode(", ",$values).")".$where;
	}


	public function createUpdateInsert($_table,$_data)
	{
	$keys   = array();
	$values = array();
	$upd = array();
	$i = 0;
	foreach ($_data as $key=>$value)
		{
		$keys[]   = $this->escapeKey($key);
		$values[] = $this->escapeValue($value);
		$upd[]    = $keys[$i] . '=' . $values[$i];
		$i++;
		}
	return "INSERT INTO ".$this->escapeKey($_table)."
		  (".implode(", ",$keys).")
		VALUES
		  (".implode(", ",$values).")
		ON DUPLICATE KEY UPDATE
		  ".implode(", ",$upd)."
		";
	}

	public function createDelete($_table,$_id)
	{
	$where  = array();
	foreach ($_id as $key=>$value)
		{
		$where[] = $this->escapeKey($key)."=" . $this->escapeValue($value);
		}
	return "DELETE FROM ".$this->escapeKey($_table)." WHERE ".implode(' AND ',$where);
	}

	public function escapeValue($value='')
	{
	return $this->sql[0]->conn->quote($value);
	}

	public function unEscapeValue($value='')
	{
	return null;// $this->conn->quote($value);
	}

	public function escapeKey($key='')
	{
	return "`".str_replace("`","``",$key)."`";
	}

	public function escapeSelect($value='')
	{
	$m = strlen($value);
	$value = preg_replace('~UPDATE|REPLACE|DELETE|CREATE~i','',$value);
	$n = strlen($value);
	return $m==$n ? $value : '';
	}

};

/*
$sql = new classSql(array(
	'host'  => 'localhost',
	'user'  => 'root',
	'psw'   => '',
	'db'    => 'urb_news',
	'debug' => true
	));
*/

// --- pdo transaction ---
/**
 * Connect to MySQL and instantiate the PDO object.
 * Set the error mode to throw exceptions and disable emulated prepared statements.
 */
/*
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '', array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
));
 
 
//We are going to assume that the user with ID #1 has paid 10.50.
$userId = 1;
$paymentAmount = 10.50;
 
 
//We start our transaction.
$pdo->beginTransaction();
 
//We will need to wrap our queries inside a TRY / CATCH block.
//That way, we can rollback the transaction if a query fails and a PDO exception occurs.
try{
 
    //Query 1: Attempt to insert the payment record into our database.
    $sql = "INSERT INTO payments (user_id, amount) VALUES (?, ?)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $userId, 
            $paymentAmount,
        )
    );
    
    //Query 2: Attempt to update the user's profile.
    $sql = "UPDATE users SET credit = credit + ? WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $paymentAmount, 
            $userId
        )
    );
    
    //We've got this far without an exception, so commit the changes.
    $pdo->commit();
    
} 
//Our catch block will handle any exceptions that are thrown.
catch(Exception $e){
    //An exception has occured, which means that one of our database queries
    //failed.
    //Print out the error message.
    echo $e->getMessage();
    //Rollback the transaction.
    $pdo->rollBack();
}

----

<?php
$dbh = new PDO ('','','',array( PDO :: ATTR_PERSISTENT=>true));
$dbh->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION );
$dbh -> exec ( "select 1;" ); //should be autocommitted
try
{
   //$dbh->commit/rollback here fixes the issue;
   $dbh -> beginTransaction (); // <- fails "with there is already an active transaction"
   $dbh -> exec ( "select 1;" );
   $dbh -> commit ();
}
catch ( Exception $e )
{
  $dbh -> rollBack ();
}
?>
----

<?php
// Begin a transaction, turning off autocommit 
$dbh->beginTransaction();

// Insert multiple records on an all-or-nothing basis 
$sql = 'INSERT INTO fruit
    (name, colour, calories)
    VALUES (?, ?, ?)';

$sth = $dbh->prepare($sql);

foreach ($fruits as $fruit) {
    $sth->execute(array(
        $fruit->name,
        $fruit->colour,
        $fruit->calories,
    ));
}

// Commit the changes 
$dbh->commit();

// Database connection is now back in autocommit mode 
?>

*/
?>