MySQL table doesn't get all values from PHP array

Question

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);           
    }
}
}

Have you tried to [***escape** query parameters*](http://stackoverflow.com/questions/10646142/what-does-it-mean-to-escape-a-string) ? — BlitZ, Jun 30 '15 at 02:04
@Num6 Well, I just learned about that by reading your text. I'm not sure if this would be the issue, but I will try anyway. — Leandro, Jun 30 '15 at 02:13

score 1 · Answer 1 · answered Jun 30 '15 at 03:18

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!

score 0 · Answer 2 · answered Jun 30 '15 at 02:07

0

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.

answered Jun 30 '15 at 02:07

mike.k

3,277
1
12
18

I have found that using the Query log is very helpful also. http://stackoverflow.com/questions/650238/how-to-show-the-last-queries-executed-on-mysql – Arturo Alvarado Jun 30 '15 at 02:19

MySQL table doesn't get all values from PHP array

2 Answers2