I currently have an issue where when I attempt to insert data from a CSV file into a MySQL database using PHP I get an error that is caused by empty fields within the CSV file.

In each row of data that has at least one empty field, it produces an error and doesn't insert the whole row into the database due to the empty field(s).

I was wondering if anybody knows how to find each empty field within the CSV file and populate them before they are inserted ("0" for empty int fields and "N/A" for empty varchar fields) without going through the CSV file and filling in the data manually.

Code for inserting the data into database

foreach($gymarr as $row){
    $day = $row[0];
    $routine= $row[1];
    $time= $row[2];
    $type= $row[3];
    $run= $row[4];
    $weights= $row[5];
    $tally= $row[6];


$sqlinsert = "INSERT INTO Gym (day, routine, time, type, run, weights, tally) 
 VALUES ('$day', '$routine', $time, '$type', '$run', '$weights', tally)";

$result = $conn->query($sqlinsert);

CodePudding user response：

Empty values are valid for sql insertion. The error

"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 ' '50kg')' at line 4"

means that the values themselves are breaking your query due to syntax. If you bind the values then the database will have the query and values separated and your source should no longer be breaking the values. Mysql allows empty ''.

The correct, best and safest way to resolve your problem is with bound parameters. The following function I wrote some years back have simplified this type of work for me and they prevent sql from being contaminated by the values.

functions:

function dbConnect($dbname,$username,$password,$servername = "localhost"){
    if(empty($dbname) || empty($username) || empty($password)) return "Undefined database, username or password.";

    try {
        $conn = new PDO("mysql:host=$servername;charset=utf8;dbname=".$dbname, $username, $password);
        // set the PDO error mode to exception
        $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        $conn->setAttribute(PDO::ATTR_EMULATE_PREPARES,false);
        $conn->setAttribute(PDO::MYSQL_ATTR_LOCAL_INFILE, true);
        return $conn;
    }
    catch(PDOException $e){
        return "Connection failed: " . $e->getMessage();
    }
}

function dbQuery($conn,$sql="", $parameters=array(),$constant = PDO::FETCH_ASSOC){
    try{
        $stmt = $conn->prepare($sql);
    } catch(PDOException $e){
        return $e;
    }
    foreach($parameters as $key=>$value) $stmt->bindValue( $key 1, $value);
    try{
        $stmt->execute(); 
        if ($stmt->columnCount() > 0){
            $result = $stmt->fetchAll($constant); //in cases of select something, return the rows.
        } else {
            $result = $stmt->rowCount();//in case of update/insert/delete statements: get number of rows affected
        }
    } catch(PDOException $e){
        $result = $e->getMessage();
    }
    return $result;
}

The code:

$conn = dbConnect($dbname,$username,$password,$servername);//you have to populate these values yourself

foreach($gymarr as $row){
    $sql = "INSERT INTO Gym (day, routine, time, type, run, weights, tally) 
     VALUES (?, ?, ?, ?, ?, ?, ?)";
    $result = dbQuery($conn, $sql,$row);//row is any flat, two dimensional array. It must have the same number of values as the number of '?' in your sql for each one to bind to and in the order that those columns appear in your query.
}

If you prefer to MySQLi as your database connection then see https://www.w3schools.com/php/php_mysql_prepared_statements.asp.

CodePudding user response：

Maybe this way is useful for you:

$day = $row[0] !== "" ? $row[0] : "n/a";
$routine= $row[1] !== "" ? $row[1] : "n/a";
$time= $row[2] !== "" ? $row[2] : "n/a";
$type= $row[3] !== "" ? $row[3] : "n/a";
$run= $row[4] !== "" ? $row[4] : "n/a";
$weights= $row[5] !== "" ? $row[5] : "n/a";
$tally= $row[6] !== "" ? $row[6] : "n/a";

You can replace "n/a" with "0" if needed.