function add_member($username, $password) { require 'password.php'; try { define('DB_HOST', getenv('OPENSHIFT_MYSQL_DB_HOST')); define('DB_PORT', getenv('OPENSHIFT_MYSQL_DB_PORT')); define('DB_USER', getenv('OPENSHIFT_MYSQL_DB_USERNAME')); define('DB_PASS', getenv('OPENSHIFT_MYSQL_DB_PASSWORD')); define('DB_NAME', getenv('OPENSHIFT_GEAR_NAME')); try { $dsn = 'mysql:dbname=members;host=' . DB_HOST . ';port=' . DB_PORT; $db = new PDO($dsn, DB_USER, DB_PASS); } catch (PDOException $ex) { echo 'Error!: ' . $ex->getMessage(); die; } $password = password_hash($password, PASSWORD_BCRYPT); echo $password; $query = $db->query("USE members"); $query = $db->query("INSERT INTO members (username, password) VALUES ('{$username}', '{$password}')"); header('Location: main_login.php'); return TRUE; } catch (Exception $ex) { return FALSE; } }
public function testEquipmentUpdate() { $this->delete($this->insert(array(Object::TIME_IN_SECONDS => 3600, Object::DISTANCE => 12, Object::SHOEID => 1))); $this->delete($this->insert(array(Object::TIME_IN_SECONDS => 3600, Object::DISTANCE => 12, Object::SHOEID => 2))); $this->assertEquals(array('km' => 10, 'time' => 3000), $this->PDO->query('SELECT `km`, `time` FROM `' . PREFIX . 'shoe` WHERE `id`=1 AND `accountid`=0')->fetch(PDO::FETCH_ASSOC)); $this->assertEquals(array('km' => 0, 'time' => 0), $this->PDO->query('SELECT `km`, `time` FROM `' . PREFIX . 'shoe` WHERE `id`=2 AND `accountid`=0')->fetch(PDO::FETCH_ASSOC)); }
function init_db($db_file_name) { global $db; $need_init_db = !file_exists($db_file_name); $dsn = 'sqlite:' . $db_file_name; $db = new PDO($dsn); if (!$db) { die('failed to open sqlite.'); } if ($need_init_db) { $sql = <<<SQL create table pages ( id integer primary key, title varchar(255) unique, body text, created_at timestamp default current_timestamp, updated_at timestamp default current_timestamp ) SQL; $result = $db->query($sql); $sql = <<<SQL insert into pages (id, title, body) values (1, 'index', '...') SQL; $result = $db->query($sql); } }
/** * Setting default values on the PDO instance like errormode, encoding and * so on. */ public function setDefaultAttributes() { $this->_pdo->setAttribute(\PDO::ATTR_CASE, \PDO::CASE_NATURAL); $this->_pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION); $this->_pdo->setAttribute(\PDO::ATTR_ORACLE_NULLS, \PDO::NULL_TO_STRING); $this->_pdo->query("SET NAMES utf8"); }
function optimizeDB($text) { global $pathToIndex; // SETTING BEGIN // Run the task every n days $scheduleDays = 10; // SETTING END $loggixDB = $pathToIndex . Loggix_Core::LOGGIX_SQLITE_3; $filename = $loggixDB . '.BAK'; if (!file_exists($filename) || filemtime($filename) + 86400 * $scheduleDays <= time()) { if (copy($loggixDB, $filename)) { chmod($filename, 0666); $backupDB = 'sqlite:' . $filename; $bdb = new PDO($backupDB); // Garbage Collection (/admin/delete.php) $maxLifeTime = get_cfg_var("session.gc_maxlifetime"); $expirationTime = time() - $maxLifeTime; $sql = 'DELETE FROM ' . SESSION_TABLE . ' ' . 'WHERE ' . "sess_date < '" . $expirationTime . "'"; $bdb->query($sql); // Vacuum DB $bdb->query('VACUUM'); if (rename($loggixDB, $loggixDB . '.OLD')) { copy($filename, $loggixDB); chmod($loggixDB, 0666); } if (file_exists($loggixDB)) { unlink($loggixDB . '.OLD'); } } } return $text; }
/** * Вынимает данные пользователя из БД * @param mixed $sourceUserId ИД пользователя или его логин */ public function pickUserData($sourceUserId) { // Подключаем базу - источник $this->_debugMessage("Connecting to source database ..."); $dsn = "mysql:dbname={$this->_sourceDb['dbname']};"; $dsn .= "host={$this->_sourceDb['host']}"; $db = new PDO($dsn, $this->_sourceDb['user'], $this->_sourceDb['pass'], array(PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES \'UTF8\'')); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $result = $db->query("SELECT id FROM users\n WHERE id = '{$sourceUserId}' OR user_login = '******' LIMIT 1"); $sourceUserId = $result->fetchColumn(); $tablesSelected = array(); // Делаем выборку таблиц и их данных из массива $this->_debugMessage("Selecting tables ..."); foreach ($this->_tablesTemplate as $table => $params) { if (!isset($params['userId']) && !isset($params['selectCase'])) { die('Can\'t select table without key or `where` statement!'); } $sql = 'select * from `' . $table . '` where '; $sql .= isset($params['selectCase']) ? str_ireplace('{$userId}', $sourceUserId, $params['selectCase']) : " `{$params['userId']}`={$sourceUserId};"; $result = $db->query($sql); $tablesSelected[$table] = $result->fetchAll(PDO::FETCH_ASSOC); $this->_debugMessage("`{$table}` selected;"); } return $tablesSelected; }
/** * @inheritdoc */ public function initialize() { $createPhotoTable = <<<SQL CREATE TABLE IF NOT EXISTS "photo" ( "uuid" TEXT NOT NULL PRIMARY KEY, "resourceId" TEXT NOT NULL, "name" TEXT NOT NULL, "httpUrl" TEXT NOT NULL, "position" INT NOT NULL, "filePath" TEXT ) SQL; $this->pdo->query($createPhotoTable); $createAlternativeTextTable = <<<SQL CREATE TABLE IF NOT EXISTS "alternative_text" ( "photo_uuid" TEXT NOT NULL, "alt" TEXT NOT NULL, "lang" TEXT NOT NULL, FOREIGN KEY(photo_uuid) REFERENCES photo(uuid) ) SQL; $this->pdo->query($createAlternativeTextTable); $this->pdo->query("CREATE INDEX resource ON \"photo\" (resourceId);"); $this->pdo->query("CREATE INDEX fk_alt_photo_uuid ON \"alternative_text\" (photo_uuid);"); }
static function getSQLiteDB() { if (file_exists(SABRE_TEMPDIR . '/testdb.sqlite')) { unlink(SABRE_TEMPDIR . '/testdb.sqlite'); } $pdo = new PDO('sqlite:' . SABRE_TEMPDIR . '/testdb.sqlite'); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdo->query(' CREATE TABLE calendarobjects ( id integer primary key asc, calendardata text, uri text, calendarid integer, lastmodified integer ); '); $pdo->query(' CREATE TABLE calendars ( id integer primary key asc, principaluri text, displayname text, uri text, ctag integer, description text, calendarorder integer, calendarcolor text, timezone text, components text );'); $pdo->query('INSERT INTO calendars (principaluri,displayname,uri,description,calendarorder,calendarcolor,components) VALUES ("principals/user1","user1 calendar","UUID-123467","Calendar description", "1", "#FF0000","VEVENT,VTODO");'); $stmt = $pdo->prepare('INSERT INTO calendarobjects (calendardata, uri, calendarid, lastmodified) VALUES (?, "UUID-2345", 1, DATETIME("NOW"))'); $stmt->execute(array(self::getTestCalendarData())); return $pdo; }
function getItems4Client() { $DB = new PDO('mysql:host=' . DB_HOST . ';dbname=' . DB_NAME, DB_USERNAME, DB_PASSWORD); $table = DB_TABLE; //检查一下是否存在表 $sql = "SHOW TABLES LIKE '%{$table}%';"; $rc = $DB->query($sql)->rowCount(); if (!$rc) { $sqlFile = file_get_contents('items.sql'); $DB->exec($sqlFile); } $sql = "SELECT * FROM `{$table}` LIMIT 0,10"; $query = $DB->query($sql); $query->setFetchMode(PDO::FETCH_ASSOC); $rt = $query->fetchAll(); if ($rt) { $items = array(); foreach ($rt as $item) { $tmp = array(); $tmp['openid'] = $item['tb_item_id']; $tmp['iid'] = $item['tb_iid']; $tmp['price'] = $item['price'] ? $item['price'] / 100 : $item['reserve_price'] / 100; $tmp['name'] = $item['name']; $tmp['pic'] = $item['pic']; $tmp['itemType'] = $item['is_mall'] + 1; // 客户端判断是 1:集市店,2:天猫店 $items[] = $tmp; } Ajax::go($items); } else { Ajax::error('数据查询失败.'); } }
public function testLongWork() { // work_test $pdo = new PDO_instance('sqlite::memory:', null, null, [\PDO::ATTR_ERRMODE => \PDO::ERRMODE_EXCEPTION]); $pdo->query('CREATE TABLE IF NOT EXISTS `work_test` ( `key` varchar(128) DEFAULT NULL, `value` varchar(256) DEFAULT NULL );'); $pdo->query(" DELETE FROM `work_test`;"); // clear table data for ($i = 1; $i <= 100; $i++) { $pdo->query("INSERT INTO `work_test` (`key`, `value`) VALUES ('key" . $i . "', '" . mt_rand(0, 50) . "');"); } $builder = new Builder($pdo); $this->assertInstanceOf('\\YepSQL\\Builder', $builder); # $this->assertEquals(3230, count($builder->loadFromFile(__DIR__ . '/true_long.sql'))); $count = $pdo->query('SELECT count(*) FROM `work_test` WHERE `value` > 32;')->fetchColumn(); $re = $builder->workLongMore([':more' => 32])->fetchColumn(); print_r($re); $this->assertEquals($count, $re); # $count = $pdo->query('SELECT count(*) FROM `work_test` WHERE `value` < 32;')->fetchColumn(); $re = $builder->workLongLess([':less' => 32])->fetchColumn(); print_r($re); $this->assertEquals($count, $re); # }
public static function export(\PDO $database, $databaseName) { try { $stmt = $database->query('SHOW TABLES'); $db = new Database($databaseName); foreach ($stmt as $k => $v) { $table = new Table(); $table->setName($v[0]); $query = $database->query('SHOW COLUMNS FROM ' . $table->getName()); $columns = $query->fetchAll(\PDO::FETCH_ASSOC); foreach ($columns as $c) { $column = new Column(); $column->setName($c['Field']); $column->setDefault($c['Default']); $column->setExtra($c['Extra']); $column->setKey($c['Key']); $column->setNull($c['Null']); $column->setType($c['Type']); $table->addColumn($column); } $db->addTable($table); } $stmt->closeCursor(); } catch (\PDOException $e) { echo "Error: " . $e->getMessage(); exit; } return $db; }
/** * Connects to the database without initializing contao * * @param OutputInterface $output = null * * @throws \Exception * @return \PDO */ public function getConnection(OutputInterface $output = null) { if ($output == null) { $output = new NullOutput(); } if ($this->_connection) { return $this->_connection; } $this->detectDbSettings($output); if (!extension_loaded('pdo_mysql')) { throw new \Exception('pdo_mysql extension is not installed'); } if (strpos($this->dbSettings['host'], '/') !== false) { $this->dbSettings['unix_socket'] = $this->dbSettings['host']; unset($this->dbSettings['host']); } else { if (strpos($this->dbSettings['host'], ':') !== false) { list($this->dbSettings['host'], $this->dbSettings['port']) = explode(':', $this->dbSettings['host']); } } $this->_connection = new \PDO($this->dsn(), $this->dbSettings['username'], $this->dbSettings['password']); /** @link http://bugs.mysql.com/bug.php?id=18551 */ $this->_connection->query("SET SQL_MODE=''"); try { $this->_connection->query('USE `' . $this->dbSettings['dbname'] . '`'); } catch (\PDOException $e) { } $this->_connection->query("SET NAMES utf8"); $this->_connection->setAttribute(\PDO::ATTR_EMULATE_PREPARES, true); $this->_connection->setAttribute(\PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, true); return $this->_connection; }
/** * Execute une requête SQL (mysql). * * @todo implémenter le template. * @todo eviter de dupliquer les objets PDO, mais maintenir le renvoi d'une collection ouverte sur la BD * @param string $sql * @param string $template * @throws PDOException * @throws Afup_Directory_Exception * @return integer|PDOStatement insert or select */ private function executeSql($sql, $template = null, $values = null) { if ($values) { $stmt = $this->pdo->prepare($sql); if ($stmt) { if (isset($values[0]) && is_array($values[0])) { foreach ($values as $value) { $stmt->execute($value); } } else { $stmt->execute($values); } } } else { $stmt = $this->pdo->query($sql, PDO::FETCH_ASSOC); } $queryType = strtolower(substr($sql, 0, 6)); switch ($queryType) { case 'select': return $stmt; // break; // break; case 'insert': case 'update': case 'delete': unset($stmt); return $this->pdo->lastInsertId(); // break; // break; default: throw new Afup_Directory_Exception("This request do not return values."); break; } }
/** * Drop test table, only on Travis CI */ public function tearDown() { if (!$this->isTravisCi()) { return true; } $this->pdo->query("DROP TABLE hello"); }
function like($pid, $how) { // database connection $dsn = "mysql:host=localhost;dbname=fundbook"; //$options = array(PDO::"MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8'); $db = new PDO($dsn, "root", "fundbook"); $projects = $db->query("SELECT * FROM projects WHERE pid='{$pid}'"); foreach ($projects as $project) { $author = $project["user"]; $goal = $project["goal"]; $category = $project["topic"]; $title = $project["name"]; $numBackers = $project["numBackers"]; $raisedAmt = $project["raisedAmt"]; } if (isset($_COOKIE["email"])) { $email = $_COOKIE["email"]; $interested = $db->query("SELECT * FROM topicInterests WHERE person='{$email}' and topic='{$category}'"); // then add to interest foreach ($interested as $interest) { if ($how == 'explicit') { $data = array('action' => 'like', "msg" => "added already"); echo json_encode($data); exit; } } $db->query("INSERT INTO topicInterests VALUES ('{$email}', '{$category}')"); if ($how == 'explicit') { $data = array('action' => 'like', 'msg' => 'added'); echo json_encode($data); } } exit; }
/** * Override the template-fetching-function of the Parser * * @global string $IP * @global string $wgTemplatePath * @global string $wgTemplateExtension * @global string $wgTemplatePrefix * @param Title $title * @return array */ function fetchTemplateAndTitle($title) { #echo "\n--- Trying to find offline template: $title ---\n"; global $wgTemplateDB, $wgTemplateFileID; $finalTitle = $title; $template_text = null; # $$$ need to fix later for all languages # We pad the title with '~' to force the database to import strings $title_orig = '~' . $wgTemplateFileID . '~' . strtolower($title); $db = new PDO('sqlite:' . $wgTemplateDB); $tl = $db->quote($title_orig); #echo "\n--- ($title_orig) --- \n"; $result = $db->query("SELECT body FROM templates WHERE title = {$tl} LIMIT 1"); $data = $result->fetchAll(); $max_loop_count = 25; while ($max_loop_count && sizeof($data) == 0) { $result = $db->query("SELECT redirect FROM redirects WHERE title = {$tl} LIMIT 1"); $data = $result->fetchAll(); if (sizeof($data) == 0) { break; } $redirect = $db->quote($data[0]['redirect']); $result = $db->query("SELECT body FROM templates WHERE title = {$redirect} LIMIT 1"); $data = $result->fetchAll(); --$max_loop_count; } if (sizeof($data) > 0) { $template_text = substr($data[0]['body'], 1); #echo "\n--- TT:($template_text):TT --- \n"; } else { $template_text = ''; } $ret = array($template_text, $finalTitle); return $ret; }
/** * @param InputInterface $input * @param OutputInterface $output * @return bool|\PDO */ protected function validateDatabaseSettings(InputInterface $input, OutputInterface $output) { try { $dsn = sprintf("mysql:host=%s;port=%s", $this->config->getString('db_host'), $this->config->getString('db_port')); $db = new \PDO($dsn, $this->config->getString('db_user'), $this->config->getString('db_pass')); $dbName = $this->config->getString('db_name'); if (!$db->query('USE `' . $dbName . '`')) { $db->query("CREATE DATABASE `" . $dbName . "`"); $output->writeln('<info>Created database ' . $dbName . '</info>'); $db->query('USE `' . $dbName . '`'); // Check DB version $statement = $db->query('SELECT VERSION()'); $mysqlVersion = $statement->fetchColumn(0); if (version_compare($mysqlVersion, '5.6.0', '<')) { throw new \Exception('MySQL Version must be >= 5.6.0'); } return $db; } if ($input->getOption('noDownload') && !$input->getOption('forceUseDb')) { $output->writeln("<error>Database {$this->config->getString('db_name')} already exists.</error>"); return false; } return $db; } catch (\Exception $e) { $output->writeln('<error>' . $e->getMessage() . '</error>'); } catch (\PDOException $e) { $output->writeln('<error>' . $e->getMessage() . '</error>'); } return false; }
function getDatabase() { $dbobject = false; global $BASE_DIR; if (file_exists("{$BASE_DIR}/authserver/authd/gaasdata.sqlite")) { try { $dbobject = new PDO("sqlite:{$BASE_DIR}/authserver/authd/gaasdata.sqlite"); } catch (PDOException $exep) { error_log("execpt on db open"); } } else { try { $dbobject = new PDO("sqlite:{$BASE_DIR}/authserver/authd/gaasdata.sqlite"); } catch (PDOException $exep) { error_log("execpt on db open"); } $sql = 'CREATE TABLE "users" ("users_id" INTEGER PRIMARY KEY AUTOINCREMENT,"users_username" TEXT, "users_realname" TEXT, "users_password" TEXT, "users_tokendata" TEXT, "users_otk" TEXT);'; $dbobject->query($sql); $sql = 'CREATE TABLE "radclients" ("rad_id" INTEGER PRIMARY KEY AUTOINCREMENT,"rad_name" TEXT, "rad_ip" TEXT, "rad_secret" TEXT, "rad_desc" TEXT);'; $dbobject->query($sql); $sql = 'CREATE TABLE "hardwaretokens" ("tok_id" INTEGER PRIMARY KEY AUTOINCREMENT,"tok_name" TEXT, "tok_key" TEXT, "tok_type" TEXT);'; $dbobject->query($sql); } return $dbobject; }
/** * @param PDO $connection * @param null $message_id * @return array */ function load_messages(\PDO $connection, $message_id = null) { if ($message_id !== null) { $message_id = (int) $message_id; } return $message_id === null ? $connection->query('SELECT m.`id`,m.`message`,m.`time`,u.`login` FROM `messages` m LEFT JOIN `users` u ON m.`user_id`=u.`id` ORDER BY m.`time` DESC')->fetchAll() : $connection->query("SELECT m.`id`,m.`message`,m.`time`,u.`login` FROM `messages` m LEFT JOIN `users` u ON m.`user_id`=u.`id` WHERE m.`id`={$message_id} ORDER BY m.`time` DESC")->fetchAll(); }
/** * This functions remove a plugin from the OCS webconsole and database. * Delete all created menu entries and all plugin related code * * @param integer $pluginid : Plugin id in DB */ function delete_plugin($pluginid) { global $l; $conn = new PDO('mysql:host=' . SERVER_WRITE . ';dbname=' . DB_NAME . '', COMPTE_BASE, PSWD_BASE); $query = $conn->query("SELECT * FROM `plugins` WHERE id = '" . $pluginid . "'"); $anwser = $query->fetch(); if (!class_exists('plugins')) { require 'plugins.class.php'; } if (!function_exists('exec_plugin_soap_client')) { require 'functions_webservices.php'; } if ($anwser['name'] != "" and $anwser['name'] != null) { require MAIN_SECTIONS_DIR . "ms_" . $anwser['name'] . "/install.php"; $fonc = "plugin_delete_" . $anwser['name']; $fonc(); } rrmdir(MAIN_SECTIONS_DIR . "ms_" . $anwser['name']); rrmdir(PLUGINS_DIR . "computer_detail/cd_" . $anwser['name']); if (file_exists(PLUGINS_SRV_SIDE . $anwser['name'] . ".zip")) { unlink(PLUGINS_SRV_SIDE . $anwser['name'] . ".zip"); exec_plugin_soap_client($anwser['name'], 0); } $conn->query("DELETE FROM `" . DB_NAME . "`.`plugins` WHERE `plugins`.`id` = " . $pluginid . " "); }
function set_score($score, $ip) { try { $dbh = new PDO("sqlite:{$this->path}"); $voted = $dbh->query("SELECT id FROM {$this->table} WHERE ip='{$ip}'"); if (sizeof($voted->fetchAll()) == 0) { $dbh->query("INSERT INTO {$this->table} (rating, ip) VALUES ({$score}, '{$ip}')"); $this->votes++; //cache average in the master row $statement = $dbh->query("SELECT rating FROM {$this->table}"); $total = $quantity = 0; $row = $statement->fetch(); //skip the master row while ($row = $statement->fetch()) { $total = $total + $row[0]; $quantity++; } $this->average = round($total * 20 / $quantity, 0); $statement = $dbh->query("UPDATE {$this->table} SET rating = {$this->average} WHERE id=1"); $this->status = '(thanks!)'; } else { $this->status = '(already scored)'; } } catch (PDOException $exception) { die($exception->getMessage()); } $dbh = NULL; }
function createDB() { $dbobject = false; global $BASE_DIR, $initState, $backEnd; try { $dbobject = new PDO("sqlite:{$BASE_DIR}/gaas/gaasd/gaasd.sqlite"); } catch (PDOException $exep) { error_log("execpt on db open"); return false; } // users_tokendata is used by ga4php, users_otk is the qrcode data link if needed, // tokentype is the software/hardware token types $sql = 'CREATE TABLE "users" ("users_id" INTEGER PRIMARY KEY AUTOINCREMENT,"users_username" TEXT, "users_realname" TEXT, "users_password" TEXT, "users_tokendata" TEXT, "users_qrcodeid" TEXT, "user_enabled" TEXT, "users_tokentype" TEXT, "users_isadmin" TEXT, "users_pin" TEXT);'; $dbobject->query($sql); //if(!$res) { //echo "Create user table failed\n"; //} $sql = 'CREATE TABLE "config" ("conf_id" INTEGER PRIMARY KEY AUTOINCREMENT,"conf_name" TEXT, "conf_value" TEXT);'; $dbobject->query($sql); $sql = 'CREATE TABLE "radclients" ("rad_id" INTEGER PRIMARY KEY AUTOINCREMENT,"rad_name" TEXT, "rad_ip" TEXT, "rad_secret" TEXT, "rad_desc" TEXT);'; $dbobject->query($sql); $sql = 'CREATE TABLE "hardwaretokens" ("tok_id" INTEGER PRIMARY KEY AUTOINCREMENT,"tok_name" TEXT, "tok_key" TEXT, "tok_type" TEXT);'; $dbobject->query($sql); return true; }
/** * Sets up the PDO SQLite table and * initialises the PDO connection * * @param array configuration * @throws Kohana_Cache_Exception */ protected function __construct(array $config) { parent::__construct($config); $database = Arr::get($this->_config, 'database', NULL); if ($database === NULL) { throw new Kohana_Cache_Exception('Database path not available in Kohana Cache configuration'); } // Load new Sqlite DB $this->_db = new PDO('sqlite:' . $database); // Test for existing DB $result = $this->_db->query("SELECT * FROM sqlite_master WHERE name = 'caches' AND type = 'table'")->fetchAll(); // If there is no table, create a new one if (0 == count($result)) { $database_schema = Arr::get($this->_config, 'schema', NULL); if ($database_schema === NULL) { throw new Kohana_Cache_Exception('Database schema not found in Kohana Cache configuration'); } try { // Create the caches table $this->_db->query(Arr::get($this->_config, 'schema', NULL)); } catch (PDOException $e) { throw new Kohana_Cache_Exception('Failed to create new SQLite caches table with the following error : :error', array(':error' => $e->getMessage())); } } }
public function doDefault() { $dsn = 'uri:file://E:\\WWW\\weixin\\dsn.txt'; $p_user = '******'; $p_pw = '123456'; $pdo = new PDO($dsn, $p_user, $p_pw); //获取title $pid = $_GET['pid']; $pre = $pdo->query("select `Name` from product where `ProductId`='" . $pid . "'"); $rt = $pre->fetchColumn(); //根据产品ID获取属于该产品自身的属性ID $attr_ids = array(); $re = $pdo->query("select `AttrId` from productattr where `ProductId`='" . $pid . "'"); while ($row = $re->fetch()) { $attr_ids[] = $row['AttrId']; } //标题、属性内容 $res = $pdo->query("select ap.`Name`, `at`.*, `at`.AttrParent_ParentId as atpid from attrparent ap, attr at where ap.ParentId = `at`.AttrParent_ParentId and `at`.AttrId in (" . implode(',', $attr_ids) . ')'); while ($row = $res->fetch()) { $pr[$row['atpid']][] = $row; } //其他属性 $req = $pdo->query("select ap.`Name`, `at`.*, `at`.AttrParent_ParentId as atpid from attrparent ap, attr at where ap.ParentId = `at`.AttrParent_ParentId and `at`.AttrId not in (" . implode(',', $attr_ids) . ')' . " and `at`.AttrParent_ParentId = 26"); while ($rq = $req->fetch()) { $qr[] = $rq; } $page = $this->app->page(); $page->value('title', $rt); $page->value('det', $pr); $page->value('attq', $qr); $page->output(); }
/** * Prepare and execute a query. * * If the query fails, output a diagnostic message * @param string $query * Query to run * @return bool */ public function do_query($query) { // echo "do_query($query)\n"; // $stmt = $this->pdo->query( $query, PDO::FETCH_ASSOC ); // echo "PDO returned"; // var_dump($stmt); $string = preg_replace("/^#[^\n]*\$/m", "\n", $query); $string = preg_replace("/^(--[^-]).*/m", "\n", $string); $queries = preg_split('/;\\s*$/m', $string); foreach ($queries as $query) { $query = trim($query); if (!empty($query)) { $result = $this->pdo->query($query); if ($this->pdo->errorCode() == 0) { continue; } else { var_dump($result); var_dump($this->pdo->errorInfo()); // die( "Cannot execute $query: " . $this->pdo->errorInfo() ); } } } /******* * if ( $this->pdo->errorCode() == 0 ) { * //echo "returning the PDOStmt\n"; * return $stmt; * } * * // operation failed, so output description of where and why * $errorInfo = $this->pdo->errorInfo(); * echo "Oops, can't do query:\n {$query}\n in " * . basename( __FILE__) . " line " . __LINE__.":\n " * . $errorInfo[0] . ": " . $errorInfo[2] . "\n Call stack:\n"; * $backtrace = debug_backtrace(); * $dir_name = dirname( __FILE__ ); * $cwd_len = strlen( $dir_name ) + 1; * foreach ($backtrace as $frame ) { * echo " "; * if ( array_key_exists( 'class', $frame ) ) { * echo " class {$frame['class']}"; * if ( array_key_exists( 'function', $frame ) ) { * echo " method {$frame['function']}"; * } * } * else { * if ( array_key_exists( 'function', $frame ) ) { * echo " function {$frame['function']}"; * } * } * if ( array_key_exists( 'file', $frame ) ) { * echo " file ". substr( $frame['file'], $cwd_len ); * } * if ( array_key_exists( 'line', $frame ) ) { * echo " line {$frame['line']}"; * } * echo "\n"; * } ******/ return TRUE; }
public function insert($table, $dataobj) { if (is_array($dataobj)) { $dataobj = (object) $dataobj; } if (empty($dataobj) || !is_object($dataobj)) { return false; } if (isset($dataobj->id)) { unset($dataobj->id); } $fields = ''; $values = ''; foreach ($dataobj as $field => $value) { if ($fields) { $fields .= ', '; } if ($values) { $values .= ', '; } $fields .= "`" . $field . "`"; $values .= "'" . $value . "'"; } $sql = "INSERT INTO {$table} ({$fields}) VALUES({$values})"; $this->_db->query($sql); return $this->_db->lastInsertId(); }
/** * Prepare clean SMF forum */ public function forumPrepare() { $version = $this->askDefault("SMF Core Version", self::SMF_VERSION); // download $zipFile = sys_get_temp_dir() . DIRECTORY_SEPARATOR . 'smf-' . $version . '.zip'; if (!file_exists($zipFile)) { $url = 'http://download.simplemachines.org/index.php/smf_' . str_replace('.', '-', $version) . '_install.zip'; file_put_contents($zipFile, fopen($url, 'r')); } // clean dir $dir = $this->askDefault("Install directory path", '/var/www/html/smf'); if (is_dir($dir)) { $clean = $this->askDefault('Directory exists. Clean ' . $dir, 'y'); if ($clean == 'y') { $this->taskCleanDir([$dir])->run(); } } // extract $zip = new ZipArchive(); if ($zip->open($zipFile) === true) { $zip->extractTo($dir); $zip->close(); $this->say('Extracted ' . $zipFile); } // fix file and folder access $this->taskExecStack()->stopOnFail()->exec('chown www-data:www-data -R ' . $dir)->exec('chmod og+rwx -R ' . $dir)->run(); //database $dbName = $this->askDefault("MySQL DB_NAME", 'smf'); $rootPassword = $this->askDefault("MySQL root password", ''); $dbh = new PDO('mysql:host=localhost', 'root', $rootPassword); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $dbh->query("DROP DATABASE IF EXISTS {$dbName}"); $dbh->query("CREATE DATABASE {$dbName}"); $this->say('Created schema ' . $dbName); }
function getRecette($id) { $dbh = new PDO('mysql:host=localhost;dbname=Marmiton', 'root', 'root'); $sql1 = "SELECT * FROM Recette WHERE id = '" . $id . "'"; $recette = ''; $resultats = $dbh->query($sql1); while ($resultat = $resultats->fetchAll(PDO::FETCH_ASSOC)) { $recette = $resultat; } $sql2 = "SELECT * FROM `Ingredients` WHERE id_rec = '" . $id . "'"; $ing = ""; $resultats = $dbh->query($sql2); while ($resultat = $resultats->fetchAll(PDO::FETCH_ASSOC)) { $ing = $resultat; } $sql3 = "SELECT * FROM `Etape` WHERE id_recette = '" . $id . "'"; $etape = ''; $resultats = $dbh->query($sql3); while ($resultat = $resultats->fetchAll(PDO::FETCH_ASSOC)) { $etape = $resultat; } $sql4 = "SELECT * FROM `Tag` WHERE id_recette = '" . $id . "'"; $resultats = $dbh->query($sql4); $tag = ""; while ($resultat = $resultats->fetchAll(PDO::FETCH_ASSOC)) { $tag = $resultat; } $array = array('recette' => $recette, 'ing' => $ing, 'etape' => $etape, 'tag' => $tag); $json = json_encode($array); return $json; }
public function testEmptySpecificKeys() { $this->PDO->exec('INSERT INTO `' . PREFIX . 'table` (`key`, `foo`, `bar`) VALUES ("1", "", "")'); $Updater = new UpdaterForObject_MockTester($this->PDO); $Updater->update(new UpdaterObject_MockTester(array('foo' => 'test', 'bar' => 'test')), array()); $this->assertEquals(array('key' => '1', 'foo' => '', 'bar' => ''), $this->PDO->query('SELECT `key`, `foo`, `bar` FROM `' . PREFIX . 'table`')->fetch(PDO::FETCH_ASSOC)); }