PHP MySQL::insert方法代码示例

function parseFile($file, $type)
{
    $sql = new MySQL();
    $sql->connect('127.0.0.1', 'root', 'root');
    $objReader = PHPExcel_IOFactory::createReader($type);
    $chunkSize = 200;
    $i = 1;
    $sql->clear('price_liga');
    $r = array();
    for ($startRow = 0; $startRow <= 5000; $startRow += $chunkSize + 1) {
        $chunkFilter = new chunkReadFilter($startRow, $chunkSize);
        $objReader->setReadFilter($chunkFilter);
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($file);
        $data = $objPHPExcel->getActiveSheet()->toArray();
        foreach ($data as $k => $v) {
            if ($data[$k][0] == '') {
                unset($data[$k]);
            } else {
                $sql->insert('price_liga', array('id' => $i, 'cat_num' => $data[$k][0], 'brand' => ucwords(strtolower($data[$k][1])), 'article' => $data[$k][2], 'descr' => str_replace("'", "\\'", $data[$k][3]), 'model' => str_replace("'", "\\'", $data[$k][4]), 'size' => $data[$k][5], 'price' => $data[$k][6], 'amount' => $data[$k][8]), true);
                $i++;
            }
        }
    }
    //print_r($r);
    $sql->close();
    return array('counter' => $i);
}

function parseFile($file, $type)
{
    $sql = new MySQL();
    $sql->connect('127.0.0.1', 'root', 'root');
    $objReader = PHPExcel_IOFactory::createReader($type);
    $chunkSize = 200;
    $i = 1;
    $sql->clear('price_v8');
    for ($startRow = 0; $startRow <= 5000; $startRow += $chunkSize + 1) {
        $chunkFilter = new chunkReadFilter($startRow, $chunkSize);
        $objReader->setReadFilter($chunkFilter);
        $objReader->setReadDataOnly(true);
        $objPHPExcel = $objReader->load($file);
        $data = $objPHPExcel->getActiveSheet()->toArray();
        foreach ($data as $k => $v) {
            if (trim($data[$k][0]) == 'Артикул' || $data[$k][3] == '' || strstr($data[$k][3], 'камера') || $data[$k][7] == '') {
                unset($data[$k]);
            } else {
                $descr = str_replace('Ш', 'xSTUDEDx', trim($data[$k][3]));
                $descr = preg_replace('/[а-яА-Я]/', '', $descr);
                $sql->insert('price_v8', array('id' => $i, 'article' => trim($data[$k][0]), 'descr' => str_replace("'", "\\'", $descr), 'cat_num' => trim($data[$k][6]), 'season' => trim($data[$k][7]), 'price' => trim($data[$k][9]), 'amount' => trim(preg_replace('/[а-яА-Яa-zA-Z]{0,}/', '', $data[$k][10]))), true);
                $i++;
            }
        }
    }
    $sql->close();
    return array('counter' => $i);
}

