使用 IN() 子句的 PHP/MYSQL 更新

Question

今天，我尝试在 MySQL 中创建一个查询，该查询将更新所有在字符串中具有 id 的 invoice_numbers。

UPDATE userhour SET invoice_number='{$invoice_number}' 
 WHERE id IN (" . $userhoursString . ")";

所有字段都存在，查询也有效，但是我不断收到此错误，但无法摆脱:

Database Query failed: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '1' at line 1

Last Query content: 1

我的浏览器上的回显给了我:UPDATE userhour SET Invoice_number='2016030953500' WHERE id IN (24)

它在 phpmyadmin 中完美运行，所以我完全迷失了。

/////编辑/////

Field                Type    Null    Key   Default    Extraid                   int(11) NO      PRI   NULL       Auto_incrementproject_id           int(11) NO      NULL  payment_id           int(11) NO      NULL  userhour_category_id int(11) NO      NULL  minutes_spend        int(11) NO      NULLdescription          text    NO      NULLactive               int(11) NO      NULLinvoice_number       varchar NO      0date_created         datetimeNO      NULLdate_modified        datetimeNO      NULL

<?php
require_once("Classes/ini_classes.php");
$_SESSION["errors"] = "";

if (!isset($_SESSION['username'])) {
    redirect_to('login.php');
} else {
    require("Classes/phpMailer/PHPMailerAutoload.php");
    error_reporting( 0 );
    define('EURO',chr(128));

    $customer_id    =   $Database->escape_value($_GET['customer']);
    $project_id     =   $Database->escape_value($_GET['project']);
    $vat_id         =   $_POST['vat'];

    // retrieve all necessary database information.
    $customer_result_set            =       $Database->fetchSingleArray($Database->customQuery("SELECT * FROM customer WHERE id='{$customer_id}'"));
    $project_result_set             =       $Database->fetchSingleArray($Database->customQuery("SELECT * FROM project WHERE id='{$project_id}'"));
    $vat_result_set                 =       $Database->fetchSingleArray($Database->customQuery("SELECT * FROM vat WHERE id='{$vat_id}'"));

    // get all customer info in variables
    $insert_customer_name           =       $customer_result_set['customer_name'];
    $insert_customer_streetname     =       $customer_result_set['streetname'];
    $insert_customer_city           =       $customer_result_set['city'];
    $insert_customer_zip_code       =       $customer_result_set['zip_code'];
    $insert_customer_province       =       $customer_result_set['province'];

    // get all necessary project info
    $insert_project_name            =       $project_result_set['project_name'];

    // calculate the price variables
    $total_time                     =   $_POST['time_so_far'];
    $price_per_hour                 =   $_POST['price_per_hour'];
    $vat                            =   $vat_result_set['percentage'];
    $subTotal                       =   ($price_per_hour / 60) * $total_time;
    $grandTotal                     =   (($subTotal * $vat) / 100) + $subTotal;
    $grandTotalDisplay              =   number_format((float)$grandTotal, 2, '.', '');


    $date_today             =   strtotime(date('Y-m-d'));
    $deadline_date          =   strtotime('+30 day', $date_today);
    $deadline_formatted     =   gmdate("m-d-Y", $deadline_date);

    // rwewrite the array to a string and then get all userhours associated.
    $userhoursToBill        =   $_POST['billed'];
    $userhoursString        =   implode(', ', $userhoursToBill);
    $userhour_result_set    =   $Database->fetchAllArray($Database->customQuery("SELECT * FROM userhour WHERE id IN ($userhoursString) AND invoice_number='0'"));

    $invoice_number         =   date('Ymd') . $customer_id . $project_id . date('s');


    // email to send to
    $email_to_send_to       =   $Database->escape_value($_POST['email']);
}

