Is it possible to select certain elements of the database table?

Question

For example: I have a table name tbl_admin and I have so many table elements within that table.

Like (id, fname, lname, contact_info, email, ip, date, status, etc.and so on upto 20 elements).

Now I need to exclude only 3 elements from that table as you can say (fname, lname and contact_info) and select all others. Is this possible by using the mysql query?

Please help me if this is possible. Thanks,

no its not possible you have to write all the fields which you want to select — Moeed Farooqui, Feb 09 '14 at 05:48

score 1 · Answer 1 · edited May 23 '17 at 12:06

Short Answer You can't exclude columns explicitly, but you can do so implicitly by selecting only the other columns:

SELECT
    ID,
    EMAIL,
    IP,
    DATE,
    STATUS
FROM tbl_admin

Long Answer It turns out I was wrong with the short answer, technically there is a way as illustrated with the question here.

To do this in PHP, I'd recommend creating a view in the MySQL database that would encapsulate the SQL. Then you would just select * from that view.

Alternatively, you could create a stored procedure, and pass in the names of the columns that you want to be filtered out.

score 0 · Answer 2 · answered Feb 09 '14 at 05:36

0

Select fname,sname,contact from table_admin

answered Feb 09 '14 at 05:36

Swapnil

654
7
27

sorry, my mistake. i want to exclude these fields – Feb 09 '14 at 05:38

score 0 · Answer 3 · edited May 23 '17 at 12:06

0

Yes, so you will have something like this:

SELECT
    id,
    fname,
    lanem
FROM
    tbl_admin

You just need to specify what are those and in what order. Instead of using the * to fetch all records (columns) in its default order.

Edit

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

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Actually, I just got it from another's posts so all credits goes to him. HERE

edited May 23 '17 at 12:06

Community

1
1

answered Feb 09 '14 at 05:36

Craftein

762
5
10

sorry, my mistake. i want to exclude these fields – Feb 09 '14 at 05:37
1

okay done. found it on some post, but i and many others thinks this is not a good practice though. – Craftein Feb 09 '14 at 05:55

score 0 · Answer 4 · edited May 23 '17 at 11:44

Latest Answer

ANSWER BY Mahomedalid in Select all columns except one in MySQL?

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

PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

For your situation replace the <columns_to_omit> with fname,lname,contact_info

Old Answer

Syntax for retrieving particular column values from table is

SELECT column_name(s)
FROM table_name

All columns can be retrieved using SELECT * instead of SELECT column_name(s)

if you are directing your question towards a particular language like PHP then the syntax can be like this

<?php
$con=mysqli_connect("localhost","username","password","database");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$result = mysqli_query($con,"SELECT fname,lname,contact_info FROM tbl_admin");?>

i want to exclude only 3 column, because i have 20+ column and i dont want to select all other fields — , Feb 09 '14 at 05:43
i have more then 30 fields, so i can't type select (column1,column2,column3,column4,column5..... column30) — , Feb 09 '14 at 05:51

score 0 · Accepted Answer · answered Feb 09 '14 at 05:53

Try:

$result = mysql_query("SHOW COLUMNS FROM tbl_admin");

if (mysql_num_rows($result) > 0) {
    while ($row = mysql_fetch_assoc($result)) {
        $columns[] = $row['Field'];
    }
}
//fill all columns here that you want to exclude
$columns_to_exclude = array('fname','lname','contact_info'); 
$sql = "SELECT (";
foreach($columns as $ind=>$val){

    if(!in_array($val,$columns_to_exclude))
    {
        $sql .= $val.", ";
    }
}
$sql = rtrim($sql,", ");
$sql .= ") FROM tbl_admin;";

echo $sql;

Is it possible to select certain elements of the database table?

5 Answers5