PHP type::prepare方法代碼示例

/**
 * Funktion zum endgültigem Löschen von Produkten
 * @param type $id
 * @param type $db
 */
function deleteProduct($id, $db)
{
    $user_id = $_SESSION['user_id'];
    //debug($id);
    try {
        $db->beginTransaction();
        $stmt = $db->prepare('SELECT ' . 'products.product_image_path ' . 'FROM products ' . 'WHERE products.product_id = ?');
        $stmt->execute(array($id));
        $image_path = $stmt->fetch(PDO::FETCH_ASSOC);
        $stmt = $db->prepare('DELETE ' . 'FROM package_has_products ' . 'WHERE package_has_products.product_id = ? ' . 'AND package_has_products.user_id = ?');
        $stmt->execute(array($id, $user_id));
        $stmt = $db->prepare('DELETE ' . 'FROM products ' . 'WHERE products.product_id = ?');
        $stmt->execute(array($id));
        $stmt = $db->prepare('DELETE ' . 'FROM product_has_category ' . 'WHERE product_has_category.product_id = ?');
        $stmt->execute(array($id));
        $stmt = $db->prepare('SELECT ' . 'product_has_properties.properties_id ' . 'FROM product_has_properties ' . 'WHERE product_has_properties.product_id = ?');
        $stmt->execute(array($id));
        $properties = $stmt->fetchAll(PDO::FETCH_ASSOC);
        foreach ($properties as $key => $property) {
            $stmt = $db->prepare('DELETE ' . 'FROM properties ' . 'WHERE property_id = ?');
            $stmt->execute(array($property['properties_id']));
        }
        $stmt = $db->prepare('DELETE ' . 'FROM product_has_properties ' . 'WHERE product_has_properties.product_id = ?');
        $stmt->execute(array($id));
        $stmt = $db->prepare('DELETE ' . 'FROM product_has_subcategory ' . 'WHERE product_has_subcategory.product_id = ?');
        $stmt->execute(array($id));
        $stmt = $db->prepare('DELETE ' . 'FROM user_has_products ' . 'WHERE user_has_products.product_id = ? ' . 'AND user_has_products.user_id = ?');
        $stmt->execute(array($id, $user_id));
        $db->commit();
    } catch (PDOException $e) {
        $db->rollBack();
        $e->getMessage();
    }
    if (empty($e)) {
        $img = '';
        if (is_dir($image_path['product_image_path']) && ($pp = opendir($image_path['product_image_path']))) {
            while (($file = readdir($pp)) !== false) {
                if ($file != "." && $file != "..") {
                    $img[] = $file;
                }
            }
            closedir($pp);
        }
        foreach ($img as $key => $image) {
            unlink($image_path['product_image_path'] . $image);
        }
        rmdir($image_path['product_image_path']);
        return true;
    } else {
        //var_dump($e);
        return false;
    }
}

/**
 * Funktion zum laden von ähnlichen Artikeln für die Detailseite
 * @param type $id ProduktID
 * @param type $category KategorieID
 * @param type $subcategory UnterkategorieID
 * @param type $db Datenbankobject
 * @return type Array / Bool
 */
