获取sql查询的开始/结束时间

Question

I'm parsing an CSV to a MySQL table using PHP.

1. I have timestamp columns in my table "File" called "StartTime" (start of the parsing of the file), "EndTime"(end of parsing of the file) and "CreationDate"(the time the row was created). What ways are there to get these times? Is NOW() a good solution?

2. I also have a column called "Rows with errors" which is the number of rows that has errors in them and thus won't parse. How can I retrieve the number of rows with errors in the CSV file? (A row with an error would be a row where the types are wrong, for example it should be: [varchar, int, timestamp, timestamp, timestamp]]

Here's my code:

function parse($file) { 

$file_handle = fopen($file, "r"); //opens CSV
while (($row = fgetcsv($file_handle, 1000, ",")) !== false){

$file_name = basename($file);
$Rows_with_errors = ?;
$StartDate= date("Y-m-d H:i:s", time() ); //?
$EndDate=?;
$CreationDate=?;

$sql = "INSERT INTO file (Filename, TotalNumberOfRows, RowsWithErrors, StartDate, EndDate, CreationDate) 
VALUES('$file_name','$Total_nr_of_Rows', '$Rows_with_errors', '$StartDate', '$EndDate', '$CreationDate')";

global $conn;
$conn->query($sql); //executes the query
}

if ($sql)
    {
        echo 'Data uploaded to database!';
    }
else {
        echo "Error: " . $sql . "<br>" . $conn->error;
}
}

Answer 1

I never run this, hope this works. Explanation is within the code.

<?php

function parse($file) {

    //Initialize some variables here. Probably in some constants etc.
    $dateRegex = '|^\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2}$|'; //Just a rough check. I purposely skip the case of like 9999-99-99 99:99:99. Up to you to refine this.
    $dateFormat = 'Y-m-d H:i:s';

    //In fact checking the file exist or not is probably useful too:
    //if (is_file($file)) ...

    //Opens CSV in read mode
    $file_handle = fopen($file, "r");

    //We don't need to put this in the loop since it's a one time job
    $file_name = basename($file);

    //We also initialize the variable outside the while loop so that we can increment it
    $Rows_with_errors = 0;
    $Total_nr_of_Rows = 0;

    //StartDate of the parsing, shouldn't it be outside of the while loop too?
    //Also, using "date" will gives you the time in the timezone that you set in your php.ini's date.timezone property.
    //Using "gmdate" will gives you the time in GMT time.
    $StartDate = date($dateFormat, time());

    while (($row = fgetcsv($file_handle, 1000, ",")) !== false) {

        ++$Total_nr_of_Rows;

        //So here, we are looping row by row. Do your checking here:
        if (!(is_string($row[0]) &&
            is_numeric($row[1]) &&
            preg_match($dateRegex, $row[2]) &&
            preg_match($dateRegex, $row[3]) &&
            preg_match($dateRegex, $row[4]))) {

                //Increment the error
                ++$Rows_with_errors;
        }
    }

    //That's it. We insert it now
    //Creation date & end date, probably the same, no? we initialize here:
    $CreationDate = $EndDate = date($dateFormat, time());

    //This is bad. There is no escaping, and YOU ARE RISK OF QUERY INJECTION FROM THE $file_name VARIABLE.
    //I won't discuss it here since this is not the scope of the question.
    $sql = "INSERT INTO file (Filename, TotalNumberOfRows, RowsWithErrors, StartDate, EndDate, CreationDate) 
VALUES('$file_name','$Total_nr_of_Rows', '$Rows_with_errors', '$StartDate', '$EndDate', '$CreationDate')";

    global $conn;
    $conn->query($sql); //executes the query

    if ($sql){
        echo 'Data uploaded to database!';
    }else {
        echo "Error: " . $sql . "<br>" . $conn->error;
    }
}