Delete a MySQL row from a drop-down HTML menu

KevBurgess · December 14, 2018

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

requinix · December 14, 2018

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.

KevBurgess · December 15, 2018

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

Barand · December 15, 2018

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>
   .....

KevBurgess · December 15, 2018

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

Barand · December 15, 2018

See

handling post variables

KevBurgess · December 15, 2018

7 hours ago, Barand said:

See

handling post variables

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

Sign In

Delete a MySQL row from a drop-down HTML menu

Recommended Posts

KevBurgess

Link to comment

Share on other sites

requinix

Link to comment

Share on other sites

KevBurgess

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

KevBurgess

Link to comment

Share on other sites

Barand

Link to comment

Share on other sites

KevBurgess

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information