0

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?

EDIT

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

opendatabase();

$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 != "") {

    opendatabase();
    $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.

10
  • 1
    UPDATE will never insert a new row. Some other part of your application is doing the insert. Feb 20, 2017 at 19:27
  • The VALUES looks suspicious though. Shows us the whole code. Do you have INSERT INTO ... ON DUPLICATE KEY UPDATE ...? Feb 20, 2017 at 19:31
  • @ypercubeᵀᴹ I don't have that. I will try it now.
    – Robusto
    Feb 20, 2017 at 19:40
  • I didn't suggest you try it. Show us your code, all of it. Feb 20, 2017 at 19:42
  • @ypercubeᵀᴹ Edited to show all the relevant code.
    – Robusto
    Feb 20, 2017 at 19:48

1 Answer 1

1

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.