Page 1 of 1

Deleting query

Posted: Fri Mar 25, 2011 10:13 am
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

Re: Deleting query

Posted: Fri Mar 25, 2011 10:43 am
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.

Re: Deleting query

Posted: Fri Mar 25, 2011 10:50 am
by shauns2007
Yes, it is unique. I'm not sure I understand what u mean. Sorry I'm new to all this :)

Re: Deleting query

Posted: Fri Mar 25, 2011 11:23 am
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]";

Re: Deleting query

Posted: Fri Mar 25, 2011 12:31 pm
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?

Re: Deleting query

Posted: Fri Mar 25, 2011 7:37 pm
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>"


Re: Deleting query

Posted: Sat Mar 26, 2011 3:10 am
by shauns2007
Thanks guys, got it working :) would you mind explaining why use a hidden field?

Re: Deleting query

Posted: Sat Mar 26, 2011 6:43 am
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.

Re: Deleting query

Posted: Sat Mar 26, 2011 12:38 pm
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");
              } 
              
   		}

Re: Deleting query

Posted: Mon Mar 28, 2011 12:58 pm
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.

Re: Deleting query

Posted: Tue Mar 29, 2011 5:56 pm
by miramichiphoto
Check the action associated with the form. Be sure the right script is handling the request. One action per form.

Re: Deleting query

Posted: Thu Mar 31, 2011 8:48 am
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