-1

I have looked at other similar Questions, But cannot seem to find a solution. Any help would be appreciated.

When Run, It displays the state error. I'm trying to update a existing record, When i click submit, the following error occurs.

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 ' Name= , Time= , Date=, Description= , Organiser= , Place= , Other_info= , Organ' at line 1

<?php
$servername = "localhost";
$username = "ilyasm2";
$password = "PASSWORD";
$dbname = "ilyasm2_db";

try {
    $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);

    $sql = "UPDATE Event SET Event_category= $event_category, Name= $name, Time= $time, Date=$date, Description= $description, Organiser= $organiser, Place= $place, Other_info= $other_info, Organiser_email= $organiser_email, Your_Name= $your_name";

    // Prepare statement
    $stmt = $conn->prepare($sql);

    // execute the query
    $stmt->execute();

    // echo a message to say the UPDATE succeeded
    echo $stmt->rowCount() . " records UPDATED successfully";
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?>
Félix Adriyel Gagnon-Grenier
  • 8,422
  • 10
  • 52
  • 65
M Ilyas
  • 1
  • 4
  • What's the question? – Te Ko Aug 26 '17 at 19:54
  • Hi, So im trying to update a existing record, When i click submit, the following error occurs. 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 ' Name= , Time= , Date=, Description= , Organiser= , Place= , Other_info= , Organ' at line 1 – M Ilyas Aug 26 '17 at 19:55
  • 1
    You seem to not bind the placeholders to any actual values. You should have a bunch of `$stmt->bindParam('$event_category', $event_category)` and so on, in there somewhere. – Te Ko Aug 26 '17 at 20:00
  • Also you have no condition on the `UPDATE` query so it's gonna update ALL of the records in the table, not a specific one. – Te Ko Aug 26 '17 at 20:01
  • Would a WHERE clause work? If so how would i add this to current Query.Thanks – M Ilyas Aug 26 '17 at 20:03
  • Yes, a `WHERE` at the end of the query that chooses the record that should be updated would be sufficient. Please consult https://dev.mysql.com/doc/refman/5.7/en/update.html – Te Ko Aug 26 '17 at 20:06
  • Hi, I binded the placeholders and added a WHERE clause but the error has now gone, But when clickong on submit, Nothing happens, seems to fail silently. Thanks – M Ilyas Aug 26 '17 at 20:12

1 Answers1

0

When PHP runs this line:

$sql = "UPDATE Event SET Event_category= $event_category, Name= $name, Time= $time, Date=$date, Description= $description, Organiser= $organiser, Place= $place, Other_info= $other_info, Organiser_email= $organiser_email, Your_Name= $your_name";

It tries to parse a value for each of the strings. The strings most likely do not exist? and thus PHP gives warnings about it (i assume). MySQL tells you that this string has been submitted:

UPDATE Event SET Event_category= , Name= , Time= , Date=, Description= , Organiser= , Place= , Other_info= , Organiser= Your_Name=

which is an invalid SQL statement.

NEVER EVER use strings directly in mysql statements(!) it could make your code vulnerable to SQL injections but use something like this:

$sql = 'SELECT name, colour, calories FROM fruit WHERE calories < :calories AND colour = :colour';

$sth = $dbh->prepare($sql);

$sth->execute(array(':calories' => 150, ':colour' => 'red'));

$red = $sth->fetchAll();

In this case PDO knows that it has to replace :colour with 'red' and :calories with '150'.

Note: without a WHERE clausule in your SQL statement, this query will update ALL records... If you have only one record, that might not be a problem. But for performance it is wise to always use a where on a primary key field. (e.g. Event_id)

Jeffrey
  • 1,766
  • 2
  • 24
  • 44