Jump to content

Delete from DB problem


StateRd84
 Share

Recommended Posts

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

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 by Krewe
Link to comment
Share on other sites

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

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

Engines are per table... and you can switch the engine like

ALTER TABLE `posts` ENGINE = InnoDB;

Link to comment
Share on other sites

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...