PHP SQL::execute方法代码示例

	public static function add($username = "", $md5_pw_hash = "", $email = null, $enabled = 1,  $acces_level = 25) {
		$date_created = time();
		$md5_pw = md5($md5_pw_hash, false);

		$sql = 'INSERT INTO ' . self::getFullTableName() . ' (
			username,
			email,
			password,
			enabled,
			access_level,
			date_created
		) VALUES (
			:username,
			:email,
			:password,
			:enabled,
			:access_level,
			:date_created

		)';
		return SQL::execute(
			self::getConnection(),
			$sql,
			array(
				"username" => $username,
				"email" => $email,
				"password" => $md5_pw,
				"enabled" => $enabled,
				"access_level" => $acces_level,
				"date_created" => $date_created
			)
		);

	}

	/**
	 * @param int $fid - forums user id
	 * @param int $gid - game user id
	 * @param int $from - bann beginn
	 * @param int $till - bann ends
	 * @param int $level - Bann is active for: Forum (1), Game (2) or both (0)
	 * @param string $unbannedby - User is Unbanned by
	 * @param string $unbannip - IP from Unbanner
	 * @return int
	 */
	public static function add($fid, $gid, $from, $till, $unbannedby, $unbannip, $level = 0) {
		$sql = 'INSERT INTO ' . self::getFullTableName() . ' (
			banned_fid,
			banned_gid,
			banned_from,
			banned_till,
			level,
			unbannedby,
			unbannip,
			date
		) VALUES (
			:fid,
			:gid,
			:from,
			:till,
			:level,
			:unbannedby,
			:unbannip,
			:date
		)';

		return SQL::execute(self::getConnection(), $sql,
			array(
				"fid" => $fid,
				"gid" => $gid,
				"from" => $from,
				"level" => $level,
				"till" => $till,
				"unbannedby" => $unbannedby,
				"unbannip" => $unbannip,
				"date" => common_functions::currentdate(),
			)
		);
	}

	/**
	 * @param int $group_id
	 * @param int $user_id
	 * @param int $leader
	 * @param int $pending
	 * @return bool
	 */
	public static function add($group_id, $user_id, $leader = 0, $pending = 0) {
		$sql = 'INSERT INTO ' . self::getFullTableName() . ' (
			group_id,
			user_id,
			group_leader,
			user_pending
		) VALUES (
			:g_id,
			:u_id,
			:g_leader,
			:pending
		)';

		$result = SQL::execute(self::getConnection(), $sql, array("g_id" => $group_id, "u_id" => $user_id, "g_leader" => $leader, "pending" => $pending));

		if($result !== false) {
			// Include libs
			require_once(LIB_DIR . DS . 'class.phpbb_account.php');
			// Empty permission for creating new
			phpbb_account::clearUserPerms($user_id);

			// Send PM new Group
			if(SEND_PM_GROUPCHANGE)
				get_phpbb_info::$instance->sendPM(output::newGroupPM($user_id, phpbb_groups::getGroupName($group_id)), SYSTEM_USER, "Willkommen in der Benutzergruppe " . phpbb_groups::getGroupName($group_id), $user_id);

			return true;
		}
		return false;
	}

 public function execute($db)
 {
     $sql_result = parent::execute($db);
     $result = NULL;
     if ($sql_result && mysqli_num_rows($sql_result) != 0) {
         $result = mysqli_fetch_array($sql_result, MYSQL_ASSOC);
     }
     $this->set_return_data($result);
 }

	public static function delete($accid, $realm = -1) {
		$sql = 'DELETE FROM ' . self::getFullTableName() . ' WHERE accountId = :id AND realmId = :realm';

		return SQL::execute(self::getConnection(), $sql,
			array(
				"id" => $accid,
				"realm" => $realm
			)
		);
	}

	/**
	 * @param int $accid - account id
	 * @param int $unbandate - when is ban ends
	 * @return int
	 */
	public static function delete($accid, $unbandate) {
		$sql = 'DELETE FROM ' . self::getFullTableName() . ' WHERE id = :id AND unbandate = :unban';

		return SQL::execute(self::getConnection(), $sql,
			array(
				"id" => $accid,
				"unban" => $unbandate
			)
		);
	}

	/**
	 * @param int $charguid - character guid
	 * @param int $oldacc - old account id
	 * @param int $newacc - new account id
	 * @return int
	 */
	public static function add($charguid, $oldacc, $newacc) {
		$sql = 'INSERT INTO ' . self::getFullTableName() . ' (charguid, oldaccountid, newaccountid, date) VALUES (:char, :old, :new, :date)';

		return SQL::execute(self::getConnection(), $sql,
			array(
				"char" => $charguid,
				"old" => $oldacc,
				"new" => $newacc,
				"date" => time()
				)
		);
	}

	/**
	 * @param int $user_id - forum user id
	 * @param int $date - donate date
	 * @param  int $money - donate money
	 * @return int
	 */
	public static function get($user_id, $date, $money) {
		$sql = "SELECT * FROM " . self::getFullTableName() . ' WHERE user_id = :user AND date = :date AND money = :money';

		return SQL::execute(self::getConnection(),
			$sql,
			array(
				"user" => $user_id,
				"date" => $date,
				"money" => $money
			)
		);
	}

	/**
	 * @param int $user_id
	 * @param int $cost_id
	 * @return bool
	 */
	public static function add($user_id, $cost_id) {
		$sql = 'INSERT INTO ' . self::getFullTableName() . ' (
			user_id,
			cost_id,
			`date`
		) VALUES (
			:u_id,
			:cost_id,
			:date
		)';

		$result = SQL::execute(self::getConnection(), $sql, array("u_id" => $user_id, "cost_id" => $cost_id, "date" => time()));

		if($result !== false)
			return true;
		return false;
	}

	public static function add($type, $user_id, $ip, $date, $operation, $data, $report_id = 0, $forum_id = 0, $topic_id = 0) {
		$sql = 'INSERT INTO ' . self::getFullTableName() . ' (
			log_type,
			user_id,
			forum_id,
			topic_id,
			reportee_id,
			log_ip,
			log_time,
			log_operation,
			log_data
		) VALUES (
			:type,
			:id,
			:f_id,
			:t_id,
			:report_id,
			:ip,
			:time,
			:operation,
			:data
		)';

		SQL::execute(self::getConnection(), $sql,
			array(
				"type" => $type,
				"id" => $user_id,
				"f_id" => $forum_id,
				"t_id" => $topic_id,
				"report_id" => $report_id,
				"ip" => $ip,
				"time" => $date,
				"operation" => $operation,
				"data" => $data
			)
		);
	}

	/**
	 * @param int $id
	 * @param string $reason
	 * @param int $change_points
	 * @return int
	 */
	public static function update($id, $reason = "Point change", $change_points = 0) {
		// Create entry if not exists
		if(! self::exists($id))
			self::add($id, $change_points);

		// Get userdata
		$tmp_userdata = self::get($id);

		// Set new Data
		$params["new_points"] = $tmp_userdata["points_curr"] + $change_points;
		$params["id"] = $id;
		if($change_points > 0)
			$params["new_life_points"] = $tmp_userdata["points_life"] + $change_points;

		$sql = 'UPDATE ' . self::getFullTableName() . ' SET points_curr = :new_points' . (($change_points > 0) ? ', points_life = :new_life_points' : '') . ' WHERE user_id = :id';

		// Add to VIP-Group if reaches a specific amount of points
		self::check_vip($id, $params["new_points"]);

		// Execute
		if(SQL::execute(self::getConnection(), $sql, $params) !== false)
			return points_log::add($id, $change_points, $reason);
		return false;
	}

	/**
	 * @param int $date - log date (unix)
	 * @return int
	 */
	public static function getByDate($date) {
		return SQL::execute(self::getConnection(), 'SELECT FROM ' . self::getFullTableName() . ' WHERE date = :date', array("date" => $date));
	}

