Deleting query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Deleting query

Post by shauns2007 »

Hi peeps

I have a query that selects stuff from a database and then places each item in a new row in a table using a while loop. I also made it add 2 extra columns per row with a "delete" and "edit" button. Ultimately what needs to happen is if I click the delete button it must delete that item from my database. What puzzles me is how can i make sure each delete button has a unique name so I can make sure that the correct entry is deleted and how would I call it? I hope that I have made sense lol. How is my code

Code: Select all

function manage_courses() {
		global $connection;
		$manage_course_query = "SELECT cid, cname FROM course ORDER BY cid ASC";
		$course_table = mysql_query($manage_course_query, $connection) or die ("Database course query failed: " . mysql_error());
		
		while ($row = mysql_fetch_array($course_table)) {
		$cid = $row['cid'];
		$cname = $row['cname'];
		echo '<tr>';
		echo"<td width = '40%'>" .$row['cid']. " - " . $row['cname']."</td>";
		echo "<td align = 'center'><input type = 'submit' name = {$cid} value = 'Delete'</type></td>";
		echo "<td align = 'center'><input type = 'submit' name = 'editcourse' value = 'Edit'</type></td>";
		echo '</tr>';
		}
		echo '</table>';
		
	
		}
Thanks
Kurby
Forum Commoner
Posts: 63
Joined: Tue Feb 23, 2010 10:51 am

Re: Deleting query

Post by Kurby »

Isn't your "cid" column unique? You should update and delete based on unique keys where ever possible. I would store the cid in a hidden field and then reference it that way.
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Re: Deleting query

Post by shauns2007 »

Yes, it is unique. I'm not sure I understand what u mean. Sorry I'm new to all this :)
Kurby
Forum Commoner
Posts: 63
Joined: Tue Feb 23, 2010 10:51 am

Re: Deleting query

Post by Kurby »

Each of your rows should be its own form, and each will have a hidden field with the CID in it.

Code: Select all

while ($row = mysql_fetch_array($course_table)) {
                $cid = $row['cid'];
                $cname = $row['cname'];
                echo '<form method="deleteCourse.php"><input type="hidden" name="cid" value="'.$row['cid'].'"/>';
                echo '<tr>';
                echo"<td width = '40%'>" .$row['cid']. " - " . $row['cname']."</td>";
                echo "<td align = 'center'><input type = 'submit' value = 'Delete'</type></td>";
                echo "<td align = 'center'><input type = 'submit' name = 'editcourse' value = 'Edit'</type></td>";
                echo '</tr></form>';
                }
This will post to deleteCourse.php. You now have a post variable 'cid' that will identify which unique record you should modify.

Code: Select all

$qry = "DELETE FROM course WHERE cid = $_POST[cid]";
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Re: Deleting query

Post by shauns2007 »

Thanks :)

I already have a select query in that function, would I be able to have the delete query in the same function?
miramichiphoto
Forum Newbie
Posts: 14
Joined: Thu Mar 17, 2011 1:12 pm

Re: Deleting query

Post by miramichiphoto »

I've solved the same problem recently. Like the previous poster says, make each row a form. The form action should be the same page, with the code in the head of the page. You can use as many querys, deletes, inserts as you like in that function. Here is my form handling function, and the table with the form-per-row.

Code: Select all

if (isset($_POST['submit'])) { // Form has been submitted improperly.

		$date = trim($_POST['date']);
		$time = trim($_POST['time']);
		$gym = trim($_POST['gym']);
		$id = trim($_POST['id']);
		
	if ($date == NULL || $time == NULL  )
		{
			$message = "Your Games IS NOT edited. <br />You left a feild blank or entered too many characters";
			

		} else 		{
			
			$query = "UPDATE `games` SET `date`='".$date."',`time`='".$time."',`gym`='".$gym."' WHERE `coach`='".$_SESSION['display_name']."' AND `id`='".$id."'";
			
			 mysql_query($query, $connection) or die(mysql_error());
			$message = 'Game edited successfully' ;
			
			
				}

And this is the form made with a while loop:

Code: Select all

echo "<table border='1' cellpadding='4px'  style='border-color:#FFF'>";
	   
	   
		$query = "SELECT * FROM `games` WHERE `coach`='".$_SESSION[display_name]."';";
		$result = mysql_query($query, $connection) or die(mysql_error());
		$num=mysql_num_rows($result);
		$i=0;
		
		while ($i < $num){
		

			$row = mysql_fetch_array($result);
			$game_id = $row['id'];
			
			

echo '<form action="coach_edit_schedule.php" method="post"><tr>';
echo '<td><input type="hidden" name="id"  value="'.$row['id'].'" />';
echo '<input type="text" name="date"  value="'.$row['date'].'" /></td>';
echo '<td> <input type="text" name="time"  value="'.$row['time'].'" /></td>';

echo '<td><select name="gym">';
						
echo        '<option value="'.$row['gym'].'"select">'.$row['gym'].'</option>';
echo       '<option value="JMH">JMH</option>';
echo	'<option value="MVHS">MVHS</option>';

echo'</select></td>';



//echo '<td> <input type="text" name="gym"  value="'.$row['gym'].'" /></td>';
echo '<td>'.$row['ref1'].'</td>';
echo '<td>'.$row['ref2'].'</td>';
echo '<td><input type="submit" name="submit" value="Edit Game" /></td></tr></form>';
$i++;
		}
 
  
  echo "</table>"

shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Re: Deleting query

Post by shauns2007 »

Thanks guys, got it working :) would you mind explaining why use a hidden field?
blakewilliams
Forum Newbie
Posts: 4
Joined: Fri Mar 25, 2011 1:47 am

