29 users online (1 members and 28 guests)  


  Results 1 to 6 of 6

Related

  1. Deleting One Site?    Forum: Myspace Forum
    Replies: 3
  2. 78 rpm records    Forum: General Discussion
    Replies: 2
  3. Deleting MySQL    Forum: PHP Forum
    Replies: 1
  1. #1
    minds_gifts's Avatar
    New User

    Status
    Offline
    Join Date
    Apr 2003
    Posts
    15

    Deleting records

    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

  2. #2
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    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.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  3. #3
    minds_gifts's Avatar
    New User

    Status
    Offline
    Join Date
    Apr 2003
    Posts
    15
    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

  4. #4
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    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.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?

  5. #5
    minds_gifts's Avatar
    New User

    Status
    Offline
    Join Date
    Apr 2003
    Posts
    15
    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>

  6. #6
    QuietDean's Avatar
    Administrator

    Status
    Offline
    Join Date
    Oct 2000
    Location
    Bournemouth, UK
    Posts
    2,662
    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.
    If one of our members helps you, please click the icon to add to their reputation!
    No support via email or private message - use the forums!
    Before you ask, have you Searched?



Tags for this Thread