[SOLVED] SQL Select Statment

[eRott]

I am just wondering. I have a simple database which contains usernames and passwords. I have a page which lists all these users in the database.

 

Is it possible to list all the users in the database except a certain one. For example, lets say this database contains 3 users:

 

Alpha

Bravo

Charlie

 

How would I stop one user from being displayed, lets say alpha, but still have all the rest show up. So then, it would only list:

 

Bravo

Charlie

 

 

----------

Thanks,

Regards,

Brendon

[shoz]

SELECT * FROM tablename WHERE username != 'Alpha'

[fenway]

Be aware that this prevents the use of an index on this column.

[eRott]

An Index? As in the ability to search this column? Just out of curiosity, what exactly does the "!" mean? Is it saying "select from this table where username DOES NOT equal alpha"? As well, is there a way to stop a certain username from showing while still being able to search this colum? (If that's what you mean by use of an index). I actually dont have it searchable, but that actually sounds like a good idea.

[shoz]

Is it saying "select from this table where username DOES NOT equal alpha"?

 

That's correct.

 

As well, is there a way to stop a certain username from showing while still being able to search this colum? (If that's what you mean by use of an index). I actually dont have it searchable, but that actually sounds like a good idea.

 

An index is a means by which MYSQL can find rows more quickly. You can check the sticky at the top of the forum on "Optimizing Mysql Queries" for more info. It does not prevent you from searching the column.

 

If you need help in regards to a specific query then post it and a more efficient query may be possible. Based on what you've said however there's probably no need to modify the query as all rows are being listed and an ORDERing of the results should still use an index if needed.

[eRott]

Ok, Ill be honest. I don't really understand some of what you said. Specifically this part:

 

"If you need help in regards to a specific query then post it and a more efficient query may be possible. Based on what you've said however there's probably no need to modify the query as all rows are being listed and an ORDERing of the results should still use an index if needed."

 

Ok, well, taking into consideration, that this database is small, indexing is really not inportant.

 

So, how would i search a specific colum(s). So, for example, I have a table named "members" inside this table i have 4 colums, "id", "name", "username", "password". How would I create a search that would search the "name" and "username" columns from what the user entered.

 

So, lets say there is again, 3 entries in the table; in this format (name, username)

 

jack, alpha

bill, bravo

susy, charlie

 

A user enters "bill" into the search text box and then the result is displayed. That being the users information; name, username etc.

[shoz]

Ok, Ill be honest. I don't really understand some of what you said. Specifically this part:

 

"If you need help in regards to a specific query then post it and a more efficient query may be possible. Based on what you've said however there's probably no need to modify the query as all rows are being listed and an ORDERing of the results should still use an index if needed."

 

You'd have to read the sticky first.

 

So, how would i search a specific colum(s). So, for example, I have a table named "members" inside this table i have 4 colums, "id", "name", "username", "password". How would I create a search that would search the "name" and "username" columns from what the user entered.

 

So, lets say there is again, 3 entries in the table; in this format (name, username)

 

jack, alpha

bill, bravo

susy, charlie

 

A user enters "bill" into the search text box and then the result is displayed. That being the users information; name, username etc.

 

This will search for rows with exact matches for the string 'bill' in both the username and name fields

 

SELECT * FROM tablename WHERE name = 'bill' OR username = 'bill'

 

Names beginning with 'bill'

SELECT ... WHERE name LIKE 'bill%' OR username LIKE 'bill%';

 

Names containing the string 'bill'

WHERE name LIKE '%bill%' OR ...

 

This Tutorial should be helpful. It addresses a number of the things you're likely to want to do when first learning to use MYSQL.