Posts tagged php

PHP PDO Call MySQL Stored Function

Jun 6th

Posted by editor in Development

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

Posted by editor in Development

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

(more…)

, , , , , ,

PHP PDO SPLFileObject Import CSV Files to Database

May 21st

Posted by editor in Development

Imports COMMA, TAB, SPACE delimited files to mysql, postgresql, mssql, oracle sql, sqlite etc using PDO. Make sure you have all the columns in text file.

if you are going to import large data into database at once, execute this script from command line

for TModelObject Class and PDO GenerateInsertFunction see:

http://blog.teknober.com/2011/03/06/php-pdo-generate-insert-statement/

Works but the problem with this built-in functions it ignores the cells if they are null. see the replacement function below.

public function import($path) {
    $file = new SplFileObject($path);

    $file->setFlags(SplFileObject::READ_CSV);

    /* comma seperated text file */
    $file->setCsvControl("\t");

    while ($file->valid()) {
        $line = $file->fgetcsv();
        if (count($line) == 6) {

            list($column_a, $column_b, $column_c, $column_d, $column_e, $column_f) = $line;

            $this->model_generate_insert("my_table", array(
                new TModelOjbect("column_a", $column_a, PDO::PARAM_INT),
                new TModelOjbect("column_b", $column_b, PDO::PARAM_INT),
                new TModelOjbect("column_c", $column_c, PDO::PARAM_STR, 10),
                new TModelOjbect("column_d", $column_d, PDO::PARAM_STR, 200),
                new TModelOjbect("column_e", $column_e, PDO::PARAM_INT),
                new TModelOjbect("column_f", $column_f, PDO::PARAM_INT)
            ), true, false);

        }
    }
}

fixed version:

public function import($path) {
    $file = new SplFileObject($path);

    /* set file flags */
    $file->setFlags(SplFileObject::DROP_NEW_LINE);

    while ($file->valid()) {
        /* read line from the file */
        $line = $file->fgets();

        /* split line values */
        $line = explode("\t", $line);

        /* check column count */
        if (count($line) == 6) {

            list($column_a, $column_b, $column_c, $column_d, $column_e, $column_f) = $line;

            $this->model_generate_insert("my_table", array(
                new TModelOjbect("column_a", $column_a, PDO::PARAM_INT),
                new TModelOjbect("column_b", $column_b, PDO::PARAM_INT),
                new TModelOjbect("column_c", $column_c, PDO::PARAM_STR, 10),
                new TModelOjbect("column_d", $column_d, PDO::PARAM_STR, 200),
                new TModelOjbect("column_e", $column_e, PDO::PARAM_INT),
                new TModelOjbect("column_f", $column_f, PDO::PARAM_INT)
            ), true, false);

        }

    }
}
, , , , ,

PHP PDO Generate Replace Statement

Apr 13th

Posted by editor in Development

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,
, , , , , ,

PHP Create HTML Tag-Node

Apr 13th

Posted by editor in Development 

/**
 * Generates an HTML Node
 * @param string $name
 * @param boolean $short
 * @param array $attributes
 * @param string $content
 * @return Ambigous <NULL, string>
 */
function createTag($name, $short = false, $attributes = null, $content = null) {
    $tag = null;

    if (!$short) {
        $tag = sprintf("<%s%s>%s</%s>", $name, array_to_html_attributes("=", $attributes), $content, $name);
    } else {
        $tag = sprintf("<%s%s/>", $name, array_to_html_attributes("=", $attributes));
    }

    unset($name, $short, $attributes, $content);

    return $tag;
}

see for array_to_html_attributes:  http://blog.teknober.com/2011/04/13/php-array-to-html-attributes/

, ,

PHP array to html attributes

Apr 13th

Posted by editor in Development 

/**
 * Generates HTML Node Attribures
 * @param string $glue
 * @param array $pieces
 * @return string
 */
public static function array_to_html_attributes($glue, $pieces) {
    $str = "";
    if (is_array($pieces)) {
        $str = " ";
        foreach($pieces as $key => $value) {
            if (strlen($value) > 0) {
                $str .= $key . $glue . '"' . $value . '" ';
            }
        }
    }

    return rtrim($str);
}
, , ,

PHP UTF to ASCII for URL Path

Apr 13th

Posted by editor in Development

Converting UTF8 Content Titles to ASCII Charset to get proper URLs.

iconv did part of the job well but not all of it… so the code is:

example:

