Blog
PDO
Articles related to PHP PDO Extention
OpenSSL loading openssl.cnf
Feb 8th
since the openssl.cnf path is hardcoded in binaries. to alter it on windows set environmental variable (as written):
OPENSSLDIR=C:\PATH\TO\OpenSSL\bin OPENSSL_CONF=C:\PATH\TO\OpenSSL\bin\openssl.cnf
OPENSSL_CONF doesn’t work without OPENSSLDIR even if it is set.
settings these options will allow you to use your custom configuration files when creating certificates.
batch file i use to create certificate
GenCERT.bat [save this file to OpenSSL bin directory]
@echo off md %1 openssl genrsa -des3 -out %1/%1.key 1024 openssl req -new -key %1/%1.key -out %1/%1.csr copy %1/%1.key %1/%1.key.org openssl rsa -in %1/%1.key.org -out %1/%1.key openssl x509 -req -days 365 -in %1/%1.csr -signkey %1/%1.key -out %1/%1.crt openssl x509 -outform der -in %1/%1.crt -out %1/%1.der
Sample
:: Start command line and cd to C:\PATH\TO\OpenSSL\bin :: :: usage: GenCERT.bat domain.com :: GenCERT.bat teknober.com
Batch file I use to create SSL certificate for Web etc server
GenPEM.bat [save this file to OpenSSL bin directory]
@echo off mkdir %1 openssl req -x509 -nodes -days 365 -newkey rsa:1024 -keyout %1/%1.pem -out %1/%1.pem -config "%1/%1.cfg"
Sample
:: Start command line and cd to C:\PATH\TO\OpenSSL\bin :: :: usage: GenPEM.bat domain.com :: GenPEM.bat teknober.com
for sample ssl configuration file see the bottom of the page: http://www.openssl.org/docs/apps/ca.html
PDO PDOStatement with Select Find, Skip, Take
Jan 13th
Currently working on extending PDOStatement at C level which will allow to use select, join, where, order, group, find, skip, take commands in chain.
the usage will be like:
/* Extended PDO */
$pdo = new PDO(/* dsn */);
$pdo->select('table_x')->find(array('id' => 1));
/* using alias */
$pdo->select('table_x as a')->find(array('a.id = ?'))->bind(array(1));
$pdo->select('table_x', array('table_x.id', 'table_y.name', 'table_y.description')->join_left('table_y', array('table_x.id = table_y.id'))->findAll();
/*
--output type (single)
-- find() => PDORow
-- findToCSV() => comma seperated values
-- findToTDV() => tab delimeted values
-- findToJSON() => json string
-- findToXML() => xml string
--output types (multiple)
-- findAll() => array
-- findAllToCSV() => comma separated values
-- findAllToTDV() => tab delimited values
-- findAllToJSON() => json string
-- findAllToXML() => xml string
*/
$pdo->select('table_x', array('table_x.id', 'table_y.name', 'table_y.description')->join_left('table_y', array('table_x.id = table_y.id'))->findAllToCSV();
$pdo->select('table_x', array('table_x.id', 'table_y.name', 'table_y.description')->join_left('table_y', array('table_x.id = table_y.id'))->findAllToTDV();
$pdo->select('table_x', array('table_x.id', 'table_y.name', 'table_y.description')->join_left('table_y', array('table_x.id = table_y.id'))->findAllToJSON();
$pdo->select('table_x', array('table_x.id', 'table_y.name', 'table_y.description')->join_left('table_y', array('table_x.id = table_y.id'))->findAllToXML();
If you have any suggestions on adding more commands please let me know
PHP PDO using Transactions with TModel::GenerateStatement functions
Sep 9th
Only have an example to show using the TModel::GenerateInsertStatement, TModel::GenerateUpdateStatement, TModel::GenerateReplaceStatement
In database driver settings for mysql innodb engine, mariadb aria engine: SET AUTOCOMMIT=0;
sample:
public function panel_set_category_enabled($id_category) {
/* start a transaction */
$this->model->beginTransaction();
/* set: populate related columns */
$columns = array();
$columns[] = new TModelColumn("enabled", 1, PDO::PARAM_INT);
$columns[] = new TModelColumn("date_update", TDate::getDATE_MYSQL(time()), PDO::PARAM_STR);
/* execute and return result */
$result = TModel::generateUpdateSet("tc_category", $columns, new TModelWhere("id", $id_category, PDO::PARAM_INT, null, "="), true);
if ($result) {
$this->model->commit();
return $result;
} else {
$this->model->rollBack();
return $result;
}
}
related articles on this blog:
PHP PDO Generate Insert Statement:
http://blog.teknober.com/2011/03/06/php-pdo-generate-insert-statement/
PHP PDO Generate Update Statement:
http://blog.teknober.com/2011/03/06/php-pdo-generate-update-statement/
PHP PDO Generate Replace Statement:
http://blog.teknober.com/2011/04/13/php-pdo-generate-replace-statement/
PHP PDO Using reserved words as column names
Jun 25th
Using reserved words as columns names causes problems on insert/update/replace while there is no problem with select.
/* this will not work and will throw no exception */
$result = $model->prepare("update mytable set default = 1");
$result->execute();
/* this will work */
$result = $model->prepare("update mytable set `default` = 1");
$result->execute();
PHP PDO Statement Object with CSV and JSON
Jun 25th
Implements generic object for PHP PDO
sample:
class TableModel {
private $model = null;
public function __construct($model) {
$this->model = &$model;
}
public function get_table($id = null) {
$result = $this->registry->model->prepare("select a.* from table as a where a.id = ?");
$result->bindParam(1, $id, PDO::PARAM_INT);
$result->setFetchMode(PDO::FETCH_CLASS, 'TModelRow');
$result->execute();
return $result->fetchAll();
}
}
$model = new PDO(...);
$table_model = new TableModel($model);
$rows = $table_model->get_table(93);
foreach ($rows as $row) {
print_r($row->toArray());
echo $row; // is equivalent to $row->toJSON();
echo $row->toJSON();
echo $row->toTSV();
echo $row->toCSV(); // triggers error because not implemented!
}
PHP PDO Call MySQL Stored Function
Jun 6th
Calling a MySQL Stored Function from PHP PDO Driver. [ this is not for stored procedure ]
$model = new PDO(/* enter your parameters */);
function getID_ByISO($iso = null) {
$result = $model->prepare("select getID_ByISO(?) as id");
$result->bindParam(1, $iso, PDO::PARAM_STR, 2);
$result->setFetchMode(PDO::FETCH_CLASS, 'stdClass');
$result->execute();
return $result->fetch();
}
PHP function will return stdClass Object where the result will be wrapped into.
$obj = getID_ByISO('hu');
result:
print($obj->id); /* 3 is the id for hu in my database */
in sql statement, “select myfunc(?) as id”, result is named as id to have more generic output name
PHP PDO Import GeoNames Database
May 21st
the script imports geonames database into all databases that PHP PDO drivers support.
Modify the attached sql file for your own sql server. current sql format is for MySQL 5.+ and MariaDB 5.+
Note: There is problem with hierarchy file downloaded from geonames.org. you might need to import it manually after creating table structure
Execute
$geonames = new geonames(array(
'type' => 'mysql',
'host' => '127.0.0.1',
'port' => '3310',
'dbname' => 'tws_wdb_beta',
'user' => 'tws_wdb',
'pass' => 'tws_wdb',
'args' => array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8; SET CHARACTER SET UTF8; SET COLLATION_CONNECTION = UTF8_UNICODE_CI; SET character_set_client=utf8; SET character_set_connection=utf8;")
), false);
/*
* Import
* $path: input file
* $type: related database table
*/
$geonames->import("data/admin1Codes.txt", "admin1_codes");
$geonames->import("data/admin1CodesASCII.txt", "admin1_codes_ascii");
$geonames->import("data/admin2Codes.txt", "admin2_codes");
$geonames->import("data/alternateNames.txt", "alternate_names");
$geonames->import("data/countryInfo.txt", "country_info");
$geonames->import("data/featureCodes_en.txt", "feature_codes");
$geonames->import("data/geonames.txt", "geonames");
$geonames->import("data/geonames-no-country.txt", "geonames");
$geonames->import("data/hierarchy.txt", "hierarchy");
$geonames->import("data/iso-languagecodes.txt", "iso_languages");
$geonames->import("data/postalCodes.txt", "postal_codes");
$geonames->import("data/timeZones.txt", "timezones");
$geonames->import("data/userTags.txt", "usertags");
See More
PHP PDO Generate Replace Statement
Apr 13th
MySQL Replace Manual
http://dev.mysql.com/doc/refman/5.0/en/replace.html
PHP PDO Insert Statement
http://blog.teknober.com/2011/03/06/php-pdo-generate-insert-statement/
PHP PDO Update Statement
http://blog.teknober.com/2011/03/06/php-pdo-generate-update-statement/
Sample:
$model = new PDO(/* enter your parameters */);
$statement = generateReplaceSet("mytable", new TModelColumn("name", "Teknober", PDO::PARAM_STR, 100), null, true);
PDO Statement: Replace
function generateReplaceSet($table, $columns = null, $options = null, $execute = false) {
if (isset($columns) == true) {
/* prepare result */
$result = null;
/* prepare sql */
$sql = "
REPLACE `%s` INTO
%s (%s)
VALUES
(%s);";
/* prepare columns_string */
$columns_string = null;
$values_string = null;
if (is_array($columns) == true) {
/* iterate columns to generate columns_string */
foreach ($columns as $value) {
$columns_string .= "`" . $value->getKey() . "`,";
$values_string .= "?,";
}
/* remove trailing comma */
$columns_string = substr($columns_string, 0, strlen($columns_string) - 1);
$values_string = substr($values_string, 0, strlen($values_string) - 1);
} elseif ($columns instanceof TModelColumn) {
$columns_string = "`" . $columns->getKey() . "`";
$values_string = "?";
} else {
trigger_error("No column is defined in set", E_ERROR);
}
/* prepare sql */
$sql = sprintf($sql, $options, $table, $columns_string, $values_string);
/* prepare statement */
$result = $model->prepare($sql);
/* prepare statement parameter index */
$param_index = 0;
/* bind statement parameters */
if (is_array($columns) == true) {
foreach ($columns as $column) {
$result->bindParam(++$param_index, $column->getValue(), $column->getType(), $column->getLength());
}
} elseif ($columns instanceof TModelColumn) {
$result->bindParam(++$param_index, $columns->getValue(), $columns->getType(), $columns->getLength());
} else {
trigger_error("No column is defined in set", E_ERROR);
}
/* clean up */
unset($table, $columns, $options, $sql, $columns_string, $values_string, $param_index);
/* return PDO Statement */
if ($execute) {
return $result->execute();
} else {
return $result;
}
} else {
trigger_error("No column is defined in arguments");
}
}
TModelColumn Class
class TModelColumn {
public $key = null;
public $value = null;
public $type = null;
public $length = null;
public function __construct($key, $value = null, $type = null, $length = null) {
$this->key = $key;
$this->value = $value;
$this->type = $type;
$this->length = $length;
}
public function getKey() {
return $this->key;
}
public function getValue() {
return $this->value;
}
public function getType() {
return $this->type;
}
public function getLength() {
return $this->length;
}
}
$options,
LinkedIn
Twitter
Skype