错误SQL查询php - Error SQL query php

当我试图用som moviegenres更新数据库时，我遇到了这个错误。

UPDATE RM_Movie2Genre SET
Error in executing query: 42000 Array
(
[0] => 42000
[1] => 1064
[2] => 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

电影是保存的，但不与流派。有谁能看到这里出了什么问题吗？我知道它在UPDATE RM_Movie2Genre SET中（用粗体标记），但我看不出有什么问题。。。

$output = null;
if($save) {
 // First save information on movie
 $sql = '
UPDATE RM_Movie SET
  title = ?,
  year = ?,
  image = ?,
  pris = ?,
  director = ?,
  length = ?,
  plot = ?,
  imdb = ?,
  trailer = ?,
  filter = ?
WHERE 
  id = ?
';
 $params = array($title, $year, $image, $pris, $director, $length, $plot, $imdb, $trailer, $filter, $id);
$db->ExecuteQuery($sql, $params);
// Then save information on genre
foreach($genre as $val) {
   **$db->ExecuteQuery('UPDATE RM_Movie2Genre SET', array($val));**
}
$output = 'Informationen sparades';
}

此代码将仅用更新/插入的电影替换所有电影。我将使用我的代码进行更新和插入。

// Get all genres and select those that should be selected
$sql = 'SELECT * FROM RM_MovieGenre';
$genres = $db->ExecuteSelectQueryAndFetchAll($sql);
$sql = 'SELECT idRM_MovieGenre AS id FROM RM_Movie2Genre WHERE idRM_Movie = ?';
$myGenres = $db->ExecuteSelectQueryAndFetchAll($sql, array($id), 0, PDO::FETCH_COLUMN);
$selectOptionGenres = "<select multiple name='genre[]'>";
 foreach($genres as $key => $val) {
$selected = in_array($val->id, $myGenres) ? ' selected' : null;
$selectOptionGenres .= "<option value='{$val->id}'{$selected}>{$val->name}</option>";
}
$selectOptionGenres .= '</select>';

// Check if form was submitted
$output = null;
if($save) {
// First save information on movie
$sql = '
UPDATE RM_Movie SET
  title = ?,
  year = ?,
  image = ?,
  pris = ?,
  director = ?,
  length = ?,
  plot = ?,
  imdb = ?,
  trailer = ?,
  filter = ?
 ';
 $params = array($title, $year, $image, $pris, $director, $length, $plot, $imdb, $trailer, $filter);
 $db->ExecuteQuery($sql, $params);
 // Then save information on genre
 $query="INSERT INTO RM_Movie2Genre(idRM_Movie,idRM_MovieGenre) values($id,?)";
foreach($genre as $val) {
   $val_array=array($val);
   $db->ExecuteQuery($query, $val_array);
}
 $output = 'Informationen sparades';
}
// Select information on the movie 
$sql = 'SELECT * FROM RM_Movie WHERE id = ?';
$params = array($id);
$res = $db->ExecuteSelectQueryAndFetchAll($sql, $params);
if(isset($res[0])) {
$movie = $res[0];
}
else {
 die('Failed: There is no movie with that id');
}

我想您想用更改行的值

 $id=$params[10];

我认为一个id有多个流派，并将其存储在RM_Movie2Genre表中。如果是这种情况，您应该使用INSERT查询。但首先，您应该删除旧值：

   $query="DELETE FROM RM_Movie2Genre where id=?";
   $val_array=array($id);
   $db->ExecuteQuery($query, $val_array);

现在，对于插入，如果$id是数字，请使用以下查询：

 $query="INSERT INTO RM_Movie2Genre(id,genre) values($id,?)";

否则，如果是字符串类型，则应使用单引号将$id括起来：

 $query="INSERT INTO RM_Movie2Genre(id,genre) values('$id',?)";

最后：

foreach($genre as $val) {
   $val_array=array($val);
   $db->ExecuteQuery($query, $val_array);
}

我认为UPDATE没有任何意义，因为使用更新查询，您要多次更新一行，这是无用的。无论如何，如果你仍然认为你应该update查询，那么用这个来更改$query：

 $query="UPDATE RM_Movie2Genre SET genre = ? WHERE id =$id";

如果是字符串类型：

 $query="UPDATE RM_Movie2Genre SET genre = ?  WHERE id ='$id'";

这段代码对创建和编辑都很好。谢谢大家把我带到那里！：-）

// Get all genres and select those that should be selected
$sql = 'SELECT * FROM RM_MovieGenre';
$genres = $db->ExecuteSelectQueryAndFetchAll($sql);
$sql = 'SELECT idRM_MovieGenre AS id FROM RM_Movie2Genre WHERE idRM_Movie = ?';
$myGenres = $db->ExecuteSelectQueryAndFetchAll($sql, array($id), 0, PDO::FETCH_COLUMN);
$selectOptionGenres = "<select multiple name='genre[]'>";
foreach($genres as $key => $val) {
  $selected = in_array($val->id, $myGenres) ? ' selected' : null;
  $selectOptionGenres .= "<option value='{$val->id}'{$selected}>{$val->name}</option>";
}
$selectOptionGenres .= '</select>';

// Check if form was submitted
$output = null;
if($save) {
  // First save information on movie
  $sql = '
    UPDATE RM_Movie SET
      title = ?,
      year = ?,
      image = ?,
      pris = ?,
      director = ?,
      length = ?,
      plot = ?,
      imdb = ?,
      trailer = ?,
      filter = ?
    WHERE 
      id = ?
  ';
  $params = array($title, $year, $image, $pris, $director, $length, $plot, $imdb, $trailer, $filter, $id);
  $db->ExecuteQuery($sql, $params);
    // Then save information on genre
    $query="INSERT INTO RM_Movie2Genre(idRM_Movie,idRM_MovieGenre) values($id,?)";
    foreach($genre as $val) {
       $val_array=array($val);
       $db->ExecuteQuery($query, $val_array);
    }
  $output = 'Informationen sparades';
}
// Select information on the movie 
$sql = 'SELECT * FROM RM_Movie WHERE id = ?';
$params = array($id);
$res = $db->ExecuteSelectQueryAndFetchAll($sql, $params);
if(isset($res[0])) {
  $movie = $res[0];
}
else {
  die('Failed: There is no movie with that id');
}