function getSimilarProducts($productId, $userId, $category, $subcategory, $db)
{
    $actualdate = date('Y-m-d H:i:s');
    $stmt = $db->prepare('SELECT ' . 'product_has_subcategory.product_id, ' . 'product_has_category.product_id, ' . 'products.product_name, ' . 'products.product_description, ' . 'products.product_google_description, ' . 'products.product_image_path, ' . 'products.product_price, ' . 'products.product_start, ' . 'products.product_end, ' . 'products.product_status, ' . 'subcategories.subcategory_id, ' . 'subcategories.subcategory_name, ' . 'categories.category_id, ' . 'categories.category_name ' . 'FROM product_has_subcategory ' . 'JOIN product_has_category ON product_has_category.product_id = product_has_subcategory.product_id ' . 'JOIN categories ON categories.category_id = product_has_category.category_id ' . 'JOIN subcategories ON subcategories.subcategory_id = product_has_subcategory.subcategory_id ' . 'JOIN products ON products.product_id = product_has_subcategory.product_id ' . 'JOIN user_has_products ON products.product_id = user_has_products.product_id ' . 'WHERE product_has_subcategory.subcategory_id = ' . $subcategory . ' ' . 'AND product_has_subcategory.product_id != ' . $productId . ' ' . 'AND user_has_products.user_id =  ' . $userId . ' ' . 'AND products.product_status = 1');
    $stmt->execute();
    $subcategoryResult = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($subcategoryResult as $key => $res) {
        $dateStart = explode(' ', $res['product_start']);
        $dateEnd = explode(' ', $res['product_end']);
        $dateStart[0] = explode('-', $dateStart[0]);
        $dateStart[1] = explode(':', $dateStart[1]);
        $dateEnd[0] = explode('-', $dateEnd[0]);
        $dateEnd[1] = explode(':', $dateEnd[1]);
        $stmpStart = mktime($dateStart[1][0], $dateStart[1][1], $dateStart[1][2], $dateStart[0][1], $dateStart[0][2], $dateStart[0][0]);
        $stmpEnd = mktime($dateEnd[1][0], $dateEnd[1][1], $dateEnd[1][2], $dateEnd[0][1], $dateEnd[0][2], $dateEnd[0][0]);
        $subcategoryResult[$key]['product_timestamp_start'] = $stmpStart;
        $subcategoryResult[$key]['product_timestamp_end'] = $stmpEnd;
    }
    if (empty($subcategoryResult)) {
        $stmt = $db->prepare('SELECT ' . 'product_has_category.product_id, ' . 'product_has_subcategory.product_id, ' . 'products.product_name, ' . 'products.product_description, ' . 'products.product_google_description, ' . 'products.product_image_path, ' . 'products.product_price, ' . 'products.product_start, ' . 'products.product_end, ' . 'categories.category_id, ' . 'categories.category_name, ' . 'subcategories.subcategory_id, ' . 'subcategories.subcategory_name ' . 'FROM product_has_category ' . 'JOIN product_has_subcategory ON product_has_subcategory.product_id = product_has_category.product_id ' . 'JOIN subcategories ON subcategories.subcategory_id = product_has_subcategory.subcategory_id ' . 'JOIN categories ON categories.category_id = product_has_category.category_id ' . 'JOIN products ON products.product_id = product_has_category.product_id ' . 'JOIN user_has_products ON products.product_id = user_has_products.product_id ' . 'WHERE product_has_category.category_id = ' . $category . ' ' . 'AND product_has_category.product_id != ' . $productId . ' ' . 'AND user_has_products.user_id =  ' . $userId . ' ' . 'AND products.product_status = 1');
        $stmt->execute();
    }
    $categoryResult = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($categoryResult as $key => $res) {
        $dateStart = explode(' ', $res['product_start']);
        $dateEnd = explode(' ', $res['product_end']);
        $dateStart[0] = explode('-', $dateStart[0]);
        $dateStart[1] = explode(':', $dateStart[1]);
        $dateEnd[0] = explode('-', $dateEnd[0]);
        $dateEnd[1] = explode(':', $dateEnd[1]);
        $stmpStart = mktime($dateStart[1][0], $dateStart[1][1], $dateStart[1][2], $dateStart[0][1], $dateStart[0][2], $dateStart[0][0]);
        $stmpEnd = mktime($dateEnd[1][0], $dateEnd[1][1], $dateEnd[1][2], $dateEnd[0][1], $dateEnd[0][2], $dateEnd[0][0]);
        $categoryResult[$key]['product_timestamp_start'] = $stmpStart;
        $categoryResult[$key]['product_timestamp_end'] = $stmpEnd;
    }
    if (empty($subcategoryResult) && !empty($categoryResult)) {
        $result = $categoryResult;
        return $result;
    }
    if (!empty($subcategoryResult) && empty($categoryResult)) {
        $result = $subcategoryResult;
        return $result;
    }
    if (empty($subcategoryResult) && empty($categoryResult)) {
        return false;
    }
}

 /**
  * 
  * @return type
  */
 public function getMandatoryFields()
 {
     $mandatoryQuery = "SELECT name FROM cfg_forms_fields WHERE mandatory = '1' " . "AND field_id IN (\n                    SELECT\n                        fi.field_id\n                    FROM\n                        cfg_forms_fields fi, cfg_forms_blocks fb, cfg_forms_blocks_fields_relations fbf, cfg_forms_sections fs, cfg_forms f\n                    WHERE\n                        fi.field_id = fbf.field_id\n                    AND\n                        fbf.block_id = fb.block_id\n                    AND\n                        fb.section_id = fs.section_id\n                    AND\n                        fs.form_id = f.form_id\n                    AND\n                        f.route = :route\n            )";
     $stmt = $this->dbconn->prepare($mandatoryQuery);
     $stmt->bindParam(':route', $this->formRoute, \PDO::PARAM_STR);
     $stmt->execute();
     $mandatoryFieldList = $stmt->fetchAll(\PDO::FETCH_ASSOC);
     return array_column($mandatoryFieldList, 'name');
 }

