PHP mysqli::stmt_init方法代码示例

function getTrendingArtists()
{
    $artist = array();
    $db_connection = new mysqli('stardock.cs.virginia.edu', 'cs4750ams5daa', 'music', 'cs4750ams5da');
    if (mysqli_connect_errno()) {
        echo "Connection Error!";
        return;
    }
    $file = "http://plato.cs.virginia.edu/~ams5da/ListenUp/text/trendingArtists.txt";
    $lines = file($file);
    foreach ($lines as $line) {
        $array = explode("\n", $line);
        array_push($artist, trim($array[0]));
    }
    $stmt = $db_connection->stmt_init();
    $stmt->prepare("SELECT * FROM Artists");
    $stmt->execute();
    $stmt->bind_result($a_id, $a_Name, $top_songs_key, $bio);
    while ($stmt->fetch()) {
        if (in_array($a_id, $artist)) {
            echo '<a id="' . $top_songs_key . '" class="list-group-item"><button onclick="display(this,\'' . $a_Name . '\', \'' . $a_id . '\', \'' . $bio . '\')" id="playSong" class ="btn btn-mini"><i class="fa fa-play"></i></button>' . $a_Name . '</a>';
        }
    }
    $stmt->close();
}

function bindItems($table, $name1, $name2, $id1, $id2)
{
    $mysqli = new mysqli(DB_HOST, DB_LOGIN, DB_PASSWORD, DB_NAME);
    $stmt = $mysqli->stmt_init();
    if ($stmt->prepare("INSERT INTO {$table} ({$name1}, {$name2}) VALUES (?,?)")) {
        $stmt->bind_param("ii", $id1, $id2);
        $stmt->execute();
        $stmt->close();
        $mysqli->close();
        return true;
    }
    return false;
}

 /**
  * Prepares a statement or uses an instance from the cache
  *
  * @param string $strQuery
  *
  * @return mysqli_stmt
  */
 private function getPreparedStatement($strQuery)
 {
     $strName = md5($strQuery);
     if (isset($this->arrStatementsCache[$strName])) {
         return $this->arrStatementsCache[$strName];
     }
     $objStatement = $this->linkDB->stmt_init();
     if (!$objStatement->prepare($strQuery)) {
         $this->strErrorMessage = $objStatement->error;
         return false;
     }
     $this->arrStatementsCache[$strName] = $objStatement;
     return $objStatement;
 }

function deleteSongFromPlaylist($p_id, $s_id)
{
    $db_connection = new mysqli('stardock.cs.virginia.edu', 'cs4750ams5daa', 'music', 'cs4750ams5da');
    if (mysqli_connect_errno()) {
        echo "Connection Error!";
        return;
    }
    //DELETE FROM `Contains` WHERE `Contains`.`p_id` = 7 AND `Contains`.`s_id` = \'t55174310\'"?
    $stmt = $db_connection->stmt_init();
    if ($stmt->prepare("Delete from `Contains` where p_id={$p_id} and s_id='{$s_id}'")) {
        $stmt->bind_param('is', $p_id, $s_id);
        $stmt->execute();
    }
}

 /**
  * Simple function to insert data into the database in preparation
  * @param mysqli $db
  * @param $firstname
  * @param $lastname
  * @param $age
  * @param $createdDate
  * @param $balance
  * @throws Exception
  */
 private function rawRowInsert(mysqli $db, $firstname, $lastname, $age, $createdDate, $balance)
 {
     if (!($statement = $db->stmt_init())) {
         throw new Exception('Error creating prepared statement: ' . $db->error);
     }
     if (!$statement->prepare('INSERT INTO people (firstname, lastname, age, createdDate, balance) VALUES (?, ?, ?, ?, ?)')) {
         throw new Exception('Error preparing insert query: ' . $statement->error);
     }
     if (!$statement->bind_param('ssisd', $firstname, $lastname, $age, $createdDate, $balance)) {
         throw new Exception('Error binding parameters: ' . $statement->error);
     }
     if (!$statement->execute()) {
         throw new Exception('Error executing parameters');
     }
 }

