SitePoint Sponsor

User Tag List

Results 1 to 6 of 6

Hybrid View

  1. #1
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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:
    Code:
    Code 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:

    Code:
    Code 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';
            }
        }
        };
    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. #2
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    You could do it with a second SQL query:
    Code:
    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-forei...e-development/

  3. #3
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #5
    SitePoint Wizard
    Join Date
    Dec 2003
    Location
    USA
    Posts
    2,582
    Mentioned
    29 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Member
    Join Date
    Feb 2012
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oops.. yeah makes sense.. haha.. thanks.. will try it out ...


Tags for this Thread

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
  •