SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi, I have an SQL Query where I use a LEFT JOIN. It works fine. But if I change it from "SELECT .. FROM" into "DELETE FROM" I get an ugly error message. Is it not possible to use LEFT JOINS together with "DELETE" ?
    If not, what else could I do to delete staff which is in two joined tables.
    Thx, Alex

  2. #2
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is one of the many things missing from MySQL - cascade updates and deletes. Most RDMS allow you to define whether cascade updates and deletes should be performed on tables which have a foreign key to another table. But there is no foreign key support in MySQL at this time. The only way I know is to execute seperate sql queries. This is a very poor situation as it can easily corrupt your data if the programmer forgets this or forgets or is not aware of the overall database schema. I would be pleasently suprised if someone can correct me on this or show an easier method.

  3. #3
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ok, so what could I do here:
    I have this two tables:

    [events]
    event_id / location / ...

    [date]
    date / event_id

    So I tried this here:
    DELETE FROM date, events WHERE date.date=$yesterday AND date.event_id=events.event_id
    but it doesn't work too. I can not make several querys here as for the "DELETE FROM events" query I don't know what to delete, as this information (the date of the event) is in the [date] table.

  4. #4
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I'm not sure if I follow, but it would seem you have to do something like this. that's if I understand what you are trying to delete...

    1) Do a SELECT query on table date to work out which events you need to delete from the database.
    "
    $sql = SELECT event_id FROM dates WHERE date= $yesterday";
    $result = mysql_query($sql);

    2) loop through this result set, and for each event that need to be deleted execute an SQL query to delete that event from the events table.

    while ( $event = mysql_fetch_array($result)) {

    $sql = "DELETE FROM events WHERE event_id = $event["event_id"]";
    mysql_query($sql);
    }

    3) When that's all done, do a delete query to delete all rows for yesterday from your dates table.
    $sql = "DELETE FROM date WHERE date = $yesterday;
    mysql_query($sql);

    I know its not simple or pretty - but like I said, this would all be avoided if MySQL supported foreign keys and cascade update and delete.

  5. #5
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, this helped me. Thank you

  6. #6
    SitePoint Addict
    Join Date
    Feb 2001
    Location
    Shanghai, China
    Posts
    214
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Still have a problem get it working. I get this error message:

    Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in ..... on line 22

    where line 22 is this here:

    $sql = "DELETE FROM events WHERE event_id = $event["event_id"]";

    The rest works fine, I checked it.
    Any idea, thanx, Alex

  7. #7
    ********* Callithumpian silver trophy freakysid's Avatar
    Join Date
    Jun 2000
    Location
    Sydney, Australia
    Posts
    3,798
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Oh yeah, sorry, the quote marks around "event_id" cause a parse error on the string.

    $sql = "DELETE FROM events WHERE event_id = $event[0]";

    That will work.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •