Query failed: SQLSTATE[42000]: Syntax error or access violation: 1064
CREATE TABLE members (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
username VARCHAR(30) BINARY NOT NULL UNIQUE,
password CHAR(41) NOT NULL,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
joindate DATE NOT NULL,
gender ENUM('m', 'f') NOT NULL,
favouritegenere ENUM('crime', 'horror', 'thriller', 'romance', 'scifi', 'adventure', 'nonfiction' ) NOT NULL,
emailaddress VARCHAR(50) NOT NULL UNIQUE,
otherinterests TEXT NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO members VALUES( 1, 'sparky', password('rockstar'), 'John', 'Sparks', '2007-11-13', 'm', 'crime', '[email protected]', 'Football, fishing and gardening' );
INSERT INTO members VALUES( 2, 'mary', password('rockstar'), 'Mary', 'Newton', '2007-02-06', 'f', 'thriller', '[email protected]', 'Writing, hunting and travel' );
INSERT INTO members VALUES( 3, 'jojo', password('rockstar'), 'Jo', 'Scrivener', '2006-09-03', 'f', 'romance', '[email protected]', 'Genealogy, writing and painting' );
INSERT INTO members VALUES( 4, 'marty', password('rockstar'), 'Marty', 'Pareene', '2007-01-07', 'm', 'horror', '[email protected]', 'Guitar playing, rock music and clubbing' );
INSERT INTO members VALUES( 5, 'nickb', password('rockstar'), 'Nick', 'Blakeley', '2007-08-19', 'm', 'scifi', '[email protected]', 'Watching movies, cooking, socializing' );
INSERT INTO members VALUES( 6, 'bigbill', password('rockstar'), 'Bill', 'Swan', '2006-06-11', 'm', 'nonfiction', '[email protected]', 'Tennis, judo, music' );
INSERT INTO members VALUES( 7, 'janefield', password('rockstar'), 'Jane', 'Field', '2006-03-03', 'f', 'crime', '[email protected]', 'Thai cookery, gathering travelling' );
--------------------------------------------------------------------------------------------------------------------------------------------------
"",
"username" => "",
"password" => "",
"firstName" => "",
"lastName" => "",
"joinDate" => "",
"gender" => "",
"favouriteGenre" => "",
"emailAddress" => "",
"otherInterests" => ""
);
private $_genres = array(
"crime" => "Crime",
"horror" => "Horror",
"thriller" => "Thriller",
"roance" => "Romane",
"sciFi" => "Sci-Fi",
"adventure" => "Adventure",
"nonFiction" => "Non-Fiction"
);
public static function getMembers($startRow, $numRows, $order ) {
$conn = parent::connect();
$sql = "SELECT SQL_CALC_FOUND_ROWS * FROM" .TBL_MEMBERS ."ORDER BY $order LIMIT :startRow, :numRows";
try{
$st = $conn->prepare($sql);
$st -> bindValue(":startRow", $startRow, PDO::PARAM_INT );
$st -> bindValue(":numRows", $numRows, PDO::PARAM_INT);
$st->execute();
$members = array();
foreach($st->fetchAll() as $row){
$members[] = new Member ($row);
}
$st = $conn->query("SELECT found_rows() AS totalRows");
$row = $st->fetch();
parent::disconnect($conn);
return array($members, $row["totalRows"] );
}catch(PDOException $e) {
parent::disconnect($conn);
die("Query failed: ". $e->getMessage() );
}
}
public static function getMember($id) {
$conn = parent::connect();
$sql = "SELECT * FROM " . TBL_MEMBERS . "WHERE id = :id";
try{
$st = $conn->prepare($sql);
$st->bindValue(":id", $id, PDO::PARAM_INT );
$st->execute();
$row = $st->fetch();
parent::disconnect($conn);
if($row) return new Member ($row);
}catch(PDOException $e){
parent::disconnect($conn);
die("Query failed: ".$e->getMessage() );
}
}
public function getByUsername($username) {
$conn = parent::connect();
$sql = "SELECT * FROM" . TBL_MEMBERS."WHERE username = :username";
try{
$st = $conn->prepare($sql);
$st->bindValue(":username", $username, PDO::PARAM_STR);
$st->execute();
$row = $st-fetch();
parent::disconnect($conn);
if($row) return new Member($row);
}catch(PDOException $e){
parent::disconnect($conn);
die("Query failed: ". $e->getMessage());
}
}
public static function getByEmailAddress($emailAddress){
$conn = parent::connect();
$sql = "SELECT * FROM" . TBL_MEMBERS. "WHERE emailAddress = :emailAddress";
try{
$st = $conn->prepare($sql);
$st->bindValue(":emailAddress", $emailAddress, PDO::PARAM_STR);
$st->execute();
$row = $st->fetch();
parent::disconnect($conn);
if($row) return new Member($row);
}catch(PDOException $e){
parent::disconnect($conn);
die("Query failed: ".$e->getMessage());
}
}
public function getGenderString() {
return($this->data["gender"] == "f") ? "Female" : "Male";
}
public function getFavouriteGenreString(){
return($this->_genres[$this->data["favouriteGenre"]] );
}
public function getGenres(){
return $this->_genres;
}
public function insert() {
$conn = parent::connect();
$sql = "INSERT INTO".TBL_MEMBERS."(
username,
password,
firstName,
lastName,
joinDate,
gender,
favouriteGenres,
emailAddress,
otherInterests
)VALUES(
:username,
:password(:password),
:firstName,
:lastName,
:joinDate,
:gender,
:favouriteGenres,
:emailAddress,
:otherInterests
)";
try{
$st = $conn->prepare($sql);
$st->bindValue(":username", $this->data["username"], PDO::PARAM_STR);
$st->bindValue(":password", $this->data["password"], PDO::PARAM_STR);
$st->bindValue(":firstName", $this->data["firstName"], PDO::PARAM_STR);
$st->bindValue(":lastName", $this->data["lastName"], PDO::PARAM_STR);
$st->bindValue(":joinDate", $this->data["joinDate"], PDO::PARAM_STR);
$st->bindValue(":gender", $this->data["gender"], PDO::PARAM_STR);
$st->bindValue(":favouriteGenre", $this->data["favouriteGenre"], PDO::PARAM_STR);
$st->bindValue(":emailAddress", $this->data["emailAddress"], PDO::PARAM_STR);
$st->bindValue(":otherInterests", $this->data["otherInterests"], PDO::PARAM_STR);
$st->execute();
parent::disconnect($conn);
}catch(PDOException $e){
parent::disconnect($conn);
die("Query failed:".$e->getMessage());
}
}
}
?>
