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

Where to find skilled freelance designers?

photoshop

Photoshop or Sketch?

Is Directory Submissions still an effective way for link...

How to get PR for website?

What is Google Admob?