Deleting records

**minds_gifts** · 04-20-2003, 06:03 AM

Hello everybody,

I'm looking for some advancement to one of my scripts written for deleting records.I've got 3 tables: topics, subtopics and articles.Under each topic there can be many subtopics and under each subtopic there are many articles.
I want to make three forms where I would be able to delete topics, subtopics and articles.

As of now, I've done in this way:
1.I just display all the topic names each with a check box and all the checked boxes will be deleted using the delete button.
2.I list all the topics and when i select a topic, i show all the subtopics related to the topic and rest is the same as above.
3.Select a topic from the list box, it then gets me all the subtopics in other list box, select a subtopic now, I get all the articles.

With this , i'm able to delete the records from the respective tables.

I want to make it secure, that is, If the user tries to delete a topic, I need to check if there are any subtopics exsisting.If yes, then it has to show me a mesg.If no, then the topic can be deleted.
Similarly with subtopics, I want to make sure if there are any articles exsisting for this subtopics.

How can I make a look up.

Could somebody please help me out.

Many thanks

**QuietDean** · 04-20-2003, 06:17 AM

Hiya.

You don't say what you are using, mySQL, Access?

Presumably, (using articles as an example) each article has a column telling it what subforum it belongs to. So (using article_subforum as the name of the column) if I count the articles like so -

Code:

select count(*) where article_subforum='whateverid'

and of course, if it returns anything higher than 0, then you can use an if statement to output your error instead of the deletion query.

**minds_gifts** · 04-20-2003, 09:41 AM

Hello dean,

I'm using mysql.

I could'nt really get you.Well, here is the code which I'm using in order to delete sub-topics.I'm getting the list of topics and then select a topic and then list all the sub-topics related to that topic.
Could you please explain me further.
My tables look like follows:

1.topics(topic_id, topic_name)
2.subtopics(subtopic_id, topic_id, subtopic_name)
3.articles(article_id, subtopic_id, topic_id, article_name and so on)

PHP Code:


<?

include "../dbconnection.dam";

?>



<?php 

// if you have chosen subtopics to delete 

if(isset($HTTP_POST_VARS['action'])){ 

   $qry3="SELECT SUBTOPIC_ID FROM subtopic WHERE SUBTOPIC_ID='".$HTTP_POST_VARS['SUBTOPIC_ID']."'"; 

   $result_3=mysql_query($qry3) or die(mysql_error()); 

// begin the query  

   $qry4 = "DELETE FROM subtopic WHERE SUBTOPIC_ID IN('" . implode("','", $HTTP_POST_VARS['to_delete']) . "')";



   while($row_4=mysql_fetch_row($result_3)){ 

      if(isset($HTTP_POST_VARS[$row_4->SUBTOPIC_ID])){  

// the checkboxes were named for the subtopic_id 

         $qry4 .= "OR SUBTOPIC_ID=".$row_4->SUBTOPIC_ID; 

// add this subtopic to the query string 

      } 

   } 

   mysql_query($qry4) or die(mysql_error()); 

} 



// get the list of topic names 

$qry1="SELECT TOPIC_ID, TOPIC_NAME FROM topic ORDER BY TOPIC_NAME"; 

$result_1=mysql_query($qry1) or die(mysql_error()); 



// if you have chosen a topic, get the list of subtopics 

if(isset($HTTP_POST_VARS['TOPIC_ID'])){ 

   $qry3="SELECT SUBTOPIC_ID, SUBTOPIC_NAME, TOPIC_ID FROM subtopic WHERE TOPIC_ID='".$HTTP_POST_VARS['TOPIC_ID']."' ORDER BY SUBTOPIC_NAME"; 

   $result_3=mysql_query($qry3) or die(mysql_error()); 

} 



?> 



<HTML> 

<!--- create the topic select box ---> 

<form name="topic_form" action="<?=$PHP_SELF?>" method="post"> 

   <select name="TOPIC_ID"> 

      <option value="">Choose a topic</option> 

<?



    while($row=mysql_fetch_object($result_1)){ 

    echo "<option value=\"".$row->TOPIC_ID."\">".$row->TOPIC_NAME."</option>"; 

      }

?> 

   </select> 

   <input type="submit" value="Set Topic"> 

</form> 

 

   <!--- include topic_ID for continuity ---> 

   <input type="hidden" name="TOPIC_ID" value="<?=$HTTP_POST_VARS['TOPIC_ID']?>"> 

   

</form> 



<!--- create the list of subtopics ---> 

<form name="delete_form" action="<?=$PHP_SELF?>" method="post"> 

<?

   if(isset($HTTP_POST_VARS['TOPIC_ID'])){ 

   while($row_3=mysql_fetch_object($result_3)){ 

// subtopics named for the subtopic_id (aids in ease of retrieval after submittal 

  echo "<input type=\"checkbox\" name=\"to_delete[]\" value=\"".$row_3->SUBTOPIC_ID."\">".$row_3->SUBTOPIC_NAME."<BR>";





      } 

   }

?> 



   <input type="hidden" name="TOPIC_ID" value="<?=$HTTP_POST_VARS['TOPIC_ID']?>"> 

  

<!--- "action" defined so there is a definite switch to determine if things should be deleted ---> 

   <input type="hidden" name="action" value="delete"> 

   <input type="submit" value="Delete"> 

<FORM>

<INPUT TYPE="Button" VALUE="Back" 

onClick="window.location= 'menu.php' "> 

</FORM>

</form> 

</HTML>

Now, I only want to check if there are any articles corresponding to this subtopic_id which I marked for deletion.
If there are, then a mesg box else deleted.

Many thanks

