TRIM not working in my PHP Code while uploading CSV

[akshayhomkar]

Hello

 

I am Importing CSV file using PHP but it imports with blank lines and white spaces 

 

please guide how to recover this 

 

 

i am attaching both files for reference

 

awaiting your valuable reply

dayseatchart.php

[Psycho]

i am attaching both files for reference

 

I only see the PHP script. Where's the CSV file? Also, please explain what is it about the results of the execution that are not what you want to occur. In other words, where are you seeing that there are white-space characters not getting trimmed?

 

EDIT: If your only problem is that there are blank lines, then that is not a problem with trim(). That means you have lines in the import file that do not have data. Or at least not in the format you expect. With any process where you are receiving data from a user, you should never ASSUME the data will be valid and in the format you expect. You need to add validation logic to verify. And, FYI: your script is wide open to SQL Injection

Edited November 7, 2014 by Psycho

[akshayhomkar]

CSV file not uploading it says not allowed to upload

 

while fetching the table after uploading it shows me the special character where the white space and it was not removing the blank lines in the CSV file also

 

Not only problem with blank lines I have problem also with space before or after any field

 

while uploading CSV  file its shows me 

• report.csv

  You aren't permitted to upload this kind of file

 

Edited November 7, 2014 by akshayhomkar

[Psycho]

1. As I was saying, if there are lines in your import file that do not have data or are not in the format you expect, then trim() will do nothing to resolve that. Your code, right now, inserts a new record in the DB for every line in the import file regardless of what is contained on that line.

 

2. You state you are getting "special character" where the white space is. If you "see" a special character, it is not a white-space character.

 

As I stated you need to add validation logic to your code to ensure the data is what you expect. For example, I would expect that you would want to ensure there are at least 9 values on each line. But, maybe you want to reject the line if there are more than nine as well. Second, what "types" of values do you expect for each field? Do some need to be numeric values (int or float), do some need to be dates, or what? You would want to validate each field based on what field type it needs to be. Lastly, are any fields "required"? If so, then you should reject records where any required fields are empty. Or perhaps you want to insert NULL when there is an empty field. Now that I think of it, trying to insert data that is not appropriate for the field type could cause some odd characters in the DB. I'm not sure as I always check my data before inserting.

 

Basically, the code you have is woefully inadequate. But, since I don't know the context of the data you are inserting I don't know what logic you need. Plus, without an example file I can't be sure of what the problem really is.

 

But, what I would probably do is create a function that I pass each line to. The function will process the line of data and ensure it is valid for insertion into the DB. If it is valid, it returns the data back to the process to do the insertion. Otherwise it returns false. There are several other problems with your code that I'm not going to address, but here is a quick example of a possible solution. The function would be modified based upon all the necessary business rules.

 

Here is a rough example. This is not tested, so there may be some minor syntax or other issues to resolve.

<?php
session_start();
if (!array_key_exists("user", $_SESSION)) {
    header('Location: index.php');
    exit;
}
 
$sDbHost = 'localhost';
$sDbName = 'examcenter';
$sDbUser = 'examcenter';
$sDbPwd  = 'examcenter';
 
$dbConn = new mysqli ($sDbHost, $sDbUser, $sDbPwd,$sDbName);
if ($dbConn->connect_error) {
    die('Connect Error (' .$dbConn->connect_errno . ') '
            . $dbConn->connect_error);
}
 
function validRecord($data)
{
    //Check if line has 9 values
    if(count($data) != 9) { return false; }
 
    ##Verify each piece of data based on the field type
    ##These are just examples, you need to do it based on your actual needs
    //Verify date and time
    $timestamp = strtotime($data[0] . ' ' . $data[1]);
    if(!$timestamp) { return false; }
    else
    {
        $date = date('Y-m-d', $timestamp);
        $time = date('h:i:s', $timestamp);
    };
    //Verify subcode is not empty
    $subcode = trim($data[2]);
    if(empty($subcode)) { return false; }
    //Verify subname is not empty
    $subname = trim($data[3]);
    if(empty($subname)) { return false; }
    //Verify seatno is integer
    $seatno = intval($data[4]);
    if(!$seatno) { return false; }
    //Verify center is not empty
    $center = trim($data[5]);
    if(empty($center)) { return false; }
    //Verify center is not empty
    $coucode = trim($data[6]);
    if(empty($coucode)) { return false; }
    //Verify center is not empty
    $yearcode = trim($data[7]);
    if(empty($yearcode)) { return false; }
    //Verify center is not empty
    $mastercode = trim($data[8]);
    if(empty($mastercode)) { return false; }
 
    //All data is valid return result
    return array(
        'date'       => $date,
        'time'       => $time,
        'subcode'    => $subcode,
        'subname'    => $subname,
        'seatno'     => $seatno,
        'center'     => $center,
        'coucode'    => $coucode,
        'yearcode'   => $yearcode,
        'mastercode' => $mastercode,
        '' => $date,
    )
}
 
if(isset($_POST["Import"]))
{
    include 'config.php';
 
    $filename = $_FILES["file"]["tmp_name"];
    $ext = substr($filename, strrpos($filename,"."), (strlen($filename)-strrpos($filename,".")));
 
    if(!$_FILES["file"]["size"] > 0)
    {
 
        $file = fopen($filename, "r");
        while (($row = fgetcsv($file, 100000000, ",")) !== FALSE)
        {
            $emapData = validRecord($row);
            if(!$emapData)
            {
                //Line is invalid, add error condition if you with
            }
            else
            {
                //Data is valid - insert it
                //This should be updated to use a prepared statement
                $sql = "INSERT into report
                            (date, time, subcode, subname, seatno, center, coucode, yearcode, mastercode)
                        VALUES
                            ('{$emapData['date']}', '{$emapData['time']}', '{$emapData['subcode']}', '{$emapData['subname']}',
                             {$emapData['seatno'], '{$emapData['center']}', '{$emapData['coucode']}',
                             '{$emapData['yearcode']}', '{$emapData['mastercode']}')";
                mysqli_query($dbConn, $sql);
            }
        }
        fclose($file);
        echo "<script>alert('CSV File has been successfully Imported')</script>";
    }
    else
    {
        echo "Invalid File:Please Upload CSV File";
    }
}
 
if(isset($_POST["Delete"]))
{
    include 'config.php';
    mysqli_query($dbConn,"DELETE FROM exseatchart") or trigger_error(mysqli_error($dbConn),E_USER_ERROR);
    mysqli_query($dbConn,"UPDATE block SET status= 0 ") or trigger_error(mysqli_error($dbConn),E_USER_ERROR);
    mysqli_query($dbConn,"UPDATE supportstaff SET status=0, blockname='0' ")
        or trigger_error(mysqli_error($dbConn),E_USER_ERROR);
    echo "<script>alert('Record Deleted Successfully')</script>";
}
?>
 

FYI: You should consider creating a single field int he DB to hold the Date & Time rather than two separate fields.

Edited November 7, 2014 by Psycho

[akshayhomkar]

Thank you very much works like charm