Query failed: SQLSTATE[42000]: Syntax error or access violation: 1064

Avatar
  • Answered
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()); } } } ?>
Avatar
Arn
Hello Manoj03h, Sorry for the problem that you're having with the database. First, just to make sure it's clear, we do not provide coding support as it is beyond the scope of our support. However, we do try to help where we can or point you in a direction that may provide assistance. The error message that you provided, "Query failed: SQLSTATE[42000]: Syntax error or access violation: 1064" is indicating that somewhere in your SQL query, there is either code that has an extra space or no space, or has caused a problem in such a way to result in a possible syntax error. The error also indicates that there is a possible access violation with how you're connecting to the database. Check out this forum post with something similar. It may give you some insight on how best to resolve the problem. If you continue to have the problem, we would need more information. A URL associated with an account (if you are hosting with us), steps to duplicate the problem, and full error messages so that we can investigate it further. You may need to consult with a developer/database programmer who can look at the problem in order to resolve the issue. Apologies again for the problem. If you have any further questions or comments, please let us know. Regards, Arnel C.