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

[tuttymutty]

Hi,

 

Please can anyone help on the below? i literally am such a noob at this and i have no idea  

 

"Connection failed: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1"

 

This is my php code:

 

 

 

  

<?php
// define variables and set to empty values
$Class_ID = $Class_Description = $Class_Date = $Class_Time = $fk1_Instructor_ID = $fk2_Customer_ID = "";

//setting the database connection parameters 
$servername = "localhost";
$username = "root";
$password = "";
$dbname = "profitness_erd";

//if the POST method is used to submit the update form then the Classes id is set to the one 
//specified in the Classes No text field in the form
if ($_SERVER['REQUEST_METHOD'] === 'POST') {
   $Class_ID = test_input($_POST["Class_ID"]);
}

//if the GET method was used to call the update PHP script from a different page then 
if ($_SERVER['REQUEST_METHOD'] === 'GET') {
	//check whether the parameter of the Classes's id has been specified when the Delete PHP script is called
	if(isset($_GET['id'])) {
	    $Class_ID = $_GET["id"];  
	} 	
	//if the id of the Classes has not been specified a default Class_ID of 12 is being used
	else {
	    $Class_ID = 'A01';
	}
}

try {
 //creating the database connection object
 $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
 // set the PDO error mode to exception 
 $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

 //prepare the SELECT statement to choose all Classess matching the Classes ID
 $selected_Classes = $conn->prepare("SELECT * FROM Classes WHERE Class_ID='$Class_ID'");
 //execute the SELECT statement
 $selected_Classes->execute();

 //if the form was submitted (using the POST method)
 if ($_SERVER["REQUEST_METHOD"] == "POST") {

 //collect all the form entries and store them in variables 
 $Class_ID = test_input($_POST["Class_ID"]);
 $Class_Description = test_input($_POST["Class_Description"]);
 $Class_Date = test_input($_POST["Class_Date"]);			    
 $Class_Time = test_input($_POST["Class_Time"]);
 $fk1_Instructor_ID = test_input($_POST["fk1_Instructor_ID"]);
 $fk2_Customer_ID = test_input($_POST["fk2_Customer_ID"]);
  		
 //prepare the UPDATE statement (using the modified values that the user has changed to)
 $update_sql = "UPDATE Classes SET Class_ID='$Class_ID', Class_Description='$Class_Description', Class_Date='$Class_Date', Class_Time='$Class_Time', fk1_Instructor_ID='$fk1_Instructor_ID', fk2_Customer_ID='$fk2_Customer_ID' WHERE Class_ID=$Class_ID'"; 
  
 //execute the UPDATE statement
 if ($conn->exec($update_sql)) {	
	//display confirmation that the record has been updated successfully.
	echo "Your record updated successfully.";
 } 
  else {
	  //echo the update statement error which is generated if the statement fails; 
	  echo "Error: "  . $update_sql. "<br>" . $conn->error;		
 }
  //close the database connection
  $conn = null;
 }
}
//deal with any PDO connection execeptions.
catch(PDOException $e)
{
    echo "Connection failed: " . $e->getMessage();
}

//the function test_input strips any blanks, special characters, 
//etc. and returns back the cleaned data
function test_input($data) {
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}
?>

<h3>Update a Class</h3>
<form method="post"
  action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" 
  name="new_Classes">

<?php
while($result = $selected_Classes->fetch(PDO::FETCH_OBJ))
{	

//display the values returned from the SELECT query in the text fields of the form    	
//Make the Classes No readonly (i.e. the user will not be able to change the Classes no in the form!)
echo 'Class ID <input name="Class_ID" type="text" size="11" maxlength="11" value="' .$result->Class_ID .'" readonly/><br />';

echo 'Class Description <input name="Class_Description" type="text" size="35" maxlength="80" value="' .$result->Class_Description . '" /><br />';
echo 'Class Date <input name="Class_Date" type="text" size="35" maxlength="80" value="'  .$result->Class_Date . '"/> <br />';		
echo 'Class Time <input name="Class_Time" type="text" size="35" maxlength="80" value="'  .$result->Class_Time . '"/> <br />';	
echo 'Instructor ID <input name="fk1_Instructor_ID" type="text" size="35" maxlength="80" value="'  .$result->fk1_Instructor_ID . '"/> <br />';	
echo 'Customer ID <input name="fk2_Customer_ID" type="text" size="35" maxlength="80" value="'  .$result->fk2_Customer_ID . '"/> <br />';	

echo '</select>';
}	
?>

<p></p>
	<input type="reset" name="reset" />
	<input type="submit" name="submit" />
</form>

<?php
	//link this PHP file to the PDO Select script (select_all_Classess.php)
	echo "<p>";
	echo "<a href='displayclasses.php'>Display all Class records</a>";
	echo "</p>";
?>

 

[tuttymutty]

i dont know why it posted three times! sorry

[mac_gyver]

your program logic will default to the catch{...} block for all database errors, so even though the message being output says, "Connection failed: ..., you actually have an error at one of the query statements.

 

i recommend that you change your code to consistently use one variable name to hold the sql query statements, something like $sql. then, in the catch{...} block, if $sql is empty, you know you reached that point due to a connection error. if $sql is not empty, you know you reached that point due to a query error. this logic will let you echo the $sql query statement as part of the error so that you can look to see what might be wrong with it.

 

change your catch{...} block to something like the following - 

} catch(PDOException $e) {
    $status = empty($sql) ? 'Connection failed':" Query failed: $sql";
    echo "$status, Error: {$e->getMessage()}, File: {$e->getFile()}, Line: {$e->getLine()}";
}