Re: Deleting query

Post by blakewilliams »

The hidden field is just for the sake of not having to display the 'cid' as well as avoiding storing the value in an editable text input.

Safer, easier and takes a lot of the guess work out of the form's post info.
shauns2007
Forum Commoner
Posts: 27
Joined: Wed Feb 02, 2011 11:34 am

Re: Deleting query

Post by shauns2007 »

When I click my edit button it also deletes. I tried changing the name of the button but no luck. I added an if statement redirecting to the index.php page if the edit button is clicked which works. So basically I get redirected and that line on my page gets deleted. Here is the code. Thanks :)

Code: Select all

	function manage_courses() {
		global $connection;
		$manage_course_query = "SELECT cid, cname FROM course ORDER BY cid ASC";
		$course_table = mysql_query($manage_course_query, $connection) or die ("Database course query failed: " . mysql_error());
		$num = mysql_num_rows($course_table);
		$i = 0;

		while ($i < $num) {
		$row = mysql_fetch_array($course_table);
		$cid = $row['cid'];
		$cname = $row['cname'];
		
				echo '<form action="course_man.php" method = "post"><input type="hidden" name="cid" value="'.$row['cid'].'"/>';
                echo '<tr>';
                echo"<td width = '40%'>" .$row['cid']. " - " . $row['cname']."</td>";
                echo "<td align = 'center'><input type = 'submit' value = 'Delete'</type>";
                echo '<input type="hidden" name="cname" value="'.$row['cname'].'"/></td>';
                echo "<td align = 'center'><input type = 'submit' value = 'Edit'</type></td>";
                echo '</tr></form>';
                $i++;
                }
                
                if (isset($_POST['cid'])) {
               	$qry = "DELETE FROM course WHERE cid = $_POST[cid]";
			  	$result = mysql_query($qry, $connection) or die ("Database course delete query failed: " . mysql_error());
			  	$increment = "ALTER TABLE course AUTO_INCREMENT = 1";
			  	$inc_reset = mysql_query($increment,$connection) or die ("Could not reset auto increment: " . mysql_error());
			  	header("location:course_man.php");
			  	}
			  	
			  	if (isset($_POST['cname'])){
			  	header("location:index.php");
              } 
              
   		}
Kurby
Forum Commoner
Posts: 63
Joined: Tue Feb 23, 2010 10:51 am

Re: Deleting query

Post by Kurby »

The php page needs to determine which submit button was clicked. If you give each submit input a name then there will be a $_POST value associated with it.

Code: Select all

<input type='submit' name='delete' value = 'delete'>
<input type='submit' name='edit' value = 'edit'>

if(isset($_POST['delete']))
//Do delete stuff
if(isset($_POST['edit']))
//do edit stuff.
miramichiphoto
Forum Newbie
Posts: 14
Joined: Thu Mar 17, 2011 1:12 pm

Re: Deleting query

Post by miramichiphoto »

Check the action associated with the form. Be sure the right script is handling the request. One action per form.
foxmahesh
Forum Newbie
Posts: 5
Joined: Thu Mar 31, 2011 8:34 am

Re: Deleting query

Post by foxmahesh »

pass your unique/primary key field value with delete button

you can check in your Database for primary key if it not exist please make new one and send this id(i am giving my dummy name for field) with delete button
Post Reply