// create the PDF
    class PDF_receipt extends FPDF
    {
        function ___construct($orientation = 'P', $unit = 'pt', $format = 'A4', $margin = 40)
        {
            $this->FPDF($orientation, $unit, $format);
            $this->SetTopMargin($margin);
            $this->SetRightMargin($margin);
            $this->SetLeftMargin($margin);

            $this->SetAutoPageBreak(true, $margin);
        }

        function Header()
        {
            $this->SetFont('Arial', 'B', 20);
            $this->SetFillColor(36, 96, 84);
            $this->SetTextColor(225);
            $image = "images/logo-for-invoice.jpg";
            $this->Image($image);

        }

        function Footer()
        {
            $this->SetFont('Arial', '', 12);
            $this->SetTextColor(0);
            $this->Cell(0, 20, "Thank you for purchasing with us.", 'T', 0, 'C');
        }
    }


    $pdf =  new PDF_receipt();
    $pdf->AddPage();
    $pdf->SetFont('Arial', '', 12);

    $pdf->Ln();
    $pdf->SetY(30);
    $pdf->SetFillColor(14);
    $pdf->SetTextColor(225);

    $pdf->SetFillColor(255);
    $pdf->SetTextColor(0);
    $pdf->SetY(45);

    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Cell(190, 7,  "Invoice for: " , 0, 1, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(135, 7,  $insert_customer_name, 0, 0, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(55, 7, 'Reniax communication solutions', 0, 1, 'L', true);

    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(135, 7, $insert_customer_streetname, 0, 0, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(55, 7, 'Richterslaan 177', 0, 1, 'L', true);
    $pdf->SetFont('Arial', '', 10);

    $pdf->Cell(135, 7, $insert_customer_zip_code. ', ' . $insert_customer_city, 0, 0, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(55, 7, '3431 AJ, Nieuwgein', 0, 1, 'L', true);

    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(135, 7, $insert_customer_province, 0, 0, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(55, 7, 'Tel: 030-6300415', 0, 1, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Ln();

    $pdf->SetFont('Arial', '', 18);
    $pdf->SetTextColor('19', '141', '223');
    $pdf->Cell(0,15, 'INVOICE', 0, 1, 'L');
    $pdf->SetFont('Arial', '', 12);
    $pdf->Cell(0, 1,'', 'B', 1, 'L', true);
    $pdf->SetTextColor(0);
    $pdf->SetFont('Arial', '', 12);
    $pdf->Ln();

    // border fix
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(0, 3,'', 0, 1, 'L', true);

    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(40, 7,'Invoice number: ', 0, 0, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(55, 7, $invoice_number, 0, 0, 'L', true);

    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(40, 7,'Payment terms:', 0, 0, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(55, 7,'Payment within 30 days.', 0, 1, 'L', true);

    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(40, 7, 'Date: ' . date('M-d-Y'), 0, 0, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(55, 7, date('M-d-Y'), 0, 0, 'L', true);

    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(40, 7,'Bank account name: ' , 0, 0, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(55, 7,'Rodney Wormsbecher', 0, 1, 'L', true);

    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(40, 7,'Payment due: ', 0, 0, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(55, 7, $deadline_formatted, 0, 0, 'L', true);

    $pdf->SetFont('Arial', 'B', 10);
    $pdf->Cell(40, 7,'IBAN: ', 0, 0, 'L', true);
    $pdf->SetFont('Arial', '', 10);
    $pdf->Cell(45, 7,'RABO NL00 1234 1234', 0, 1, 'L', true);
    // border fix
    $pdf->Cell(0, 3,'', 'B', 1, 'L', true);
    $pdf->Ln();


    $pdf->SetFont('Arial', 'B', 12);
    $pdf->Ln(10);

    $pdf->SetTextColor('19', '141', '223');
    $pdf->Cell(110, 10, "Description", 1, 0, 'C', true);
    $pdf->Cell(30, 10, "Date", 1, 0, 'C', true);
    $pdf->Cell(25, 10, "Time", 1, 0, 'C', true);
    $pdf->Cell(25, 10, "Price", 1, 1, 'C', true);
    $pdf->SetTextColor(0);
    $pdf->SetFillColor(243);
    $pdf->Cell(110, 1, "", 'LRT', 0, 'C', true);
    $pdf->Cell(30, 1, "", 'LRT', 0, 'C', true);
    $pdf->Cell(25, 1, "", 'LRT', 0, 'C', true);
    $pdf->Cell(25, 1, "", 'LRT', 1, 'C', true);
    $pdf->SetFillColor(255);




    // total width: 190
    $pdf->SetFont('Arial', '', 10);
    $counter = 0;
    foreach ($userhour_result_set as $userhour) {
        if (($counter % 2) == 0 ) {
            $pdf->SetFillColor(243);
        } else {
            $pdf->SetFillColor(255);
        }
        $pdf->Cell(110, 8, ' ' . substr($userhour['description'], 0, 63), "LR", 0, 'L', true);
        $pdf->Cell(30, 8, substr($userhour['date_created'], 0, 10), "LR", 0, 'C', true);
        $pdf->Cell(25, 8, ' ' . calculateMinTohours($userhour['minutes_spend']), "LR", 0, 'L', true);
        $pdf->Cell(25, 8, ' ' . EURO . number_format((float)( $price_per_hour / 60), 2, '.', '') * $userhour['minutes_spend'], "LR", 1, 'L', true);

        $counter++;
    }

    $pdf->Cell(140, 8, '', 'T', 0, 'R', true);
    $pdf->Cell(25, 8,  ' ' . calculateMinTohours($total_time), 1, 0, 'L', true);
    $pdf->Cell(25, 8, ' ' . EURO . number_format((float)$subTotal, 2, '.', ''), 1, 1, 'L', true);

    $pdf->Cell(140, 8, '', 0, 0, 'R', true);
    $pdf->Cell(25, 8,  'VAT: ', 'T', 0, 'R', true);
    $pdf->Cell(25, 8, ' ' . $vat . "%", 1, 1, 'L', true);

    $pdf->Cell(140, 8, '', 0, 0, 'R', true);
    $pdf->Cell(25, 8,  'Total: ', 0, 0, 'R', true);
    $pdf->Cell(25, 8, ' ' . EURO . number_format((float)$grandTotalDisplay, 2, '.', ''), 1, 1, 'L', true);


    $pdf->Ln(10);
    $pdf->Output('invoices/' . $invoice_number . '.pdf', 'F');


    // send the PDF through mail.
    $mail = new PHPMailer();
    $mail->IsSMTP();
    $mail->Mailer = "SMTP";
    $mail->Host = "smtp-mail.outlook.com";
    $mail->Port = "587"; // 8025, 587 and 25 can also be used. Use Port 465 for SSL.
    $mail->SMTPAuth = true;
    $mail->SMTPSecure = 'TLS';
    $mail->Username = "rodney22@live.nl";
    $mail->Password = "rodney2802";

    $mail->From     = "rodney22@live.com";
    $mail->FromName = "Rodney Wormsbecher";
    $mail->AddAddress($email_to_send_to);
    $mail->AddReplyTo("rodney22@live.nl", "Rodney Wormsbecher");

    $mail->Subject  = "Invoice #" . $invoice_number . " - customer: " . $insert_customer_name . " project: " . $insert_project_name;
    $mail->Body     = "The invoice for " . $insert_customer_name . " project: " . $insert_project_name;
    $mail->AddAttachment('invoices/' . $invoice_number . '.pdf', 'invoice for: ' . $invoice_number);
    $mail->WordWrap = 50;

    if(!$mail->Send()) {
        $_SESSION['flash_message']      =   'Mailer error: ' . $mail->ErrorInfo;
        exit;
    }


    // get all userhours and update their invoice numbers

    $sql = "UPDATE userhour SET invoice_number='{$invoice_number}'  WHERE id IN (" . $userhoursString . ")";
    echo $sql;
    $update  =  $Database->customQuery($Database->customQuery($sql));


    // when finished redirect.
    $log_message        =   "[" . date("Y-m-d H:i:s") . "]  [ INVOICE CREATE ]  " . $invoice_number . " has been created. by " . ucfirst($_SESSION['username']);
    WriteToLog($log_message, 0);

    $_SESSION['flash_message']      =   $invoice_number . " has been created.";
  //  redirect_to("customer_view.php?project=$project_id&customer=$customer_id");
?>

有人知道我做错了什么吗？非常感谢任何帮助

Answer 1

错误在这一行:

$update  =  $Database->customQuery($Database->customQuery($sql));

你现在可以自己发现它了，不是吗？

有两次对 $Database->customQuery() 的调用。内部调用运行查询并可能返回1(或TRUE)。外部调用尝试将字符串 '1' 作为 SQL 查询运行，因此出现错误消息。

将其更改为:

$update = $Database->customQuery($sql);