Doubts With Select Queries

Hi all:

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 ??....Hope to get some help really soon...Thanks in advance!


student table withdraw table
----------------------- -------------------------------------
|student_name | status | student_name | withdraw_reason
----------------------- -------------------------------------
| John | 0 | John | migrate
----------------------- -------------------------------------
| James | 1 |
-----------------------


Regards,
Irin Chiang.

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 ??

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.

--
Jason Wong -> Gremlins Associates -> www.gremlins.biz
Open Source Software Systems Integrators
* Web Design & Hosting * Internet & Intranet Applications Development *
------------------------------------------
Search the list archives before you post
http://marc.theaimsgroup.com/?l=php-general
------------------------------------------
/*
Die, v.:
To stop sinning suddenly.
-- Elbert Hubbard
*/