function validationmail($email)
{
    require_once 'mysql/mysql.php';
    require 'mysql/mysql_settings.php';
    //Build mysql connection
    $mysql = new MySQL($mysql_user, $mysql_pw, $mysql_server, $mysql_db);
    //Create request
    $request = 'INSERT INTO validationmails(email) VALUES ("' . $email . '");';
    //Perform request
    $response = $mysql->insert($request);
    //Check if request was successfull
    if (!$response) {
        $echo = mysqli_error($mysql->getCon());
    } else {
        $id = $mysql->insert_id;
    }
    return email($email, '
			Hi ' . $user . ',
			um loszulegen klick einfach auf den Link und los geht\'s!
				
			http://fragdichab.de/?p=1&id=' . $id . '&req=validation
			
			Sch�ne Gr��e,
			Ruben & Jannes
			', 'Bestaetigungsmail');
}

function register($email, $password, $username)
{
    require_once 'mysql/mysql.php';
    require 'mysql/mysql_settings.php';
    /**
     * Establish new connection
     * Check if email is already registered
     * If so give out failure message, otherwise give out success
     */
    $mysql = new MySQL($mysql_user, $mysql_pw, $mysql_server, $mysql_db);
    /**
     *Check if email is registered
     */
    $request = 'SELECT user.idUser FROM user WHERE user.email="' . $email . '";';
    $response = $mysql->request($request);
    $response = json_decode(json_encode($response), true);
    //If so return failure message
    if (!empty($response)) {
        $echo = 'invalid email';
        return $echo;
    }
    /**
     * If not put user into database
     */
    $request = 'INSERT INTO user(name,password,email,valid) VALUES ("' . $username . '","' . $password . '","' . $email . '",0);';
    $response = $mysql->insert($request);
    //Check if insertion worked out
    if (!$response) {
        $echo = mysqli_error($mysql->getCon());
    } else {
        $echo = 'done';
    }
    //Return result
    return $echo;
}

 public static function signup($user)
 {
     $mysql = new MySQL();
     $id = $mysql->insert("user", $user);
     $mysql->closeCon();
     return $id;
 }

 public static function insertDiscuss($disObj)
 {
     $mysql = new MySQL();
     $res = $mysql->insert("page_discs", $disObj);
     $mysql->closeCon();
     PageService::updateDisNum($disObj['pageid']);
     return $res;
 }

 /**
  * This function will compress data for storage in `tbl_cache`.
  * It is left to the user to define a unique hash for this data so that it can be
  * retrieved in the future. Optionally, a `$ttl` parameter can
  * be passed for this data. If this is omitted, it data is considered to be valid
  * forever. This function utilizes the Mutex class to act as a crude locking
  * mechanism.
  *
  * @see toolkit.Mutex
  * @param string $hash
  *  The hash of the Cached object, as defined by the user
  * @param string $data
  *  The data to be cached, this will be compressed prior to saving.
  * @param integer $ttl
  *  A integer representing how long the data should be valid for in seconds.
  *  By default this is null, meaning the data is valid forever
  * @return boolean
  *  If an error occurs, this function will return false otherwise true
  */
 public function write($hash, $data, $ttl = null)
 {
     if (!Mutex::acquire($hash, 2, TMP)) {
         return false;
     }
     $creation = time();
     $expiry = null;
     $ttl = intval($ttl);
     if ($ttl > 0) {
         $expiry = $creation + $ttl * 60;
     }
     if (!($data = $this->compressData($data))) {
         return false;
     }
     $this->forceExpiry($hash);
     $this->Database->insert(array('hash' => $hash, 'creation' => $creation, 'expiry' => $expiry, 'data' => $data), 'tbl_cache');
     Mutex::release($hash, TMP);
     return true;
 }

 public function testFlush()
 {
     $connection = new MySQL(self::$pdo);
     $data1 = array('id' => 1, 'username' => 'username1');
     $data2 = array('id' => 2, 'username' => 'username2');
     $data3 = array('id' => 3, 'username' => 'username3');
     $connection->insert('user', $data1);
     $connection->insert('user', $data2);
     $connection->insert('user', $data3);
     $stmt = self::$pdo->prepare('SELECT COUNT(*) AS count FROM user');
     $stmt->execute();
     $count = $stmt->fetchColumn();
     $stmt->closeCursor();
     $this->assertEquals(3, $count);
     $connection->flush('user');
     $stmt = self::$pdo->prepare('SELECT COUNT(*) AS count FROM user');
     $stmt->execute();
     $count = $stmt->fetchColumn();
     $stmt->closeCursor();
     $this->assertEquals(0, $count);
 }

function parseFile($file, $type)
{
    $sql = new MySQL();
    $sql->connect('127.0.0.1', 'root', 'root');
    $objReader = PHPExcel_IOFactory::createReader($type);
    $sheets = $objReader->listWorksheetNames($file);
    $i = 1;
    $sql->clear('price_moscow');
    foreach ($sheets as $sheet) {
        $chunkSize = 200;
        if (strstr($sheet, 'Шины')) {
            $r = array();
            for ($startRow = 0; $startRow <= 4000; $startRow += $chunkSize + 1) {
                $chunkFilter = new chunkReadFilter($startRow, $chunkSize);
                $objReader->setReadFilter($chunkFilter);
                $objReader->setReadDataOnly(true);
                $objReader->setLoadSheetsOnly($sheet);
                $objPHPExcel = $objReader->load($file);
                $data = $objPHPExcel->getActiveSheet()->toArray();
                foreach ($data as $k => $v) {
                    if ($data[$k][2] == '' || $data[$k][2] == 'Модель') {
                        unset($data[$k]);
                    } else {
                        $season = strstr($data[$k][7], 'Летняя') ? 0 : (strstr($data[$k][7], 'Зимняя') ? 1 : 2);
                        $vtype = strstr($data[$k][8], 'Легковая') ? 0 : (strstr($data[$k][8], 'Грузовая') ? 1 : 2);
                        $stunds = $data[$k][10] == '' || $data[$k][10] == 'Нет' ? 0 : 1;
                        $xl = $data[$k][12] == '' || $data[$k][12] == 'Нет' ? 0 : 1;
                        $runflat = $data[$k][13] == '' || $data[$k][13] == 'Нет' ? 0 : 1;
                        $sql->insert('price_moscow', array('id' => $i, 'article' => trim($data[$k][0]), 'brand' => ucwords(strtolower(trim($data[$k][1]))), 'model' => str_replace("'", "\\'", trim($data[$k][2])), 'width' => trim($data[$k][3]), 'height' => trim($data[$k][4]), 'diameter' => (int) preg_replace('/[a-zA-Z]/', '', trim($data[$k][5])), 'weight_speed' => trim($data[$k][6]), 'season' => $season, 'v_type' => $vtype, 'studs' => $stunds, 'xl' => $xl, 'runflat' => $runflat, 'price_rrc' => $data[$k][17], 'price_opt' => $data[$k][18], 'amount' => 0), true);
                        $i++;
                    }
                }
            }
        }
    }
    $sql->close();
    return array('counter' => $i);
}

 $current_city = isset($_POST['city']) ? $_POST['city'] : '';
 $gender = isset($_POST['gender']) ? $_POST['gender'] : '';
 $bmonth = isset($_POST['bmonth']) ? $_POST['bmonth'] : '';
 $bday = isset($_POST['bday']) ? $_POST['bday'] : '';
 $byear = isset($_POST['byear']) ? $_POST['byear'] : '';
 if (empty($hometown)) {
     $hometown = $current_city;
 }
 list($firstname, $middlename, $lastname) = split(' ', $name);
 if (!isset($lastname)) {
     $lastname = $middlename;
     $middlename = '';
 }
 try {
     $db = new MySQL();
     $db->insert('login', array('username' => $username, 'access_level' => 1, 'last_login' => date('Y-m-d'), 'date_joined' => date('Y-m-d'), 'logins' => 1));
     $user_id = $db->insertID();
     $db->query('UPDATE login SET pass = PASSWORD("' . mysql_real_escape_string($password) . '") WHERE user_id = ' . $user_id);
     $db->insert('info', array('user_id' => $user_id, 'firstname' => $firstname, 'middlename' => $middlename, 'lastname' => $lastname, 'email' => $email, 'gender' => $gender, 'hometown' => $hometown, 'current_city' => $current_city, 'birth_month' => $bmonth, 'birth_day' => $bday, 'birth_year' => $byear));
     $db->insert('socialhns', array('user_id' => $user_id));
     if (LOCAL) {
         $db->insert('hns_desktop', array('user_id' => $user_id));
         $db->insert('homenetspaces', array('user_id' => $user_id));
     }
     if ($db->affectedRows() == 1) {
         $_SESSION['logged'] = true;
         $_SESSION['user_id'] = $user_id;
         $_SESSION['username'] = $username;
         $_SESSION['access_level'] = 1;
         $_SESSION['last_login'] = date('Y-m-d');
         if (isset($middlename) && !empty($middlename)) {

     $community = isset($_POST['city']) ? $_POST['city'] : '';
     $gender = isset($_POST['gender']) ? $_POST['gender'] : '';
     $bmonth = isset($_POST['bmonth']) ? $_POST['bmonth'] : '';
     $bday = isset($_POST['bday']) ? $_POST['bday'] : '';
     $byear = isset($_POST['byear']) ? $_POST['byear'] : '';
     if (empty($community)) {
         $community = $hometown;
     }
     list($firstname, $middlename, $lastname) = split(' ', $name);
     if (!$lastname) {
         $lastname = $middlename;
         unset($middlename);
     }
     try {
         $db = new MySQL();
         $db->insert(array('username' => $username, 'password' => $password, 'access_level' => 1, 'last_login' => date('Y-m-d'), 'date_joined' => date('Y-m-d'), 'last_login_ip' => $ip), 'login');
         $db->insert(array('user_id' => $db->insertID(), 'firstname' => $firstname, 'middlename' => $middlename, 'lastname' => $lastname, 'email' => $email, 'gender' => $gender, 'hometown' => $hometown, 'community' => $community, 'birth_month' => $bmonth, 'birth_day' => $bday, 'birth_year' => $byear, 'logins' => 1));
     } catch (Exception $e) {
         echo $e->getMessage();
         exit;
     }
     loggedIn();
 }
 if (isset($_GET['p'])) {
     if ($_GET['p'] == 'username') {
         try {
             $db = new MySQL();
             $db->query('SELECT username FROM login WHERE username = "' . $_GET['username'] . '"');
             echo $db->numRows();
         } catch (Exception $e) {
             echo $e->getMessage();

	$db->sfquery(array('SELECT * FROM misc WHERE username = "%s" AND pass = PASSWORD("%s") LIMIT 1',$_POST['username'],$_POST['password']));
	if ($db->numRows() > 0) {
		$row = $db->fetchAssocRow();
		loggedIn($row);
	} else die('0');
} catch(Exception $e) {
	echo $e->getMessage();
	exit();
}
} elseif (isset($_POST['register'])) {
$username = (isset($_POST['username'])) ? $_POST['username'] : '';
$password = (isset($_POST['password'])) ? $_POST['password'] : '';
try {
	$db = new MySQL();
	$db->insert('misc', array(
		'username'=>$username,
		'date_joined'=>date('Y-m-d'),
	));
	$user_id = $db->insertID();
	$db->query('UPDATE misc SET pass = PASSWORD("'.mysql_real_escape_string($password).'") WHERE user_id = '.$user_id);
	if ($db->affectedRows() == 1) {
		$_SESSION['logged'] = true;
		$_SESSION['user_id'] = $user_id;
		$_SESSION['username'] = $username;
		die('true');
	} else die('false');
} catch(Exception $e) {
	echo $e->getMessage();
	exit();
}
loggedIn();
}

} else {
    if ($action == 'submit_deljob') {
        $DB = new MySQL(Loader::$config['dbconf']['main']['dbname'], Loader::$config['dbconf']['main']['user'], Loader::$config['dbconf']['main']['passwd'], Loader::$config['dbconf']['main']['host'], Loader::$config['dbconf']['main']['port']);
        $sql = "update curweekjob set enable=0 where id=" . $req;
        $ret = $DB->executeSQL($sql);
        if (!$ret) {
            echo json_encode(array('ret' => 'false', '更新失败!'));
            die;
        }
        echo json_encode(array('ret' => 'true', '更新成功!'));
    } else {
        if ($action == 'submit_addlastjob') {
            $DB = new MySQL(Loader::$config['dbconf']['main']['dbname'], Loader::$config['dbconf']['main']['user'], Loader::$config['dbconf']['main']['passwd'], Loader::$config['dbconf']['main']['host'], Loader::$config['dbconf']['main']['port']);
            $val = array('job' => $req['job'], 'classmate' => $req['classmate'], 'process' => $req['process'], 'teamname' => $req['teamname'], 'plantime' => json_encode($req['plantime']));
            $datatypes = array('str', 'str', 'int', 'str', 'str');
            $ret = $DB->insert("lastweekjob", $val, '', $datatypes);
            if (!$ret) {
                echo json_encode(array('ret' => 'false', '更新失败!'));
                die;
            }
            echo json_encode(array('ret' => 'true', '更新成功!'));
        } else {
            if ($action == 'submit_dellastjob') {
                $DB = new MySQL(Loader::$config['dbconf']['main']['dbname'], Loader::$config['dbconf']['main']['user'], Loader::$config['dbconf']['main']['passwd'], Loader::$config['dbconf']['main']['host'], Loader::$config['dbconf']['main']['port']);
                $sql = "update lastweekjob set enable=0 where id=" . $req;
                $ret = $DB->executeSQL($sql);
                if (!$ret) {
                    echo json_encode(array('ret' => 'false', '更新失败!'));
                    die;
                }
                echo json_encode(array('ret' => 'true', '更新成功!'));

COLLATE=\'utf8_bin\'
ENGINE=MyISAM;';
        if (defined('UPGRADE')) {
            $sql = 'ALTER TABLE `vm`
	ADD COLUMN `vz_id` INT(10) NOT NULL DEFAULT \'0\' AFTER `vm_id`,
	ADD INDEX `idx_vz_id` (`vz_id`);

ALTER TABLE `user`
	ADD INDEX `idx_name` (`name`),
	ADD INDEX `idx_language` (`language`),
	ADD INDEX `idx_date_created` (`date_created`);';
        }
        $db->executeSQL($sql);
        // Create administrator
        if (!defined('UPGRADE')) {
            $db->insert('user', array('is_admin' => 1, 'is_active' => 1, 'name' => $_SESSION['name'], 'email_address' => $_SESSION['email_address'], 'password' => hashed($_SESSION['password']), 'language' => $_SESSION['language'], 'date_created' => date('Y-m-d H:i:s')));
        }
        $configurations = '<?php
define(\'INSTALLED\', 1);
define(\'SVMC_VERSION\', \'' . $version . '\');

$config[\'dbHost\'] = \'' . $data->dbHost . '\';
$config[\'dbUser\'] = \'' . $data->dbUser . '\';
$config[\'dbPass\'] = \'' . $data->dbPass . '\';
$config[\'dbName\'] = \'' . $data->dbName . '\';
$config[\'language\'] = \'' . $_SESSION['language'] . '\';
?>';
        file_put_contents(ROOT . 'configuration.php', $configurations);
        if (defined('UPGRADE')) {
            $out .= '<h1>' . UPGRADE_COMPLETED . '</h1>

 if ($cat_id == 0) {
     foreach ($GLOBALS['news_cat'] as $cur_cat) {
         if ($cur_cat['web_id'] != $org_id) {
             continue;
         }
         if ($org_id != $dst_id) {
             $db->update($setting['db']['pre'] . "news_cat", array("web_id" => $dst_id), array("cat_id", "n=", $cur_cat['cat_id']));
         }
         $db->select($pre_org . "news_show", "*", array("cat_id", "n=", $cur_cat['cat_id']), array("order" => "news_id asc"));
         $id_list = array();
         while ($record = $db->GetRS()) {
             $the_id = array();
             $the_id['old'] = $record['news_id'];
             $record['news_id'] = 0;
             $record['web_id'] = $dst_id;
             $db2->insert($pre_dst . "news_show", $record, true);
             $the_id['new'] = $db2->GetInsertId();
             $id_list[] = $the_id;
         }
         for ($i = 0, $m = count($id_list); $i < $m; $i++) {
             if ($id_list[$i]['new'] == 0) {
                 break;
             }
             $db->select($pre_org . "news_detail", "*", array("news_id", "n=", $id_list[$i]['old']), array("order" => "news_id asc, page asc"));
             while ($record = $db->GetRS()) {
                 $record['id'] = 0;
                 $record['news_id'] = $id_list[$i]['new'];
                 $db2->insert($pre_dst . "news_detail", $record, true);
             }
             $db->delete($pre_org . "news_show", array("news_id", "n=", $id_list[$i]['old']));
             $db->delete($pre_org . "news_detail", array("news_id", "n=", $id_list[$i]['old']));