SELECT * except statement...

Mr_Chew

I have an administrative backend system with a members page that outputs all the members from the database in a table viewable for the admin to see and choose to delete members or not.

How can I select to view all the members and output every one except the admin? Obvoiusly I don't want the admin account showing with the option of deleting the account while logged in as admin. I was wondering if there is some kind of mysql query somewhere along the lines of SELECT * EXCEPT (admin account...)

If anyone can help I would appreciate it!

laserlight

It is a matter of determining the correct WHERE clause. For example, if you have a field that is non-zero for admins, you might write:

SELECT * FROM users WHERE admin=0

leatherback

Of course, if you have a whole series of levels for different usertypes you can:

select * from users where levele <> $adminlevel

Mr_Chew

i am not sure at all what you guys mean by this... i understand that it is through the WHERE clause that will determine whether or not to hide my admin account in the row of members displayed to the admin for deletion.

My mysql database table is set up with ID, username, & password fields.. what WHERE clause would I use in my select statment to make sure my 'admin' account username does not appear in the query of outputted members to an html table from the database...

SELECT " FROM register WHERE/LIMIT username 'admin' ?

NogDog

SELECT * FROM register WHERE username != 'admin'

However, if I were doing it I would add an intenger column to the table called 'admin' with a default of 0 (not an admin account), then set the value to a non-zero number for each admin account. This gives you the ability to add additional admin accounts, possibly with different levels of responsibility and thus different values, while never having to worry about changing the PHP code if/when you add/change accounts in the DB. Then to get all normal users you could simply query:

SELECT * FROM register WHERE admin = 0

Mr_Chew

I understand the idea you are relating to me but am not exactly 100% sure how to implement it. I have a db called register with a register table holding multiple user account information. So you're saying for me to add an 'admin' table to the 'register' database, create an ID column with the first field having a default value of 0 labeled 'admin', then add more fields with field titles being numbers?

Could you possibly explain how exactly I would do this? I'm using phpmyadmin and am not the best at MySQL but understand basics. Thank you!

JVassie

What i think NogDog meant was, instead of addign another table, you merely add another field to the already existing register table, and you name that field 'admin' and it can have a value of 0 or 1, or more if necessary.

laserlight

So you're saying for me to add an 'admin' table to the 'register' database, create an ID column with the first field having a default value of 0 labeled 'admin', then add more fields with field titles being numbers?

Yes, adding a new column, possibly named 'admin' though you could also name it 'level' or something like that, is the right way to go. However, you should not add more columns "with field titles being numbers". Rather, you can either leave the admin column as it is, or you can create a separate table for admin ranks, and have the admin column be a foreign key to that table. This new table would then have the various ranks, one per row.

NogDog

I was simply suggesting adding one new column to the existing table with the users' data (apparently the "register" table?).

ALTER TABLE `register` ADD `admin` TINYINT( 1 ) UNSIGNED NOT NULL DEFAULT '0'

Then simply check the value of the 'admin' column when querying that table to determine if a user is an admin user (value = 1) or a normal user (value = 0). (Optionally, you could have multiple levels of access simply by using additional values, but for now you just need the 0/1 option.)