Mysql – UPDATE adds a new row when I merely want to edit an old one


Here's my code:

$sql = "UPDATE `news` SET (headline, text, startDate, endDate, imageLocation, imagePlacement, imgStyles, link) ";
$sql .="VALUES('$headline','$text','$startDate','$endDate','$imageLocation','$imagePlacement','$imgStyles','$link') ";
$sql .= "WHERE id = $id";

I've tried REPLACE INTO, INSERT INTO, and all kinds of variations on single quotes, etc., to no avail. I even hardcoded the ID (22) in case that wasn't getting picked up correctly, but every time I try something like this I get a new row, which means I have two entries with slightly different data in each.

The table's primary key is ID and it's set to AUTO_INCREMENT.

Any thoughts on this?


Here's the entire code block on the PHP page:

$id = $_GET["newschoice"];

if(empty($headline)) $headline = "none";
if(empty($text)) $text = "none";
if(empty($imageLocation)) $imageLocation = "";
if(empty($imagePlacement)) $imagePlacement = "0";
if(empty($imgStyles)) $imgStyles= "none";
if(empty($link)) $link = "none";


$sql = "UPDATE `news` SET (headline, text, startDate, endDate, imageLocation, imagePlacement, imgStyles, link) ";
$sql .="VALUES('$headline','$text','$startDate','$endDate','$imageLocation','$imagePlacement','$imgStyles','$link') ";
$sql .= "WHERE id = $id";   

if($result = mysqli_query($sql))
  print "<p class=\"text\">Record successfully added to table!</p>";
else {
  print "<p class=\"text\">Problem adding record: ".mysql_error()."</p>";

Then elsewhere there is more to fill the form with the correct table row:

$id = $_GET["newschoice"];

if (isset($id) && $id != "") {

    $sql = "select headline, text, startDate, endDate, imageLocation, imagePlacement, imgStyles, link FROM news WHERE id = $id";
    $result = mysql_query($sql);
    $row = mysql_fetch_object($result);
    $left = "";
    $right = "";
    $dbImg = "";
    if ($row->imagePlacement == 0) 
        $left = "checked";
    if ($row->imagePlacement == 1) 
        $right= "checked";
    if (isset($row->imageLocation)) {
      $dbImg = "<img src=\"/images/uploads/$row->imageLocation\">";
    $showCameo = "style=\"display:block\"";

  $headline = $_REQUEST["headline"];
  $text= $_REQUEST["text"];
  $startDate= $_REQUEST["startDate"];
  $endDate= $_REQUEST["endDate"];
  $imageLocation= $_REQUEST["imageLocation"];
  $imagePlacement= $_REQUEST["imagePlacement"];
  $imgStyles = $_REQUEST["imgStyles"];
  $link = $_REQUEST["link"]; 

Apologies if this is hopelessly clunky. I'm not a dba but have had this task thrust upon me.

Best Answer

Check the database and do a select first using the same criteria and see what it returns. As mentioned above, UPDATE will not insert a new row. I'm positive your select will return more than one row and then you can work back from there to find the issue in your data or your code.

Also, no need to use INSERT INTO - that will not update but try to insert new records and any records that satisfy your where clause.

You could try to debug and see what value is being passed into the $id variable once you worked out the sql.