"disable" some MySql records so they are not returned when searching?

Question

I have a mysql database which I support using phpMyAdmin.

The website this is for is a classifieds website.

The issue here is that whenever a new classified is posted (and inserted into mysql database), I need to first review it manually.

My question is, is there anyway to tell mysql that "this record is inactive, so don't return it"?

I know of one way offcourse, to add a column named "state" and change it to "active" for all classifieds which I want active. But is there any other method built into mysql which does the same?

In other words, I dont want the record returned when users search the website if the record isn't "reviewed" first by me.

EDIT:

I know of the WHERE clause, that is not my Q here. Is there any other method built into phpMyAdmin?

Thanks

Answer 1

Have a reviewed column in the table of type BOOLEAN. Initialize it to false, and update to true once a listing has been reviewed. Whenever you get the list of classifieds for the site include reviewed=true in the WHERE clause.

There is no way built into phpMyAdmin.

Answer 2

I know of the WHERE clause, that is not my Q here. Is there any other method built into phpMyAdmin?

no, it's not. your should realy use a "state" column to get what you want.

Answer 3

There really isn't a way to achieve this without having some sort of DB field that tells you which records you've reviewed and which you haven't.

So the only sensible answer you're going to get is "add a state field".

The question really is why you don't want to do it that way? (especially if you already know how to do it). If you can answer that, you may get some useful help help with solving the problems you perceive the obvious answer may cause you.

Answer 4

No there is no built-in method. Beside of methods mentioned above you could try using views to filter inactive records. Instead of:

SELECT * FROM table WHERE status = 'active';

use a view:

CREATE VIEW only_active AS SELECT * FROM table WHERE status = 'active';

and then just

SELECT * FROM only_active

That makes you sure that you will never get inactive records incidentally.

Answer 5

If you are using the ORM Doctrine, you can use the SoftDelete behavior, which does exactly the thing you want. You can build query's and the "SoftDeleted" records do not return. You should be using Doctrine for it though.

If you can not go with Doctrine, I would use Piotr Pankowski's solution. The only thing is that I would use a DateTime field and not an Boolean field. Set the field to the current DateTime when it's added, and NULL when it's not disabled.

Answer 6

On a row level there are no properties other then ones you specify yourself. So, the answer is definitively no.

Using views as Piotr described might be useful to you; especially if you reverse the situation and

• rename the original table
• create a view in it's name that will return only 'reviewed' records

This way the solution might be transparent to your application.

Few notes:

• The database does not care if myphpadmin or any other client/application is looking at the data, all clients are essentially equal; so the implication is that what you ask can not be possible (otherwise, how would this hiding mechanism determine if it should display the rows or hide them?).
• There is a semi exception though - RDBMS that support row-level security could do this (but effectively such system store additional attribute per row)

EDIT: Forgot to link http://www.sqlmaestro.com/resources/all/row_level_security_mysql/ This gives some examples and details.