从许多表中删除数据的正确方法是什么？

When you want to delete a category - it will first delete all the related products with the related groups and attributes. A product have many relationships.

This is how it deal when deleting a category:

if (isset($_POST['catid']) && $_POST['catid'] > 0) {

    $category_id = mysql_real_escape_string($_POST['catid']);

    $SQL = "SELECT * FROM products WHERE category_id = '$category_id'";
    $q_item = mysql_query($SQL);

    while ($dItem = mysql_fetch_assoc($q_item)) {
        $itemid = $dItem['id'];

        $SQL = "SELECT * FROM option_groups WHERE item_id = " . $itemid;
        $q = mysql_query($SQL);

        while ($option_group = mysql_fetch_assoc($q)) {
            $optionGroup = $option_group['id'];

            $SQL = "SELECT * FROM options WHERE option_group_id = " . $optionGroup;
            $q = mysql_query($SQL);
            while ($row = mysql_fetch_assoc($q)) {
                $option = $row['id'];
                mysql_query("DELETE FROM options WHERE id = " . $option);
                mysql_query("DELETE FROM e_groups_options WHERE option_id = " . $option);
            }

            mysql_query("DELETE FROM option_groups WHERE item_id = " . $itemid);
            mysql_query("DELETE FROM products WHERE id = " . $itemid);
        }
    }

    $SQL = "DELETE FROM categories WHERE id = '$category_id'";
    mysql_query($SQL);

}

You can see, I have used included many DELETE query and the code look a bit messy, is there alternative way and safer?

Storage Engine is MyISAM and I have over 100,000 rows of data in the tables (fields are indexed).

If these tables were related with a foreign key, you could specify ON DELETE CASCADE in the foreign key and just delete the Category. You'll have to switch to InnoDB to be able to apply this. Citing the documentation on foreign keys:

For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it

Take into account that cascaded actions do not fire triggers.

You can switch your tables to InnoDB with ALTER TABLE table_name ENGINE = InnoDB. You can add a foreign key with the ALTER TABLE statement, for instance:

ALTER TABLE products
ADD FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE CASCADE;

With this foreign key, whenever you issue a DELETE statement on categories, the RDBMS will also delete the products referencing this category.

I think the best way to work here is to use a storage engine supporting foreign key constraints (like InnoDB) and let it handle this via ON DELETE CASCADE so the DBMS will do the work for you.

MyISAM does not support foreign keys so far.

If you do not want to or can't change your database engine to innodb, you can do deletes over multiple tables with join:

DELETE t1, t2, t3
FROM tabel1 AS t1
LEFT JOIN table2 AS t2 ON t1.id = t2.pid
LEFT JOIN table3 AS t3 ON t2.id = t3.pid
WHERE t1.id = $id

If there is a way to join all the tables from which you want to delete rows, you can adapt your code to execute a single query like this.

Since you are using MyISAM and cannot do a cascading delete, you could simply do a joined delete.

DELETE table1, table2 FROM table1 LEFT JOIN table2 ON table1.id = table2.t1_id WHERE table1.id = $id

An answer has already been accepted, but for interest: if you are stuck on a database that doesn't do cascading deletes, you could use an ORM such as Propel. I believe that can be configured to do cascading deletes manually where it is not supported by the db natively.