Page 1 of 1
Deleting records based on the selection in a listbox
Posted: Thu Feb 20, 2003 4:15 am
by minds_gifts
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
Posted: Thu Feb 20, 2003 9:50 am
by daven
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
Posted: Thu Feb 20, 2003 10:33 am
by minds_gifts
Thanks daven,
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).
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.
Many thanks
Posted: Thu Feb 20, 2003 12:31 pm
by daven
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.
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>
Posted: Fri Feb 21, 2003 4:44 pm
by minds_gifts
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
Posted: Fri Feb 21, 2003 5:03 pm
by daven
Hmm....ponder ponder....
Could you post a link up to the page where you have the code? I might be able to help figure it out if I can see the errors generated/play with the script.
Where is it breaking (ie--how far can you get before it throws an error)?
Posted: Fri Feb 21, 2003 6:06 pm
by minds_gifts
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
Posted: Sat Feb 22, 2003 7:10 pm
by daven
1. You do not need to put quotes around the variables in your mysql_connect statement. It should read: $db_conn = mysql_connect($location,$username,$password);
2. for the Topic select, you should use $result_1, not $result
Try these fixes and then let me see again.
Posted: Sun Feb 23, 2003 2:30 pm
by minds_gifts
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.
Code: Select all
$qry4="DELETE FROM articles WHERE ARTICLE_ID=0";
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,
Posted: Mon Feb 24, 2003 9:21 am
by daven
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".
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
}
}
?>
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.
Posted: Mon Feb 24, 2003 10:03 am
by minds_gifts
Hello daven,
The url which shows my code is'nt the latest one.Well, I corrected all the errors.Anyways, lemme go through it and will let you know the status.I hope, I'll fix it by myself
Thank you
Posted: Mon Feb 24, 2003 5:38 pm
by minds_gifts
hello daven,
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']) . "')";
and
Code: Select all
echo "<input type="checkbox" name="to_delete[]" value="".$row_3->ARTICLE_ID."">".$row_3->ARTICLE_NAME."<BR>";
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.