CODEWITHSUNDEEP
phpmysqlarrays
Leandro
Leandro

Reputation: 377

MySQL table doesn't get all values from PHP array

I am reading the lines from a CSV file and storing in a PHP array. I created a function to save the array content into a MySQL table. However, it only saves around 210 rows while the array has more than 100K positions... I checked the array size and it shows much more than what is being saved into the database. Is there any limit per operation that MySQL accepts?

Also, MySQL doesn't show any error. It only loads those few records and that's it.

My function:

    function save_uar($data)
{       
    if (isset($data))
    {               
        foreach($data as $row)
        {
            $id = $row['_id'];
            $period = $row['period'];
            $reviewStatus = $row['reviewStatus'];
            $dataSource = $row['dataSource'];
            $dataId = $row['dataId'];
            $offset = $row['offset'];
            $employeeId = $row['employeeId'];
            $firstName = $row['firstName'];
            $lastName = $row['lastName'];
            $resourceName = $row['resourceName'];
            $resourceType = $row['resourceType'];
            $resourceUserId = $row['resourceUserId'];
            $role = $row['role'];
            $reference = $row['reference'];
            $resourceGroup = $row['resourceGroup'];
            $businessUnit = $row['businessUnit'];
            $lifeCycle = $row['lifeCycle'];
            $userComment = $row['userComment'];
            $extractDate = $row['extractDate'];
            $reason = $row['reason'];
            $reviewDate = $row['reviewDate'];
            $reviewerId = $row['reviewerId']
            $sql = "INSERT INTO uar VALUES (
            '".$id."', '
            ".$period."', '
            ".$reviewStatus."', '
            ".$dataSource."', '
            ".$dataId."',
            ".$offset.", 
            ".$employeeId.", '
            ".$firstName."', '
            ".$lastName."', '
            ".$resourceName."', '
            ".$resourceType."', '
            ".$resourceUserId."', '
            ".$role."', '
            ".$reference."', '
            ".$resourceGroup."', '
            ".$businessUnit."', '
            ".$lifeCycle."', '
            ".$userComment."', '
            ".$extractDate."', '
            ".$reason."', '
            ".$reviewDate."', '
            ".$reviewerId."'
            ); ";
            $result = $this->db->query($sql);           
    }
}
}

Upvotes: 1

Views: 72

Answers (2)

Leandro
Leandro

Reputation: 377

As it was suggest by @Num6, after escaping the variables the code worked. I used a CodeIgniter function:

$sql = "INSERT INTO uar VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)"; 

            $result = $this->db->query($sql, array($id, $period ,$reviewStatus, $dataSource, $dataId, $offset, $employeeId,
            $firstName, $lastName, $resourceName, $resourceType, $resourceUserId, $role,
            $reference, $resourceGroup, $businessUnit, $lifeCycle, $userComment, $extractDate,
            $reason, $reviewDate, $reviewerId));

I confirmed the issue when I tried to run the function directly from the browser (I was following the jquery result before). Now, I am having issues with the time spent to insert the execute the queries, but this is another issue not direct related tho this topic.

Thank you everyone!

Upvotes: 1

mike.k
mike.k

Reputation: 3437

Perhaps one of the queries makes it stop because it supplies invalid data for its column?

Are error and warning messages turned on? You can do this at the top of your PHP:

error_reporting(E_ALL);
ini_set('display_errors', 1);

And make it stop on a bad query to catch it:

if ($result === false) exit(0);

You should consider reading one csv row at a time, and passing that to a prepared statement instead of filling an array with 100k arrays.

Upvotes: 0

Related Questions