?>
<!DOCTYPE html>
<html>
<head>
	<title>Таланты</title>
	<link rel="stylesheet" type="text/css" href="https://cdnjs.cloudflare.com/ajax/libs/chosen/1.4.2/chosen.css">
</head>
<body>
<?php 
if ($_GET['email'] != '') {
    $email = $_GET['email'];
    foreach (SQL::execute("SELECT * FROM competency;") as $e) {
        $competency[$e['id']] = $e['name_ru'];
    }
    $ratings = array(1 => "Основной", 2 => "Промежуточный", 3 => "Опытный", 4 => "Передовой", 5 => "Эксперт");
    $profile = SQL::execute("SELECT P.*,C.name_ru FROM `kontrotenko_super_new`.`profile_item` P LEFT JOIN `kontrotenko_super_new`.`competency` C ON P.competency=C.id  WHERE P.email='{$email}';");
    echo "<button class=add_profile>Добавить</button><form method=post><table> <thead> <tr> <th>#</th> <th>Навык</th> <th>Уровень</th> </tr> </thead> <tbody>";
    foreach ($profile as $e) {
        echo '<tr><td>' . $e['id'] . '</td><td>';
        echo "<select name='prof[" . $e['id'] . "]' class='chzn'>";
        foreach ($competency as $k => $v) {
            $selected = $k == $e['competency'] ? 'selected' : '';
            echo '<option ' . $selected . ' value=' . $k . '>' . $v . '</option>';
        }
        echo '</select></td><td>';
        echo "<select name='rat[" . $e['id'] . "]' class='chzn'>";
        foreach ($ratings as $k => $v) {
            $selected = $k == $e['rating'] ? 'selected' : '';
            echo '<option ' . $selected . ' value=' . $k . '>' . $v . '</option>';
        }
        echo '</select></td></tr>';

	/**
	 * @param string $code
	 * @return int
	 */
	public static function delete($code) {
		return SQL::execute(self::getConnection(), 'DELETE FROM ' . self::getFullTableName() . ' WHERE code = :code', array("code" => $code));
	}

     }
 }
 $ps->params = $params;
 if ($ps->checkSignature($_GET['SignatureValue'])) {
     //Данные прошли проверку
     //if($ps->params['Type']=='balance'){
     $rs = $ST->select("SELECT * FROM sc_income \r\n\t\t\t\tWHERE \r\n\t\t\t\t\tuserid=" . intval($ps->params['UserId']) . "\r\n\t\t\t\t\tAND pay_id=" . intval($ps->InvId) . "\r\n\t\t\t\t\tAND type='robokassa'");
     if ($rs->next()) {
         //перевод уже был, всё ок
         echo 'OK' . $_GET['InvId'];
         exit;
     } else {
         if ($ps->params['Type'] == 'balance') {
             //пополним баланс
             $ST->update('sc_users', array('balance=balance+' . floatval($ps->OutSum)), "u_id=" . intval($ps->params['UserId']));
             $rs = $ST->execute("SELECT balance FROM sc_users WHERE u_id=" . intval($ps->params['UserId']));
             if ($rs->next()) {
                 $ST->insert('sc_income', array('userid' => intval($ps->params['UserId']), 'sum' => floatval($ps->OutSum), 'balance' => floatval($rs->getFloat('balance')), 'type' => 'robokassa', 'description' => 'Приход с робокассы', 'pay_id' => intval($ps->InvId), 'pay_string' => $_SERVER['QUERY_STRING']));
             }
         }
         //если указан номер заявки
         if (isset($ps->params['OrderId'])) {
             $rs = $ST->select("SELECT * FROM sc_users WHERE u_id=" . intval($ps->params['UserId']));
             if ($rs->next()) {
                 $user = $rs->getRow();
                 //						$rs=$ST->execute("SELECT * FROM sc_cc_order WHERE userid=".intval($ps->params['UserId'])." AND id=".intval($ps->params['OrderId']));
                 $rs = $ST->select("SELECT * FROM sc_shop_order WHERE userid=" . intval($ps->params['UserId']) . " AND id=" . intval($ps->params['OrderId']));
                 if ($rs->next() && floatval($ps->OutSum) >= $rs->getInt('total_price')) {
                     //							$ST->update('sc_users',array('balance=balance-'.$rs->getInt('summ')),"u_id=".intval($ps->params['UserId']));
                     $ST->update('sc_shop_order', array('pay_time' => date('Y-m-d H:i:s'), 'pay_status' => 'ok'), 'id=' . intval($ps->params['OrderId']));
                     //Оплата заказа