Re: [PHP] Doubts With Select Queries

On Wednesday 03 December 2003 17:30, irinchiang@justeducation.com wrote:

> Having some doubts with sql select queries here :
>
> Right now, I have a "student" and a "withdraw" tables.
> For example, when student "John" has withdrawn, his name will be inserted
> into "withdraw" table but John's record will be kept in "student" table for
> a period of 30 days before it is deleted from "student" table. In the
> meantime, in "student" table, the status for John should be set to "0"
> (inactive). Wonder the solutions I have mentioned is possible? And if so,
> how shld i go about doing it ??
On Wednesday 03 December 2003 18:00:07, php-general@gremlins.biz wrote:

I'm assuming you don't need help with the obvious, like how to insert John
into the "withdraw" table and how to set his status to 0.

So to implement your "keep for 30 days" thing, you need another column
(probably in the "student" table) that is of type DATE and holds the date
when "status" was set to 0.

Then you can do something like (exact query depends on what DB you're using)

DELETE FROM student WHERE (DATE_SUB(NOW(), INTERVAL 30 DAY) >
name_of_date_column)

Also I hope you're not using the "student_name" column as a unique identifier
to identify your students. You should have an extra column, say "student_id",
which is of type sequence or auto increment (again, depending on your DB) as
your unique student identifier.

And please, in future ask php/db related questions on the php-db list.

--
-------------------------------------------------------------------------------

Not to worry, I already have a unique identifier to identify my "student"
which wil be the student_id (which I have set as the primary key). Thanks for
your previous solution, anyway stil having some doubts here:

What if within these 30 days, student John actually decided not to withdraw
anymore and to continue to study in the school? Is there any way whereby I can
set the status to "0" again n then John will not be deleted from the DB??
Wonder if this would be possible??>?

Regards,
Irin Chiang.