Exclude specific columns from SHOW COLUMNS mysql

Question

I have this query: SHOW COLUMNS FROM mash , which works fine in my while loop to create a select element made from table column names. But in my table I have "id" and "tstamp" that I don't want in the select element, can these columns be excluded?

 echo "<form action='".$_SERVER['PHP_SELF']."' method='get'>"; connectDB(); $result = mysql_query("SHOW COLUMNS FROM mash") or die(mysql_error()); echo '<select name="column" class="column">'; while ($row = mysql_fetch_array($result)) { echo "<option value='".$row[0]."'>".ucwords($row[0])."</option>"; } closeConn(); echo '</select>'; echo "</form>"; 

Answer 1

PHP path:

Use continue in a while loop when these fields are retrieved, for example:

 echo "<form action='".$_SERVER['PHP_SELF']."' method='get'>"; connectDB(); $result = mysql_query("SHOW COLUMNS FROM mash") or die(mysql_error()); echo '<select name="column" class="column">'; while ($row = mysql_fetch_array($result)) { if($row[0] == 'id' || $row[0] == 'tstamp') continue; echo "<option value='".$row[0]."'>".ucwords($row[0])."</option>"; } closeConn(); echo '</select>'; echo "</form>"; 

This will simply skip the id and tstamp and process the rest. continue used in loop structures to skip the rest of the current iteration of the loop and continue execution when evaluating the state, and then at the beginning of the next iteration.

---

MySQL path:

Delete these fields in the query as follows:

 SHOW COLUMNS FROM mash WHERE Field NOT IN ('id', 'tstamp'); 

Answer 2

Yes it is possible. Instead of SHOW COLUMNS use INFORMATION_SCHEMA .

INFORMATION_SCHEMA is an ANSI way to retrieve metadata from a relational database. In MySQL you can use:

 $sql = "select column_name from information_schema.columns c " + " where c.table_schema = 'db_name' " + " and c.table_name='table_name' " + " and c.column_name like 'name%'"; 

An information schema has the advantage of being compliant with SQLANSI. You can use it in MySQL, PostgreSQL, SQLServer and other relational databases.

Answer 3

You can use the LIKE operator.

 SHOW COLUMNS FROM mash LIKE "name%" 

Answer 4

To see only the “Collation” column that appears in SHOW FULL COLUMNS from tablename for only one field, this:

 select COLLATION_NAME from information_schema.columns where TABLE_SCHEMA = 'tableschemaname' and TABLE_NAME = 'tablename' and COLUMN_NAME = 'fieldname'; 

Ddrop fieldname in where if you want to see this column for all field names.

To see all the possible column names that appear with SHOW FULL COLUMNS , so you can choose what you want:

 select * from information_schema.columns where TABLE_SCHEMA = 'tableschemaname' and TABLE_NAME = 'tablename' and COLUMN_NAME = 'fieldname';