Thread: Deleting records
Results 1 to 6 of 6
Related
-
Deleting One Site? Forum: Myspace Forum
Replies: 3 -
78 rpm records Forum: General Discussion
Replies: 2 -
Deleting MySQL Forum: PHP Forum
Replies: 1
-
04-20-2003, 06:03 AM #1
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
-
04-20-2003, 06:17 AM #2
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 -
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.Code:select count(*) where article_subforum='whateverid'
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?
-
04-20-2003, 09:41 AM #3
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)
Now, I only want to check if there are any articles corresponding to this subtopic_id which I marked for deletion.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>
If there are, then a mesg box else deleted.
Many thanks
-
04-20-2003, 01:03 PM #4
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?
-
04-22-2003, 07:13 AM #5
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
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.PHP Code:$result_sub=mysql_query("SELECT * FROM articles WHERE SUBTOPIC_ID = 2");
$number = mysql_num_rows($result_sub);
echo("$number");
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>
-
04-22-2003, 11:54 AM #6
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?
Reply With Quote
Cloudjiffy- PaaS for Developers
11-30-2021, 09:46 PM in Web Hosting Forum