Posts tagged statement

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