function unsubscribeFromPlaylist($p_id, $username)
{
    $db_connection = new mysqli('stardock.cs.virginia.edu', 'cs4750ams5daa', 'music', 'cs4750ams5da');
    if (mysqli_connect_errno()) {
        echo "Connection Error!";
        return;
    }
    //echo "Connection made!";
    // "DELETE FROM `cs4750ams5da`.`Subscribes_to` WHERE `Subscribes_to`.`Username` = \'ams5da\' AND `Subscribes_to`.`p_id` = 2"?
    $stmt = $db_connection->stmt_init();
    if ($stmt->prepare("Delete FROM Subscribes_to WHERE Username = ? and p_id = ?")) {
        $stmt->bind_param("si", $username, $p_id);
        $stmt->execute();
    }
}

function updatePlaylistName($p_id, $new_p_name)
{
    $db_connection = new mysqli('stardock.cs.virginia.edu', 'cs4750ams5daa', 'music', 'cs4750ams5da');
    if (mysqli_connect_errno()) {
        echo "Connection Error!";
        return;
    }
    $stmt = $db_connection->stmt_init();
    if ($stmt->prepare("UPDATE `Playlists` SET `p_name`= ? WHERE `p_id` = ?")) {
        $stmt->bind_param("si", $new_p_name, $p_id);
        $stmt->execute();
        //echo $userID;
        while ($stmt->fetch()) {
        }
    }
}

function getAllSongs($userID)
{
    $db_connection = new mysqli('stardock.cs.virginia.edu', 'cs4750ams5daa', 'music', 'cs4750ams5da');
    if (mysqli_connect_errno()) {
        echo "Connection Error!";
        return;
    }
    //original query "select s_id, Title, a_name FROM `Songs` natural join `Performed_by` where Songs.s_id = Performed_by.s_id"
    $stmt = $db_connection->stmt_init();
    if ($stmt->prepare("select s_id, Title, a_name FROM song_artist_album ORDER BY `song_artist_album`.`Title` ASC")) {
        $stmt->execute();
        $stmt->bind_result($s_id, $title, $name);
        while ($stmt->fetch()) {
            echo "<li id=" . $s_id . " onclick=\"play(this.id)\"><a href=\"javascript:;\">" . $title . " - " . $name . "</a></li>";
        }
    }
}

 public function insert($table, array $data)
 {
     //create prepare statement, etc. INSERT INTO `people` (`firstname`, `lastname`, `age`, `createdDate`) VALUES (?, ?, ?, ?)
     $keys = array_keys($data);
     $dbColumnName = '(';
     foreach ($keys as $key) {
         $dbColumnName .= '`' . $key . '`, ';
     }
     $dbColumnName = substr($dbColumnName, 0, -2);
     $dbColumnName .= ')';
     $table = 'INSERT INTO `' . $table . '` ' . $dbColumnName;
     $numberOfValues = count($data);
     $values = $this->_createValues($numberOfValues);
     $dataType = $this->_checkTypeOfValues($data);
     $type = '';
     //getting first letter from each of value type
     foreach ($dataType as $word) {
         $letter = substr($word, 0, 1);
         $type .= $letter;
     }
     $dataValues = count($data) >= 1 ? ' VALUES (' . $values . ')' : '';
     $sql = $table . $dataValues;
     //$stmt initialization
     $stmt = self::$_db->stmt_init();
     //prepare statement
     if ($sqlPrepare = $stmt->prepare($sql)) {
         $whereClause = $this->createParameters($data);
         $values = array();
         $v = $whereClause->getValues();
         foreach ($v as &$value) {
             array_push($values, &$value);
         }
         $typeList = $whereClause->getTypeList();
         $functionParams = array_merge(array(&$typeList), $values);
         call_user_func_array(array($stmt, 'bind_param'), $functionParams);
         $result = $stmt->execute();
         if (true === $result) {
             return $stmt->insert_id;
         } else {
             throw new Exception('Error: ' . $stmt->error);
         }
         $stmt->close();
     } else {
         throw new Exception("Error: " . $stmt->error);
     }
 }

