MySQL: check if a column exists

[Alex]

basically i'm writing a very abstract select function in PHP that selects stuff from a table passed as a parameter and optionally orders it by a second parameter passed.

if the column doesn't exist though i get an order when i try to ORDER BY...

and i don't want to use multiple queries, i know how it can be done that way but i wanna learn some SQL IF statments so here goes:

how is it done?

ive tried a lot of examples from google and here is the closest that i got but it returns an unspecified error...


also still no room for the ELSE clause... help plz!
thx!

-Alex

 

[Hyperblaze]

I'd HIGHLY recommend to avoid using "SELECT *" like the plague.

Grab the fields you want. Avoiding unnecessary load on the database.

 

[Crusty]

Originally posted by: Hyperblaze
I'd HIGHLY recommend to avoid using "SELECT *" like the plague.

Grab the fields you want. Avoiding unnecessary load on the database.

Especially if you plan on scaling the application up to a large user base

 

[Alex]

fair enough, i usually avoid SELECT * also because i can see exactly what data i need to print on my page later
this is just a small learning example though...

any solutions to the problem?

 

[EagleKeeper]

Do you have the ability to retrieve the schema itself?

Not a MySQL person, but I know I have done it using the Microsoft ODBC connection & dataset API

 

[Crusty]

You can do SHOW CREATE TABLE `insert_table_name`; That will give you an SQL query that can be used to create the table again, or you can do DESCRIBE `insert_table_name`; That will give you a recordset with the list of columns and settings as parameters.

 

[PhatoseAlpha]

You are doing this in a stored procedure you're creating, right? Not just typing in the SQL?

 

[Alex]

i want to do this using just 1 SQL query.... ive been told it could be used with the following SQL construct (IF EXISTS) along with the schema, but i cant figure out the syntax
and yes its a local db on my test machine so i have root access

 

[Markbnj]

All the schema metadata is stored in the system tables, which are queryable at your level of privilege. So you can get the column names for the table and look in the result set to see if the one you want is there or not.

The tables are found in sysobjects, and the columns in syscolumns keyed on the table's object id.

Edit: and I can't read... MySQL.... let's see...

INFORMATION_SCHEMA.tables
INFORMATION_SCHEMA.columns

http://dev.mysql.com/doc/refma.../en/columns-table.html

 

[Alex]

Originally posted by: Markbnj
All the schema metadata is stored in the system tables, which are queryable at your level of privilege. So you can get the column names for the table and look in the result set to see if the one you want is there or not.

The tables are found in sysobjects, and the columns in syscolumns keyed on the table's object id.

Edit: and I can't read... MySQL.... let's see...

INFORMATION_SCHEMA.tables
INFORMATION_SCHEMA.columns

http://dev.mysql.com/doc/refma.../en/columns-table.html

thanks, so is there any way using IF EXSITS to make this into 1 query only?

for example...

"SELECT data1, data2 FROM table x WHERE condition y = z [IF COLUMN col1 exists in table ] ORDER BY col1 [ELSE] order by data1

??

 

[Markbnj]

Yeah, sure. Join the tables and columns tables to get the set of columns in the table of interest, and use that as a subquery to a WHERE 'ColumnName' IN SELECT Columns from... " query. Or something along those lines.

 

[Alex]

care to be more specific please?

 

[Markbnj]

I'm afraid I don't have time to be more specific as I am leaving on a trip in a couple of hours. Maybe someone else can take a stab at it. The bottom line is that you can test the inclusion of a column name in the result set of column names obtained by querying the INFORMATION_SCHEMA tables, and based on the boolean results of that test you can do different things in the query.

 

[hans007]

you could i suppose... do something like this.

i havent done anything in sql in a while... so i dont remember the exact functions.

but you could do something like... select * from table limit 1; (or however limits are done in mysql).

when you get an array back, i know in php it'd be something like mysql_fetch_assoc.

so you get an assoc array back and you could so something like $array['columnyouwant'] and check if its is_null. honestly i havent checked if this works, but its worth a try.

 

[Alex]

Originally posted by: hans007
you could i suppose... do something like this.

i havent done anything in sql in a while... so i dont remember the exact functions.

but you could do something like... select * from table limit 1; (or however limits are done in mysql).

when you get an array back, i know in php it'd be something like mysql_fetch_assoc.

so you get an assoc array back and you could so something like $array['columnyouwant'] and check if its is_null. honestly i havent checked if this works, but its worth a try.

thanks for taking the time to reply....

yeah i know how to do this using multiple queries in PHP but i was wondering whether it was possible to do using only 1 SQL query that used conditional statements/operators... thx