SitePoint Sponsor

User Tag List

Results 1 to 23 of 23
  1. #1
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Check the validation before deleting records

    hi there,
    This is a simple script which I'm using to delete subtopics.
    Well, I have Topics and under each topic there are many subtopics and under each subtopic there are many articles.
    First, I'm getting all the topic names in a list box, select a topic and then all the subtopics related to that topic are shown.
    Now, when i check a subtopic and hit the button delete the subtopics can be deleted.But, before that, I want to check if there are any articles exsisting under this subtopic which I've chosen for deletion.
    So, if there are any articles under this subtopic, i need to show a mesg that they cant be deleted and if there are no articles they can be deleted.
    My problem is only to check the last part, that is in-order to check if there are any articles or not.

    Could somebody of you please take a look and tell me what would be the error.

    Thanks in advance
    Code:
    <?
    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' ");
    if (mysql_num_rows($result_sub)>0)
    // echo "there is more than zero";
    // else
    // echo "there are none";
    $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> 
    
    Last edited by chinni; Apr 25, 2003 at 06:25.

  2. #2
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Could you post your table schema (structure)? Where in that piece of code did you do the checking for existing articles (sorry, it's a little difficult to read)?

  3. #3
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Three tables:
    1.Topic(topic_id, topic_name)
    2.subtopic(subtopic_id,topic_id,subtopic_name)
    3.articles(article_id,subtopic_id,topic_id,article_name)

    This piece of code checks if there are any articles in the articles table for that subtopic_id which I've checked for deletion.
    Code:
    // 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' "); if 
    (mysql_num_rows($result_sub)>0) 

  4. #4
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    How does that piece of code fail to work? (What errors? What happens that makes you know that it doesn't work?)

    It seems like it should work.

  5. #5
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I did some de-bugging in this way:

    I commented out my delete query and try to check if $result_sub is returning me any value.

    I checked the box and hit the button delete, it always shows me there are none.There are articles actually articles present under this subtopic_id which I marked for deletion.

    why is this query failing???


  6. #6
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    $result_sub mysql_query("SELECT COUNT(*) FROM articles WHERE subtopic_id='$subtopic_id' ");
    if ( 
    mysql_result$result_sub) > ) {
      echo 
    'There are existing articles under this sub-topic';

    Try echoing your query.

    Is subtopic_id a numeric or string field? If numeric, removing the single quotes.

  7. #7
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm...it says the query is empty:-(

  8. #8
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That can't be.

    Post what you are using now.

  9. #9
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    <?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'];
    $result_sub = mysql_query("SELECT COUNT(*) FROM articles WHERE subtopic_id='$subtopic_id' " ); 
    if ( mysql_result( $result_sub, 0 ) > 0 ) { 
    echo 'There are existing articles under this sub-topic'; 
    } 
    $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()); 
    } 
    ?>


  10. #10
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK I understood what you're doing up to here (I made a few changes so it looks a bit cleaner.

    PHP Code:
    <?php// if you have chosen subtopics to delete 
    if(isset($HTTP_POST_VARS['action'])){ 

        
    $subtopic_id $HTTP_POST_VARS['SUBTOPIC_ID'];
        
    $qry3 "SELECT SUBTOPIC_ID FROM subtopic WHERE SUBTOPIC_ID='$subtopic_id'"
        
    $result_3 mysql_query($qry3) or die(mysql_error()."<br />SQL: $qry3"); 

        
    // begin the query
        
    $sql "SELECT COUNT(*) FROM articles WHERE subtopic_id='$subtopic_id'";
        
    $result_sub mysql_query($sql) or die(mysql_error()."<br />SQL: $sql"); 
        if ( 
    mysql_result$result_sub) > ) {
            
    // there are existing articles
            
    echo 'There are existing articles under this sub-topic'
        } 
        else {
            
    // there are no articles - safe to delete
            
    $qry4 "DELETE FROM subtopic WHERE SUBTOPIC_ID IN('" implode("','"$HTTP_POST_VARS['to_delete']) . "')";
    For this part:
    PHP Code:


            
    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()); 
    you lost me - what are you trying to accomplish here? Also, note that $result_sub is now the resultset for the query SELECT COUNT(*) FROM articles WHERE subtopic_id='$subtopic_id'", so you can't use it for anything besides getting the count.

  11. #11
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I lost myself now.
    Do you find anything wrong now here?
    Code:
    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 
    } 
    } 
    
    I corrected my while st, i think it has to be $result_3.

    Even this does'nt work out to me now:-(

  12. #12
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
    <?php// if you have chosen subtopics to delete 
    if(isset($HTTP_POST_VARS['action'])){ 

        
    $subtopic_id $HTTP_POST_VARS['SUBTOPIC_ID'];
        
    $qry3 "SELECT SUBTOPIC_ID FROM subtopic WHERE SUBTOPIC_ID='$subtopic_id'";
        echo 
    'Performing query: '$qry3;
        
    $result_3 mysql_query($qry3) or die(mysql_error()."<br />SQL: $qry3"); 

        
    // begin the query
        
    $sql "SELECT COUNT(*) AS count FROM articles WHERE subtopic_id='$subtopic_id'";
        echo 
    'Performing query: '$sql;
        
    $result_sub mysql_query($sql) or die(mysql_error()."<br />SQL: $sql"); 
        if ( 
    mysql_result$result_sub0'count' ) > ) {
            
    // there are existing articles
            
    echo 'There are existing articles under this sub-topic'
        } 
        else {
            
    // there are no articles - safe to delete
            
    $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 
                

            }
            echo 
    'Performing query: '$qry4;
            
    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()); 

    ?>
    OK try this and see how it goes. Post the output.

  13. #13
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    OK, I found something after debugging now.
    I tried to give a subtopic id and check if its returning any value.Yes, it is showing me there are some articles under this subtopic id, thats fine!
    Code:
    $sql = "SELECT COUNT(*) FROM articles WHERE SUBTOPIC_ID = 34 "; 
    
    Now, I tried with another subtopic id where are there are no articles.But, it still gives me there are articles present.Thats pittiful!

    The reason what I found from my following code is:
    Code:
    // the checkboxes were named for the subtopic_id 
    $qry4 .= "OR SUBTOPIC_ID=".$row_4->SUBTOPIC_ID; 
    
    that the check boxes are named for the subtopic_id.

    Is this wrong???or would be any other error.

    Please help me!!

    Thanks once again

  14. #14
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Since subtopic_id is an integer column, remove the single quotes around it in the query.

    Try with a fixed subtopic_id that you know the number of articles of, and echo the number of articles to see if it matches.

    Do this, in other words:
    PHP Code:
    echo mysql_result$result_sub0'count' ); 

  15. #15
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Joel,
    This is what it showed on the screen.

    Performing query: SELECT SUBTOPIC_ID FROM subtopic WHERE SUBTOPIC_ID=''Performing query: SELECT COUNT(*) AS count FROM articles WHERE subtopic_id=''Performing query: DELETE FROM subtopic WHERE SUBTOPIC_ID IN('34')

    The subtopic is being deleted but there are articles under it.

  16. #16
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Code:
    Performing query: SELECT SUBTOPIC_ID FROM subtopic WHERE SUBTOPIC_ID=''
    Here is your problem - seems like $HTTP_POST_VARS['SUBTOPIC_ID'] is not correctly being the POSTed subtopic_id.

    You do have a form field with name="SUBTOPIC_ID", don't you? And the form method="post"?

  17. #17
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hmm....I dont think so.

    Is this the one which is really troubling me.
    Code:
    // 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>";
    

  18. #18
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    You mean you don't think so that is the problem, or you dont think so that you have a form field with name="SUBTOPIC_ID"?

  19. #19
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I dont think theres a form field with the name SUBTOPIC_ID.thats for sure!
    SUBTOPIC_ID(in block letters) is my column name in both the tables.

  20. #20
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well then you need one!

    Otherwise, how are you going to let the user submit the SUBTOPIC_ID of the subtopic that he wants to delete?

  21. #21
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, this is gonna do that work!
    Code:
    if(isset($HTTP_POST_VARS['action'])){ 
    $subtopic_id = $HTTP_POST_VARS['SUBTOPIC_ID']; 
    
    and at the end, i got this one:
    Code:
    <input type="hidden" name="action" value="delete"> 
    <input type="submit" value="Delete"> 
    
    its really pittiful that i aint figure it out still, huh

    I'm glad that you spent lots of time on my script.

  22. #22
    SitePoint Wizard silver trophy redemption's Avatar
    Join Date
    Sep 2001
    Location
    Singapore
    Posts
    5,269
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yeah I know that will check for 'action' being set, meaning the form has been submitted, but where is 'SUBTOPIC_ID' going to come from?

    You may want to look for a tutorial on how to do form-processing with PHP. It should help you quite a bit.

  23. #23
    SitePoint Enthusiast
    Join Date
    May 2002
    Posts
    79
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    My checkboxes are named with the SUBTOPIC_ID.
    Sorry, If I'm wrong.I'm dumb now and my mind is bogged.
    could you please add that line.If you cant, no probs.

    thanks a lot


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •