php - 按特定顺序运行 MySQL 查询

Question

我想知道是否有人可以在这里给我建议。我有一个页面正在更新数据库中的信息，从上一页我发布了任何新图像(也带有标题)，并且有一个用于删除图像的复选框。在处理页面上我有以下代码:

<?php
    session_start();
    $dbhost = 'removed';
    $dbuser = 'removed';
    $dbpass = 'removed';
    $dbname = 'removed';

    function reArrayFiles($file_post) {
        $file_ary = array();
        $file_count = count($file_post['name']);
        $file_keys = array_keys($file_post);
        for ($i=0; $i<$file_count; $i++) {
            foreach ($file_keys as $key) {
                $file_ary[$i][$key] = $file_post[$key][$i];
            }
        }
        return $file_ary;
    }
    try {
        $dbo = new PDO('mysql:host=localhost;dbname='.$dbname, $dbuser, $dbpass);
    }catch (PDOException $e) {
        print "Error!: " . $e->getMessage() . "<br/>";
    die();
    }

    $query="SELECT * FROM `soldier_info` WHERE `soldier_id` = " . $_POST['soldier_id'] ;
    foreach ($dbo->query($query) as $row) {
        $soldier_pre_images = explode(",", $row['soldier_images']);
    }

    if($_POST['deletephoto']){
        $imagestodelete=$row['soldier_images'];
        $captionstodelete=$row['soldier_images_captions'];
        foreach($_POST['deletephoto'] as $todelete){
            $deleteexplode = explode("--",$todelete);
            echo $deleteexplode;
            $deleteexplodepath = $deleteexplode[0];
            $deletecaption=$deleteexplode[1];
            $imagesafterdelete= str_replace($deleteexplodepath.",","",$imagestodelete);
            echo $imagesafterdelete;
            $captionsafterdelete = str_replace($deletecaption."--","",$captionstodelete);
            echo $captionsafterdelete;
            $unlinkpath = str_replace('site url','..',$deleteexplodepath);
            unlink($unlinkpath);
        }

        try {
            $dbo = new PDO('mysql:host=localhost;dbname='.$dbname, $dbuser, $dbpass);
            $dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $q = $dbo->prepare("UPDATE `soldier_info` SET `soldier_images`= :image, `soldier_images_captions`= :captions WHERE `soldier_id`= :id");
            $q->execute(array(":image" => $imagesafterdelete, ":captions" => $captionsafterdelete, ":id" => $_POST['soldier_id']));
        }catch (PDOException $e) {
            $db_error = "Error!: " . $e->getMessage() . "<br/>";
            die();
        } 
    }

    if ($_FILES['photo']) {
        $file_ary = reArrayFiles($_FILES['photo']);
        $capcount = 0;
        foreach ($file_ary as $file) {
            $allowedExts = array("gif", "jpeg", "jpg", "png");
            $temp = explode(".", $file["name"]);
            $extension = end($temp);
            if ((($file["type"] == "image/gif")||($file["type"] == "image/jpeg")||($file["type"] == "image/jpg")||($file["type"] == "image/pjpeg")||($file["type"] == "image/x-png")||($file["type"] == "image/png"))&&($file["size"] < 9000000) && in_array(strtolower($extension), $allowedExts)) {
                  if ($file["error"] > 0) {
                    $file_error = "Return Code: " . $file["error"] . "<br>";
                  } else {
                    $newfilename = "../assets/uploads/images/" . $_POST['soldier_id'] . "/" . $file["name"];
                    $file_fullname = "site urlv/assets/uploads/images/" . $_POST['soldier_id'] . "/" . $file["name"];
                    $new_caption = $_POST['image_captions'][$capcount];
                    if (file_exists($newfilename)) {
                      $file_exists = $file["name"] . " already exists. ";
                    } else {
                        if(is_dir("../assets/uploads/images/" . $_POST['soldier_id'])){
                            move_uploaded_file($file["tmp_name"],$newfilename);
                            if (strlen($uploaded_images)>1){
                            $uploaded_images = $uploaded_images . ",". $file_fullname;
                            }else{
                            $uploaded_images = $file_fullname;
                            }
                            if (strlen($uploaded_captions)>1){
                            $uploaded_captions = $uploaded_captions . "--". $new_caption;
                            }else{
                            $uploaded_captions = $new_caption;
                            }
                        }else{
                            mkdir("../assets/uploads/images/" . $_POST['soldier_id'], 0777, true);      
                            move_uploaded_file($file["tmp_name"],$newfilename);
                            if (strlen($uploaded_images)>1){
                            $uploaded_images = $uploaded_images . ",". $file_fullname;
                            }else{
                            $uploaded_images = $file_fullname;
                            }
                            if (strlen($uploaded_captions)>1){
                            $uploaded_captions = $uploaded_captions . "--". $new_caption;
                            }else{
                            $uploaded_captions = $new_caption;
                            }
                        }
                    }
                    if (strlen($row['soldier_images'])>1){
                    $uploaded_images = $row['soldier_images'] . ",". $uploaded_images;
                    }else{
                    $uploaded_images = $uploaded_images;
                    }
                  }
                } else {
                  $file_invalid = "The file is not a jpg/gif/png file, or is larger than 20mb. Please try again.";
                }
                $capcount = $capcount+1;
        }
        try {
            $dbo = new PDO('mysql:host=localhost;dbname='.$dbname, $dbuser, $dbpass);
            $dbo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
            $q = $dbo->prepare("UPDATE `soldier_info` SET `soldier_images`= :image, `soldier_images_captions`= :captions  WHERE `soldier_id`= :id");
            $q->execute(array(":image" => $uploaded_images, ":captions" => $uploaded_captions, ":id" => $_POST['soldier_id']));
        }catch (PDOException $e) {
            $db_error = "Error!: " . $e->getMessage() . "<br/>";
            die();
        }
    }

    $query="SELECT * FROM `soldier_info` WHERE `soldier_id` = " . $_POST['soldier_id'] ;
    foreach ($dbo->query($query) as $row) {
        $firstname = $row['firstname'];
        $surname = $row['surname'];
        $yearofbirth = $row['yearofbirth'];
        $dateofdeath = date("d/m/Y", strtotime($row['dateofdeath']));
        $ageatdeath = $row['ageatdeath'];
        $regiment = $row['regiment'];
        $battallion_brigade = $row['battallion_brigade'];
        $Coybty = $row['Coy/bty'];
        $rank = $row['rank'];
        $solider_number = $row['soldier_number'];
        $Next_of_Kin = $row['Next_of_Kin'];
        $CWGC = $row['CWGC'];
        $Place_Died = $row['Place_Died'];
        $Connection_to_Wymeswold = $row['Connection_to_Wymeswold'];
        $Cemetery = $row['Cemetery'];
        $LRoH = $row['LRoH'];
        $Grave = $row['Grave'];
        $Memorial = $row['Memorial'];
        $Pier_Face = $row['Pier_Face'];
        $Other_Mem = $row['Other_Mem'];
        $Other_Details = $row['Other_Details'];
        $soldier_images = explode(",", $row['soldier_images']);
        $soldier_images_captions = explode("--", $row['soldier_images_captions']);
    }
    $dbo = null;
    ?>

这不是一个实时站点，正在测试/开发中。然而，我看到的问题是，所有查询都在运行，但页面加载就像选择首先运行一样(我的意思是返回的记录是处理更新之前的记录。我可以强制执行此选择吗？最后运行以便获取最新记录？

Answer 1

我认为您在最后一次选择时获得了“原始”数据，因为您在不同的连接上运行查询。因此，它们将存在于孤立的事务中，当最后一个选择运行时，这些事务可能无法正确提交。

如果你在同一个 PDO 对象中运行它们，我认为它会起作用。

(如果您采用 POST 参数并将它们连接到 SQL 中，您的服务器将被黑客攻击。这只是时间问题，您在这里有 URL 可供所有人查看。)