PHP脚本中的SQL查询不会填充数据库中的表

Question

In PHP script I parsed some .csv file and trying to execute on a few ways and this is a closest I can get. When I run query manually in database everything is o.k but when I go through the the script I just got New record created successfully and the table stays empty except ID which count how many inserts I got.

o.k that's cool optimization but I still don't getting the data. Yap the $dataPacked is clear below is my whole script can you pls gave some suggestion.

<?php

class AdformAPI {

    private $baseUrl = 'https://api.example.com/Services';

    private $loginUrl = '/Security/Login';

    private $getDataExportUrl = '/DataExport/DataExportResult?DataExportName=ApiTest';

    public function login($username, $password) {
        $url = $this->baseUrl . $this->loginUrl;
        $params = json_encode(array('UserName' => $username, 'Password' => $password));
        $response = $this->_makePOSTRequest($url, $params);
        $response = json_decode($response, true);

        if (empty($response['Ticket'])) {
            throw new \Exception('Invalid response');
        }

       // var_dump($response);
        return $response['Ticket'];
    }

    public function getExportData($ticket) {
        $url = $this->baseUrl . $this->getDataExportUrl;
        $ch = curl_init();
        curl_setopt_array($ch, array(
            CURLOPT_RETURNTRANSFER => 1,
            CURLOPT_URL => $url,
        ));
        curl_setopt($ch, CURLOPT_HTTPHEADER, array(
            'Ticket: '. $ticket
        ));

        $output = curl_exec($ch);

        return $output;
    }

    public function downloadFileFromUrl($url, $savePath) {
        $ch = curl_init();
        curl_setopt($ch, CURLOPT_URL, $url);
        curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
        curl_setopt($ch, CURLOPT_SSLVERSION,3);
        $data = curl_exec ($ch);
        $error = curl_error($ch);
        curl_close ($ch);

//        if (!is_dir($savePath) && is_writable($savePath)) {
            $file = fopen($savePath, "w+");
            fputs($file, $data);
            fclose($file);
//        } else {
//            throw new \Exception('Unable to save file');
//        }
    }

    private function _makePOSTRequest($url, $json_data) {
        $ch = curl_init();

        curl_setopt($ch,CURLOPT_URL, $url);
        curl_setopt($ch,CURLOPT_RETURNTRANSFER,true);
        curl_setopt($ch,CURLOPT_HEADER, false);
        curl_setopt($ch, CURLOPT_POST, count($json_data));
        curl_setopt($ch, CURLOPT_POSTFIELDS, $json_data);

        $output = curl_exec($ch);
        curl_close($ch);

        return $output;
    }
}

// Login and data download url
$api = new AdformAPI();
$ticket = $api->login('example', '123546');
$exportDataResponseJson = $api->getExportData($ticket);
$exportDataResponse = json_decode($exportDataResponseJson, true);

if (empty($exportDataResponse['DataExportResult']) || $exportDataResponse['DataExportResult']['DataExportStatus'] != "Done") {
    throw new \Exception('GetDataExport invalid response');
}

// Download zip
$fileDir = '/var/www/html/app-catalogue/web/export';
$fileName = 'report.zip';
$filePath = $fileDir . DIRECTORY_SEPARATOR . $fileName;
$api->downloadFileFromUrl($exportDataResponse['DataExportResult']['DataExportResultUrl'], $filePath);

// Unzip
$zip = new ZipArchive;
$res = $zip->open($filePath);
$csvFilename = '';
if ($res === true) {
    for ($i = 0; $i < $zip->numFiles; $i++) {
        $csvFilename = $zip->getNameIndex($i);
    }
    $zip->extractTo($fileDir);
    $zip->close();
} else {
    throw new Exception("Unable to unzip file");
}

// Parse CSV
$csvPath = $fileDir . DIRECTORY_SEPARATOR . $csvFilename;

if (is_readable($csvPath)) {
    $dataCsv = file_get_contents($fileDir . DIRECTORY_SEPARATOR . $csvFilename);
    $dataArr = explode("
", $dataCsv);
    $dataPacked = array();

    foreach ($dataArr as $row) {
        $row = str_replace(" ", "", $row);
        //$row = wordwrap($row, 20, "
", true);
        $row = preg_replace('/^.{20,}?\b/s', "$0&nbsp", $row);
        $row = explode("\t", $row);
        $dataPacked[] = $row;
    }
}



// SQL Connestion

$servername = "192.168.240.22";
$username = "liferaypublic";
$password = "liferaypublic";
$dbname = "liferay_dev";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
$conn->set_charset("utf8");
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}



$conn->query("set names 'utf8'");
$sql = " INSERT INTO ho_adform_reports (`Timestamp`, `Campaign`, `Paid_Keywords`, `Natural_Search_Keywords`, `Referrer_Type`, `Referrer`, `Page`, `Order_ID`)
VALUES ";

$flag = true;

foreach($dataPacked as $rowArray) {
    if($flag or count($rowArray)<= 7) { $flag = false; continue; }
    $sql .= "('".implode("','", $rowArray)."'),";
}
$sql = trim($sql,",");

echo $sql; //For debug only

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();

//var_dump($dataPacked);

Answer 1

try this - I am assuming you have sanitised the values in $dataPacked.

Note edited to addslashes just in case.

$conn->query("set names 'utf8'");
$sql = " INSERT INTO ho_adform_reports (`Timestamp`, `Campaign`, `Paid_Keywords`, `Natural_Search_Keywords`, `Referrer_Type`, `Referrer`, `Page`, `Order_ID`)
VALUES ";

$flag = true;

foreach($dataPacked as $rowArray) {

    if($flag or count($rowArray)<= 7) { $flag = false; continue;}
    foreach ($rowArray as $k=>$v) {
         $sanitised = preg_replace("/[^[:alnum:][:space:]]/ui", '', $v);
         $rowArray[$k] = addslashes(trim($sanitised));
    }
    $sql .= "('".implode("','", $rowArray)."'),";
}
$sql = trim($sql,",");

echo $sql; //For debug only

if ($conn->query($sql) === TRUE) {
    echo "New record created successfully";
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();