/**
 * Prints out the title of the supplied topic
 * 
 * @param type $db DB Connection to use
 * @param type $xtopic The topic to get the title of 
 * @param type $xbegin 
 */
function get_title($db, $xtopic, $xbegin)
{
    if (is_numeric($xtopic) && is_numeric($xbegin)) {
        $query = $db->prepare("SELECT topic_title FROM topics WHERE topic_id = ?");
        $query->execute(array($xtopic));
        if ($query->rowCount() < 1) {
            die;
        }
        $topic = $query->fetch(PDO::FETCH_ASSOC);
        echo $topic['topic_title'];
    }
}

 /**
  * SQL query in the traditional from
  * 
  * @param type $query
  * @param type $params
  * @return type
  */
 public function raw_query($query, $params = null)
 {
     if (!empty($query)) {
         $this->prepared_query = $this->db->prepare($query);
         $this->prepared_query->execute($params);
         return $this->prepared_query;
     } elseif ($this->mode == 'dev') {
         throw new Exception('Empty query');
     } else {
         die;
     }
 }

/**
 * Funktion zum zufälligen Laden eines Produktes
 * @param type $db
 * @return type
 */
function getRandomProduct($db)
{
    $stmt = $db->prepare('SELECT ' . 'product_id, ' . 'product_iframe ' . 'FROM ' . 'products ' . 'WHERE products.product_proof = ? ' . 'AND products.product_featured = ? ' . 'AND products.product_id = ? ');
    $rand = rand(1, 4);
    if ($stmt != false) {
        $stmt->execute(array(1, 0, $rand));
        $result = $stmt->fetch(PDO::FETCH_ASSOC);
        if ($result) {
            return $result;
        } else {
            return false;
        }
    }
}

 public function call($sql)
 {
     try {
         //$this->con->beginTransaction();
         $stmt = $this->con->prepare($sql);
         $this->con->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, TRUE);
         $this->con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
         $stmt->execute();
         $retorno = $stmt->fetch(PDO::FETCH_NAMED);
         //$this->con->commit();
         Conect::destroy();
         return $retorno;
     } catch (Exception $ex) {
         throw $ex;
     }
 }

/**
 * Function to add a record in the history table. The function receives a PDO object,
 * creates the insert query via prepared statements, to ensure data validity, and 
 * registers it in the database.
 * @param type $pdo     PDO object to use for the insertion
 * @param type $data    Data array containing the information to be entered in the 
 *                      database
 * @return boolean      For error checking purposes, return true on success and 
 *                      false on error
 */
