Page 1 of 1

To delete records after a condition is satisfied

Posted: Sat Apr 26, 2003 2:45 pm
by minds_gifts
Hello everybody,

I've written a PHP script in-order to delete the records from a table, but before they get actually deleted I want to check one condition.I've been struggling with this script since 2 days and I'm not able to fix it.
I've written one more script for the same functionality and this also driving me nuts.I would be really glad if somebody can help me.

Thanks in advance

My first script

Code: Select all

<?
include "dbconnection.oam";
?>

<?php 
// if you have chosen subtopics to delete 
    if(isset($HTTP_POST_VARS&#1111;'action']))&#123; 

    $subtopic_id = $HTTP_POST_VARS&#1111;'SUBTOPIC_ID']; 
    $qry3 = "SELECT TOPIC_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(*) AS 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, 0, 'count' ) > 0 ) &#123; 
 	echo 'There are articles existing under this sub-topic'; 
    &#125; 
    else &#123; 

// there are no articles - safe to delete 
   $qry4 = "DELETE FROM subtopic WHERE subtopic_id IN('" . implode("','", $HTTP_POST_VARS&#1111;'to_delete']) . "')"; 
    while( $row_4 = mysql_fetch_row($result_3) )&#123; 
   if( isset($HTTP_POST_VARS&#1111;$row_4->SUBTOPIC_ID]) )&#123; 

// the checkboxes were named for the subtopic_id 
   $qry4 .= "OR SUBTOPIC_ID=".$row_4->SUBTOPIC_ID; 

// add this subtopic to the query string 
    &#125; 
        &#125; 

   mysql_query($qry4) or die(mysql_error()); 
    &#125; 
&#125; 

// 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&#1111;'TOPIC_ID']))&#123; 
$qry3="SELECT SUBTOPIC_ID, SUBTOPIC_NAME, TOPIC_ID FROM subtopic WHERE TOPIC_ID='".$HTTP_POST_VARS&#1111;'TOPIC_ID']."' ORDER BY SUBTOPIC_NAME"; 
$result_3=mysql_query($qry3) or die(mysql_error()); 
&#125; 
?> 

<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))&#123; 
    echo "<option value="".$row->TOPIC_ID."">".$row->TOPIC_NAME."</option>"; 
      &#125;
?> 
   </select> 
   <input type="submit" value="Set Topic"> 
</form> 
 
   <!--- include topic_ID for continuity ---> 
   <input type="hidden" name="TOPIC_ID" value="<?=$HTTP_POST_VARS&#1111;'TOPIC_ID']?>"> 
   
</form> 

<!--- create the list of subtopics ---> 
<form name="delete_form" action="<?=$PHP_SELF?>" method="post"> 
<?
   if(isset($HTTP_POST_VARS&#1111;'TOPIC_ID']))&#123; 
   while($row_3=mysql_fetch_object($result_3))&#123; 
// subtopics named for the subtopic_id (aids in ease of retrieval after submittal 
  echo "<input type="checkbox" name="to_delete&#1111;]" value="".$row_3->SUBTOPIC_ID."">".$row_3->SUBTOPIC_NAME."<BR>";


      &#125; 
   &#125;
?> 

   <input type="hidden" name="TOPIC_ID" value="<?=$HTTP_POST_VARS&#1111;'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>
For the above code, I list all the topic names and then i press a button in-oder to show all the subtopic names.Now, I check a box and press the button delete.If there are no articles under the subtopic then the subtopic can be deleted else it has to show me a mesg.Now, when i run this script, ir-respective if the articles present or not, I get the sub-topics deleted.

I tried to debug giving the subtopic_id directly in the following query:

Code: Select all

$sql = "SELECT COUNT(*) AS count FROM articles WHERE SUBTOPIC_ID='$subtopic_id'";
I checked for two cases:
case1: tried to delete the subtopic when there are no articles in it, it is deleted.
case2: tried to delete the subtopic when there are articles and it has showed me a mesg.

But, why does the whole script kicks me off with $subtopic_id.

Please help me.

Thanks everybody

Ps: if somebody is willing to look at my other script, I can post here.

suggestion

Posted: Sat Apr 26, 2003 5:11 pm
by phpScott
Try rewriting the line

Code: Select all

$sql = "SELECT COUNT(*) AS count FROM articles WHERE SUBTOPIC_ID='$subtopic_id'";
as some thing like

Code: Select all

$sql = "SELECT COUNT(SUBTOPIC_ID) AS totalSubArt FROM articles WHERE SUBTOPIC_ID='$subtopic_id'";
as it may be getting confused with using the AS count it may also not like usint the * in the COUNT() statement.

phpScott

Posted: Sun Apr 27, 2003 8:21 am
by minds_gifts
Ok, I figured out where the error is and now it works accodingly.I also have another strange problem.

when I select a subtopic name which has no articles under it and press the button delete, I get the mesg: There are articles existing under this sub-topic. and also it shows me "QUERY IS EMPTY"
How can I avoid this query is empty?? why does it return query is empty.


Thanks in advance

Code: Select all

<?
include "../dbconnection.dam"
?>
<?php 
// if you have chosen subtopics to delete 
    if(isset($HTTP_POST_VARS['action'])){ 
    $subtopic_id = $HTTP_POST_VARS['SUBTOPIC_ID']; 
    $qry3 = "SELECT TOPIC_ID FROM subtopic WHERE SUBTOPIC_ID='$subtopic_id'"; 
    $result_3 = mysql_query($qry3) or die(mysql_error()."<br />SQL: $qry3" ); 

// begin the query 
 foreach( $HTTP_POST_VARS[ "to_delete" ] as $key => $value )
{
   $sql = "SELECT SUBTOPIC_ID FROM articles WHERE SUBTOPIC_ID = '" . $value . "'";
   $result_sub = mysql_query($sql) or die(mysql_error()."<br />SQL: $sql" );
   if ( mysql_num_rows( $result_sub ) > 0 )
    {
       echo 'There are articles existing 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 
    } 
      } 

   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>

Posted: Sun Apr 27, 2003 8:26 am
by minds_gifts
OK, I fixed it :lol: