Can't do a simple flippin' update . . .

[metazai]

Every time I pat myself on the back for having learned something, I screw up something simple like this . . . . yarrgh!

Ok, I've got a page where basic entries (title and entry) in an existing SQL table can be changed. The following code displays the correct entries via a post from the previous page (entryid), but the submit button invariable produces:

"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"

PHP Code:

<?php
$lnk = mysql_connect('mysqladdress', 'login', 'password') or die (mysql_error());
mysql_select_db('tablename',$lnk);
$query_title = "SELECT title FROM tablename WHERE id = ".$entryid;
$pre_title = mysql_query($query_title, $lnk) or die(mysql_error());
$row_title = mysql_result($pre_title,0,0);
$title = stripslashes($row_title);
$query_entry = "SELECT entry FROM tablename WHERE id = ".$entryid;
$pre_entry = mysql_query($query_entry, $lnk) or die(mysql_error());
$row_entry = mysql_result($pre_entry,0,0);
$entry = stripslashes($row_entry); ?>

<form method="post" action="<?=$PHP_SELF?>">
<?php echo($entryid); ?>
<input type="text" name="newtitle" value="<?php echo($title); ?>">
<br />
<textarea name="newentry" rows="10" cols="70">
<?php echo($entry); ?>
</textarea>
<br />
<input type="Submit" name="submitentry" value="submit">
</form>
<?php if ($submitentry == "submit") { 
$updquery = "UPDATE may2004 SET title=\'".$newtitle."\',entry=\'".$newentry."\' WHERE id=\'".$entryid."\'";
mysql_query($updquery,$lnk) or die(mysql_error()); 
echo("<p>Your entry has been updated.</p>"); 
}
    ?>

I know it's something simple, but I just can't see it.

[sde]

well the problem may be that you don't have a single quote around the value you are sending to your query.

i refined your top php script a bit so you don't have to us multiple queries. check out the comments.

caution though, i don't know your data schema, and if you are even using 2 tables, i just assumed it:

PHP Code:

<?
$lnk = mysql_connect('mysqladdress', 'login', 'password') or die (mysql_error());

// this must be the Database Name, NOT the tablename.
mysql_select_db('dbname',$lnk);

// try using single quotes around the value you are querying
// also, just use a join instead of making 2 queries
$sql = "SELECT table1.title AS title,table2.entry AS entry FROM table1,table2 
        WHERE table1.id = '".$entryid."' AND table1.id=table1.id";
        
// when i'm having sql problems, i usually echo my sql statement,
// then i can copy and paste into phpmyadmin ( or whatever db admin you use )
// to see the exact error mysql generates.
echo $sql . "<br><br>\n";

// you really don't need the second argument of mysql_query if you only have 1 db open
$result = mysql_query($query_title) or die(mysql_error());

// now run a loop in case you retrieve multiple results:
while($row = mysql_fetch_array($result)){
  $title = stripslashes($row['title']);
  $entry = stripslashes($row['entry']);

  // print result if you want
  echo $title . " : " $entry . "<br>\n";
}
?>

let me know if this works.

[sde]

p.s. if you don't want to go that deep by trying the join, just modify your sql statement to this:

PHP Code:

$query_title = "SELECT title FROM tablename WHERE id = '".$entryid."'"; 

echo $query_title; 


if that still generates errors, copy that query to mysql and see if the mysql error gives you more specific info.

[metazai]

Thanks, I went with your suggestions in the first post to tighten the code, and it works fine. (I did have the database name and not the table name in the mysql_select_db, I'm just weary from staring at this thing so long!)

However, I wasn't having trouble getting the data, rather updating it. The above form no longer generates an error, but neither does it actually change the data in the database.

[sde]

maybe your update query is wrong. echo it and see what it is sending to mysql.

[metazai]

Interesting . . . according to the echo, it's attempting to run the SELECT from the top script again, bu this time with no id number at all. I guess the PHP_SELF starts at the top of the page again. I just tried moving this section of code:

PHP Code:

<?php if ($submitentry == "submit") { 
$updquery = "UPDATE may2004 SET title='".$newtitle."',entry='".$newentry."' WHERE id='".$entryid."'"; 
mysql_query($updquery,$lnk) or die(mysql_error()); 
echo("<p>Your entry has been updated.</p>"); 
} 
    ?>

To the top with a break command after the entry was updated, but still nothing. Do I need to process the changed values from the form using post somehow?

[sde]

what is this script supposed to do? i am getting the feeling that it is horrible backwards. =)

