Hi,

I'm attempting to create a web form to allow an administrative user to
"clean up" the records of a (former) user, but I am uncertain about how
to phrase my query in SQL.

The web form (the back end of which will be speaking to the database via
PHP) knows the id of the former user, and needs to carry out the following
actions when the form is submitted:

The user may have zero or more records in each of the following tables,
which can be found as the person_id field in such records will match the
user id:

researcher
publication
publication_source

qualification
profile_record


Any matching records found should be deleted. For a simple query on
*one* of these tables, this would be (with the known user id instantiated
via PHP):

DELETE FROM researcher WHERE person_id = $php_person_id;


I guess I could just emit several sequential queries, but I feel it's
better to make just one query, so that it either all succeeds or all fails,
should something go wrong. (This is hopefully all that would have to worry
about with this approach, as only a handful of admin users can access this
particular form, and as the former user is no longer here, they cannot
attempt to edit their record elsewhere.)

I have a horrible feeling that this would involve some kind of JOIN, which
I (still) don't fully grok properly (JOINs give me enough brain hurt even
for just SELECTs :-( ), but I'm not really sure what I should be trying
to join on here..


To top it off, at the same time I also need to UPDATE the user's record in
the 'person' table, to set most of the fields to NULL (apart from the
user's id, name fields, and a few others required for record-keeping
purposes as other systems need this data). Is it somehow possible to
squeeze this somewhere into the same query? (Hmm, life would be so much
easier if MyISAM tables supported transactions..)


Thanks for any pointers in the right direction,


David.

David wrote:
names....etc.. so multiple deletes across tables are possible, and the
where condition I guess will be adjusted to the same table as the delete
criterion is being applied to. So:

$query=sprintf("DELETE FROM researcher, kitchen_list,xmas_fund,
employees WHERE person_id =%d", $php_person_id);

mysql_query($query);

Should work..

http://dev.mysql.com/doc/refman/5.1/en/delete.html

I'd setup up a test database and see what the exact syntax needs to be.

I don't see how you can combine an update and a delete in one sql
statement though.

On 12/21/2011 6:57 AM, David wrote:
It helps if your database is properly designed:

1. Use InnoDB as your engine
2. Set up the proper foreign keys with ON DELETE CASCADE
3. Delete the row in the person table

The database will do the rest.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex (AT) attglobal (DOT) net
==================

On 2011-12-21, The Natural Philosopher wrote:
Ah, I didn't realise that you could delete from multiple tables "just
like that", just by listing them all! That's handy (and a lot simpler
than I had feared it would be).


Yep, I'm working on a test system so that if I break things I can put it
back together again. Only once I'm sure that something is working do I
roll it out to the live environment.


Hmm, that's a bit of a shame. The updating of the other table is
essentially the part (in this system) that shows that the deletes have
taken place, so they really ought to both take place simultaneously.
(It's for an internal system, with limited access, so it's probably not
the end of world if I have to make two separate queries, but I'd like to
get into good habits if I can.)

Thanks for your help,


David.

On 2011-12-21, Jerry Stuckle wrote:
I'm fast coming to the conclusion myself that that's really the only
sensible choice, given all the limitations in MyISAM that I seem to
discover, the more I read. Unfortunately, I "inherited" this database,
and change is not a luxury that I've really had the time to investigate.
Are there any gotchas in converting a database from MyISAM to InnoDB
that I would need to be aware of (assuming that an existing database can
be converted, of course)?

I'm becoming vaguely aware of this as I read..

That wouldn't work in this case, however, as the record in the person
table is not to be deleted entirely (as some of the data is required
for ongoing record-keeping) but only have the data in no-longer-needed
fields removed, so I wouldn't be able to trigger the deletion of the
records in the other tables this way. :-(

It certainly would be a nifty feature otherwise.


Thanks for your advice,

David.

The Natural Philosopher:



I don't think the manual allows such query. A USING clause is mandatory
in this example, I believe.

Check out ALTER TABLE foo Engine=InnoDB;

Check out Chapter 13 of the MySQL 5.5 documentation.
Note that some things InnoDB does *NOT* have include:
- Full-text search index
- Geospatial indexing support
so if you're using these, watch out. Note that mixing table types
is allowed. MyISAM also allows NULL values in indexed columns but
the documentation doesn't mention this for InnoDB.

Even if you're not going to delete the person record, this may come
in useful elsewhere.

It isn't necessary that everything be accomplished with one query,
when you have transactions.

On 12月21日, 下�7时57分, David
<da... (AT) 55952163-3189045 (DOT) bogus.domain.invalid> wrote:
you can execute more sql query
such as delete from table1 where user_id = $user_id;
delete from table2 where user_id = $user_id;
hope can help you