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

[astralkid]

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]Delete[/php]

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:

[php]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';
    }
}
};[/php]

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.

[Jkwakkel]

Why do you have the select statement there? because directly after setting $query to the select statement your overwrite it with the delete statement. Also I doubt that categories.id is the same as posts.id. I guess in posts you have some table with the category id, so it should be ON categories.id = categoriesId";

Anyhow first update the posts

UPDATE posts SET categoriesId = 1 WHERE categoriesId= $id;

How I would write your select statement:

SELECT p.id, c.id FROM posts p INNER JOIN categories c ON c.id = p.id however I guess it has to be c.id = p.categoriesId?. This query would also give you all the posts and not just those you trying to change.

The delete I would write:

DELETE FROM $table WHERE id = $id

To write the function for you I would need more info on the database, like does the post have the id of the category and how is it called.