Jump to content
KevBurgess

Delete a MySQL row from a drop-down HTML menu

Recommended Posts

Hi all,

I am currently learning PHP and have the homework to produce a function that can delete a row in a MySQL database table by clicking on an item in a drop-down menu in a web page.

The code I have produced up until now is this:

<!DOCTYPE HTML>
<html lang="de">
<head>
	<meta charset="utf-8" />
	<title>E3_Artikel_Löschen</title>

</head>
<body>
<form method = "GET">

<?php
	$anr="";
	
	try {
	$pdo = new PDO ('mysql:dbname=bestelldatenbank;host=localhost;charset=utf8', 'root', '');
	} catch (PDOException $error){
		die ($error->getMessage());
		}		
?>

	<div>
		<p>
			<label for="artikel">Artikel: </label>
				<select id="artikel" name="artikel">
					<?php
						$sqlSelect = "SELECT anr, name FROM artikel ORDER BY anr ASC";
						foreach ($pdo->query($sqlSelect) as $row) {
							echo "<option value=$row[0]>$row[0] | $row[1]</option>\n";
							$anr = $row[0];

						}
					?>
				</select>
			
			<input type = "submit" value = "Delete row" />
			
		</p>
	</div>

<?php
	function artLoeschen($anr) {
		echo "Function called $anr";
		if(isset($_GET[$anr])) {
			$anr = $_GET[$anr];
			$sqlDelete = $pdo->query("DELETE FROM artikel WHERE anr = :anr");
			if ($stmt = $pdo->prepare($sqlDelete)) {
				$stmt->bindParam(':anr', $anr);
				$stmt->execute();
			}
			echo "<h2><b>Artikel gelöscht!</b></h2>";
		}
	}
?>

</form>
</body>
</html>

So, I have observed the following when I run the script in a browser:

1.  The HTML works as expected and I get a drop-down list with the article number and description of each item in the affected table.

2.  I can click on an item in the list and it populates the top item in the drop-down list.

3.  When I click delete row, the selected item is not deleted.

4.  There are no error messages returned but the function is not executed (at least not as I would like to expect).

 

I have obviously missed something or made a mistake in my code. 

I would be very grateful for any help...this is driving me mad! :)

Regards,

Kevin

Share this post


Link to post
Share on other sites

You may have that function but you don't have anything that calls it. Functions don't run automatically.

You need to add some code that decides whether it should call the function, and if so with what argument.

Share this post


Link to post
Share on other sites
10 hours ago, requinix said:

You may have that function but you don't have anything that calls it. Functions don't run automatically.

You need to add some code that decides whether it should call the function, and if so with what argument.

You are of course right and I forgot to include the line in my code.

I had tried to include this call in several places to test the results, all unsuccessful:

<?php artLoeschen($anr); ?>

Unfortunately I was only guessing where the line should go and that is what is irritating me because nothing I tried seemed to work.  

Where on earth should this call go in order to submit the form after selecting the appropriate item so the item is deleted?

Regards,

Kev

 

Share this post


Link to post
Share on other sites
1 hour ago, KevBurgess said:

Where on earth should this call go

At the top of the script, before the HTML.

If your intent is change data, such as a delete, insert, update, use form method POST. Use GET when you want to get data for display.

Check if data was posted. If it was, process the posted data.

if ($_SERVER['REQUEST_METHOD']=='POST') {
    //  process the $_POST data
}

<html>
   .....

 

Share this post


Link to post
Share on other sites
1 hour ago, Barand said:

At the top of the script, before the HTML.

If your intent is change data, such as a delete, insert, update, use form method POST. Use GET when you want to get data for display.

Check if data was posted. If it was, process the posted data.


if ($_SERVER['REQUEST_METHOD']=='POST') {
    //  process the $_POST data
}

<html>
   .....

 

Hi Barand,

many thanks for the tip, it certainly helped.

When I click on the "Submit" button though the DELETE statement isn't carried out and the contents of the drop-down list are simply reset.

