Inserting data into a MySQL table with PHP when the values being inserted do not match the number of columns...
0
I have a mysql table with over 100 columns, but I've shortened it to 6 for this example:
col0(autoincrement), col1, col2, col3, col4, col5, col6
I'm getting data from external sources ($csvcontent below), and that data will have 6 or less values per line. I'd like to use PHP to read these values into an array, and insert the array values into into my mysql table.
$csvcontent = file from external source
$fieldseparator = ",";
$lineseparator = "n";
$linearray = array();
foreach(explode($lineseparator,$csvcontent) as $line) {
$linearray = explode($fieldseparator,$line);
$linemysql = implode("','",$linearray);
$query = "insert into MYTABLE values('','$linemysql');";
}
How can I insert the following rows, assuming that I don't know ahead of time how many values are stored inside of $csvcontent? This existing code works well when I always have 6 values, but not when I have fewer.
insert into MYTABLE values('','1','2','3','4','5','6'); //works
insert into MYTABLE values('','1','2','3','4'); //doesn't work
insert into MYTABLE values('','1','2','3','4','5'); //doesn't work
php mysql
asked Nov 20 '18 at 18:26
GrumbleSharkGrumbleShark
1
|
show 5 more comments
0
I have a mysql table with over 100 columns, but I've shortened it to 6 for this example:
col0(autoincrement), col1, col2, col3, col4, col5, col6
I'm getting data from external sources ($csvcontent below), and that data will have 6 or less values per line. I'd like to use PHP to read these values into an array, and insert the array values into into my mysql table.
$csvcontent = file from external source
$fieldseparator = ",";
$lineseparator = "n";
$linearray = array();
foreach(explode($lineseparator,$csvcontent) as $line) {
$linearray = explode($fieldseparator,$line);
$linemysql = implode("','",$linearray);
$query = "insert into MYTABLE values('','$linemysql');";
}
How can I insert the following rows, assuming that I don't know ahead of time how many values are stored inside of $csvcontent? This existing code works well when I always have 6 values, but not when I have fewer.
insert into MYTABLE values('','1','2','3','4','5','6'); //works
insert into MYTABLE values('','1','2','3','4'); //doesn't work
insert into MYTABLE values('','1','2','3','4','5'); //doesn't work
php mysql
asked Nov 20 '18 at 18:26
GrumbleSharkGrumbleShark
1
INSERT INTO mytable SET col1=1, col2=2...
– miken32
Nov 20 '18 at 18:28
1
You should be using prepared statements though; very simple to prepare and execute queries like this using PDO.
– miken32
Nov 20 '18 at 18:29
1
Mention columns you want to insert into. Likeinsert into MYTABLE(column1,column2) values('1','2')
– vivek_23
Nov 20 '18 at 18:31
@vivek_23 My problem is that I don't know ahead of time how many columns there will be. I could count the number if items in $linearray and try to display that number of columns, but I was hoping for something cleaner.
– GrumbleShark
Nov 20 '18 at 18:34
1
My problem is that I don't know ahead of time how many columns there will be ... is not an ideal database design or usage. One should never leave DDL and DML calls open-ended for application to manipulate schema or data on the fly. Relational DBs are planned systems and queries should be explicit regarding identifiers like tables and columns.
– Parfait
Nov 20 '18 at 18:57
|
show 5 more comments
0
0
0
1
I have a mysql table with over 100 columns, but I've shortened it to 6 for this example:
col0(autoincrement), col1, col2, col3, col4, col5, col6
I'm getting data from external sources ($csvcontent below), and that data will have 6 or less values per line. I'd like to use PHP to read these values into an array, and insert the array values into into my mysql table.
$csvcontent = file from external source
$fieldseparator = ",";
$lineseparator = "n";
$linearray = array();
foreach(explode($lineseparator,$csvcontent) as $line) {
$linearray = explode($fieldseparator,$line);
$linemysql = implode("','",$linearray);
$query = "insert into MYTABLE values('','$linemysql');";
}
How can I insert the following rows, assuming that I don't know ahead of time how many values are stored inside of $csvcontent? This existing code works well when I always have 6 values, but not when I have fewer.
insert into MYTABLE values('','1','2','3','4','5','6'); //works
insert into MYTABLE values('','1','2','3','4'); //doesn't work
insert into MYTABLE values('','1','2','3','4','5'); //doesn't work
php mysql
asked Nov 20 '18 at 18:26
GrumbleSharkGrumbleShark
1
I have a mysql table with over 100 columns, but I've shortened it to 6 for this example:
col0(autoincrement), col1, col2, col3, col4, col5, col6
I'm getting data from external sources ($csvcontent below), and that data will have 6 or less values per line. I'd like to use PHP to read these values into an array, and insert the array values into into my mysql table.
$csvcontent = file from external source
$fieldseparator = ",";
$lineseparator = "n";
$linearray = array();
foreach(explode($lineseparator,$csvcontent) as $line) {
$linearray = explode($fieldseparator,$line);
$linemysql = implode("','",$linearray);
$query = "insert into MYTABLE values('','$linemysql');";
}
How can I insert the following rows, assuming that I don't know ahead of time how many values are stored inside of $csvcontent? This existing code works well when I always have 6 values, but not when I have fewer.
insert into MYTABLE values('','1','2','3','4','5','6'); //works
insert into MYTABLE values('','1','2','3','4'); //doesn't work
insert into MYTABLE values('','1','2','3','4','5'); //doesn't work
php mysql
php mysql
asked Nov 20 '18 at 18:26
GrumbleSharkGrumbleShark
1
asked Nov 20 '18 at 18:26
GrumbleSharkGrumbleShark
1
asked Nov 20 '18 at 18:26
GrumbleSharkGrumbleShark
1
asked Nov 20 '18 at 18:26
GrumbleSharkGrumbleShark
1
asked Nov 20 '18 at 18:26
GrumbleSharkGrumbleShark
1
1
INSERT INTO mytable SET col1=1, col2=2...
– miken32
Nov 20 '18 at 18:28
1
You should be using prepared statements though; very simple to prepare and execute queries like this using PDO.
– miken32
Nov 20 '18 at 18:29
1
Mention columns you want to insert into. Likeinsert into MYTABLE(column1,column2) values('1','2')
– vivek_23
Nov 20 '18 at 18:31
@vivek_23 My problem is that I don't know ahead of time how many columns there will be. I could count the number if items in $linearray and try to display that number of columns, but I was hoping for something cleaner.
– GrumbleShark
Nov 20 '18 at 18:34
1
My problem is that I don't know ahead of time how many columns there will be ... is not an ideal database design or usage. One should never leave DDL and DML calls open-ended for application to manipulate schema or data on the fly. Relational DBs are planned systems and queries should be explicit regarding identifiers like tables and columns.
– Parfait
Nov 20 '18 at 18:57
|
show 5 more comments
INSERT INTO mytable SET col1=1, col2=2...
– miken32
Nov 20 '18 at 18:28
1
You should be using prepared statements though; very simple to prepare and execute queries like this using PDO.
– miken32
Nov 20 '18 at 18:29
1
Mention columns you want to insert into. Likeinsert into MYTABLE(column1,column2) values('1','2')
– vivek_23
Nov 20 '18 at 18:31
@vivek_23 My problem is that I don't know ahead of time how many columns there will be. I could count the number if items in $linearray and try to display that number of columns, but I was hoping for something cleaner.
– GrumbleShark
Nov 20 '18 at 18:34
1
My problem is that I don't know ahead of time how many columns there will be ... is not an ideal database design or usage. One should never leave DDL and DML calls open-ended for application to manipulate schema or data on the fly. Relational DBs are planned systems and queries should be explicit regarding identifiers like tables and columns.
– Parfait
Nov 20 '18 at 18:57
INSERT INTO mytable SET col1=1, col2=2...– miken32
Nov 20 '18 at 18:28
INSERT INTO mytable SET col1=1, col2=2...– miken32
Nov 20 '18 at 18:28
1
1
You should be using prepared statements though; very simple to prepare and execute queries like this using PDO.
– miken32
Nov 20 '18 at 18:29
You should be using prepared statements though; very simple to prepare and execute queries like this using PDO.
– miken32
Nov 20 '18 at 18:29
1
1
Mention columns you want to insert into. Like
insert into MYTABLE(column1,column2) values('1','2')– vivek_23
Nov 20 '18 at 18:31
Mention columns you want to insert into. Like
insert into MYTABLE(column1,column2) values('1','2')– vivek_23
Nov 20 '18 at 18:31
@vivek_23 My problem is that I don't know ahead of time how many columns there will be. I could count the number if items in $linearray and try to display that number of columns, but I was hoping for something cleaner.
– GrumbleShark
Nov 20 '18 at 18:34
@vivek_23 My problem is that I don't know ahead of time how many columns there will be. I could count the number if items in $linearray and try to display that number of columns, but I was hoping for something cleaner.
– GrumbleShark
Nov 20 '18 at 18:34
1
1
My problem is that I don't know ahead of time how many columns there will be ... is not an ideal database design or usage. One should never leave DDL and DML calls open-ended for application to manipulate schema or data on the fly. Relational DBs are planned systems and queries should be explicit regarding identifiers like tables and columns.
– Parfait
Nov 20 '18 at 18:57
My problem is that I don't know ahead of time how many columns there will be ... is not an ideal database design or usage. One should never leave DDL and DML calls open-ended for application to manipulate schema or data on the fly. Relational DBs are planned systems and queries should be explicit regarding identifiers like tables and columns.
– Parfait
Nov 20 '18 at 18:57
|
show 5 more comments
1 Answer
1
active
oldest
votes
0
Well, based on the size of $linearray, you can create a column sequence and attach that to your insert query.
<?php
$columns = ['col1','col2','col3','col4','col5','col6'];
$linearray_samples = [
[1,2,3,4,5],
[1,2,3],
[1,2,3,4],
[1],
[1,2],
[1,2,3,4,5,6]
];
foreach($linearray_samples as $each_sample){
echo "(",commaSeparatedColumns($each_sample,$columns),")",PHP_EOL;
}
function commaSeparatedColumns($sample,$columns){
return implode(",",array_slice($columns,0,count($sample)));
}
The code outputs:
(col1,col2,col3,col4,col5)
(col1,col2,col3)
(col1,col2,col3,col4)
(col1)
(col1,col2)
(col1,col2,col3,col4,col5,col6)
Demo: https://3v4l.org/QLnFo
answered Nov 20 '18 at 18:44
vivek_23vivek_23
2,6722618
1
Thank you for your answer, I'm currently playing around with it.
– GrumbleShark
Nov 21 '18 at 16:34
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
});
}
});
draft saved
draft discarded
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%2f53399260%2finserting-data-into-a-mysql-table-with-php-when-the-values-being-inserted-do-not%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
0
Well, based on the size of $linearray, you can create a column sequence and attach that to your insert query.
<?php
$columns = ['col1','col2','col3','col4','col5','col6'];
$linearray_samples = [
[1,2,3,4,5],
[1,2,3],
[1,2,3,4],
[1],
[1,2],
[1,2,3,4,5,6]
];
foreach($linearray_samples as $each_sample){
echo "(",commaSeparatedColumns($each_sample,$columns),")",PHP_EOL;
}
function commaSeparatedColumns($sample,$columns){
return implode(",",array_slice($columns,0,count($sample)));
}
The code outputs:
(col1,col2,col3,col4,col5)
(col1,col2,col3)
(col1,col2,col3,col4)
(col1)
(col1,col2)
(col1,col2,col3,col4,col5,col6)
Demo: https://3v4l.org/QLnFo
answered Nov 20 '18 at 18:44
vivek_23vivek_23
2,6722618
1
Thank you for your answer, I'm currently playing around with it.
– GrumbleShark
Nov 21 '18 at 16:34
add a comment |
0
Well, based on the size of $linearray, you can create a column sequence and attach that to your insert query.
<?php
$columns = ['col1','col2','col3','col4','col5','col6'];
$linearray_samples = [
[1,2,3,4,5],
[1,2,3],
[1,2,3,4],
[1],
[1,2],
[1,2,3,4,5,6]
];
foreach($linearray_samples as $each_sample){
echo "(",commaSeparatedColumns($each_sample,$columns),")",PHP_EOL;
}
function commaSeparatedColumns($sample,$columns){
return implode(",",array_slice($columns,0,count($sample)));
}
The code outputs:
(col1,col2,col3,col4,col5)
(col1,col2,col3)
(col1,col2,col3,col4)
(col1)
(col1,col2)
(col1,col2,col3,col4,col5,col6)
Demo: https://3v4l.org/QLnFo
answered Nov 20 '18 at 18:44
vivek_23vivek_23
2,6722618
1
Thank you for your answer, I'm currently playing around with it.
– GrumbleShark
Nov 21 '18 at 16:34
add a comment |
0
0
0
Well, based on the size of $linearray, you can create a column sequence and attach that to your insert query.
<?php
$columns = ['col1','col2','col3','col4','col5','col6'];
$linearray_samples = [
[1,2,3,4,5],
[1,2,3],
[1,2,3,4],
[1],
[1,2],
[1,2,3,4,5,6]
];
foreach($linearray_samples as $each_sample){
echo "(",commaSeparatedColumns($each_sample,$columns),")",PHP_EOL;
}
function commaSeparatedColumns($sample,$columns){
return implode(",",array_slice($columns,0,count($sample)));
}
The code outputs:
(col1,col2,col3,col4,col5)
(col1,col2,col3)
(col1,col2,col3,col4)
(col1)
(col1,col2)
(col1,col2,col3,col4,col5,col6)
Demo: https://3v4l.org/QLnFo
answered Nov 20 '18 at 18:44
vivek_23vivek_23
2,6722618
Well, based on the size of $linearray, you can create a column sequence and attach that to your insert query.
<?php
$columns = ['col1','col2','col3','col4','col5','col6'];
$linearray_samples = [
[1,2,3,4,5],
[1,2,3],
[1,2,3,4],
[1],
[1,2],
[1,2,3,4,5,6]
];
foreach($linearray_samples as $each_sample){
echo "(",commaSeparatedColumns($each_sample,$columns),")",PHP_EOL;
}
function commaSeparatedColumns($sample,$columns){
return implode(",",array_slice($columns,0,count($sample)));
}
The code outputs:
(col1,col2,col3,col4,col5)
(col1,col2,col3)
(col1,col2,col3,col4)
(col1)
(col1,col2)
(col1,col2,col3,col4,col5,col6)
Demo: https://3v4l.org/QLnFo
answered Nov 20 '18 at 18:44
vivek_23vivek_23
2,6722618
answered Nov 20 '18 at 18:44
vivek_23vivek_23
2,6722618
answered Nov 20 '18 at 18:44
vivek_23vivek_23
2,6722618
answered Nov 20 '18 at 18:44
vivek_23vivek_23
2,6722618
2,6722618
1
Thank you for your answer, I'm currently playing around with it.
– GrumbleShark
Nov 21 '18 at 16:34
add a comment |
1
Thank you for your answer, I'm currently playing around with it.
– GrumbleShark
Nov 21 '18 at 16:34
1
1
Thank you for your answer, I'm currently playing around with it.
– GrumbleShark
Nov 21 '18 at 16:34
Thank you for your answer, I'm currently playing around with it.
– GrumbleShark
Nov 21 '18 at 16:34
add a comment |
draft saved
draft discarded
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.
draft saved
draft discarded
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%2f53399260%2finserting-data-into-a-mysql-table-with-php-when-the-values-being-inserted-do-not%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
INSERT INTO mytable SET col1=1, col2=2...– miken32
Nov 20 '18 at 18:28
1
You should be using prepared statements though; very simple to prepare and execute queries like this using PDO.
– miken32
Nov 20 '18 at 18:29
1
Mention columns you want to insert into. Like
insert into MYTABLE(column1,column2) values('1','2')– vivek_23
Nov 20 '18 at 18:31
@vivek_23 My problem is that I don't know ahead of time how many columns there will be. I could count the number if items in $linearray and try to display that number of columns, but I was hoping for something cleaner.
– GrumbleShark
Nov 20 '18 at 18:34
1
My problem is that I don't know ahead of time how many columns there will be ... is not an ideal database design or usage. One should never leave DDL and DML calls open-ended for application to manipulate schema or data on the fly. Relational DBs are planned systems and queries should be explicit regarding identifiers like tables and columns.
– Parfait
Nov 20 '18 at 18:57