[Resolved] update / insert mystery

happyme

I wrote a script to update my news database and it worked great. I cut it down a bit to update a smaller news items list and now it won't add new records or update existing ones.

Earlier in the script it happily displays the content of database, creates a from filled with existing values to edit but the actual adding or editing doesn't work or return any errors. The variables I'm using all have the values I'd expect when I echo them.

I'm completely at a loss as to why this doesn't work.

Julia 🙁

The Table I'm using is:

CREATE TABLE ThisWeek (
id tinyint(4) NOT NULL auto_increment,
content text,
addy varchar(60) default NULL,
date timestamp(8) NOT NULL,
approved tinyint(1) default NULL,
name varchar(25) default NULL,
appby varchar(25) default NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id)
) TYPE=MyISAM;

The PHP code is:


//return from form, connect to database

$tablename="ThisWeek";

if ($submit) 

{

// here if no ID then adding else we're editing

// $id, $content, $addy, $email, $yourname, 
// and $approved come from a form

if ($id) {$sql = "UPDATE $tablename SET content='$content', addy='$addy', email='$email', appby='$yourname', approved = '$approved' WHERE id=$id";} 

else {$sql = "INSERT INTO $tablename (content, addy, email, name, appby, approved) VALUES '$content', '$addy', '$email', '$yourname', '$yourname', '$approved')";}

// run SQL against the DB

$result = mysql_query($sql);

//check to see if it worked
if ($result){echo "<p><font face=$font>Record updated/edited! $yourname</p>";}

else {echo "still not working";}

//echo variables to see if they're OK 
echo "<p>content=$content, addy=$addy, email=$email, appby=$yourname, approved = $approved, id=$id</p>";

}

sgoku01

You should try this:

$sql = "INSERT INTO $tablename (content, addy, email, name, appby, approved) VALUES [b]([/b]'$content', '$addy', '$email', '$yourname', '$yourname', '$approved')";

(changed: add a ( to VALUES

happyme

well spotted, but I lost the ( in cut and pasting the code.

Still a mystery

cgraz

Change your code to this and tell us everything it prints.

$tablename="ThisWeek";

if ($submit) {

// here if no ID then adding else we're editing

// $id, $content, $addy, $email, $yourname, 
// and $approved come from a form

   if ($id) {
      $sql = "UPDATE $tablename SET content='$content', addy='$addy', email='$email', appby='$yourname', approved = '$approved' WHERE id='$id'";
   } else {
      $sql = "INSERT INTO $tablename (content, addy, email, name, appby, approved) VALUES ('$content', '$addy', '$email', '$yourname', '$yourname', '$approved')";
   }

// run SQL against the DB

   $result = mysql_query($sql) or die(mysql_error()); // mysql_error() is prob the best way to troubleshoot this kind of thing

   print $sql . "<br>"; // print query string to see what it looks like. this always helps me

//check to see if it worked
   if ($result) {
      echo "<p><font face=$font>Record updated/edited! $yourname</p>";
   } else {
      echo "still not working";
   }

//echo variables to see if they're OK 
   echo "<p>content=$content, addy=$addy, email=$email, appby=$yourname, approved = $approved, id=$id</p>";

}

Cgraz

happyme

Thank you so much

adding or die(mysql_error())

let me know what the problem was- I'd cut the email column out of the database and I sorted it.

And I've learnt something to include in every msysql query to help me troubleshoot in future.

🙂

Julia