**QuietDean** · 04-20-2003, 01:03 PM

hiya.

basically, the sql snippet I gave returns a count of matching records. so, in other words, use the count to see if there are any sub_topics whose topic_id is equal to 'to_delete'. If it returns 0, go ahead and delete. Anything else, cheeky message comes up.

Have a play with count(*). its mosr useful.

**minds_gifts** · 04-22-2003, 07:13 AM

I used sql_num_rows in-order to see if it returns any value and then i can go for deleting.
When I try to delete the subtopics though there are actually articles present under them, they get deleted and when there are no articles, it gets deleted too.

I tried to debug, it always returns that there are no articles under the subtopic which I've marked for deletion.But, there are articles under that sub-topic.

I de-bugged in a different way, like giving the sub-topic id no directly and it worked, that is, it has shown me the number if there are articles present or not.

This is the query which I used to check if there are any articles present or not

PHP Code:


$result_sub=mysql_query("SELECT * FROM articles WHERE SUBTOPIC_ID = 2");

   $number = mysql_num_rows($result_sub); 

   echo("$number");

This is my actual script.I first list all topics and select a topic and it shows me all sub-topics with a check box.I check the checkbox and click the button delete.It has to be deleted only if there are no articles and if it has, I need to show some message.

Could somebody please help me out in-order to fix my script.

Thanks in advance

PHP Code:


<?

//Db connection



include "dbconnection.oam";

?>



<?php 

// if you have chosen subtopics to delete 

if(isset($HTTP_POST_VARS['action'])){ 

   $qry3="SELECT SUBTOPIC_ID FROM subtopic WHERE SUBTOPIC_ID='".$HTTP_POST_VARS['SUBTOPIC_ID']."'"; 

   $result_3=mysql_query($qry3) or die(mysql_error()); 

// begin the query  

   $subtopic_id=$HTTP_POST_VARS['SUBTOPIC_ID'];

   echo $subtopic_id; 

   $result_sub=mysql_query("SELECT * FROM articles WHERE SUBTOPIC_ID='$subtopic_id' ");

   $number = mysql_num_rows($result_sub); 

   echo("$number"); 





   $qry4 = "DELETE FROM subtopic WHERE SUBTOPIC_ID IN('" . implode("','", $HTTP_POST_VARS['to_delete']) . "')";



   while($row_4=mysql_fetch_row($result_sub)){ 

      if(isset($HTTP_POST_VARS[$row_4->SUBTOPIC_ID])){  

// the checkboxes were named for the subtopic_id 

         $qry4 .= "OR SUBTOPIC_ID=".$row_4->SUBTOPIC_ID; 

// add this subtopic to the query string 

      } 

   } 

   mysql_query($qry4) or die(mysql_error()); 

}





// get the list of topic names 

$qry1="SELECT TOPIC_ID, TOPIC_NAME FROM topic ORDER BY TOPIC_NAME"; 

$result_1=mysql_query($qry1) or die(mysql_error()); 



// if you have chosen a topic, get the list of subtopics 

if(isset($HTTP_POST_VARS['TOPIC_ID'])){ 

   $qry3="SELECT SUBTOPIC_ID, SUBTOPIC_NAME, TOPIC_ID FROM subtopic WHERE TOPIC_ID='".$HTTP_POST_VARS['TOPIC_ID']."' ORDER BY SUBTOPIC_NAME"; 

   $result_3=mysql_query($qry3) or die(mysql_error()); 

} 



?> 



<HTML> 

<!--- create the topic select box ---> 

<form name="topic_form" action="<?=$PHP_SELF?>" method="post"> 

   <select name="TOPIC_ID"> 

      <option value="">Choose a topic</option> 

<?



    while($row=mysql_fetch_object($result_1)){ 

    echo "<option value=\"".$row->TOPIC_ID."\">".$row->TOPIC_NAME."</option>"; 

      }

?> 

   </select> 

   <input type="submit" value="Set Topic"> 

</form> 

 

   <!--- include topic_ID for continuity ---> 

   <input type="hidden" name="TOPIC_ID" value="<?=$HTTP_POST_VARS['TOPIC_ID']?>"> 

   

</form> 



<!--- create the list of subtopics ---> 

<form name="delete_form" action="<?=$PHP_SELF?>" method="post"> 

<?

   if(isset($HTTP_POST_VARS['TOPIC_ID'])){ 

   while($row_3=mysql_fetch_object($result_3)){ 

// subtopics named for the subtopic_id (aids in ease of retrieval after submittal 

  echo "<input type=\"checkbox\" name=\"to_delete[]\" value=\"".$row_3->SUBTOPIC_ID."\">".$row_3->SUBTOPIC_NAME."<BR>";





      } 

   }

?> 



   <input type="hidden" name="TOPIC_ID" value="<?=$HTTP_POST_VARS['TOPIC_ID']?>"> 

  

<!--- "action" defined so there is a definite switch to determine if things should be deleted ---> 

   <input type="hidden" name="action" value="delete"> 

   <input type="submit" value="Delete"> 

<FORM>

<INPUT TYPE="Button" VALUE="Back" 

onClick="window.location= 'menu.php' "> 

</FORM>

</form> 

</HTML>

**QuietDean** · 04-22-2003, 11:54 AM

The only real difference that springs to mind from your working, debug script is that it is not enscapulating the 2 with ' symbols.

Try removing those from the script proper.

Thread: Deleting records

Thread Tools

Related

Deleting One Site? Forum: Myspace Forum

78 rpm records Forum: General Discussion

Deleting MySQL Forum: PHP Forum

Deleting records

Tags for this Thread

Cloudjiffy- PaaS for Developers