please explain.

also, note, you are not sending an 'entryid' in your form for the update execute. you would need something like

PHP Code:

<input type=hidden name=entryid value="<?=$entryid?>">

if you expect the update to perform correctly.

[metazai]

Fair enough. This script is supposed to view a blog title and entry that has been passed to it from the previous page using the entryid variable. It views the information in the content of a form so that changes could be made to the existing information and modified in the database upon hitting the submit button. After that, I don't mind if the form fields are blank, as long as the information was updated. I didn't think I had to pass the entryid value again, since it was established earlier it should still exist, right?

Here is the full code as it is right now, may2004 is the table name:

PHP Code:

<? 
$lnk = mysql_connect('mysql address', 'login', 'password') or die (mysql_error()); 
mysql_select_db('databasename',$lnk); 
$sql = "SELECT title,entry FROM may2004 WHERE id = '".$entryid."'"; 
$result = mysql_query($sql) or die(mysql_error()); 
while($row = mysql_fetch_array($result)){ 
  $title = stripslashes($row['title']); 
  $entry = stripslashes($row['entry']); 
} 
?> 
<form method="post" action="<?=$PHP_SELF?>"><?php echo($entryid); ?> <input type="text" name="newtitle" value="<?php echo($title); ?>"><br /><textarea name="newentry" rows="10" cols="70"><?php echo($entry); ?></textarea><br /><input type="Submit" name="submitentry" value="submit"></form>
<?php if ($submitentry == "submit") { 
      $updquery = "UPDATE may2004 SET title='".$newtitle."',entry='".$newentry."' WHERE id='".$entryid."'";
      mysql_query($updquery,$lnk) or die(mysql_error());
      echo $sql . "<br><br>\n"; 
        echo("<p>Your entry has been updated.</p>"); }
    ?>

[sde]

first off, i highly recommend that you do NOT name your tables with a month/year like you have in your update query.

the better thing to do is simply add a 'time' field to your blog table for each entry.

the code example below does not reflect that change, but here is how i think your page should be setup.

note: this assumes that the entryid variable was sent from the previous page with the POST method. If you use the GET method ( in the url ) you will need to modify it a bit.

PHP Code:

<?
// connect & select
$lnk = mysql_connect('mysqladdress', 'login', 'password') or die (mysql_error());
mysql_select_db('dbname',$lnk);

// handle update
// here we test for the post variable action.  this is better than
// testing for $submit because if the user presses 'enter' instead of clicking
// the button 'submit', then the variable 'submit' would not get sent. 
// see the form below to see the hidden variable 'action'
if ($_POST['action']=="update") {
  $updquery = "UPDATE may2004 SET title='".$_POST['newtitle']."',entry='".$_POST['newentry']."' 
                WHERE id='".$_POST['entryid']."'";
  mysql_query($updquery) or die(mysql_error());
  echo("<p>Your entry has been updated.</p>");
}

// define sql
$sql = "SELECT title,entry from table where entryid='".$_POST['entryid']."'";
// execute query
$result = mysql_query($query_title) or die(mysql_error());

// since the entryid field is unique, and it is only possible to have
// one result, then we can use 'if' instead of 'while' to assign the 
// result row to the array variable $row
if($row = mysql_fetch_array($result)){
  
  // notice the 2 hiden variables we have in the form.
  // 'action' and 'entryid'
  echo "<form method=\"post\" action=\"".$PHP_SELF."\">
  <input type=\"hidden\" name=\"action\" value=\"update\">
  <input type=\"hidden\" name=\"entryid\" value=\"".$_POST['entryid']."\">
  
  <input type=\"text\" name=\"newtitle\" value=\"".stripslashes($row['title']."\">
  <br />
  <textarea name=\"newentry\" rows=\"10\" cols=\"70\">
  ".stripslashes($row['entry']."
  </textarea>
  <br />
  <input type=\"Submit\" name=\"submitentry\" value=\"submit\">
  </form>";
} else {
  // this will show if the entry id is not valid
  echo "invalid id';
}
?>

[metazai]

There were some dogged little errors in that code (closing parentheses on stripslashes, etc.) that took some finding, and I tweaked some other things to get it to work, but you got me going in the right direction!

Thanks!

[sde]

oh yeah.. caution, that code wasn't tested =)

it was more for an example of the logic.

glad it helped.