Deleting records based on the selection in a listbox
Moderator: General Moderators
-
minds_gifts
- Forum Commoner
- Posts: 63
- Joined: Mon Feb 10, 2003 4:23 am
Deleting records based on the selection in a listbox
hello everybody,
Iwould like to delete records from a table - how do i want to do is...
First, i want to list all the topics from one table and when i select a topic, i want to show all the sub-topics from another table.These two tables topics and sub-topics have relation.NOw, i want to select a sub-topic and corresponding article names have to be displayed and at the end of each article name, i want to show a check box and when i check this box and hit the delete button, the record has to be deleted.i.e, the article name has to be deleted for the corresponding sub-topic and to the topic.
Could somebody please help me how to write such a script or if somebody has an example, could you please show me up.
Many thanks
Iwould like to delete records from a table - how do i want to do is...
First, i want to list all the topics from one table and when i select a topic, i want to show all the sub-topics from another table.These two tables topics and sub-topics have relation.NOw, i want to select a sub-topic and corresponding article names have to be displayed and at the end of each article name, i want to show a check box and when i check this box and hit the delete button, the record has to be deleted.i.e, the article name has to be deleted for the corresponding sub-topic and to the topic.
Could somebody please help me how to write such a script or if somebody has an example, could you please show me up.
Many thanks
- daven
- Forum Contributor
- Posts: 332
- Joined: Tue Dec 17, 2002 1:29 pm
- Location: Gaithersburg, MD
- Contact:
It sounds like you want a pair of tiered select boxes, and then a list based upon them.
select_Topic --> select_SubTopic --> list_Article
There are two ways to do this.
1. Resubmit the page after each selection, and load the proper data. This requires multiple page refreshing and DB hits, but would be fairly clean and easy to accomplish.
2. Use lots of javascript. Check out viewtopic.php?t=6568 for examples on the tiered select boxes. For the list part, you would need to do something similar, but have an array of <input type=checkbox> and output the array to a specified location in your form. This method permits you to avoid multiple page re-loads (keep it all client side), but is not as simple or clean.
I would recommend the resubmittal method. Trust me, it is much easier. If you need some code examples for it, just let me know and I will come up with one (I have to get a meeting now, or I would write it out for you).
Cheers
select_Topic --> select_SubTopic --> list_Article
There are two ways to do this.
1. Resubmit the page after each selection, and load the proper data. This requires multiple page refreshing and DB hits, but would be fairly clean and easy to accomplish.
2. Use lots of javascript. Check out viewtopic.php?t=6568 for examples on the tiered select boxes. For the list part, you would need to do something similar, but have an array of <input type=checkbox> and output the array to a specified location in your form. This method permits you to avoid multiple page re-loads (keep it all client side), but is not as simple or clean.
I would recommend the resubmittal method. Trust me, it is much easier. If you need some code examples for it, just let me know and I will come up with one (I have to get a meeting now, or I would write it out for you).
Cheers
-
minds_gifts
- Forum Commoner
- Posts: 63
- Joined: Mon Feb 10, 2003 4:23 am
Thanks daven,
Many thanks
Well, I've written a script where i list all the topic names, hit a button and then it shows me all the sub-topic names in another list box.Now, i've no clue how to proceed further.If you find some free time, could you please show me an example.I would recommend the resubmittal method. Trust me, it is much easier. If you need some code examples for it, just let me know and I will come up with one (I have to get a meeting now, or I would write it out for you).
Many thanks
- daven
- Forum Contributor
- Posts: 332
- Joined: Tue Dec 17, 2002 1:29 pm
- Location: Gaithersburg, MD
- Contact:
This example supposes the following conditions:
1. database connection: $db_conn
2. database type: MySQL
3. database table: topic. columns: topic_ID, topic
4. database table: subtopic. columns: subtopic_ID, subtopic, subtopic_TopicID
5. database table: article. columns: article_ID, article_name, article_SubtopicID
This should work, although I have not tested it out. If you have any questions/problems with it, just let me know.
1. database connection: $db_conn
2. database type: MySQL
3. database table: topic. columns: topic_ID, topic
4. database table: subtopic. columns: subtopic_ID, subtopic, subtopic_TopicID
5. database table: article. columns: article_ID, article_name, article_SubtopicID
This should work, although I have not tested it out. If you have any questions/problems with it, just let me know.
Code: Select all
<?php
// if you have chosen articles to delete
if(isset($_POST['action'])){
$qry3="SELECT article_ID FROM article WHERE article_SubtopicID='".$_POST['subtopic_ID']."'";
$result_3=mysql_query($qry3,$db_conn) or die(mysql_error());
$qry4="DELETE FROM article WHERE article_ID=0"; // begin the query (article_ID=0 is there just to have an initial WHERE clause
while($row_4=mysql_fetch_row($result_3)){
if(isset($_POST[$row->article_ID])){ // the checkboxes were named for the article_ID
$qry4 .= "OR article_ID=".$row->article_ID; // add this article to the query string
}
}
mysql_query($qry4,$db_conn) or die(mysql_error());
}
// get the list of topics
$qry1="SELECT topic_ID, topic FROM topic ORDER BY topic";
$result_1=mysql_query($qry1,$db_conn) or die(mysql_error());
// if you have chosen a topic, get the list of subtopics
if(isset($_POST['topic_ID'])){
$qry2="SELECT subtopic_ID, subtopic, subtopic_TopicID FROM subtopic WHERE subtopic_TopicID='".$_POST['topic_ID']."' ORDER BY subtopic";
$result_2=mysql_query($qry2,$db_conn) or die(mysql_error());
}
// if you have chosen a subtopic, get the list of articles
if(isset($_POST['subtopic_ID'])){
$qry3="SELECT article_ID, article_name, article_SubtopicID FROM article WHERE article_SubtopicID='".$_POST['subtopic_ID']."' ORDER BY article_name";
$result_3=mysql_query($qry3,$db_conn) 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>
<?// also look into mysql_fetch_row, mysql_fetch_array, and mysql_fetch_assoc
while($row=mysql_fetch_object($result)){
echo "<option value="".$row->topic_ID."">".$row->topic."</option>";
}?>
</select>
<input type="submit" value="Set Topic">
</form>
<!--- create the subtopic select box --->
<form name="subtopic_form" action="<?=$PHP_SELF?>" method="post">
<select name="subtopic_ID">
<option value="">Choose a subtopic</option>
<?if(isset($_POST['topic_ID'])){
while($row_2=mysql_fetch_object($result_2)){
echo "<option value="".$row_2->subtopic_ID."">".$row_2->subtopic."</option>";
}
}?>
</select>
//<!--- include topic_ID for continuity --->
<input type="hidden" name="topic_ID" value="<?=$_POST['topic_ID']?>">
<input type="submit" value="Set SubTopic">
</form>
<!--- create the list of articles --->
<form name="delete_form" action="<?=$PHP_SELF?>" method="post">
<?if(isset($_POST['subtopic_ID'])){
while($row_3=mysql_fetch_object($result_3)){
// articles named for the article_ID (aids in ease of retrieval after submittal
echo "<input type="checkbox" name="".$row_3->article_ID."" value=1>".$row_3->article_name."<BR>";
}
}?>
<!--- include topic_ID & subtopic_ID for continuity --->
<input type="hidden" name="topic_ID" value="<?=$_POST['topic_ID']?>">
<input type="hidden" name="subtopic_ID" value="<?=$_POST['subtopic_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>
</HTML>-
minds_gifts
- Forum Commoner
- Posts: 63
- Joined: Mon Feb 10, 2003 4:23 am
Hi there daven,
First many thanks for your post and for yout time.
Unfortunately it does'nt seems to be get working with me, have to figure it out.Well, my db design looks like follows....
1.database table: topic. columns - topic_id, topic_name
2.database table: subtopic. columns - subtopic_id, topic_id, subtopic_name
3.database table: articles. columns - article_id, subtopic_id, topic_id, article_name and article_text.
As far as I understood your code, it has to even work with my design too.Absolutely have no clue why does'nt this work with me.
Anyways, I'm thankful to you.
Cheers
First many thanks for your post and for yout time.
Unfortunately it does'nt seems to be get working with me, have to figure it out.Well, my db design looks like follows....
1.database table: topic. columns - topic_id, topic_name
2.database table: subtopic. columns - subtopic_id, topic_id, subtopic_name
3.database table: articles. columns - article_id, subtopic_id, topic_id, article_name and article_text.
As far as I understood your code, it has to even work with my design too.Absolutely have no clue why does'nt this work with me.
Anyways, I'm thankful to you.
Cheers
-
minds_gifts
- Forum Commoner
- Posts: 63
- Joined: Mon Feb 10, 2003 4:23 am
hello,
well, this is my work page.
http://inkaytown.f2o.org
and heres the form
http://inkaytown.f2o.org/admin/delete.php
The source code for delete.php can be viewed here
http://inkaytown.f2o.org/code/delete.html
So, in my database the topic names are movies and cricket teams and there are also sub-topic names related to each topic and articles are also present.
Thank you so much
well, this is my work page.
http://inkaytown.f2o.org
and heres the form
http://inkaytown.f2o.org/admin/delete.php
The source code for delete.php can be viewed here
http://inkaytown.f2o.org/code/delete.html
So, in my database the topic names are movies and cricket teams and there are also sub-topic names related to each topic and articles are also present.
Thank you so much
-
minds_gifts
- Forum Commoner
- Posts: 63
- Joined: Mon Feb 10, 2003 4:23 am
hello daven,
I corrected those errors, but, still i dont see the records being deleted.The topics, subtopics and article names are listed after each operation.
Well, this is the delete st which is being used.
First of all, i dont have any articles with ARTICLE_ID=0 and I think this statement has to be modified w.r.t all article_id's because If the user checks 3 checkboxes and hit the button delete, all the records has to be deleted.
Dont you think so??Anyways, I'm glad for helping me.
Regards,
I corrected those errors, but, still i dont see the records being deleted.The topics, subtopics and article names are listed after each operation.
Well, this is the delete st which is being used.
Code: Select all
$qry4="DELETE FROM articles WHERE ARTICLE_ID=0";Dont you think so??Anyways, I'm glad for helping me.
Regards,
- daven
- Forum Contributor
- Posts: 332
- Joined: Tue Dec 17, 2002 1:29 pm
- Location: Gaithersburg, MD
- Contact:
Article_ID=0 is merely an initiation point.
The purpose of the while loop is to append relevant article_IDs to the SQL string. So if you check article #1 (article_ID=1) the string should end up as:
"DELETE FROM articles WHERE ARTICLE_ID=0 OR ARTICLE_ID=1".
In the code you have posted at http://inkaytown.f2o.org/code/delete.html, you have a few errors still.
1. you must use the proper row names. In the delete statement below, you are setting $row_4, but you call $row, which is never set.
2. mysql_fetch_row retrieves things in an indexed array (ex: $row_4[0]). mysql_fetch_object retrieves things with the associative arrow (ex: $row_4->article_ID). Read the php manual on these functions!
3. PHP & MySQL are case sensitive. So if your db column is "article_ID" then you cannot retrieve "ARTICLE_ID".
Basically , you just need to start error checking your code. As I said when I first posted the example code, I had not tested it, so it probably had some errors.
The purpose of the while loop is to append relevant article_IDs to the SQL string. So if you check article #1 (article_ID=1) the string should end up as:
"DELETE FROM articles WHERE ARTICLE_ID=0 OR ARTICLE_ID=1".
In the code you have posted at http://inkaytown.f2o.org/code/delete.html, you have a few errors still.
1. you must use the proper row names. In the delete statement below, you are setting $row_4, but you call $row, which is never set.
2. mysql_fetch_row retrieves things in an indexed array (ex: $row_4[0]). mysql_fetch_object retrieves things with the associative arrow (ex: $row_4->article_ID). Read the php manual on these functions!
3. PHP & MySQL are case sensitive. So if your db column is "article_ID" then you cannot retrieve "ARTICLE_ID".
Code: Select all
<?php
$qry4="DELETE FROM articles WHERE ARTICLE_ID=0";
while($row_4=mysql_fetch_row($result_3)){
if(isset($_POST[$row->ARTICLE_ID])){ // the checkboxes were named for the article_ID
$qry4 .= "OR ARTICLE_ID=".$row->ARTICLE_ID; // add this article to the query string
}
}
?>-
minds_gifts
- Forum Commoner
- Posts: 63
- Joined: Mon Feb 10, 2003 4:23 am
-
minds_gifts
- Forum Commoner
- Posts: 63
- Joined: Mon Feb 10, 2003 4:23 am
hello daven,
well I got it done now.
Here are the couple of changes I made to the script.
and
Though I dont find any bugs now, I still find something which does'nt look good.Anyways, it aint a serious problem though.In the list box, i always see select the topic though it is being selected and its the same with the sub-topic.Rest is everything fine:-)
Thank you so much.
well I got it done now.
Here are the couple of changes I made to the script.
Code: Select all
$qry4 = "DELETE FROM articles WHERE ARTICLE_ID IN('" . implode("','", $_POST['to_delete']) . "')";Code: Select all
echo "<input type="checkbox" name="to_delete[]" value="".$row_3->ARTICLE_ID."">".$row_3->ARTICLE_NAME."<BR>";Thank you so much.