Select ALL but ONE with MySQL

rubric

I have a table in MySQL that has many columns and I want to select all columns except the password field. Is there a keyword in MySQL in a Select statement that will ignore a column?

For example:

SELECT * (but not password) FROM users;

I do not want to type all the fields I want like this:

SELECT c1, c2, c3, c4, c5, ... , cN FROM users;

Anyone have an answer?

Thanks.

LordShryku

No, you'd need to specify the columns.

helz

Lord,
is there any way to scan a table and load all the field names into an array (probably seperated by commas)?
if you can then you might be able to do that, remove the password field from the array then use a foreach and an explode to load the fieldnames back into a select query and do it that way.

sounds like to much hard work but i guess if you have a table with a hell of a lot of fields then it could save some time 🙂

LordShryku

Yeah, that sounds like a hack, but if that's the way you want to go, sure, use [man]mysql_list_fields[/man]

helz

Grabbed and tweaked from php.net 🙂

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
$fields = mysql_list_fields("dbnamehere", "tbnamehere", $link);
$columns = mysql_num_fields($fields);
for ($i = 0; $i < $columns; $i++) {
   $field .= mysql_field_name($fields, $i).", ";
}
$field1 = str_replace(strrchr($field, ", "), "", $field);
//$field1 is now a string with all the fields using a comma as a delimiter
//eg: field1, field2, field3, blah...
$field2 = str_replace(", password field here", "", $field1);
//now $field2 should be the full list without the password field
//just load it into a new select query:
$query = mysql_query("SELECT $field2 FROM tablename") or die(mysql_error());