input: ÉçÖóáˇ21õ & test

output: ecooa21o-test  [you wont have duplicated -'s]

public static function toAsciiUrl($url) {
    $result = array();
    $url = iconv("UTF-8", "ASCII//IGNORE//TRANSLIT", $url);
    for($i = 0; $i < strlen($url); $i++) {
        switch (ord($url[$i])) {
            case 32: /*   */
            case 38: /* & */
            case 45: /* - */
                $result[] = '-';
                break;
            case 48: /* 0 */
                $result[] = '0';
                break;
            case 49: /* 1 */
                $result[] = '1';
                break;
            case 50: /* 2 */
                $result[] = '2';
                break;
            case 51: /* 3 */
                $result[] = '3';
                break;
            case 52: /* 4 */
                $result[] = '4';
                break;
            case 53: /* 5 */
                $result[] = '5';
                break;
            case 54: /* 6 */
                $result[] = '6';
                break;
            case 55: /* 7 */
                $result[] = '7';
                break;
            case 56: /* 8 */
                $result[] = '8';
                break;
            case 57: /* 9 */
                $result[] = '9';
                break;
            case 65: /* A */
            case 97: /* a */
            case 131: /* â */
            case 132: /* ä */
            case 133: /* à */
            case 134: /* å */
            case 142: /* Ä */
            case 143: /* Å */
            case 160: /* á */
            case 181: /* Á */
            case 182: /* Â */
            case 183: /* À */
            case 198: /* ã */
            case 199: /* Ã */
                $result[] = 'a';
                break;
            case 66: /* B */
            case 98: /* b */
                $result[] = 'b';
                break;
            case 67: /* C */
            case 99: /* c */
            case 128: /* Ç */
            case 135: /* ç */
                $result[] = 'c';
                break;
            case 68: /* D */
            case 100: /* d */
                $result[] = 'd';
                break;
            case 69: /* E */
            case 101: /* e */
            case 130: /* é */
            case 136: /* ê */
            case 137: /* ë */
            case 138: /* è */
            case 144: /* É */
            case 210: /* Ê */
            case 211: /* Ë */
            case 212: /* È */
                $result[] = 'e';
                break;
            case 70: /* F */
            case 102: /* f */
                $result[] = 'f';
                break;
            case 71: /* G */
            case 103: /* g */
            case 166: /* G */
            case 167: /* g */
                $result[] = 'g';
                break;
            case 72: /* H */
            case 104: /* h */
                $result[] = 'h';
                break;
            case 73: /* I */
            case 105: /* i */
            case 139: /* ï */
            case 140: /* î */
            case 141: /* i */
            case 152: /* I */
            case 161: /* í */
            case 214: /* Í */
            case 215: /* Î */
            case 216: /* Ï */
            case 222: /* Ì */
            case 236: /* ì */
                $result[] = 'i';
                break;
            case 74: /* J */
            case 106: /* j */
                $result[] = 'j';
                break;
            case 75: /* K */
            case 107: /* k */
                $result[] = 'k';
                break;
            case 76: /* L */
            case 108: /* l */
                $result[] = 'l';
                break;
            case 77: /* M */
            case 109: /* m */
                $result[] = 'm';
                break;
            case 78: /* N */
            case 110: /* n */
            case 164: /* ñ */
            case 165: /* Ñ */
                $result[] = 'n';
                break;
            case 79: /* O */
            case 111: /* o */
            case 147: /* ô */
            case 148: /* ö */
            case 149: /* ò */
            case 153: /* Ö */
            case 155: /* ø */
            case 157: /* Ø */
            case 162: /* ó */
            case 224: /* Ó */
            case 226: /* Ô */
            case 227: /* Ò */
            case 228: /* õ */
            case 229: /* Õ */
                $result[] = 'o';
                break;
            case 80: /* P */
            case 112: /* p */
                $result[] = 'p';
                break;
            case 81: /* Q */
            case 113: /* q */
                $result[] = 'q';
                break;
            case 82: /* R */
            case 114: /* r */
                $result[] = 'r';
                break;
            case 83: /* S */
            case 115: /* s */
            case 158: /* S */
            case 159: /* s */
                $result[] = 's';
                break;
            case 84: /* T */
            case 116: /* t */
                $result[] = 't';
                break;
            case 85: /* U */
            case 117: /* u */
            case 129: /* ü */
            case 150: /* û */
            case 151: /* ù */
            case 154: /* ü */
            case 163: /* ú */
            case 233: /* Ú */
            case 234: /* Û */
            case 235: /* Ù */
                $result[] = 'u';
                break;
            case 86: /* V */
            case 118: /* v */
                $result[] = 'v';
                break;
            case 87: /* W */
            case 119: /* w */
                $result[] = 'w';
                break;
            case 88: /* X */
            case 120: /* x */
                $result[] = 'x';
                break;
            case 89: /* Y */
            case 121: /* y */
            case 237: /* ÿ */
                $result[] = 'y';
                break;
            case 90: /* Z */
            case 122: /* z */
                $result[] = 'z';
                break;
            case 95: /* _ */
                $result[] = '_';
                break;

            case 145: /* æ */
            case 146: /* Æ */
                $result[] = 'ae';
                break;

            case 225: /* ß */
                $result[] = 'ss';
                break;
        }
    }

    unset($url, $i);

    /* remove duplicated - and return */
    return preg_replace('{(-)\1+}','$1', implode("", $result));
}
, ,

PHP PDO Generate Insert Statement

Mar 6th

Posted by editor in Development

MySQL Insert Manual

http://dev.mysql.com/doc/refman/5.0/en/insert.html

PHP PDO Update Statement

http://blog.teknober.com/2011/03/06/php-pdo-generate-update-statement/

PHP PDO Replace Statement

http://blog.teknober.com/2011/04/13/php-pdo-generate-replace-statement/

Sample

/* throws error */
$result = generateInsertSet("subscription");

/* insert: single column */
$result = generateInsertSet("subscription", new TModelObject('email', "test@domain.com", PDO::PARAM_STR, 200));

/* insert: multiple column */
$result = generateInsertSet("subscription", array(
             new TModelColumn('md5', md5("test@domain.com" . TDate::getDATE_MYSQL(time())), false, PDO::PARAM_STR, 100),
             new TModelColumn('email', "test@domain.com", PDO::PARAM_STR, 200),
             new TModelColumn('active', 1, PDO::PARAM_INT),
             new TModelColumn('valid', 0, PDO::PARAM_INT)
          ));

$result->execute();

PDO Statement: Insert

function generateInsertSet($table, $columns = null, $execute = false, $return = false) {
    if (isset($columns) == true) {
        /* prepare result */
        $result = null;

        /* prepare sql */
        $sql = "
                INSERT 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 $column) {
                $columns_string .= sprintf(" `%s`,", $column->getKey());
                $values_string .= "?,";
            }

            /* clean up */
            unset($column);

            /* 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 = sprintf("`%s`", $columns->getKey());
            $values_string = "?";
        } else {
            trigger_error("No column is defined in set", E_USER_ERROR);
        }

        /* prepare sql */
        $sql = sprintf($sql, $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());
            }

            /* clean up */
            unset($column);
        } elseif ($columns instanceof TModelColumn) {
            $result->bindParam(++$param_index, $columns->getValue(), $columns->getType(), $columns->getLength());
        } else {
            trigger_error("No column is defined in set", E_USER_ERROR);
        }

        /* clean up */
        unset($table, $columns, $sql, $columns_string, $values_string, $column, $param_index);

        /* return PDO Statement */
        if ($execute) {
            $result->execute();

            if ($return) {
                return self::$instance->lastInsertId();
            }
        } else {
            return $result;
        }
    } else {
        trigger_error("No column is defined in arguments");
    }
}

