function get_addresses($values = NULL) { if (!(isset($values['user_id']) || isset($values['customer_id']))) { throw new Exception("Must provide user_id or customer_id"); } $query = "SELECT a.id address_id, a.last_updated, a.name, address1, address2, city, state, zipcode, type FROM addresses a \n LEFT JOIN user_addresses ua ON a.id = ua.address_id \n LEFT JOIN customer_addresses ca ON a.id = ca.address_id \n WHERE (ua.user_id = :user_id or ca.customer_id = :customer_id) "; $execArray['user_id'] = isset($values['user_id']) ? $values['user_id'] : -1; $execArray['customer_id'] = isset($values['customer_id']) ? $values['customer_id'] : -1; if (isset($values['address_id'])) { $query .= " AND a.id = :address_id "; $execArray['address_id'] = (int) $values['address_id']; } if (isset($values['type'])) { $query .= " AND type = :type "; $execArray['type'] = (int) $values['type']; } $dbh = new PDOConnection(); $sth = $dbh->prepare($query); if (!$sth->execute($execArray)) { throw new Exception($sth->errorInfo()[2]); } $addressArray = array(); foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) { $addressArray[] = $row; } return $addressArray; }
function update_address($addressInfo) { if (!(isset($addressInfo['address_id']) && (isset($addressInfo['customer_id']) || isset($addressInfo['user_id'])))) { throw new Exception("ERROR: address_id and customer_id or user_id required"); } $oldInfo = get_addresses($addressInfo)[0]; //takes customer/user id and address id if (empty($oldInfo)) { throw new Exception("Could not find address id for customer or user."); } $addressInfo = array_replace($oldInfo, $addressInfo); $query = "UPDATE addresses SET name = :name, address1 = :address1, address2 = :address2, city = :city, state = :state, zipcode = :zipcode, type = :type WHERE id = :address_id"; $dbh = new PDOConnection(); $sth = $dbh->prepare($query); $sth->bindParam(':name', $addressInfo['name']); $sth->bindParam(':address1', $addressInfo['address1']); $sth->bindParam(':address2', $addressInfo['address2']); $sth->bindParam(':city', $addressInfo['city']); $sth->bindParam(':state', $addressInfo['state']); $sth->bindParam(':zipcode', $addressInfo['zipcode']); $sth->bindParam(':type', $addressInfo['type'], PDO::PARAM_INT); $sth->bindParam(':address_id', $addressInfo['address_id'], PDO::PARAM_INT); if (!$sth->execute()) { throw new Exception("ERROR: could not update address - " . $sth->errorInfo()[2]); } return $addressInfo; }
function update_inventory($inventoryInfo) { if (!isset($inventoryInfo['inventory'])) { throw new Exception('Must provide \'inventory\''); } $dbh = new PDOConnection(); $query = "INSERT INTO inventory(\n product_id, unit_id, quantity\n )\n VALUES(\n :product_id, :unit_id, :quantity\n )\n ON DUPLICATE KEY UPDATE\n quantity = :quantity"; $product_id = -1; $unit_id = -1; $qantity = -1; $response = ''; $sth = $dbh->prepare($query); $sth->bindParam(':product_id', $product_id, PDO::PARAM_INT); $sth->bindParam(':unit_id', $unit_id, PDO::PARAM_INT); $sth->bindParam(':quantity', $quantity); foreach ($inventoryInfo['inventory'] as $inventory) { $product_id = $inventory['product_id']; $unit_id = $inventory['unit_id']; $quantity = $inventory['quantity_id']; if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } } return true; }
function update_unit($unitInfo) { if (!isset($unitInfo['id'])) { throw new Exception("Product id required."); } $id = $unitInfo['id']; $dbh = new PDOConnection(); $oldValues = get_units(array('id' => $id))[0]; //returns array of units if (empty($oldValues)) { throw new Exception("Product id: '" . $id . "' not found!"); } $query = "UPDATE units \n SET code = :code, \n description = :description, \n active = :active\n WHERE id = :id"; $sth = $dbh->prepare($query); $code = isset($unitInfo['code']) ? $unitInfo['code'] : $oldValues['code']; $description = isset($unitInfo['description']) ? $unitInfo['description'] : $oldValues['description']; $active = isset($unitInfo['active']) ? $unitInfo['active'] : $oldValues['active']; $sth->bindParam(':id', $id, PDO::PARAM_INT); $sth->bindParam(':code', $code); $sth->bindParam(':description', $description); $sth->bindParam(':active', $active, PDO::PARAM_INT); if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } return true; }
function update_product($prodInfo) { if (!isset($prodInfo['id'])) { throw new Exception("Product id required."); } $dbh = new PDOConnection(); $query = "SELECT id,code,description,price,active,last_updated FROM products WHERE id = :id"; $sth = $dbh->prepare($query); $id = $prodInfo['id']; $sth->bindParam(':id', $id, PDO::PARAM_INT); if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } if (!($oldValues = $sth->fetch())) { throw new Exception("Product id: '" . $id . "' not found!"); } $query = "UPDATE products \n SET code = :code, \n description = :description, \n price = :price, \n class = :class, \n active = :active \n WHERE id = :id"; $sth = $dbh->prepare($query); $code = isset($prodInfo['code']) ? $prodInfo['code'] : $oldValues['code']; $description = isset($prodInfo['description']) ? $prodInfo['description'] : $oldValues['description']; $price = isset($prodInfo['price']) ? $prodInfo['price'] : $oldValues['price']; $class = isset($prodInfo['class']) ? $prodInfo['class'] : $oldValues['class']; $active = isset($prodInfo['active']) ? $prodInfo['active'] : $oldValues['active']; $sth->bindParam(':id', $id, PDO::PARAM_INT); $sth->bindParam(':code', $code); $sth->bindParam(':description', $description); $sth->bindParam(':price', $price); $sth->bindParam(':class', $class, PDO::PARAM_INT); $sth->bindParam(':active', $active, PDO::PARAM_INT); $sth->execute(); return true; }
function verifyUserIsAdmin($username) { $dbh = new PDOConnection(); $query = 'SELECT user_id FROM admins JOIN users ON users.id = user_id WHERE username = :username'; $sth = $dbh->prepare($query); $sth->bindParam(':username', $username, PDO::PARAM_STR); if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } $result = $sth->fetchAll(); $retval = !empty($result); return $retval; }
function get_product_classes($classFilters = NULL) { $dbh = new PDOConnection(); $query = "SELECT * FROM product_classes "; $classArray = array(); $sth = $dbh->prepare($query); $sth->execute(); $result = $sth->fetchAll(PDO::FETCH_ASSOC); foreach ($result as $row) { $classArray[] = $row; } return $classArray; }
function add_product_class($info) { $dbh = new PDOConnection(); $product_id = isset($info['product_id']) ? $info['product_id'] : ''; $unit_id = isset($info['unit_id']) ? $info['unit_id'] : ''; $description = isset($info['description']) ? $info['description'] : ''; if (!$product_id) { $product_code = isset($info['product_code']) ? $info['product_code'] : ''; if (!$product_code) { throw new Exception("Product id or code required"); } $query = "SELECT id FROM products WHERE code = :code"; $sth = $dbh->prepare($query); $sth->bindParam(':code', $product_code); if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } $product_id = $sth->fetchColumn(); } if (!$unit_id) { $unit_code = isset($info['product_code']) ? $info['unit_code'] : ''; if (!$unit_code) { throw new Exception("Unit id or code required"); } $query = "SELECT id FROM units WHERE code = :code"; $sth = $dbh->prepare($query); $sth->bindParam(':code', $unit_code); if (!$sth->execute()) { throw new Exception($sth->errorInfo[2]); } $unit_id = $sth->fetchColumn(); } $query = "SELECT id FROM product_unit WHERE product_id = :pid AND unit_id = :uid"; $sth = $dbh->prepare($query); $sth->bindParam(':pid', $product_id, PDO::PARAM_INT); $sth->bindParam(':uid', $unit_id, PDO::PARAM_INT); $sth->execute(); if ($sth->rowCount() > 0) { throw new Exception("Product/unit entry already exists."); } $query = "INSERT INTO product_unit(product_id,unit_id,description) VALUES(:pid,:uid,desc)"; $sth = $dbh->prepare($query); $sth->bindParam(':pid', $product_id, PDO::PARAM_INT); $sth->bindParam(':uid', $unit_id, PDO::PARAM_INT); $sth->bindParam(':description', $description, PDO::PARAM_STR); if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } return true; }
/** * @codeCoverageIgnore */ protected static function initialize() { static::$classNS = get_called_class(); static::$tableName = static::getTableName(static::$classNS); $pdo = PDOConnection::getInstance(); static::$DBH = $pdo->connect(); }
public static function getInstance() { if (self::$db_singleton == null) { self::$db_singleton = new PDO("mysql:host=" . self::$dbhost . ";dbname=" . self::$dbname . ";charset=utf8", self::$dbuser, self::$dbpass, array(PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION)); } return self::$db_singleton; }
public function revisionData() { $dao = new JobRevisionDataDao(PDOConnection::connectINIT()); $data = $dao->getData($this->request->id_job, $this->auth_param); $result = new Json_RevisionData_Job($data); $this->response->json($result->render()); }
public static function obtain($server = null, $user = null, $pass = null, $database = null) { if (!self::$instance) { self::$instance = new PDOConnection($server, $user, $pass, $database); } return self::$instance; }
function add_product_class($classArray) { $dbh = new PDOConnection(); $code = $classArray['code']; $description = $classArray['description']; $query = "SELECT code FROM product_classes where code = :code"; $sth = $dbh->prepare($query); $sth->bindParam(':code', $code, PDO::PARAM_STR); $sth->execute(); if ($sth->rowCount() > 0) { throw new Exception("Class code exists"); } $query = "INSERT INTO product_classes(description,code) VALUES(:description,:code)"; $sth = $dbh->prepare($query); $sth->bindParam(':code', $code, PDO::PARAM_STR); $sth->bindParam(':description', $description, PDO::PARAM_STR); return $sth->execute(); }
function get_units($filters = NULL) { $dbh = new PDOConnection(); $query = "SELECT id, code, description, active, last_updated FROM units "; $query .= GetOptionalParams($filters); $units = array(); $sth = $dbh->prepare($query); if (isset($filters['id'])) { $sth->bindParam(':id', $filters['id'], PDO::PARAM_INT); } elseif (isset($filters['code'])) { $sth->bindParam(':code', $filters['code']); } $sth->execute(); $result = $sth->fetchAll(PDO::FETCH_ASSOC); foreach ($result as $row) { $units[] = $row; } return $units; }
public function getPosiciones() { $list = array(); $db = PDOConnection::getInstance(); $req = $db->prepare('SELECT posicion as posicion, FK_pincho_prem as id_pincho FROM premiados WHERE FK_premio_prem =?'); $req->execute(array($this->getId())); foreach ($req->fetchAll() as $posicion) { $list[] = array($posicion['posicion'], Pincho::find($posicion['id_pincho'])->getNombre()); } return $list; }
function get_customers($values = NULL) { $dbh = new PDOConnection(); $query = "SELECT id, code, name, active, last_updated FROM customers "; if (isset($values['id'])) { $optional[] = "id = :id "; } if (isset($values['code'])) { $optional[] = "code = :code "; } if (isset($values['active'])) { $optional[] = "active = :active "; } if (!empty($optional)) { $query .= ' WHERE '; $countOpt = count($optional); for ($i = 0; $i < $countOpt; ++$i) { $query .= ($i > 0 ? ' AND ' : ' ') . $optional[$i]; } } $sth = $dbh->prepare($query); if (isset($values['id'])) { $sth->bindParam(':id', $values['id'], PDO::PARAM_INT); } if (isset($values['code'])) { $sth->bindParam(':code', $values['code'], PDO::PARAM_STR); } if (isset($values['active'])) { $sth->bindParam(':active', $values['active'], PDO::PARAM_INT); } if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } $customerArray = array(); foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) { $customerArray[] = $row; } return array('customers' => $customerArray); }
function update_user($user) { if (!(isset($user['email']) || isset($user['username']) || isset($user['password']))) { throw new Exception("Nothing changed!"); } $dbh = new PDOConnection(); $query = "SELECT id,username,email,password,token,last_updated FROM users WHERE id = :id"; $sth = $dbh->prepare($query); $id = $user['user_id']; $sth->bindParam(':id', $id, PDO::PARAM_INT); if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } if (!($oldValues = $sth->fetch())) { throw new Exception("User id: '" . $id . "' not found!"); } // if you change username you must provide password if (isset($user['username']) && !isset($user['password'])) { throw new Exception("Must provide password to change username."); } $email = isset($user['email']) ? $user['email'] : $oldValues['email']; $username = isset($user['username']) ? $user['username'] : $oldValues['username']; $password = isset($user['password']) ? hash_password($user['password'], $username) : $oldValues['password']; $token = $oldValues['token']; $query = "UPDATE users \n SET username = :username, email = :email, password = :password \n WHERE id = :id"; $sth = $dbh->prepare($query); $sth->bindParam(':id', $id, PDO::PARAM_INT); $sth->bindParam(':username', $username); $sth->bindParam(':email', $email); $sth->bindParam(':password', $password); if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } if (isset($user['password'])) { $token = GenerateToken($username, $user['password']); StoreToken($username, $token); } return array('id' => $id, 'email' => $email, 'username' => $username, 'token' => $token); }
function get_users($values = NULL) { $dbh = new PDOConnection(); $query = "SELECT id, username, email FROM users "; if (isset($values['id'])) { $optional .= "id = :id "; } if (isset($optional) && $optional !== '') { $query .= ' WHERE ' . $optional; } $sth = $dbh->prepare($query); if (isset($values['id'])) { $sth->bindParam(':id', $values['id'], PDO::PARAM_INT); } if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } $userArray = array(); foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) { $userArray[] = $row; } return array('users' => $userArray); }
function add_unit($unitArray) { $dbh = new PDOConnection(); $code = $unitArray['code']; $description = $unitArray['description']; $query = "SELECT id,code FROM units WHERE code = :code"; $sth = $dbh->prepare($query); $sth->bindParam(':code', $code, PDO::PARAM_STR); if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } if ($sth->rowCount() > 0) { throw new Exception("Unit code exists"); } $query = "INSERT INTO units(code, description) VALUES(:code, :description)"; $sth = $dbh->prepare($query); $sth->bindParam(':code', $code, PDO::PARAM_STR); $sth->bindParam(':description', $description, PDO::PARAM_STR); if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } return true; }
function get_products($info = NULL, &$error = NULL) { $dbh = new PDOConnection(); $query = "SELECT p.id product_id, p.code product_code, p.description product_desc, p.price, pc.id class_id, pc.code class_code, pc.description class_desc \n FROM products p \n LEFT JOIN product_classes pc ON p.class = pc.id "; $optionalParams = ''; $code = ''; if (isset($info['code'])) { $optionalParams .= 'p.code = :prod_code '; $code = $info['code']; } if ($optionalParams != '') { $query .= "WHERE " . $optionalParams; } $sth = $dbh->prepare($query); $paramArray = array(':prod_code' => $code); $sth->execute($paramArray); $productArray = array(); $result = $sth->fetchAll(PDO::FETCH_ASSOC); foreach ($result as $row) { $productArray[] = $row; } return array('products' => $productArray); }
function get_warehouses($opts = NULL) { $dbh = new PDOConnection(); //not supported yet $customer_id = isset($opts['customer_id']) ? $opts['customer_id'] : ''; //not supported yet $query = "SELECT id, code, name, address1, address2, city, state, zipcode, delivery_allowed, active, last_updated FROM warehouses "; if (isset($opts['id'])) { $query .= " WHERE id = :id"; } $sth = $dbh->prepare($query); if (isset($opts['id'])) { $sth->bindParam(':id', $opts['id'], PDO::PARAM_INT); } if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } $warehouses = array(); foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) { $warehouses[] = $row; } return $warehouses; }
public static function get($profile) { $params = array(); $config = new Config('lib/db_profiles/' . $profile . '.ini'); $params['dsn'] = self::getDSN($config->getParam('General', 'driver'), $config->getParam('General', 'host'), $config->getParam('General', 'port'), $config->getParam('General', 'dbname')); $params['username'] = $config->getParam('General', 'username'); $params['password'] = $config->getParam('General', 'password'); if ($config->getCategory('DriverOptions') != FALSE) { $params['driver_options'] = self::getDriverOptions($config->getCategory('DriverOptions')); } else { $params['driver_options'] = array(); } return PDOConnection::getInstance($params); }
function get_cart($cartInfo) { $dbh = new PDOConnection(); $query = "SELECT u.id user_id, u.username, u.email, h.address_id, delivery_date, delivery_method, shipping_type, comments, shipping_comments, h.last_updated \n FROM cart_headers h \n LEFT JOIN users u ON u.id = h.user_id \n WHERE user_id = :user_id "; $user_id = $cartInfo['user_id']; $sth = $dbh->prepare($query); $sth->bindParam(':user_id', $user_id); if (!$sth->execute()) { throw new Exception('ERROR in get_cart(): ' . $sth->errorInfo()[2]); } if ($sth->rowCount() <= 0) { throw new Exception('No cart found for user_id: ' . $user_id); } $cartArray = $sth->fetch(PDO::FETCH_ASSOC); $details = get_cart_details($dbh, $user_id); //calculate total price $cartArray['total_price'] = array_sum(array_map(function ($row) { return $row['line_price']; }, $details)); //uncomment if you want details passed in the main get_cart function $cartArray['lines'] = $details; return $cartArray; }
function update_product_class($classArray) { $dbh = new PDOConnection(); $query = "SELECT id,code,description,last_updated FROM product_classes WHERE id = :id"; $sth = $dbh->prepare($query); $id = $classArray['id']; $sth->bindParam(':id', $id, PDO::PARAM_INT); if (!$sth->execute()) { throw new Exception($sth->errorInfo()[2]); } if (!($oldValues = $sth->fetch())) { throw new Exception("Class id: '" . $id . "' not found!"); } $query = "UPDATE product_classes SET code = :code, description = :description WHERE id = :id"; $sth = $dbh->prepare($query); $code = isset($classArray['code']) ? $classArray['code'] : $oldValues['code']; $description = isset($classArray['description']) ? $classArray['description'] : $oldValues['description']; $sth->bindParam(':id', $id, PDO::PARAM_INT); $sth->bindParam(':code', $code); $sth->bindParam(':description', $description); $sth->execute(); return true; }
/** * constructor **/ public function __construct() { $this->host = db_host(); $this->dbname = db_name(); $this->user = db_user(); $this->pass = db_psw(); $this->path = db_path(); switch (db_type()) { case "mysql": $dsn = 'mysql:host=' . $this->host . ";port=" . db_port() . ';dbname=' . $this->dbname; break; case "sqlite": $dsn = 'sqlite:' . $this->path . ';'; break; case "postgresql": $dsn = 'pgsql:host=' . $this->host . ";port=" . db_port() . ';dbname=' . $this->dbname; break; default: $dsn = 'mysql:host=' . $this->host . ";port=" . db_port() . ';dbname=' . $this->dbname; } $connection = new PDOConnection($dsn, $this->user, $this->pass, [PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8', PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY, PDO::ATTR_TIMEOUT => 60 * 60 * 60 * 60, PDO::ATTR_EMULATE_PREPARES => false, PDO::ATTR_PERSISTENT => false]); $connection->connect(); $this->dbh = new Database($connection); }
function get_product_units($info = NULL) { $dbh = new PDOConnection(); $query = "SELECT \n pu.id product_unit_id,\n p.id product_id, \n p.code product_code, \n p.description product_description, \n pu.price, \n pc.id class_id, \n pc.code class_code, \n pc.description class_description, \n u.id unit_id, \n u.code unit_code, \n u.description unit_description \n FROM product_unit pu\n LEFT JOIN units u ON pu.unit_id = u.id \n LEFT JOIN products p ON pu.product_id = p.id \n LEFT JOIN product_classes pc ON p.class = pc.id "; $optionalParams = array(); if (isset($info['product_code'])) { $optionalParams[] = 'p.code = :product_code '; $product_code = $info['product_code']; } if (count($optionalParams) > 0) { $query .= "WHERE "; $query .= implode("AND ", $optionalParams); } $sth = $dbh->prepare($query); if (isset($product_code)) { $sth->bindParam(':product_code', $product_code); } $sth->execute(); $productArray = array(); foreach ($sth->fetchAll(PDO::FETCH_ASSOC) as $row) { $productArray[] = $row; } return array('product_units' => $productArray); }
/** * * Genera 4 codigos de voto mas a partir del id de un pincho y los añade * en la base de datos. * @param string $tipob es el identificador de un pincho * int $numCV es el numero de codigos de voto asociados a un pincho. * @access public * */ public function generateMoreCV($IdPi, $numCV) { $db = PDOConnection::getInstance(); $stmt = $db->prepare("SELECT idCV FROM codVoto where pinchoId=?"); //cuenta los codigos de voto de un pincho $stmt->execute(array($IdPi)); $CV = $stmt->fetch(PDO::FETCH_ASSOC); for ($i = 1; $i <= 4; $i++) { $idCVtemp = $numCV + $i; $IdVoto = $IdPi . $idCVtemp; //print_r($IdVoto);die(); $stmt = $db->prepare("INSERT INTO codVoto values (?,?)"); $stmt->execute(array($IdVoto, $IdPi)); } //print_r($IdPi);die(); }
public function __construct() { parent::__construct(); $userid = ereg_replace('\\.', '', $_SERVER['REMOTE_ADDR']); $expires = time() + 1200; $table = 'user_online'; $column = array('expires', 'uid', 'mag_id', 'guest'); $values = array($expires, $userid, 0, 1); if (parent::CountRow($table, 'uid', $userid) < 1) { try { $sqlString = parent::InsertString($table, $column); $statement = $this->isConnect->prepare($sqlString); $statement = parent::bindState($statement, $column, $values); $statement->execute(); } catch (PDOException $e) { parent::ErrorException('Insert', $e, $sqlString); } } }
protected function DoOpen() { try { $this->statement = $this->pdoConnection->GetConnectionHandle()->query($this->GetSQL()); if (!$this->statement) { return false; } return true; } catch (PDOException $e) { $this->lastException = $e; return false; } }
<?php require_once 'includes.php'; $session = new Session(); $tpl = new Template(); $tpl->assign('system_name', $systemName); $tpl->assign('system_version', $systemVersion); if ($session->isRegistered('client')) { $pdo = PDOConnection::getInstance(); $client_session = $session->get('client'); $client = array('client_id' => $client_session['client_id'], 'status' => 3); $pdo->prepare('UPDATE client SET status = :status WHERE client_id = :client_id LIMIT 1')->execute($client); /* Histórico */ $pdo->prepare('UPDATE client_history SET status = :status WHERE client_id = :client_id LIMIT 1')->execute($client); $session->destroy('client'); } $tpl->show();