StateRd84 Posted May 27, 2012 Share Posted May 27, 2012 I am still very new to php, so keep this in mind. This example comes from a tutorial. I have a database with two tables: categories table; 2 fields: `id` and `name`.posts table; 5 fields: `id`, `cat_id`, `title`, `content` and `post_date`. If I have a post entered that uses, for example, category id: 3, name: fun. I then delete that category from the categories table. The post remains with the cat_id of 3 (since it's relational). Since the id column in the categories table is auto-increment the id of 3 is never used again, so does not exist in the categories table. The problem is that that record cannot be accessed with the current code. Link to comment Share on other sites More sharing options...
niche Posted May 27, 2012 Share Posted May 27, 2012 Please post your code and tell us how you need to access your tables. Link to comment Share on other sites More sharing options...
Krewe Posted May 27, 2012 Share Posted May 27, 2012 (edited) If i understand your problem correctly...You are wanting to delete the posts that were with the category you just deleted, no?So just have two queries, (granted I don't know how you are telling the server/database which category to delete): $id = $_GET['id'];$query = "DELETE FROM categories WHERE id = $id";$resultSet = mysql_query($query,$connection);$query2 = "DELETE FROM posts WHERE cat_id = $id";$resultSet2 = mysql_query($query,$connection); That code is assuming you are getting the id of the category from a link.But that will delete the category, then delete any posts associated with that category. Edited May 27, 2012 by Krewe Link to comment Share on other sites More sharing options...
StateRd84 Posted May 27, 2012 Author Share Posted May 27, 2012 I would like to be able to show the post that no longer has a related category. Remember that the id in the categories table no longer exists, however the related cat_id in the posts table is still there. Here is the relevent code the first is the function used to get the posts: function get_posts($id = null, $cat_id = null) { $posts = array(); $query = "SELECT `posts`.`id` AS `post_id`, `categories`.`id` AS `category_id`, `title`, `content`, `date_posted`, `categories`.`name` FROM `posts` INNER JOIN `categories` ON `categories`.`id` = `posts`.`cat_id`"; if ( isset($id) ) { $id = (int) $id; $query .= " WHERE `posts`.`id` = '{$id}'"; echo mysql_error(); } if ( isset($cat_id) ) { $cat_id = (int) $cat_id; $query .= " WHERE `categories`.`id` = '{$cat_id}'"; } $query .= " ORDER BY `post_id` DESC"; $query = mysql_query($query); while ( $row = mysql_fetch_assoc($query) ) { $posts[] = $row; } return $posts;} The next is the page used to display the posts: <?php include_once('resources/init.php'); $posts = ( isset($_GET['id']) ) ? get_posts($_GET['id']) : get_posts();?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"><html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en"><head> <style> ul { list-style-type: none; } li { display: inline; margin-right: 20px; } </style> <title>My Big Blog</title></head><body> <div> <ul> <li><a href="index.php"> Index </a></li> <li><a href="add_post.php"> Add Post </a></li> <li><a href="add_category.php"> Add Category </a></li> <li><a href="category_list.php"> Category List </a></li> </ul> </div><!--End of Nav division--> <div> <h1>My Big Blog</h1> <?php foreach ( $posts as $post ) { ?> <h2><a href="index.php?id=<?php echo $post['post_id']; ?>"><?php echo $post['title']; ?></a></h2> <p>Posted On: <?php echo date('m-d-Y h:i:s', strtotime($post['date_posted']) ); ?> in <a href="category.php?id=<?php echo $post['category_id']; ?>"><?php echo $post['name']; ?></a></p> <div> <?php echo nl2br($post['content']); ?> </div> <div> <ul> <li><a href="delete_post.php?id=<?php echo $post['post_id']; ?>">Delete This Post</a></li> <li><a href="edit_post.php?id=<?php echo $post['post_id']; ?>">Edit This Post</a</li> </ul> </div> <?php } ?> </div></body></html> Link to comment Share on other sites More sharing options...
boen_robot Posted May 27, 2012 Share Posted May 27, 2012 It would be a lot more efficient if you use foreign keys. See this tutorial for details on how to apply them.P.S. You'll probably need the SET NULL action, and this also means making the rest of your app aware that NULL means "no category". Link to comment Share on other sites More sharing options...
StateRd84 Posted May 27, 2012 Author Share Posted May 27, 2012 It would be a lot more efficient if you use foreign keys. Can you change the engine on an existing database? Link to comment Share on other sites More sharing options...
boen_robot Posted May 27, 2012 Share Posted May 27, 2012 Engines are per table... and you can switch the engine like ALTER TABLE `posts` ENGINE = InnoDB; Link to comment Share on other sites More sharing options...
justsomeguy Posted May 28, 2012 Share Posted May 28, 2012 You can also use a left join on that query to get all posts regardless of whether or not they're in a category. In that case the fields from the category table would be set to null. Link to comment Share on other sites More sharing options...
Recommended Posts
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now