从循环查询中插入多行-解网

问：

我正在尝试从表中获取客户列表，然后每天将数据附加到他们身上。我不知道如何在数据库中插入具有相同名称的多行

我有 3 个表：公司、工作、报告。我正在做一个 2 个循环，将表格中的公司和工作数据转换为一个表格，该表格每天添加到一个表格中，该表格将进入 repots 表格。我尝试将其作为数组并在 while 循环、foreach 循环中执行，但每次都没有奏效。我在某个地方出错了，我只是不知道在哪里

 $companyquery = "SELECT * FROM companiestest WHERE (enabled != 'true') ORDER BY name";
    $companies = $mysqli->query($companyquery);
    while($company = $companies->fetch_assoc()) {
        $cid = htmlspecialchars($company['id']);
        $companyname = htmlspecialchars($company['name']);
        echo "<tr>";
        $jobquery = "SELECT * FROM jobstest WHERE (cid = " . $company['id'] . ") AND (enabled != 'true') ORDER BY jobname";
        $jobs = $mysqli->query($jobquery);
            while($job = $jobs->fetch_assoc()) {                
                $bid = htmlspecialchars($job['id']);
                $product = htmlspecialchars($job['product']);
                $server = htmlspecialchars($job['server']);
                $medium = htmlspecialchars($job['medium']);
                $jobname = htmlspecialchars($job['jobname']);
                echo "<input type='hidden' name='backupid' value='$bid''>";
                echo "<input type='hidden' name='companyname' value='$companyname''>";
                echo "<input type='hidden' name='companyid' value='$cid'>";
                echo "<td>$companyname - $cid</td>";
                echo "<td>$product</td>";
                echo "<td>$server</td>";
                echo "<td>$medium</td>";
                echo "<td>$jobname</td>";
                echo "<td><select name='result' required>
                    <option disabled selected value>Result</option>
                    <option value='success'>Successful</option>
                    <option value='warn'>Warning</option>
                    <option value='fail'>Fail</option>
                </select></td>";
                echo "<td><input type='text' name='ticket' placeholder='Ticket Number'></td>";
                echo "</tr>";

if(isset($_POST['submit'])){
        $companyname = strip_tags(trim($_POST['companyname']));
        $date = strip_tags(trim($_POST['date']));
        $staffmember = strip_tags(trim($_POST['staff']));
        $result = strip_tags(trim($_POST['result']));
        $ticket = strip_tags(trim($_POST['ticket']));
        $companyid = strip_tags(trim($_POST['companyid']));
        $jobid = strip_tags(trim($_POST['jobid']));

        $array = array();
        array_push($array, $companyname, $date, $staffmember, $result, $ticket, $companyid, $jobid);

        //$array = "('$companyname', '$date', '$staffmember', '$result', '$ticket', '$companyid')";

        print_r($array);

        $query = "INSERT INTO reporttest (company, date, staff, result, ticketnum, cid, bid) VALUES ($companyname, $date, $staffmember, $result, $ticket, $companyid, $jobid)";
        //echo "$query";
}

PHP 循环 mysqli

这是开发的早期阶段，因此在进行准备和绑定之前，我需要先获得正确的语法。我忘记了报价，所以要补充一点，谢谢，Enabled是数据库中的二进制值，所以当客户离开公司时，我们会在数据库中将其标记为false，这会将他们从页面上的列表中删除

0赞 aynber 9/12/2019

我建议现在使用准备好的语句和装订，因为它将消除一大堆令人头疼的引用问题，比如这个。如果 enabled 是布尔值，则需要传递 1/0 而不是字符串。

答：

-1赞 Nasherx 9/20/2019 #1

答案是一个具有键增量的 foreach 循环，它将每个变量插入到自己的数组中。然后在数组上绑定一个 foreach 并执行到数据库中。仍然有很多修改与数据有关，并限制查询中的列，但到目前为止，它似乎没有问题。

毫无疑问，有更好的方法可以做到这一点，但目前还没有人提出任何帮助建议

    $companyquery = "SELECT * FROM companiestest WHERE (enabled != 'true') ORDER BY name";
    $companies = $mysqli->query($companyquery);
    while($company = $companies->fetch_assoc()) {
        $cid = htmlspecialchars($company['id']);
        $companyname = htmlspecialchars($company['name']);
        echo "<tr>";
        $backupjobquery = "SELECT * FROM backupjobstest WHERE (cid = " . $company['id'] . ") AND (enabled != 'true') ORDER BY jobname";
        $backupjobs = $mysqli->query($backupjobquery);
            foreach($backupjobs as $key => $backupjob){
                $key++;

                $bid = htmlspecialchars($backupjob['id']);
                $product = htmlspecialchars($backupjob['product']);
                $server = htmlspecialchars($backupjob['server']);
                $medium = htmlspecialchars($backupjob['medium']);
                $jobname = htmlspecialchars($backupjob['jobname']);
                echo "<input type='hidden' name='backupid[]' value='$bid'>";
                echo "<input type='hidden' name='companyid[]' value='$cid'>";
                echo "<td>$companyname - $cid</td>";
                echo "<td>$product</td>";
                echo "<td>$server</td>";
                echo "<td>$medium</td>";
                echo "<td>$jobname</td>";
                echo "<td><select name='result[]' required>
                    <option disabled selected value>Result</option>
                    <option value='Successful'>Successful</option>
                    <option value='Warning'>Warning</option>
                    <option value='Failure'>Failure</option>
                </select></td>";
                echo "<td><input type='number' name='ticketnum[]' placeholder='Ticket Number'></td>";
                echo "</tr>";

        }
    }

    if(isset($_POST['submit'])){
        $results = array();
        $ticketnums = array();
        $cids = array();
        $bids = array();
        $date = strip_tags(trim($_POST['date']));
        $staffmember = strip_tags(trim($_POST['staff']));

        foreach ($_POST['result'] as $key => $result) {
            array_push($results, $result);
        }
        foreach ($_POST['ticketnum'] as $key => $ticketnum) {
            array_push($ticketnums, $ticketnum);
        }
        foreach ($_POST['companyid'] as $key => $cid) {
            array_push($cids, $cid);
        }
        foreach ($_POST['backupid'] as $key => $bid) {
            array_push($bids, $bid);
        }

        $sql = "INSERT INTO reporttest (date, staff, result, ticketnum, cid, bid) VALUES (?, ?, ?, ?, ?, ?)";
        $stmt = $mysqli->prepare($sql);
        if ( !$stmt ) { die('prepare failed'); }
        $stmt->bind_param("sssiii", $date, $staffmember, $result, $ticketnum, $cid, $bid);
        foreach ($bids as $key => &$bid) {
            $result = $results[$key];
            $ticketnum = $ticketnums[$key];
            $cid = $cids[$key];
            $bid = $bids[$key];
            if ($stmt->execute()) { 
                echo "Successfully updated the report";
            } else {
                echo "Error updating";
            }
            $stmt->fetch();
            printf("Date: %s, Staff: %s, result: %s, Ticket: %s, cid: %s, bid: %s", $date, $staffmember, $result, $ticketnum, $cid, $bid);
            echo "<br>";
        }
        $stmt->close();
    }

上一个：如何停止 do-while 循环和修复按钮功能？

下一个：如何在 php [duplicate] 中循环表

从循环查询中插入多行

Insert Multiple rows from loop query

评论