PHP MySQL Data Transfer Optimization
Budget: $30 – $250 USD
I am experiencing a 503 server status error when trying to move data from a large Excel file (between 5MB and 10MB) to my MySQL database using PHPspreadsheet on my PHP website hosted on a shared Linux server with GoDaddy. Smaller files work fine.
- I have checked the server's error logs but they did not provide any specific error messages or details.
- I'm looking for a solution: either a setting change that needs to occur, or a less server-intensive way to handle this.
Here's the code currently being used to move the data:
$inputFileName = "uploads/" . $file['wholesale_price_books_name'];
$objPHPExcel = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
$objPHPExcel->setActiveSheetIndexByName('PRICE BOOK');
$wholesale_price_books = $objPHPExcel->getActiveSheet()->toArray();
mysqli_query($conn, "DROP TABLE IF EXISTS wholesale_price_books2_" . $_SESSION['id']);
$sql = "CREATE TABLE wholesale_price_books2_" . $_SESSION['id'] . " (
id INT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY,";
$end_key = '';
$row_query = '';
$table_col = array();
foreach ($wholesale_price_books[0] as $key => $row) {
if ($row == Null) {
break;
}
array_push($table_col, '`' . $row . '`');
$sql .= '`' . $row . '`' . " TEXT(255) NOT NULL,";
$end_key = $key;
}
foreach ($wholesale_price_books as $key => $row) {
$tmp_row = array_slice($row, 0, ($end_key + 1));
array_walk($tmp_row, function (&$a) use ($conn) {
$a = mysqli_real_escape_string($conn, $a);
});
$row_query .= "('" . implode("','", $tmp_row) . "'),";
}
$sql = substr($sql, 0, -1) . " ) ";
$insert_query = "INSERT INTO wholesale_price_books2_" . $_SESSION['id'] . " (" . implode(',', $table_col) . ") VALUES " . substr($row_query, 0, -1);
mysqli_query($conn, $sql);
mysqli_query($conn, $insert_query);
The ideal freelancer for this project should have:
- Extensive experience with PHP and MySQL
- In-depth knowledge of PHPspreadsheet
- Excellent problem-solving skills
- Familiarity with server management and error log analysis
- Experience with handling large data transfers
Please provide your proposal if you believe you can resolve this issue efficiently.
- I have checked the server's error logs but they did not provide any specific error messages or details.
- I'm looking for a solution: either a setting change that needs to occur, or a less server-intensive way to handle this.
Here's the code currently being used to move the data:
$inputFileName = "uploads/" . $file['wholesale_price_books_name'];
$objPHPExcel = \PhpOffice\PhpSpreadsheet\IOFactory::load($inputFileName);
$objPHPExcel->setActiveSheetIndexByName('PRICE BOOK');
$wholesale_price_books = $objPHPExcel->getActiveSheet()->toArray();
mysqli_query($conn, "DROP TABLE IF EXISTS wholesale_price_books2_" . $_SESSION['id']);
$sql = "CREATE TABLE wholesale_price_books2_" . $_SESSION['id'] . " (
id INT(255) UNSIGNED AUTO_INCREMENT PRIMARY KEY,";
$end_key = '';
$row_query = '';
$table_col = array();
foreach ($wholesale_price_books[0] as $key => $row) {
if ($row == Null) {
break;
}
array_push($table_col, '`' . $row . '`');
$sql .= '`' . $row . '`' . " TEXT(255) NOT NULL,";
$end_key = $key;
}
foreach ($wholesale_price_books as $key => $row) {
$tmp_row = array_slice($row, 0, ($end_key + 1));
array_walk($tmp_row, function (&$a) use ($conn) {
$a = mysqli_real_escape_string($conn, $a);
});
$row_query .= "('" . implode("','", $tmp_row) . "'),";
}
$sql = substr($sql, 0, -1) . " ) ";
$insert_query = "INSERT INTO wholesale_price_books2_" . $_SESSION['id'] . " (" . implode(',', $table_col) . ") VALUES " . substr($row_query, 0, -1);
mysqli_query($conn, $sql);
mysqli_query($conn, $insert_query);
The ideal freelancer for this project should have:
- Extensive experience with PHP and MySQL
- In-depth knowledge of PHPspreadsheet
- Excellent problem-solving skills
- Familiarity with server management and error log analysis
- Experience with handling large data transfers
Please provide your proposal if you believe you can resolve this issue efficiently.