function store_game_in_db($pdo, $data)
{
    if (!isset($data['first_player_name']) || !isset($data['second_player_name']) || !isset($data['player_won']) || !isset($data['game_date'])) {
        return false;
    }
    try {
        $stmt = $pdo->prepare('insert into history (first_player_name, second_player_name, player_won, game_date) ' . 'values (:first_player_name, :second_player_name, :player_won, :game_date)');
        $stmt->bindValue(":first_player_name", $data['first_player_name']);
        $stmt->bindValue(":second_player_name", $data['second_player_name']);
        $stmt->bindValue(":player_won", $data['player_won']);
        $stmt->bindValue(":game_date", $data['game_date']);
        $stmt->execute();
    } catch (PDOException $e) {
        return false;
    }
    return true;
}

 /**
  *
  * 
  */
 public function execute($sql, $values = null)
 {
     //
     $stmt = $this->_pdo->prepare($sql);
     //
     if (is_array($values)) {
         foreach ($values as $token => $value) {
             $stmt->bindValue($token, $value);
         }
     }
     //
     try {
         $stmt->execute();
     } catch (PDOException $ex) {
         throw new Exception($ex->getMessage(), intval($ex->getCode()));
     }
     //
     return $stmt;
 }

/**
 * 
 * @param type $connection
 * @param type $sql
 * @param type $values
 * @param type $conditions
 * @return string
 */