TModelColumn

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;
    }
}
, , ,

PHP PDO Generate Update Statement

Mar 6th

Posted by editor in Development

MySQL Update Manual

http://dev.mysql.com/doc/refman/5.0/en/update.html

PHP PDO Insert Statement

http://blog.teknober.com/2011/03/06/php-pdo-generate-insert-statement/

PHP PDO Replace Statement

http://blog.teknober.com/2011/04/13/php-pdo-generate-replace-statement/

Sample:

$model = new PDO(/* enter your parameters */);

$statement = generateUpdateSet("mytable", new TModelColumn("name", "Teknober", PDO::PARAM_STR, 100), new TModelWhere("id", 4, PDO::PARAM_INT, null, "="), true);

Sample 2:

$model = new PDO(/* enter your parameters */);

$statement = generateUpdateSet("mytable", new TModelColumn("name", "Teknober", PDO::PARAM_STR, 100), new TModelWhere("name", "A%", PDO::PARAM_STR, 100, "LIKE"), true);

PDO Statement: Update

function generateUpdateSet($table, $columns = null, $wheres = null, $execute = false) {
    if (isset($columns) == true) {
        $sql = null;
        $result = null;

        /* prepare columns_string */
        $columns_string = null;

        if (is_array($columns) == true) {
            /* iterate columns to generate columns_string */
            foreach ($columns as $value) {
                if ($value instanceof TModelColumn) {
                    $columns_string .= sprintf("`%s` = ?,", $value->getKey());
                } else {
                    trigger_error("column parameter must be instance of TModelColumn");
                }
            }

            /* clean up */
            unset($value);

            /* remove trailing comma */
            $columns_string = substr($columns_string, 0, strlen($columns_string) - 1);
        } elseif ($columns instanceof TModelColumn) {
            $columns_string = sprintf("`%s` = ?", $columns->getKey());
        } else {
            trigger_error("No column is defined in set", E_USER_ERROR);
        }

        /* prepare where_string */
        $wheres_string = null;

        if (is_array($wheres) === true) {
            foreach ($wheres as $where) {
                if ($where instanceof TModelWhere) {
                    $wheres_string .= sprintf(" `%s` %s ? %s", $where->getKey(), $where->getOperator(), $where->getCondition());
                } else {
                    trigger_error("where parameter must be instance of TModelWhere");
                }
            }

            /* clean up */
            unset($value);
        } elseif ($wheres instanceof TModelWhere) {
            $wheres_string = sprintf(" `%s` %s ?", $wheres->getKey(), $wheres->getOperator());
        }

        /* prepare sql */
        if (strlen($columns_string) > 0) {
            $sql = sprintf("UPDATE `%s` SET %s WHERE%s;", $table, $columns_string, $wheres_string);
        } else {
            $sql = sprintf("UPDATe `%s` SET %s", $table, $columns_string);
        }

        /* prepare statement */
        $result = $model->prepare($sql);

        /* prepare statement parameter index */
        $param_index = 0;

        /* bind statement column 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());
        }

        /* bind statement where parameters */
        if (is_array($wheres) == true) {
            foreach ($wheres as $where) {
                $result->bindParam(++$param_index, $where->getValue(), $where->getType(), $where->getLength());
            }
        } elseif ($wheres instanceof TModelWhere) {
            $result->bindParam(++$param_index, $wheres->getValue(), $wheres->getType(), $wheres->getLength());
        }

        /* clean up */
        unset($table, $columns, $wheres, $where, $sql, $columns_string, $wheres_string, $value, $param_index);

        /* return PDO Statement */
        if ($execute) {
            return $result->execute();
        } else {
            return $result;
        }
    } else {
        trigger_error("No column is defined in arguments", E_USER_ERROR);
    }
}

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;
    }
}