function subscribeToPlaylist($p_id, $username, $p_name)
{
    $db_connection = new mysqli('stardock.cs.virginia.edu', 'cs4750ams5daa', 'music', 'cs4750ams5da');
    if (mysqli_connect_errno()) {
        echo "Connection Error!";
        return;
    }
    //echo "Connection made!";
    //SELECT Title, a_name FROM `Contains`NATURAL JOIN Performed_by where Contains.s_id = Performed_by.s_id and Contains.p_id = '7'
    //select Subscribes_to.p_id, Subscribes_to.p_name, Username, al_art_URL from Subscribes_to Join Playlists where Subscribes_to.p_id = Playlists.p_id AND Username = 'ams5da' and Subscribes_to.p_name = 'Work out!!!!' and Subscribes_to.p_id = 5
    //echo $p_name;
    $defaultIcon = "images/default-playlist-artwork.png";
    //First check if user is already subscribed to the playlist
    //Then if not, get the album URL
    $stmt = $db_connection->stmt_init();
    if ($stmt->prepare("SELECT * FROM Subscribes_to WHERE Username = ? and p_id = ? and p_name = ?")) {
        $stmt->bind_param("sis", $username, $p_id, $p_name);
        $stmt->execute();
        $stmt->store_result();
        $stmt->bind_result($user, $pid, $pname);
        $stmt->fetch();
        //echo $stmt->num_rows;
        if ($stmt->num_rows == 0) {
            $art_URL;
            if ($stmt->prepare("Select al_art_URL from Playlists WHERE p_id = ?")) {
                $stmt->bind_param("s", $p_id);
                $stmt->execute();
                $stmt->bind_result($artURL);
                while ($stmt->fetch()) {
                    $art_URL = $artURL;
                    if (!isset($art_URL) || trim($art_URL) === '') {
                        $art_URL = $defaultIcon;
                    }
                    //echo $artURL;
                }
            }
            if ($stmt->prepare("INSERT into Subscribes_to(Username, p_id, p_name) VALUES (?, ?, ?)")) {
                $stmt->bind_param("sis", $username, $p_id, $p_name);
                $stmt->execute();
                echo "<div class=\"content-grid\" id=\"" . $p_id . "\"><a href=\"#\" onClick=\"play(" . $p_id . ", '" . addslashes($p_name) . "')\"><img src=\"" . $art_URL . "\" title=\"album-name\" /></a><h3>" . $p_name . "</h3><ul><button type=\"button\" class=\"btn btn-danger btn-sm\" onClick=\"Unsubscribe(" . $p_id . ")\">Unsubscribe</button></ul></div>";
            }
        }
    }
}

 /**
  * @param	DbRequestInterface $request
  * @return	DbReponseInterface
  */
 public function execute(MysqliDriver $driver, DbRequestInterface $request, DbResponseInterface $response)
 {
     $stmt = new PreparedStmt($driver->stmt_init());
     $stmt->prepare($request->getSql());
     if ($stmt->isError()) {
         $error = $stmt->getError();
         $response->addError($error['error-text'], $error['error-nbr']);
         return $response;
     }
     /* normalize and bind parameters */
     if ($request->isValues()) {
         $stmt->organizeParams($request->getValues());
         if ($stmt->isError()) {
             $error = $stmt->getError();
             $response->addError($error['error-text'], $error['error-nbr']);
             return $response;
         }
     }
     $stmt->execute();
     if ($stmt->isError()) {
         $error = $stmt->getError();
         $response->addError($error['error-text'], $error['error-nbr']);
         return $response;
     }
     $isOrganized = $stmt->organizeResults();
     if ($stmt->isError()) {
         $error = $stmt->getError();
         $response->addError($error['error-text'], $error['error-nbr']);
         return $response;
     }
     /* database executed the query successfully and 
      * no results are needed
      */
     if ($isOrganized && !$stmt->isResultset()) {
         return $response;
     }
     $stmt->storeResults();
     $errorStack = $response->getErrorStack();
     $data = $stmt->fetch($errorStack, $request->getCallback());
     if (is_array($data)) {
         $response->setResultSet($data);
     }
     return $response;
 }

