sql count(*)

chinni_77

The following script deletes the subtopics.well, I got topics, subtopics and articles.Each topic has many subtopics and each subtopic has many articles.
Before, the subtopics are actually deleted, i want to check if there are any articles present under this subtopic id.

Table:articles(articles_id, subtopics_id, topic_id and so on.....)

I think i have to use the sql snippet with count(*) or some thing like that.

Could somebody please point me out in the right direction and where should i place in the check in-order to check it.

Thanks in advance.

<?
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>

peeter_talvistu

you just have to do a simple mysql search

$sql = mysql_query ("select * from articles where subtopic_id='$the_subtopicid_you_want_to_delete'");

then

if (mysql_num_rows($sql) == 0) {
do deleting
} else {
do nothing
}

hope this helps a bit

chinni_77

Well, I'm trying to check with mysql_numrows rather than using sql count(*).If the query result_sub returns the value > 0, then the sub-topic is not deleted else it gets deleted.
Now, when i run this code, i get the mesg the query is empty ir-respective if there are any articles or not under the subtopic.
Could you please tell me why.

Thanks in advance

// 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 * FROM articles WHERE SUBTOPIC_ID='$subtopic_id' ");
   if (mysql_numrows($result_sub)>0)

   $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()); 
}

peeter_talvistu

ok, i notice quite a many mistakes.
first let's just check it.
$number = mysql_numrows($result_sub);
echo("$number");

see, if this returns any results.

then let's make an if statement

if ($number == "0") {
delete
}

notice that you left out the { after your if statement
and you also have to delete only if the num_rows is 0 (meaning that there are no articles under the subtopic).

did this help?

chinni_77

hi peeter

well, what are the many mistakes.

It returns every time the value 0 and the sub-topics are deleted though there are articles under them.
its driving me nuts.could you pls tell me where I'm going wrong

// begin the query  

   $subtopic_id=$HTTP_POST_VARS['SUBTOPIC_ID'];
   $result_sub=mysql_query("SELECT * FROM articles WHERE SUBTOPIC_ID='$subtopic_id' ");
   $number = mysql_numrows($result_sub); 
//   echo("$number"); 

//   if (mysql_numrows($result_sub)>0)
   if ($number == "0") { 

   $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());

peeter_talvistu

the only thing i can think of right now is that maybe your sql query is wrong.

try to use only the following code:

$subtopic_id=$HTTP_POST_VARS['SUBTOPIC_ID'];
$result_sub=mysql_query("SELECT * FROM articles WHERE SUBTOPIC_ID='$subtopic_id' ");
$number = mysql_numrows($result_sub);
echo("$number");

this should show you 0 or the right number of articels if the sql statement is correct. if it isn't correct then you just have to correct it. i can't help you with that right now because i don't know your table structure.

chinni_77

I tried to debug the code several ways, but it always returns 0 though there are articles present to the corresponding subtopic_id which I marked for deletion.

Could somebody please try to help me.

Thanks a lot.

peeter_talvistu

dooh. a very easy mistake. you have misspelled the num rows statement.
it's actually
mysql_num_rows
not mysql_numrows

chinni_77

Hello peeter,

It was'nt the mistake.I corrected it before.it always show me 0 records.

Any other ideas??

Thanks for your time.

peeter_talvistu

ok, do some bug traking then

if you echo $HTTP_POST_VARS['SUBTOPIC_ID'] or later $subtopic_id does it give you the correct id or not? maybe its just an error that arises from somehwere before this script.

if this is correct try to debug it giving a correct $subtopic_id value for your query. eg. if you tried to delete subtopic with id 15 and your absolutely sure it has articles beneath it just try:

$result_sub=mysql_query("SELECT * FROM articles WHERE SUBTOPIC_ID='15 ");
$number = mysql_num_rows($result_sub);
echo("$number");

if any of these doesn't work. then i'm really starting to run out of possible solutions 😉

chinni_77

hi peeter,

Thats perfect sort of de-bugging.I tried to delete giving some sub-topic id no and it has shown me the number of articles present under those and when there are no, it returned me 0.

I guess, I'm on the track.so, how should I proceed further and fix my whole script.Hope you would help me out.

Thanks a lot

peeter_talvistu

did you do the first debuggin (for post_vars) in my last reply? if that didn't work then this was why you couldn't get it working before - something wrong with the post thingie (maybe the worng variable?)

if i understand from the first message correctly (it's 2:30 am where i am 😉 ), then you want to have a list of subtopics with a checkbox for every one of them. when the user checks the ones he/her wants to delete and presses "delete" all of those will be deleted. i think the easiest way would actually be to make a foreach loop and start it before we actually get to the part of mysql_num_rows we have been on til now.

so here goes a broad view of this:

foreach($_POST['to_delete'] AS $value) {

$result_sub=mysql_query("SELECT * FROM articles WHERE SUBTOPIC_ID='$value' ");
$number = mysql_numrows($result_sub);

if ($number == 0) {
mysql_query("delete * FROM subtopic WHERE SUBTOPIC_ID='$value' ");
}
}

this was a rough idea but maybe should even work.

btw, it just came to me that the whole reason you couldn't get anything out of it was because you made an array of those checkboxes. this foreach should solve that problem also