0

I want to select all columns from a table except one column using MySQL. I know we can select all columns using this

<?php
$query = SELECT * FROM posts;
?>

But how can I leave one specific column from the table?

Nick
  • 138,499
  • 22
  • 57
  • 95
Praveen Kumar
  • 49
  • 1
  • 6
  • Is there any particular reason why you not want to select all columns? Here is the link where this question was discussed: https://stackoverflow.com/questions/9122/select-all-columns-except-one-in-mysql. This question can be marked as Duplicate. – Serghei Leonenco May 30 '20 at 04:43
  • The question also is not related to PHP, and the PHP is invalid so likely PHP tag and code should be removed. – user3783243 May 30 '20 at 04:46
  • If you're just getting started with PHP and want to build applications, I'd strongly recommend looking at various [development frameworks](https://www.cloudways.com/blog/best-php-frameworks/) to see if you can find one that fits your style and needs. They come in various flavors from lightweight like [Fat-Free Framework](https://fatfreeframework.com/) to far more comprehensive like [Laravel](http://laravel.com/). These give you concrete examples to work from and guidance on how to write your code and organize your project's files. – tadman May 30 '20 at 04:50
  • That is not valid PHP, and even if it was, it *still* wouldn't fetch anything from the database since you don't have a database connection defined, and you don't prepare or execute the query. – tadman May 30 '20 at 04:51

1 Answers1

1

Instead of using * you list all the columns on the table, leaving out the one you don't want

MySQL has an information scheme that will list all the columns in the table; you can query it and use the returned results to paste into your code so you don't have to type it out. For example, write this:

SELECT CONCAT(column_name, ',') FROM information_schema.columns WHERE table_name = 'posts' AND column_name NOT IN ('column name to exclude')

Then copy the result and paste into your code , removing the last comma

Many query tools will also help you formulate queries in this way, or show you the queries they are running and you can copy it/paste it into your code

You might find it easier to do you database access via a library that lets you treat tables like objects; I'm not a php programmer but over in C# we have things like entity framework, a library where we can make our code side Person object have 4 properties (Name, Age, Job, SSN) and tell it that it is backed by the tblPerson table (with 5 columns) and it won't download the 5th column when doing its queries. Pretty sure php will have something like that that means you don't have to spend your life writing endless SELECT blah, blah2, blah3 ...

There is a benefit in not using * sometimes, as your queries and code carry on working if someone adds a column to a table with the same name as another column. If you had:

SELECT * FROM person JOIN address ON ...

And person had a Status column that was used in the front end app, if someone later adds another Status column to address, you'll start getting two Status columns back and your front end might not treat the second correctly; it might overwrite the first or cause a crash. If you avoided using * and picked the columns you wanted by name, the system would carry on working even with the new column

Caius Jard
  • 72,509
  • 5
  • 49
  • 80