Hello peeps!

I've been plugging away at this since 10pm, and it's almost 2am now. What I want to do is update a record in the database. Here is the scenario. I create a record, then view the records. For each record being viewed, I can edit or delete that record. I choose to edit the record. So, data is pulled from the record and put into a form, where I can "edit" what is in the form. I then click submit and away we go. What should happen is that the text in the form replaces whatever is in the referencing record in the database. Instead, what I get is an SQL error, which looks Chinese to me. The error is very little help, though it does indicate that I have a syntax error (I think). If the record already exists in the database, then I do the update. Otherwise, the same form that is used to "create" records will just give me an empty form.

Here is the error that shows in my browser:

Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement., SQL state 37000 in SQLExecDirect in f:\programs\servers\apache\websites\ddott\write2.php on line 51
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

Here is the code for the portion that does the updating:

// if this is a current record that was edited, then we do 
// NOT want to create another record with the same data
if($_POST['id']) {
	$i = $_POST['id'];
	$query = "UPDATE thoughts SET (date, title, thoughts) VALUES('$date', '$title', '$thoughts') WHERE thoughtsID='$i'";
} else {
	$query = "INSERT INTO thoughts([date], [title], [thoughts]) ";
	$query .= "VALUES('$date', '$title', '$thoughts')";
}

$query = odbc_exec($odbc, $query) or die (odbc_errormsg());

It's probably something very simple that I'm missing, but on the other hand it's been ages since I played with PHP (just a hobbyist here who is trying to make a website). Any help is appreciated :-)

    yep answer is really simple, you have wrong syntax

    UPDATE table SET someINT = $gid, name='$name' WHERE id = $id

      I changed this to reflect what you are suggesting, and am still getting the error:

      This is my table setup:

      table name -> thoughts
      thoughtsID = auto number (*key)
      date = text
      title = text
      thoughts = memo

      Here is the error my browser is showing:

      Warning: odbc_exec(): SQL error: [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement., SQL state 37000 in SQLExecDirect in f:\programs\servers\apache\websites\ddott\write2.php on line 62
      [Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.

      Here is my code:

      if($_POST['id']) {
	$i = $_POST['id'];
	$query = "UPDATE thoughts SET date='$date', title='$title',thoughts='$thoughts' WHERE thoughtsID=$i";
}

$query = odbc_exec($odbc, $query) or die (odbc_errormsg());

odbc_close($odbc);

        Is there a way to get a more descriptive explanation of what the problem with my UPDATE syntax is? I am beginning to wonder whether Access 2000 causes a lot of these problems.

          Could this have to do with field "thoughts" being of type "memo"?

            I'm betting that the error is because the thoughts is type memo. try this, make a copy of your thoughts table and set the type for thoughts to text instead of memo, then try rerunning the same code. if it works, then that would be your problem.

            p.s. I hate querrying access, see my question in same forum for my issues with it today...

              Changing the field type to 'text' didn't fix it. All my PHP books only have MySQL examples in them, so I'm going to look through them again. Otherwise, I'm beginning to get fed up with this database stuff.

                Have you tried adding parentheses around your new update statement. Perhaps something like:

                    $query = "UPDATE thoughts SET (date = '$date'), (title = '$title'), (thoughts = '$thoughts') WHERE (thoughtsID = $i)";

                Also, I know mySQL doesn't like table column names called "date". Perhaps Access has a problem with it too?

                -Antun

                Originally posted by tremere
                Any thoughts?

                  Try out this

                  $query = "UPDATE thoughts SET date='$date', title='$title',thoughts='$thoughts' WHERE thoughtsID='$i'";

                  Notice I put backquotes on date (probable reserved word in access sql), and put quotes around $i (just for fun).

                  HTH

                    Write a Reply...