Selecting all fields except only one field in mysql [duplicate]

Solution 1:

you can do it easily like that

lets say your field is an id = 5

then

   select * from your_table where id !=5 

and if you mean columns

lets say you dont want select column3

then

   select column1,column2,column4 from tablename;

if you have many columns

    SET @sql = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME),  '<columns_to_delete>,', '') 
    FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<table>'   AND TABLE_SCHEMA = '<database>'), ' FROM <table>');

    PREPARE stmt1 FROM @sql;
   EXECUTE stmt1;

Solution 2:

Yes you can fetch from information_schema.columns

SET @sql = CONCAT('SELECT ', (SELECT GROUP_CONCAT(COLUMN_NAME) FROM 
information_schema.columns WHERE table_schema = 'dbo' AND table_name = 
'tablename' AND column_name NOT IN ('c1', 'c2')), 
' from dbo.tablename');  

PREPARE stmt1 FROM @sql;

EXECUTE stmt1;

Solution 3:

Try this -

SHOW FIELDS FROM `tablename` WHERE FIELD NOT IN ('f1','f2','f3');

Execute this query and fetch the specific field-names and put each field-name into an array. then implode the array with ',' inside the select query.

$fields = implode(',',$fields_arr);
$sql = SELECT $fields FROM `tablename`;