Archive for March, 2011

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,