function execUpdate($connection, $sql, $values, $conditions)
{
    $updValues = mixVal_Cond($values, $conditions);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    try {
        $stmt = $connection->prepare($sql);
        $stmt->execute($updValues);
        $filas = $stmt->rowCount();
        return $filas;
    } catch (Exception $ex) {
        echo "Error code: " . $ex->getCode() . " __ Error message: " . $ex->getMessage();
        return "Error";
    }
}

 /**
  * Funzione per controllare accesso alle pagine
  * limitandolo soloa gli utenti che hanno effettuato il login
  * 
  * @param type $mysqli Connessione al db
  * @return boolean
  */
 function login_check($mysqli)
 {
     // Verifica che tutte le variabili di sessione siano impostate correttamente
     if (isset($_SESSION['user_id'], $_SESSION['username'], $_SESSION['login_string'])) {
         $user_id = $_SESSION['user_id'];
         $login_string = $_SESSION['login_string'];
         $username = $_SESSION['username'];
         $user_browser = $_SERVER['HTTP_USER_AGENT'];
         // reperisce la stringa 'user-agent' dell'utente.
         if ($stmt = $mysqli->prepare("SELECT password FROM users WHERE id = ? LIMIT 1")) {
             $stmt->bind_param('i', $user_id);
             // esegue il bind del parametro '$user_id'.
             $stmt->execute();
             // Esegue la query creata.
             $stmt->store_result();
             if ($stmt->num_rows == 1) {
                 // se l'utente esiste
                 $stmt->bind_result($password);
                 // recupera le variabili dal risultato ottenuto.
                 $stmt->fetch();
                 $login_check = hash('sha512', $password . $user_browser);
                 if ($login_check == $login_string) {
                     return true;
                     // Login eseguito!!!!
                 } else {
                     return false;
                     //  Login NON eseguito
                 }
             } else {
                 return false;
                 // Login non eseguito
             }
         } else {
             return false;
             // Login non eseguito
         }
     } else {
         return false;
         // Login non eseguito
     }
 }

 /**
  * Function to get all papers completed in time frame to scrape for marks, and then compare the class_totals and finish reports.
  *
  * @param type $mysqli - database object
  * @param type $username - user for db access
  * @param type $password - the users password
  * @param type $rootpath - root path of site
  * @param type $userid - the user running the script
  * @param type $start_dateSQL - start date range of papers checked
  * @param type $end_dateSQL - end date range of papers checked
  * @param type $server - the server we are checking
  * @param type $paperid - the papers we want to check (optional, all if not supplied)
  */
 public function process_papers($mysqli, $username, $password, $rootpath, $userid, $start_dateSQL, $end_dateSQL, $server, $paperid = '')
 {
     $papers = array();
     if ($paperid != '') {
         $result = $mysqli->prepare("SELECT crypt_name, property_id, paper_title, DATE_FORMAT(start_date,'%d/%m/%Y'), DATE_FORMAT(start_date,'%Y%m%d%H%i%s'), DATE_FORMAT(end_date,'%Y%m%d%H%i%s') FROM properties WHERE property_id = ?");
         $result->bind_param('i', $paperid);
     } else {
         $result = $mysqli->prepare("SELECT crypt_name, property_id, paper_title, DATE_FORMAT(start_date,'%d/%m/%Y'), DATE_FORMAT(start_date,'%Y%m%d%H%i%s'), DATE_FORMAT(end_date,'%Y%m%d%H%i%s') FROM properties WHERE paper_type = '2' AND start_date > {$start_dateSQL} AND end_date < {$end_dateSQL} AND deleted IS NULL ORDER BY start_date");
     }
     $result->execute();
     $result->bind_result($crypt_name, $paperID, $title, $display_start_date, $start_date, $end_date);
     while ($result->fetch()) {
         $papers[] = array('crypt_name' => $crypt_name, 'paperID' => $paperID, 'title' => $title, 'display_start_date' => $display_start_date, 'start_date' => $start_date, 'end_date' => $end_date);
     }
     $result->close();
     $paper_no = count($papers);
     $current_no = 0;
     $result = $mysqli->prepare("DELETE FROM class_totals_test_local WHERE user_id = ?");
     $result->bind_param('i', $userid);
     $result->execute();
     $result = $mysqli->prepare("SELECT surname, first_names, username FROM users WHERE id = ? LIMIT 1");
     foreach ($papers as $paper) {
         $url = $server . $rootpath . "/reports/class_totals.php?paperID=" . $paper['paperID'] . "&startdate=" . $paper['start_date'] . "&enddate=" . $paper['end_date'] . "&repmodule=&repcourse=%&sortby=student_id&module=1&folder=&percent=100&absent=0&direction=asc&studentsonly=1";
         $output = $this->getData($url, $username, $password);
         $marks_set = $this->parseRawMarks($output);
         $current_no++;
         $insert = $mysqli->prepare("INSERT INTO class_totals_test_local(user_id, paper_id, status) VALUES(?, ?, 'in_progress')");
         $insert->bind_param('ii', $userid, $paper['paperID']);
         $insert->execute();
         $insert->close();
         $errors = '';
         if ($marks_set === false) {
             $marks_set = array();
             $errors = "<ul><li>Couldn't access class_totals</li>\n";
         }
         foreach ($marks_set as $mark) {
             $url = $server . $rootpath . "/paper/finish.php?id=" . $paper['crypt_name'] . "&metadataID=" . $mark['metadataID'] . "&userID=" . $mark['userID'] . "&surname=Test&log_type=2&percent=" . str_replace('%', '', $mark['percent']) . "&disable_mappings=1";
             $output = $this->getData($url, $username, $password);
             $script_mark = $this->parseScript($output);
             if ($script_mark === false) {
                 if ($errors == '') {
                     $errors = '<ul>';
                 }
                 $errors .= "<li>Couldn't access finish</li>\n";
             }
             if ($script_mark != $mark['mark']) {
                 $result->bind_param('i', $mark['userID']);
                 $result->execute();
                 $result->store_result();
                 $result->bind_result($tmp_surname, $tmp_first_names, $tmp_username);
                 $result->fetch();
                 if ($errors == '') {
                     $errors = '<ul>';
                 }
                 $errors .= "<li>Problem with " . $mark['userID'] . " {$tmp_surname}, {$tmp_first_names} ({$tmp_username}) - {$script_mark} / " . $mark['mark'] . "</li>";
             }
         }
         if ($errors != '') {
             $errors .= '</ul>';
             $status = 'failure';
         } else {
             $status = 'success';
         }
         $update = $mysqli->prepare("UPDATE class_totals_test_local SET status = ?, errors = ? WHERE user_id = ? AND paper_id = ?");
         $update->bind_param('ssii', $status, $errors, $userid, $paper['paperID']);
         $update->execute();
         $update->close();
     }
     $result->close();
 }