在一个mysql查询中更新两个表(updating two tables in one mysql query)
我在AJAX调用结束时有一个很好的一点点php / mysql工作，它会更新一个表。 我现在需要更改此更新，以便帖子的PART更新一个表，另一个部分更新另一个表。 我不清楚如何正确地做到这一点。 这是我迄今为止所做的工作的代码：
if(isset($_POST['p2From'])) { $userInput = $_POST['p2From']; if(trim($userInput) == "") { $userInput = NULL; } try { $stmt = $conn->prepare("UPDATE $database.app_$applicationKey SET `p2From` = :userinput, `lastModified` = :time WHERE `appID` = :appid"); $stmt->bindParam(':userinput', $userInput, PDO::PARAM_STR, 8); $stmt->bindParam(':time', time(), PDO::PARAM_INT, 11); $stmt->bindParam(':appid', $appID, PDO::PARAM_INT, 11); $stmt->execute(); } catch(PDOException $e) { catchMySQLerror($e->getMessage()); } }我想要做的是有效地实现这一目标：
if(isset($_POST['p2From'])) { $userInput = $_POST['p2From']; if(trim($userInput) == "") { $userInput = NULL; } try { $stmt = $conn->prepare("UPDATE $database.app_AIGtabs SET `p2From` = :userinput WHERE `appID` = :appid"); $stmt->bindParam(':userinput', $userInput, PDO::PARAM_STR, 8); $stmt->bindParam(':appid', $appID, PDO::PARAM_INT, 11); $stmt->execute(); } catch(PDOException $e) { catchMySQLerror($e->getMessage()); } }和
if(isset($_POST['p2From'])) { $userInput = $_POST['p2From']; if(trim($userInput) == "") { $userInput = NULL; } try { $stmt = $conn->prepare("UPDATE $database.app_$applicationKey SET `lastModified` = :time WHERE `appID` = :appid"); $stmt->bindParam(':time', time(), PDO::PARAM_INT, 11); $stmt->bindParam(':appid', $appID, PDO::PARAM_INT, 11); $stmt->execute(); } catch(PDOException $e) { catchMySQLerror($e->getMessage()); } }但是，我想在一个更新查询中实现这一切，而不是两个。 我确信这是可能的，虽然我不清楚如何在一个mysql查询中执行此操作。 有人可以告诉我如何实现这一目标吗？
I've had a nice little bit of php/mysql working at the end of an AJAX call, which updates a table. I now need to change this update so that PART of the post updates one table and the other part updates another table. I'm unclear how to do this correctly. This is the code as to what I have had working to date:
if(isset($_POST['p2From'])) { $userInput = $_POST['p2From']; if(trim($userInput) == "") { $userInput = NULL; } try { $stmt = $conn->prepare("UPDATE $database.app_$applicationKey SET `p2From` = :userinput, `lastModified` = :time WHERE `appID` = :appid"); $stmt->bindParam(':userinput', $userInput, PDO::PARAM_STR, 8); $stmt->bindParam(':time', time(), PDO::PARAM_INT, 11); $stmt->bindParam(':appid', $appID, PDO::PARAM_INT, 11); $stmt->execute(); } catch(PDOException $e) { catchMySQLerror($e->getMessage()); } }What I want to do is effectively achieve this:
if(isset($_POST['p2From'])) { $userInput = $_POST['p2From']; if(trim($userInput) == "") { $userInput = NULL; } try { $stmt = $conn->prepare("UPDATE $database.app_AIGtabs SET `p2From` = :userinput WHERE `appID` = :appid"); $stmt->bindParam(':userinput', $userInput, PDO::PARAM_STR, 8); $stmt->bindParam(':appid', $appID, PDO::PARAM_INT, 11); $stmt->execute(); } catch(PDOException $e) { catchMySQLerror($e->getMessage()); } }And
if(isset($_POST['p2From'])) { $userInput = $_POST['p2From']; if(trim($userInput) == "") { $userInput = NULL; } try { $stmt = $conn->prepare("UPDATE $database.app_$applicationKey SET `lastModified` = :time WHERE `appID` = :appid"); $stmt->bindParam(':time', time(), PDO::PARAM_INT, 11); $stmt->bindParam(':appid', $appID, PDO::PARAM_INT, 11); $stmt->execute(); } catch(PDOException $e) { catchMySQLerror($e->getMessage()); } }However I'd like to ultimetly achieve this all in one update query as opposed to two. I'm sure this is possible, though I am very unclear how to perform this in one mysql query. Can someone show me how to achieve this?
最满意答案
使用多表更新语法： https ： //dev.mysql.com/doc/refman/5.6/en/update.html
if(isset($_POST['p2From'])) { $userInput = $_POST['p2From']; if(trim($userInput) == "") { $userInput = NULL; } try { $stmt = $conn->prepare(" UPDATE $database.app_$applicationKey JOIN $database.app_AIGtabs on $database.app_$applicationKey.`appID` = $database.app_AIGtabs.`appID` SET `lastModified` = :time, `p2From` = :userinput WHERE $database.app_$applicationKey.`appID` = :appid;"); $stmt->bindParam(':userinput', $userInput, PDO::PARAM_STR, 8); $stmt->bindParam(':time', time(), PDO::PARAM_INT, 11); $stmt->bindParam(':appid', $appID, PDO::PARAM_INT, 11); $stmt->execute(); } catch(PDOException $e) { catchMySQLerror($e->getMessage()); } }Use the Multiple-table Update syntax: https://dev.mysql.com/doc/refman/5.6/en/update.html
if(isset($_POST['p2From'])) { $userInput = $_POST['p2From']; if(trim($userInput) == "") { $userInput = NULL; } try { $stmt = $conn->prepare(" UPDATE $database.app_$applicationKey JOIN $database.app_AIGtabs on $database.app_$applicationKey.`appID` = $database.app_AIGtabs.`appID` SET `lastModified` = :time, `p2From` = :userinput WHERE $database.app_$applicationKey.`appID` = :appid;"); $stmt->bindParam(':userinput', $userInput, PDO::PARAM_STR, 8); $stmt->bindParam(':time', time(), PDO::PARAM_INT, 11); $stmt->bindParam(':appid', $appID, PDO::PARAM_INT, 11); $stmt->execute(); } catch(PDOException $e) { catchMySQLerror($e->getMessage()); } }更多推荐
发布评论