如何使用mySQLi使用预准备语句更新多个表？

I have a form with two fields with the name attribute of 'photo_title' and 'photographer_name', and a hidden field named 'photo_id'. When the user pushes the submit button, i want it to update two separate tables in the database. I can get it to update a single table, but as soon as I try to leftjoin the second table, it doesn't like it.

我有一个带有两个字段的表单,其名称属性为“photo_title”和“photographer_name”,还有一个名为“photo_id”的隐藏字段。当用户按下提交按钮时,我希望它更新数据库中的两个单独的表。我可以让它更新一个表,但是当我尝试leftjoin第二个表时,它不喜欢它。

I think there may be something wrong with my query string or the binding. How can I update two separate values in two separate tables in my Mysql database while still using prepared statements?

我认为我的查询字符串或绑定可能有问题。如何在我的Mysql数据库中的两个单独的表中更新两个单独的值,同时仍然使用预准备语句?

Here's the PHP:

这是PHP:

if (array_key_exists('update', $_POST)) { 

$sql = 'UPDATE photos SET photos.photo_title = ?, photographers.photographer_name = ?
    LEFT JOIN photographers ON photos.photographer_id = photographers.photographer_id
    WHERE photo_id = ?';

$stmt = $conn->stmt_init();
if ($stmt->prepare($sql)) { 
    $stmt->bind_param('ssi', $_POST['photo_title'], $_POST['photographer_name'], $_POST['photo_id']);       
    $done = $stmt->execute();
    }
}

Here's the form:

这是表格:

<form id="form1" name="form1" method="post" action="">
   <input name="photo_title" type="text" value=""/>
   <textarea name="photographer_name"></textarea>

   <input type="submit" name="update" value="Update entry" />
   <input name="photo_id" type="hidden" value="<?php echo $photo_id ?>"/>
</form>

1 个解决方案

#1

Here's an answer so folks who read this question see it, instead of finding it in your comment above. I'll mark this CW so I don't get any points for it.

这是一个答案,所以读过这个问题的人会看到它,而不是在上面的评论中找到它。我会标记这个CW,所以我没有得到任何积分。

UPDATE photos LEFT JOIN photographers 
  ON photos.photographer_id = photographers.photographer_id 
SET photos.photo_title = ?, photographers.photographer_name = ? 
WHERE photos.photo_id = ?

FWIW, the documentation for MySQL's UPDATE syntax is illustrative.

FWIW,MySQL的UPDATE语法的文档是说明性的。

#1