It seems to me as if the variable $anr is not being carried over into the DML statement.

Here the altered code:

<!DOCTYPE HTML>

<?php
	$pdo = new PDO ('mysql:dbname=bestelldatenbank;host=localhost;charset=utf8', 'root', '');
	$anr = "";

	if ($_SERVER['REQUEST_METHOD']=='POST') {
		artLoeschen($anr);
	}
?>

<html lang="de">
<head>
	<meta charset="utf-8" />
	<title>E3_Artikel_Löschen</title>

</head>
<body>
<form method = "POST">

	<div>
		<p>
			<label for="artikel">Artikel: </label>
				<select id="artikel" name="artikel">
					<?php
						$sqlSelect = "SELECT anr, name FROM artikel ORDER BY anr ASC";
						foreach ($pdo->query($sqlSelect) as $row) {
							echo "<option value=$row[0]>$row[0] | $row[1]</option>\n";
							$anr = $row[0];
						}
					?>
				</select>
			<input type = "submit" value = "Datensatz löschen" />
		</p>
	</div>

<?php
	function artLoeschen($anr) {		
		try {
			$pdo;
			} catch (PDOException $error){
				die ($error->getMessage());
				}
		
		if(!isset($_POST[$anr])) {
			echo "<p><b>Artikel nummer nicht gesetzt</b></p>";
		} else {
			$sqlDelete = $pdo->query("DELETE FROM artikel WHERE anr = :anr");
			if ($stmt = $pdo->prepare($sqlDelete)) {
				$stmt->bindParam(':anr', $anr);
				$stmt->execute();
				echo "<h2><b>Artikel gelöscht!</b></h2>";
			}
		} 
	$pdo = null;	
	}
	
?>

</form>
</body>
</html>

Just for my personal understanding:  What actually happens when I click on "Submit"?  Is the entire contents of the .php script run or just the dynamic component, in this case the DML statement?

Thanks for your help.....

Regards,

Kev

Share this post


Link to post
Share on other sites
7 hours ago, Barand said:

Hi again Barand,

many, many thanks for the tip.  The problem has been solved.  Here is the code:

<!DOCTYPE HTML>

<?php
	$pdo = new PDO ('mysql:dbname=bestelldatenbank;host=localhost;charset=utf8', 'root', '');
	
	unset ($anr);

	if ($_SERVER['REQUEST_METHOD']=='POST') {
		if(isset($_POST['artikel'])) {
			$anr = ($_POST['artikel']);
			artLoeschen($anr);
		} else {
			echo "<p><b>Artikel nummer nicht gesetzt</b></p>";
		}
	}
?>

<html lang="de">
<head>
	<meta charset="utf-8" />
	<title>E3_Artikel_Löschen</title>

</head>
<body>

<form method = "POST">
	<div>
		<p>
			<label for="artikel">Artikel: </label>
				<select id="artikel" name="artikel" id="artikel">
					<?php
						$sqlSelect = "SELECT anr, name FROM artikel ORDER BY anr ASC";
						foreach ($pdo->query($sqlSelect) as $row) {
							echo "<option value=$row[0]>$row[0] | $row[1]</option>\n";
						}
					?>
				</select>
			<input type = "submit" value = "Datensatz löschen" />
		</p>
	</div>

<?php
	function artLoeschen($anr) {
			$pdo = new PDO ('mysql:dbname=bestelldatenbank;host=localhost;charset=utf8', 'root', '');
			$sqlDelete = 'DELETE FROM artikel WHERE anr = :anr';
			if ($stmt = $pdo->prepare($sqlDelete)) {
				$stmt->bindParam(':anr', $anr);
				$stmt->execute();
			echo "<h2><b>Artikel gelöscht!</b></h2>";
			}
			header("refresh:3");
		} 
	$pdo = null;	
?>

</form>
</body>
</html>

Although I consider the problem resolved I would still appreciate any constructive criticism or advice.  There is always room for improvement….

Once again, thank your for your help.

Regards,

Kev

 

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.