Replacing empty values in a CSV file using PHP when inserting data into a MySQL database

Solution:

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;
}
Copy code

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.
}
Copy code

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