Php/sql JOIN help? trying to join 2 tables to be deleted

PHP
1

I’m having troubles with my blog…
I have 2 tables:

  1. posts(id, cat_id, title, contents, date_posted)
  2. categories(id, name),

and to delete the category:


```php
<a href="delete_category.php?id=<?php echo $category['id']; ?>">Delete</a>



if i view the categories and try to delete one from my cat_list.php page, it deletes the category from the list, but in my posts table, the cat_id assigned to the deleted category is still there... is there a way to, upon deleting a category, update all cat_id's from all posts and set the value to '1' (Uncategorized) or something like that? Right now if I delete a category, the post still sits there with that cat_id still assigned to it.

Here is my delete function:



 function delete($table, $id) {
        if (isset($_GET['id']) && is_numeric($_GET['id'])) {
            $table = mysql_real_escape_string($table);
            $id = (int) $id;

            $query = "SELECT
                            `posts`.`id`,
                            `categories`.`id`,
                            FROM `posts`
                                INNER JOIN {`categories`}
                            ON `categories`.`id` = `posts`.`id`";

        $query = "DELETE FROM `{$table}` WHERE `id` = '{$id}'";

        if(mysql_query($query)) {
            echo 'Ok';
        } else {
            echo 'Not Ok';
        }
    }
    }; 



I've been playing around with joins and what not but I am unsure as what to use to fix this issue. I'm not real sure what the best way to go about doing this would be. I know my sql syntax isn't correct, and I'm probably doing it wrong, but just by playing around with the code, I'm stuck :/

Any and all help is appreciated.
2

You could do it with a second SQL query:


UPDATE posts SET cat_id = 1 WHERE cat_id = $cat_id

You could also modify the table itself to give it a foreign key with ON DELETE SET NULL. This would set it to NULL (not 1), but may still serve your purposes. If you aren’t using InnoDB, you could try ON DELETE SET DEFAULT as well, and make the default cat ID be 1.

Here is an article about how to set up foreign tables: http://www.sitepoint.com/mysql-foreign-keys-quicker-database-development/

3

ok will check into the foreign key… i thought about using a 2nd update query, but a) no idea if my original query is correct or even if it is doing what it s supposed to… and b) the row in the posts table isn’t changed at all with the cat id… i guess i need an update for that to work first… i’m still trying to understand the joins syntax and whatnot… is the original query doing anything? or how should i state it?

4

Well I changed over to InnoDB, tried running this:

CREATE TABLE IF NOT EXISTS categories (
id int(3) NOT NULL AUTO_INCREMENT,
name varchar(24) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY name (name),
CONSTRAINT FK_id FOREIGN KEY (id) REFERENCES posts(cat_id) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB;

CREATE TABLE IF NOT EXISTS posts (
id int(3) NOT NULL AUTO_INCREMENT,
cat_id int(3) NOT NULL,
title varchar(255) NOT NULL,
contents text NOT NULL,
date_posted datetime NOT NULL DEFAULT ‘0000-00-00 00:00:00’,
KEY ix_posts_cat_id (cat_id),
PRIMARY KEY (id)
) ENGINE=InnoDB;

but it’s giving me an error about the foreign key needing to be referenced to INDEX or something, so i tried indexing cat_id but it’s just giving me the mysql 150 error or something… i’m confused haha…

5

You have it backwards. The foreign key should be in the posts table. The id in your categories table is not a foreign key, it’s just a regular key. The cat_id field in your posts table is not a key at all, so you need to mark it as being a foreign key.

6

oops… yeah makes sense… haha… thanks… will try it out …