TModelWhere Class

class TModelWhere extends TModelColumn {
    public $condition = null;
    public $operator = null;

    public function __construct($key, $value = null, $type = null, $length = null, $operator = null, $condition = null) {
        $this->key = $key;
        $this->value = $value;
        $this->type = $type;
        $this->length = $length;
        $this->operator = $operator;
        $this->condition = $condition;
    }

    public function getOperator() {
        return $this->operator;
    }

    public function getCondition() {
        return $this->condition;
    }
}

$options,
, , ,

PHP DOMDocument short-tag issues

Jan 28th

Posted by editor in Development

Using the single quotation mark while creating new elements and passing empty string with single quotation marks will result as short tag! for example:

function createTag($tag = null, $content = null, $id = null, $class = null) {
	if ($tag == null) {
		return $tag;
	} else {
		if (!isset($content)) {
			$tag = $this->dom->createElement($tag);
		} else {
			$patterns = array("(&)");
			$replaces = array("&");
			$result = preg_replace($patterns, $replaces, $content);

			$tag = $this->dom->createElement($tag, $content);
		}

		if (isset($id)) {
			if (preg_match("/(^[a-z]{1}[a-z0-9_]+)$/", $id)) {
				$tag->setAttribute("id", $id);
			} else {
				/* todo: throw exception */
				throw new Exception("Invalide id -> " . $id);
			}
		}

		if (isset($class)) {
			if (preg_match("/(^[a-z]{1}([a-z0-9_-\s])+)$/", $class)) {
				$tag->setAttribute("class", $class);
			} else {
				/* todo: throw exception */
				throw new Exception("Invalide class -> " . $class);
			}
		}

		return $tag;
	}
}
, ,
12»