I have table has
id,name,age,surname,lastname,catgory,active
instead of select name,age,surname,lastname,catgory from table
how can I make something like this select * from table but not select id,active
views:
166answers:
9
+6
A:
The only way to do that that I know if is to enumerate each column you do want... no negative filters that I'm aware of.
select name, age, surname, lastname, category from table
RenderIn
2010-03-02 19:17:30
+2
A:
you can't do that, sorry. Actually you shouln't have done it if you could - specifying these things explicitly is always better, assume other developer adds new field and your application will fail
Andrey
2010-03-02 19:18:03
+1
A:
I'm fairly certain you can't. Probably the best way I can think of is to create SELECT name, age, surname, lastname, category FROM table as a view, then just SELECT * FROM view. I prefer to always select from a view anyway.
However, as others have pointed out, if another column gets added to the view your application could fail. On some systems as well (PostgreSQL is a candidate) you cannot alter the table without first dropping the view so it becomes a bit cumbersome.
Andy Shellam
2010-03-02 19:18:03
A:
Is it really that big of a deal if your fetched array contains those two values? If you're not going to use them, it doesn't really matter if they're there or not. It doesn't seem very logical to keep adding additional code to not select something.
animuson
2010-03-02 19:18:12
Depends on how many columns, how much data and the system architecture - do you really want to be pulling 1,000 rows of 2 columns of data across a network that you're not actually going to use? If the web server and database are on the same machine then OK but I'd still not advise it.
Andy Shellam
2010-03-02 19:20:03
no one understand me now i want make advansed persistion system when i add new one in mysql it should appear in the php.... chit
moustafa
2010-03-02 19:20:58
@Andy: If you have that much data stored for one row wouldn't it be more logical to store key information and store the rest in a cache, therefore only pulling the information you need to access the data elsewhere?
animuson
2010-03-02 19:21:19
It's in the case of multiple rows. If the 2 columns you don't need contain 25 bytes each, and you pull back 100 rows into your application, that's 5K you've just sent across the network you didn't need to. Multiple that across 1000 visitors that's 5MB. See my point? That's a lot of traffic for data you don't need.
Andy Shellam
2010-03-02 19:55:00
+1
A:
Unless there's some special extension in MySql you cannot do that. You either get all, or have to explicitly state what you want. It is best practice to always name columns, as this will not alter the query behaviour even if the underlying table changes.
Anders Abel
2010-03-02 19:19:40
A:
but i can make it in a loop by foreach and use if key = id make contenue
moustafa
2010-03-02 19:21:51
You could certainly do this in PHP to build the SELECT statement dynamically, but that's not what you asked.
Andy Shellam
2010-03-02 19:55:39
+1
A:
There is no SQL syntax to support:
select * from table but not select id,active
If you want all but one or more columns, you have to explicitly define the list of columns you want.
OMG Ponies
2010-03-02 19:22:48
+2
A:
You should not be using select * anyway. Enumerate the columns you want and only the columns you want, that is the best practice.
HLGEM
2010-03-02 19:30:00
+1
A:
You are too advanced.
The only data language that I have seen that supports your syntax is the D language with its "...ALL BUT ..." construct:
[Wikipedia - D Language Specification][1]
There are some reference implementations available, but mostly for teaching purposes.
http://en.wikipedia.org/wiki/D_%28data_language_specification%29
Martin
2010-03-02 20:55:21