function addSongToPlaylist($songID, $songTitle, $playlistID, $playlistName, $a_Name)
{
    $db_connection = new mysqli('stardock.cs.virginia.edu', 'cs4750ams5da', 'music', 'cs4750ams5da');
    if (mysqli_connect_errno()) {
        echo "Connection Error!";
        return;
    }
    $songTitle = addslashes($songTitle);
    $playlistName = addslashes($playlistName);
    //INSERT INTO Contains (`p_id`, `s_id`, `p_name`, `Title`) VALUES (7, 't55174310', 'Fav Songs', 'Always In My Head')
    //echo($playlistID . " " . $playlistName . " " . $songID . " " . $songName);
    $stmt = $db_connection->stmt_init();
    if ($stmt->prepare("Insert into `Contains` (`p_id`, `s_id`, `p_name`, `Title`) VALUES ({$playlistID}, '{$songID}', '{$playlistName}', '{$songTitle}')")) {
        $stmt->bind_param('isss', $songID, $songTitle, $playlistID, $playlistName);
        $stmt->execute();
        //echo("executed");
        echo "<li id=" . $songID . " class=\"list-group-item\"><button id=\"deleteSong\" onclick=\"DeleteSongFromPL(this)\" class = \"btn btn-mini\"><i class=\"fa fa-times\"></i></button> " . $songTitle . " - " . $a_Name . "</li>";
    }
}

function getLikedSongs($u_id)
{
    $uName = $_SESSION['loggedin'];
    $db_connection = new mysqli('stardock.cs.virginia.edu', 'cs4750ams5daa', 'music', 'cs4750ams5da');
    if (mysqli_connect_errno()) {
        echo "Connection Error!";
        return;
    }
    $stmt = $db_connection->stmt_init();
    if ($stmt->prepare("SELECT Title, a_name, s_id FROM `Likes` NATURAL Join `Songs` NATURAL Join `Performed_by` WHERE username = '{$uName}'")) {
        $stmt->bind_param("s", $uName);
        $stmt->execute();
        $stmt->bind_result($title, $name, $s_id);
        while ($stmt->fetch()) {
            //echo($name ." ". $title . "\n");
            echo '<a id="' . $s_id . '" class="list-group-item"><button onclick="play(\'' . $s_id . '\')" id="playSong" class ="btn btn-mini"><i class="fa fa-play"></i></button>' . $title . ' - ' . $name . '</a>';
        }
    }
}

function getSongsInPlaylist($p_id)
{
    $db_connection = new mysqli('stardock.cs.virginia.edu', 'cs4750ams5daa', 'music', 'cs4750ams5da');
    if (mysqli_connect_errno()) {
        echo "Connection Error!";
        return;
    }
    //echo "Connection made!";
    //SELECT Title, a_name FROM `Contains`NATURAL JOIN Performed_by where Contains.s_id = Performed_by.s_id and Contains.p_id = '7'
    $stmt = $db_connection->stmt_init();
    if ($stmt->prepare("select s_id, Title, a_name FROM `Contains` natural join `Performed_by` where Contains.s_id = Performed_by.s_id and Contains.p_id = '{$p_id}'")) {
        $stmt->bind_param("i", $p_id);
        $stmt->execute();
        $stmt->bind_result($s_id, $Title, $name);
        while ($stmt->fetch()) {
            echo "<li id=" . $s_id . " class=\"list-group-item\"><button id=\"deleteSong\" onClick='DeleteSongFromPL(this)' class = \"btn btn-mini\"><i class=\"fa fa-times\"></i></button> " . $Title . " - " . $name . "</li>";
        }
    }
}

function addSongToLikes($user, $songID, $songName)
{
    //echo ("in php: " . $user . ", " . $songID . ", " . $songName);
    $db_connection = new mysqli('stardock.cs.virginia.edu', 'cs4750ams5daa', 'music', 'cs4750ams5da');
    if (mysqli_connect_errno()) {
        echo "Connection Error!";
        return;
    }
    $songName = addslashes($songName);
    $stmt = $db_connection->stmt_init();
    if ($stmt->prepare("INSERT INTO `Likes`(`Username`, `s_id`, `Title`, `likes?`) VALUES (?,?,?, 1)")) {
        $stmt->bind_param("sss", $user, $songID, $songName);
        $stmt->execute();
        $stmt->bind_result();
        while ($stmt->fetch()) {
            //echo($name ." ". $title . "\n");
            echo "Added " . $songName . " for " . $user;
        }
    }
}