How to check in sql if a table has got only one column?
In my php code, I would like to remove columns from a table like this :
function delete_table_column_db( $table_name, $column_attrs ){
global $wpdb;
$res = $wpdb->query( "ALTER TABLE " . $table_name . " DROP " . $column_attrs['name'] );
if( $res === false ){
error_log( sprintf("The column %s could not be deleted for the table %s", $column_attrs['name'], $table_name ) );
}
}
But this function makes an error if the table has got only one column :
ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead
So I would like to DROP TABLE if it´s the last column of the table.
For that, I need to check if it´s the last table column.
Is there a way to do this ?
php mysql
asked Nov 22 at 18:18
J.BizMai
5711619
add a comment |
In my php code, I would like to remove columns from a table like this :
function delete_table_column_db( $table_name, $column_attrs ){
global $wpdb;
$res = $wpdb->query( "ALTER TABLE " . $table_name . " DROP " . $column_attrs['name'] );
if( $res === false ){
error_log( sprintf("The column %s could not be deleted for the table %s", $column_attrs['name'], $table_name ) );
}
}
But this function makes an error if the table has got only one column :
ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead
So I would like to DROP TABLE if it´s the last column of the table.
For that, I need to check if it´s the last table column.
Is there a way to do this ?
php mysql
asked Nov 22 at 18:18
J.BizMai
5711619
Please be aware that adding or removing columns from a table is a very expensive and disruptive DB operation, and not something that should be done outside of maintenance operations.
– Sammitch
Nov 22 at 18:37
Dropping columns or altering table definitions as part of an app is usually a good sign that the design is not right in the first place. Having an evolving database structure is also a good way of making code fail when the underlying table suddenly changes.
– Nigel Ren
Nov 22 at 18:57
add a comment |
In my php code, I would like to remove columns from a table like this :
function delete_table_column_db( $table_name, $column_attrs ){
global $wpdb;
$res = $wpdb->query( "ALTER TABLE " . $table_name . " DROP " . $column_attrs['name'] );
if( $res === false ){
error_log( sprintf("The column %s could not be deleted for the table %s", $column_attrs['name'], $table_name ) );
}
}
But this function makes an error if the table has got only one column :
ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead
So I would like to DROP TABLE if it´s the last column of the table.
For that, I need to check if it´s the last table column.
Is there a way to do this ?
php mysql
asked Nov 22 at 18:18
J.BizMai
5711619
In my php code, I would like to remove columns from a table like this :
function delete_table_column_db( $table_name, $column_attrs ){
global $wpdb;
$res = $wpdb->query( "ALTER TABLE " . $table_name . " DROP " . $column_attrs['name'] );
if( $res === false ){
error_log( sprintf("The column %s could not be deleted for the table %s", $column_attrs['name'], $table_name ) );
}
}
But this function makes an error if the table has got only one column :
ERROR 1090 (42000): You can't delete all columns with ALTER TABLE; use DROP TABLE instead
So I would like to DROP TABLE if it´s the last column of the table.
For that, I need to check if it´s the last table column.
Is there a way to do this ?
php mysql
php mysql
asked Nov 22 at 18:18
J.BizMai
5711619
asked Nov 22 at 18:18
J.BizMai
5711619
asked Nov 22 at 18:18
J.BizMai
5711619
asked Nov 22 at 18:18
J.BizMai
5711619
asked Nov 22 at 18:18
J.BizMai
5711619
5711619
Please be aware that adding or removing columns from a table is a very expensive and disruptive DB operation, and not something that should be done outside of maintenance operations.
– Sammitch
Nov 22 at 18:37
Dropping columns or altering table definitions as part of an app is usually a good sign that the design is not right in the first place. Having an evolving database structure is also a good way of making code fail when the underlying table suddenly changes.
– Nigel Ren
Nov 22 at 18:57
add a comment |
Please be aware that adding or removing columns from a table is a very expensive and disruptive DB operation, and not something that should be done outside of maintenance operations.
– Sammitch
Nov 22 at 18:37
Dropping columns or altering table definitions as part of an app is usually a good sign that the design is not right in the first place. Having an evolving database structure is also a good way of making code fail when the underlying table suddenly changes.
– Nigel Ren
Nov 22 at 18:57
Please be aware that adding or removing columns from a table is a very expensive and disruptive DB operation, and not something that should be done outside of maintenance operations.
– Sammitch
Nov 22 at 18:37
Please be aware that adding or removing columns from a table is a very expensive and disruptive DB operation, and not something that should be done outside of maintenance operations.
– Sammitch
Nov 22 at 18:37
Dropping columns or altering table definitions as part of an app is usually a good sign that the design is not right in the first place. Having an evolving database structure is also a good way of making code fail when the underlying table suddenly changes.
– Nigel Ren
Nov 22 at 18:57
Dropping columns or altering table definitions as part of an app is usually a good sign that the design is not right in the first place. Having an evolving database structure is also a good way of making code fail when the underlying table suddenly changes.
– Nigel Ren
Nov 22 at 18:57
add a comment |
3 Answers
3
active
oldest
votes
To count the number of columns in a table, we will need to use INFORMATION_SCHEMA.COLUMNS. Following solution will work only if the user has access to INFORMATION_SCHEMA tables.
You can get the total number of columns in a table as follows:
SELECT COUNT(*) AS no_of_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '$your_table_name' AND
TABLE_SCHEMA = '$your_database_name'
So you can use this query first, get the number of columns. If it is more than 1, then you can proceed with the ALTER TABLE .. DROP COLUMN query.
Some of the noteworthy columns in the information_schema.columns table are:
TABLE_SCHEMA- The name of the schema (database) to which the table
containing the column belongs.
TABLE_NAME- The name of the table containing the column.
COLUMN_NAME- The name of the column.
answered Nov 22 at 18:21
Madhur Bhaiya
19.5k62236
Thank you ! What meansAS no_of_columns?
– J.BizMai
Nov 22 at 18:25
1
@J.BizMai it is just an alias for the count being received from the query. you can refer to this alias to get the value afterquery().
– Madhur Bhaiya
Nov 22 at 18:26
add a comment |
$stmt = $wpdb->query('SELECT * FROM '. $tablename);
$row_count = $stmt->rowCount();
if($row_count==1){
//drop table
}
else{
//alter table drop columns
}
Hope it helps.
answered Nov 22 at 18:44
Sachin Bahukhandi
783620
add a comment |
The complete code based on @Madhur Bhaiya answer :
function delete_table_column_db( $table_name, $column_attrs ){
global $wpdb;
$query = "SELECT COUNT(*) AS no_of_columns FROM information_schema.columns WHERE table_name = '{$table_name}'"
$check_column_count = $wpdb->get_results( $query );
$nbr_columns = (int) $check_column_count[0]['no_of_columns'];
if( $check_column_count === 1 ){
$res = $wpdb->query( "DROP TABLE {$table_name}" );
}else{
$res = $wpdb->query( "ALTER TABLE {$table_name} DROP {$column_attrs['name']}" );
}
if( $res === false ){
error_log( sprintf("The column %s could not be deleted for the table %s", $column_attrs['name'], $table_name ) );
}
}
answered Nov 22 at 18:43
J.BizMai
5711619
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%2f53436385%2fhow-to-check-in-sql-if-a-table-has-got-only-one-column%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
3 Answers
3
active
oldest
votes
3 Answers
3
active
oldest
votes
active
oldest
votes
active
oldest
votes
To count the number of columns in a table, we will need to use INFORMATION_SCHEMA.COLUMNS. Following solution will work only if the user has access to INFORMATION_SCHEMA tables.
You can get the total number of columns in a table as follows:
SELECT COUNT(*) AS no_of_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '$your_table_name' AND
TABLE_SCHEMA = '$your_database_name'
So you can use this query first, get the number of columns. If it is more than 1, then you can proceed with the ALTER TABLE .. DROP COLUMN query.
Some of the noteworthy columns in the information_schema.columns table are:
TABLE_SCHEMA- The name of the schema (database) to which the table
containing the column belongs.
TABLE_NAME- The name of the table containing the column.
COLUMN_NAME- The name of the column.
answered Nov 22 at 18:21
Madhur Bhaiya
19.5k62236
Thank you ! What meansAS no_of_columns?
– J.BizMai
Nov 22 at 18:25
1
@J.BizMai it is just an alias for the count being received from the query. you can refer to this alias to get the value afterquery().
– Madhur Bhaiya
Nov 22 at 18:26
add a comment |
To count the number of columns in a table, we will need to use INFORMATION_SCHEMA.COLUMNS. Following solution will work only if the user has access to INFORMATION_SCHEMA tables.
You can get the total number of columns in a table as follows:
SELECT COUNT(*) AS no_of_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '$your_table_name' AND
TABLE_SCHEMA = '$your_database_name'
So you can use this query first, get the number of columns. If it is more than 1, then you can proceed with the ALTER TABLE .. DROP COLUMN query.
Some of the noteworthy columns in the information_schema.columns table are:
TABLE_SCHEMA- The name of the schema (database) to which the table
containing the column belongs.
TABLE_NAME- The name of the table containing the column.
COLUMN_NAME- The name of the column.
answered Nov 22 at 18:21
Madhur Bhaiya
19.5k62236
Thank you ! What meansAS no_of_columns?
– J.BizMai
Nov 22 at 18:25
1
@J.BizMai it is just an alias for the count being received from the query. you can refer to this alias to get the value afterquery().
– Madhur Bhaiya
Nov 22 at 18:26
add a comment |
To count the number of columns in a table, we will need to use INFORMATION_SCHEMA.COLUMNS. Following solution will work only if the user has access to INFORMATION_SCHEMA tables.
You can get the total number of columns in a table as follows:
SELECT COUNT(*) AS no_of_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '$your_table_name' AND
TABLE_SCHEMA = '$your_database_name'
So you can use this query first, get the number of columns. If it is more than 1, then you can proceed with the ALTER TABLE .. DROP COLUMN query.
Some of the noteworthy columns in the information_schema.columns table are:
TABLE_SCHEMA- The name of the schema (database) to which the table
containing the column belongs.
TABLE_NAME- The name of the table containing the column.
COLUMN_NAME- The name of the column.
answered Nov 22 at 18:21
Madhur Bhaiya
19.5k62236
To count the number of columns in a table, we will need to use INFORMATION_SCHEMA.COLUMNS. Following solution will work only if the user has access to INFORMATION_SCHEMA tables.
You can get the total number of columns in a table as follows:
SELECT COUNT(*) AS no_of_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '$your_table_name' AND
TABLE_SCHEMA = '$your_database_name'
So you can use this query first, get the number of columns. If it is more than 1, then you can proceed with the ALTER TABLE .. DROP COLUMN query.
Some of the noteworthy columns in the information_schema.columns table are:
TABLE_SCHEMA- The name of the schema (database) to which the table
containing the column belongs.
TABLE_NAME- The name of the table containing the column.
COLUMN_NAME- The name of the column.
answered Nov 22 at 18:21
Madhur Bhaiya
19.5k62236
edited Nov 22 at 18:26
answered Nov 22 at 18:21
Madhur Bhaiya
19.5k62236
answered Nov 22 at 18:21
Madhur Bhaiya
19.5k62236
answered Nov 22 at 18:21
Madhur Bhaiya
19.5k62236
19.5k62236
Thank you ! What meansAS no_of_columns?
– J.BizMai
Nov 22 at 18:25
1
@J.BizMai it is just an alias for the count being received from the query. you can refer to this alias to get the value afterquery().
– Madhur Bhaiya
Nov 22 at 18:26
add a comment |
Thank you ! What meansAS no_of_columns?
– J.BizMai
Nov 22 at 18:25
1
@J.BizMai it is just an alias for the count being received from the query. you can refer to this alias to get the value afterquery().
– Madhur Bhaiya
Nov 22 at 18:26
Thank you ! What means
AS no_of_columns ?– J.BizMai
Nov 22 at 18:25
Thank you ! What means
AS no_of_columns ?– J.BizMai
Nov 22 at 18:25
1
1
@J.BizMai it is just an alias for the count being received from the query. you can refer to this alias to get the value after
query().– Madhur Bhaiya
Nov 22 at 18:26
@J.BizMai it is just an alias for the count being received from the query. you can refer to this alias to get the value after
query().– Madhur Bhaiya
Nov 22 at 18:26
add a comment |
$stmt = $wpdb->query('SELECT * FROM '. $tablename);
$row_count = $stmt->rowCount();
if($row_count==1){
//drop table
}
else{
//alter table drop columns
}
Hope it helps.
answered Nov 22 at 18:44
Sachin Bahukhandi
783620
add a comment |
$stmt = $wpdb->query('SELECT * FROM '. $tablename);
$row_count = $stmt->rowCount();
if($row_count==1){
//drop table
}
else{
//alter table drop columns
}
Hope it helps.
answered Nov 22 at 18:44
Sachin Bahukhandi
783620
add a comment |
$stmt = $wpdb->query('SELECT * FROM '. $tablename);
$row_count = $stmt->rowCount();
if($row_count==1){
//drop table
}
else{
//alter table drop columns
}
Hope it helps.
answered Nov 22 at 18:44
Sachin Bahukhandi
783620
$stmt = $wpdb->query('SELECT * FROM '. $tablename);
$row_count = $stmt->rowCount();
if($row_count==1){
//drop table
}
else{
//alter table drop columns
}
Hope it helps.
answered Nov 22 at 18:44
Sachin Bahukhandi
783620
answered Nov 22 at 18:44
Sachin Bahukhandi
783620
answered Nov 22 at 18:44
Sachin Bahukhandi
783620
answered Nov 22 at 18:44
Sachin Bahukhandi
783620
783620
add a comment |
add a comment |
The complete code based on @Madhur Bhaiya answer :
function delete_table_column_db( $table_name, $column_attrs ){
global $wpdb;
$query = "SELECT COUNT(*) AS no_of_columns FROM information_schema.columns WHERE table_name = '{$table_name}'"
$check_column_count = $wpdb->get_results( $query );
$nbr_columns = (int) $check_column_count[0]['no_of_columns'];
if( $check_column_count === 1 ){
$res = $wpdb->query( "DROP TABLE {$table_name}" );
}else{
$res = $wpdb->query( "ALTER TABLE {$table_name} DROP {$column_attrs['name']}" );
}
if( $res === false ){
error_log( sprintf("The column %s could not be deleted for the table %s", $column_attrs['name'], $table_name ) );
}
}
answered Nov 22 at 18:43
J.BizMai
5711619
add a comment |
The complete code based on @Madhur Bhaiya answer :
function delete_table_column_db( $table_name, $column_attrs ){
global $wpdb;
$query = "SELECT COUNT(*) AS no_of_columns FROM information_schema.columns WHERE table_name = '{$table_name}'"
$check_column_count = $wpdb->get_results( $query );
$nbr_columns = (int) $check_column_count[0]['no_of_columns'];
if( $check_column_count === 1 ){
$res = $wpdb->query( "DROP TABLE {$table_name}" );
}else{
$res = $wpdb->query( "ALTER TABLE {$table_name} DROP {$column_attrs['name']}" );
}
if( $res === false ){
error_log( sprintf("The column %s could not be deleted for the table %s", $column_attrs['name'], $table_name ) );
}
}
answered Nov 22 at 18:43
J.BizMai
5711619
add a comment |
The complete code based on @Madhur Bhaiya answer :
function delete_table_column_db( $table_name, $column_attrs ){
global $wpdb;
$query = "SELECT COUNT(*) AS no_of_columns FROM information_schema.columns WHERE table_name = '{$table_name}'"
$check_column_count = $wpdb->get_results( $query );
$nbr_columns = (int) $check_column_count[0]['no_of_columns'];
if( $check_column_count === 1 ){
$res = $wpdb->query( "DROP TABLE {$table_name}" );
}else{
$res = $wpdb->query( "ALTER TABLE {$table_name} DROP {$column_attrs['name']}" );
}
if( $res === false ){
error_log( sprintf("The column %s could not be deleted for the table %s", $column_attrs['name'], $table_name ) );
}
}
answered Nov 22 at 18:43
J.BizMai
5711619
The complete code based on @Madhur Bhaiya answer :
function delete_table_column_db( $table_name, $column_attrs ){
global $wpdb;
$query = "SELECT COUNT(*) AS no_of_columns FROM information_schema.columns WHERE table_name = '{$table_name}'"
$check_column_count = $wpdb->get_results( $query );
$nbr_columns = (int) $check_column_count[0]['no_of_columns'];
if( $check_column_count === 1 ){
$res = $wpdb->query( "DROP TABLE {$table_name}" );
}else{
$res = $wpdb->query( "ALTER TABLE {$table_name} DROP {$column_attrs['name']}" );
}
if( $res === false ){
error_log( sprintf("The column %s could not be deleted for the table %s", $column_attrs['name'], $table_name ) );
}
}
answered Nov 22 at 18:43
J.BizMai
5711619
edited Nov 29 at 19:17
answered Nov 22 at 18:43
J.BizMai
5711619
answered Nov 22 at 18:43
J.BizMai
5711619
answered Nov 22 at 18:43
J.BizMai
5711619
5711619
add a comment |
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53436385%2fhow-to-check-in-sql-if-a-table-has-got-only-one-column%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
Please be aware that adding or removing columns from a table is a very expensive and disruptive DB operation, and not something that should be done outside of maintenance operations.
– Sammitch
Nov 22 at 18:37
Dropping columns or altering table definitions as part of an app is usually a good sign that the design is not right in the first place. Having an evolving database structure is also a good way of making code fail when the underlying table suddenly changes.
– Nigel Ren
Nov 22 at 18:57