Delete MySql row using php.

[Teng]

Hey guys, I'm having a little trouble using the delete function.

 

I have a number of activites a user is assigned too. This is pretty much how my db structure is.

 

Activities

ActivityID (PK)

ActivityName

 

Assignments

AssignmentID (PK)

ActivityID

UserID

Location

 

Users

UserID (PK)

Firstname

Lastname

Address

 

So what i have done is at the bottom of my page is an add feature which accesses the Activities table and with a drop down menu lists all the activities available. Next to that is the Location of the activity they can select. Then next to these is an Add button. This directs the user to Add.php and checks the session to see if they are logged in. Then adds the selected Activity to the Assignments table.

 

Then above the bit where it lists all the activites which you can add is a list of Activites the user has been assigned. The php accesses the Assignments table and checks the User's session and matches it against the UserID in the Assignments table. It then displays using a for loop all the Activites the user has been assigned. Next to each of these activites is a remove button. Which when clicked I want it to delete the AssignmentID, ActivityID, UserID and Location row from the Assignments table. However the problem i have is I don't know how to tell it to delete a specific row as there is nothing linking the remove buttons to the individual activites like when I add them.

 

Any help would be great thanks.

 

 

[phat_hip_prog]

When generating the form use hidden fields to indicate appropriate id?

[Teng]

How do i assign that? here is my code. If i were assigning hidden id attributes in a for loop wouldnt it just overwrite each time? Sorry am very new to php.

 

 

<form name="Remove" method="post" action="Remove.php">

<table width="100%">

 

<?

$query = "select *from assignments where UserID = '" . $_SESSION['usertid'] ."'";

$result = $db_conn->query($query);

$num_results = $result->num_rows;

 

for ($i=0; $i <$num_resultsEnrol; $i++)

{

$row = $result->fetch_assoc();

$ActivityID = $row["ActivityID"];

$query1 = "select *from Activities where ActivityID = '" . $ActivityID."'";

$result1 = $db_conn->query($query1);

$row = $result1->fetch_assoc();

echo '<tr>';

echo '<td>'.$row["ActivityID"].': '.$row["ActivityName"].'</td>';

echo '<td align="right"><input type="submit" value="Remove"></td>';

echo '</tr>';

}

?>

[phat_hip_prog]

I'm presuming the AssignmentID is unique and what you'll need to use to delete (identify) the row.

As for writing over, no because your outputting it with echo!

[Teng]

I see what you mean with it not overwriting so how do i assign the hidden id's?

[watthehell]

 

Keep this below the Remove button

<input type="hidden" name="deleteme" value="deleteme">

 

and from php check if it is set or not (it will be set when u press the remove button) , then write your Delete query inside the if.. statement ...

 

 

cheers... 

 

 

 

[Teng]

OK i've decided on a slightly different approach to this. I want to assign to the button invidual links does that make sense? For example it will be like .....remove.php?activity=Act1

 

but when i try this it doesnt work.

 

<?       

        $query = "select *from assignments where UserID = '" . $_SESSION['usertid'] ."'";

        $result = $db_conn->query($query);

        $num_results = $result->num_rows;

 

        for ($i=0; $i <$num_resultsEnrol; $i++)

        {

            $row = $result->fetch_assoc();

            $ActivityID = $row["ActivityID"];

            $query1 = "select *from Activities where ActivityID = '" . $ActivityID."'";

            $result1 = $db_conn->query($query1);

            $row = $result1->fetch_assoc();

            echo '<form name="Remove" method="post" action="Remove.php?unit=$ActivityID=>';

            echo '<table width="100%">';

            echo '<tr>';

            echo '<td>'.$row["ActivityID"].': '.$row["ActivityName"].'</td>';

            echo '<td align="right"><input type="submit" value="Remove"></td>';

            echo '</tr>';

        }

?>

 

But it says something like requested page not found ....Remove.php?unit=$ActivityID=<table width....

 

Whats wrong with it???

[sasa]

<form name="Remove" method="post" action="Remove.php">
		<table width="100%">
<?			
		$query = "select assignments.*, Activities.ActivityName from assignments, Activities where UserID = '" . $_SESSION['usertid'] ."' and assignments.AssignmentID = Activities.AssignmentID";
		$result = $db_conn->query($query);
		$num_results = $result->num_rows;

		for ($i=0; $i <$num_resultsEnrol; $i++)
		{
			$row = $result->fetch_assoc();
			$ActivityID = $row["ActivityID"];
			//$query1 = "select *from Activities where ActivityID = '" . $ActivityID."'";
			//$result1 = $db_conn->query($query1);
			//$row = $result1->fetch_assoc();
			echo '<tr>';
			echo '<td>'.$row["ActivityID"].': '.$row["ActivityName"].'</td>';
			echo '<td align="right"><input type="submit" value="Remove" name="',$row['AssignmentID'],'></td>';
			echo '</tr>';
		} 
                        echo '</table></form>';
?>
[/quote]and in Remove.php[code]$key = array_search('Remove', $_POST);
if($key) {
   $query = "DELETE FROM assignments WHERE AssignmentID=$key";
   $result = $db_conn->query($query) or die($query);
}

[/code]

[Teng]

OK so i changed the code around and now using html link to remove.php

 

echo '<td align="right"><a href=remove.php?enrol='.$AssignmentID.'>Remove</a></td>';

 

So this creates a link such as remove.php?Assignment=1

 

But now having trouble in remove.php which i basically have nothing on how to get the Assignment=1 into a variable like for example $AssignmentLink = //code to identify that Assignment=1 therefore i would use that to say ok delete from assignments the row with assignment id as 1