Upload CSV file with 50000 records and insert data into MySQL
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I need to upload csv and import this data to MySQL table.
public function insertData()
{
while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
$query = "INSERT into deleted_users (email)
values ('" . $column[0] . "'); $query->execute();
}
}
php mysql csv-import
add a comment |
I need to upload csv and import this data to MySQL table.
public function insertData()
{
while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
$query = "INSERT into deleted_users (email)
values ('" . $column[0] . "'); $query->execute();
}
}
php mysql csv-import
Try to update your question and add some information on what you are trying to improve. If you are running into a problem, describe the problem clearly.
– Evert
Nov 25 '18 at 7:03
1
The prepare() should be outside the loop (prepare once, execute multiple...) - and it should use placeholders, as your current code is susceptible fro SQL injections.
– Lars Stegelitz
Nov 25 '18 at 8:57
Is the file local or on the MySQL server?
– Tim Biegeleisen
Nov 25 '18 at 23:34
@Tim Biegeleisen inside my project directory
– trazy97
Nov 27 '18 at 16:42
add a comment |
I need to upload csv and import this data to MySQL table.
public function insertData()
{
while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
$query = "INSERT into deleted_users (email)
values ('" . $column[0] . "'); $query->execute();
}
}
php mysql csv-import
I need to upload csv and import this data to MySQL table.
public function insertData()
{
while (($column = fgetcsv($file, 10000, ",")) !== FALSE) {
$query = "INSERT into deleted_users (email)
values ('" . $column[0] . "'); $query->execute();
}
}
php mysql csv-import
php mysql csv-import
edited Nov 25 '18 at 18:03
trazy97
asked Nov 25 '18 at 6:49
trazy97trazy97
185
185
Try to update your question and add some information on what you are trying to improve. If you are running into a problem, describe the problem clearly.
– Evert
Nov 25 '18 at 7:03
1
The prepare() should be outside the loop (prepare once, execute multiple...) - and it should use placeholders, as your current code is susceptible fro SQL injections.
– Lars Stegelitz
Nov 25 '18 at 8:57
Is the file local or on the MySQL server?
– Tim Biegeleisen
Nov 25 '18 at 23:34
@Tim Biegeleisen inside my project directory
– trazy97
Nov 27 '18 at 16:42
add a comment |
Try to update your question and add some information on what you are trying to improve. If you are running into a problem, describe the problem clearly.
– Evert
Nov 25 '18 at 7:03
1
The prepare() should be outside the loop (prepare once, execute multiple...) - and it should use placeholders, as your current code is susceptible fro SQL injections.
– Lars Stegelitz
Nov 25 '18 at 8:57
Is the file local or on the MySQL server?
– Tim Biegeleisen
Nov 25 '18 at 23:34
@Tim Biegeleisen inside my project directory
– trazy97
Nov 27 '18 at 16:42
Try to update your question and add some information on what you are trying to improve. If you are running into a problem, describe the problem clearly.
– Evert
Nov 25 '18 at 7:03
Try to update your question and add some information on what you are trying to improve. If you are running into a problem, describe the problem clearly.
– Evert
Nov 25 '18 at 7:03
1
1
The prepare() should be outside the loop (prepare once, execute multiple...) - and it should use placeholders, as your current code is susceptible fro SQL injections.
– Lars Stegelitz
Nov 25 '18 at 8:57
The prepare() should be outside the loop (prepare once, execute multiple...) - and it should use placeholders, as your current code is susceptible fro SQL injections.
– Lars Stegelitz
Nov 25 '18 at 8:57
Is the file local or on the MySQL server?
– Tim Biegeleisen
Nov 25 '18 at 23:34
Is the file local or on the MySQL server?
– Tim Biegeleisen
Nov 25 '18 at 23:34
@Tim Biegeleisen inside my project directory
– trazy97
Nov 27 '18 at 16:42
@Tim Biegeleisen inside my project directory
– trazy97
Nov 27 '18 at 16:42
add a comment |
1 Answer
1
active
oldest
votes
Perhaps the fastest way to bulk load data from PHP into MySQL is to use the LOAD DATA
tool, something like this:
$sql = "LOAD DATA LOCAL INFILE 'path/to/yourfile.csv'
INTO TABLE deleted_users
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
(@col1)
SET email = @col1;";
mysqli_query($conn, $sql);
This answer loads records with only the email
field being assigned. If you want to assign a value for the id
and type
columns, then you will need a CSV file containing these values.
table has id, email, type columns, type is static value and its not come with csv
– trazy97
Nov 25 '18 at 7:01
im getting file path errors, im taking the file uploaded from front end do I need to store it somewhere, isn't it possible to take tmp file $_FILES["file"]["tmp_name"] ?
– trazy97
Nov 25 '18 at 15:21
@trazy97 Try usingLOAD DATA LOCAL INFILE
in this case.
– Tim Biegeleisen
Nov 27 '18 at 23:47
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53465313%2fupload-csv-file-with-50000-records-and-insert-data-into-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Perhaps the fastest way to bulk load data from PHP into MySQL is to use the LOAD DATA
tool, something like this:
$sql = "LOAD DATA LOCAL INFILE 'path/to/yourfile.csv'
INTO TABLE deleted_users
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
(@col1)
SET email = @col1;";
mysqli_query($conn, $sql);
This answer loads records with only the email
field being assigned. If you want to assign a value for the id
and type
columns, then you will need a CSV file containing these values.
table has id, email, type columns, type is static value and its not come with csv
– trazy97
Nov 25 '18 at 7:01
im getting file path errors, im taking the file uploaded from front end do I need to store it somewhere, isn't it possible to take tmp file $_FILES["file"]["tmp_name"] ?
– trazy97
Nov 25 '18 at 15:21
@trazy97 Try usingLOAD DATA LOCAL INFILE
in this case.
– Tim Biegeleisen
Nov 27 '18 at 23:47
add a comment |
Perhaps the fastest way to bulk load data from PHP into MySQL is to use the LOAD DATA
tool, something like this:
$sql = "LOAD DATA LOCAL INFILE 'path/to/yourfile.csv'
INTO TABLE deleted_users
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
(@col1)
SET email = @col1;";
mysqli_query($conn, $sql);
This answer loads records with only the email
field being assigned. If you want to assign a value for the id
and type
columns, then you will need a CSV file containing these values.
table has id, email, type columns, type is static value and its not come with csv
– trazy97
Nov 25 '18 at 7:01
im getting file path errors, im taking the file uploaded from front end do I need to store it somewhere, isn't it possible to take tmp file $_FILES["file"]["tmp_name"] ?
– trazy97
Nov 25 '18 at 15:21
@trazy97 Try usingLOAD DATA LOCAL INFILE
in this case.
– Tim Biegeleisen
Nov 27 '18 at 23:47
add a comment |
Perhaps the fastest way to bulk load data from PHP into MySQL is to use the LOAD DATA
tool, something like this:
$sql = "LOAD DATA LOCAL INFILE 'path/to/yourfile.csv'
INTO TABLE deleted_users
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
(@col1)
SET email = @col1;";
mysqli_query($conn, $sql);
This answer loads records with only the email
field being assigned. If you want to assign a value for the id
and type
columns, then you will need a CSV file containing these values.
Perhaps the fastest way to bulk load data from PHP into MySQL is to use the LOAD DATA
tool, something like this:
$sql = "LOAD DATA LOCAL INFILE 'path/to/yourfile.csv'
INTO TABLE deleted_users
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY 'rn'
(@col1)
SET email = @col1;";
mysqli_query($conn, $sql);
This answer loads records with only the email
field being assigned. If you want to assign a value for the id
and type
columns, then you will need a CSV file containing these values.
edited Nov 27 '18 at 23:47
answered Nov 25 '18 at 6:57
Tim BiegeleisenTim Biegeleisen
241k13101161
241k13101161
table has id, email, type columns, type is static value and its not come with csv
– trazy97
Nov 25 '18 at 7:01
im getting file path errors, im taking the file uploaded from front end do I need to store it somewhere, isn't it possible to take tmp file $_FILES["file"]["tmp_name"] ?
– trazy97
Nov 25 '18 at 15:21
@trazy97 Try usingLOAD DATA LOCAL INFILE
in this case.
– Tim Biegeleisen
Nov 27 '18 at 23:47
add a comment |
table has id, email, type columns, type is static value and its not come with csv
– trazy97
Nov 25 '18 at 7:01
im getting file path errors, im taking the file uploaded from front end do I need to store it somewhere, isn't it possible to take tmp file $_FILES["file"]["tmp_name"] ?
– trazy97
Nov 25 '18 at 15:21
@trazy97 Try usingLOAD DATA LOCAL INFILE
in this case.
– Tim Biegeleisen
Nov 27 '18 at 23:47
table has id, email, type columns, type is static value and its not come with csv
– trazy97
Nov 25 '18 at 7:01
table has id, email, type columns, type is static value and its not come with csv
– trazy97
Nov 25 '18 at 7:01
im getting file path errors, im taking the file uploaded from front end do I need to store it somewhere, isn't it possible to take tmp file $_FILES["file"]["tmp_name"] ?
– trazy97
Nov 25 '18 at 15:21
im getting file path errors, im taking the file uploaded from front end do I need to store it somewhere, isn't it possible to take tmp file $_FILES["file"]["tmp_name"] ?
– trazy97
Nov 25 '18 at 15:21
@trazy97 Try using
LOAD DATA LOCAL INFILE
in this case.– Tim Biegeleisen
Nov 27 '18 at 23:47
@trazy97 Try using
LOAD DATA LOCAL INFILE
in this case.– Tim Biegeleisen
Nov 27 '18 at 23:47
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53465313%2fupload-csv-file-with-50000-records-and-insert-data-into-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Try to update your question and add some information on what you are trying to improve. If you are running into a problem, describe the problem clearly.
– Evert
Nov 25 '18 at 7:03
1
The prepare() should be outside the loop (prepare once, execute multiple...) - and it should use placeholders, as your current code is susceptible fro SQL injections.
– Lars Stegelitz
Nov 25 '18 at 8:57
Is the file local or on the MySQL server?
– Tim Biegeleisen
Nov 25 '18 at 23:34
@Tim Biegeleisen inside my project directory
– trazy97
Nov 27 '18 at 16:42