MySQL CRUD through HTML interface

From Knowledge Kitchen
Jump to navigation Jump to search


The following code represents a web application that allows us to manage the data stored in a favorite viking metal bands table in our database.

This example extends these concepts concepts:


MySQL table setup

Table

In this example, we assume you have already created a table named "favorite_viking_metal_bands".

Data

It is further assumed that you have imported the CSV data file of Viking metal bands into the table according to the MySQL CSV data import instructions here.

Flow diagram

This diagram shows how all the pages, scripts, and user actions fit together to create this application. MySQL CRUD through HTML Interface Flow Diagram.png


Pages

From an end-user's perspective, the application consists of several HTML pages that can appear in the browser:

Home page (read.php)

  • this page shows the user a list of all viking metal bands in alphabetic order
  • next to each band is a set of icons the user can click to Delete or Edit any given band.
  • clicking the Edit icon links the user to the Edit Band page
  • clicking the Delete icon links to the Delete Band script
  • this is the main page of our application, and gives an administrator access to all of the functionality of our app.

Add New Band page (create.php)

  • this page shows the user a form they can fill out to enter a new viking metal band into the table.
  • the user reaches this page by clicking a link from the Home page.
  • when the user submits the form on this page, the browser sends the form data to the Process New Band script

Edit Band page (edit.php)

  • this page shows the user a form pre-populated with data about a single Viking metal band
  • the user can modify this information and save the changes.
  • from an end-user perspective, this page looks similar to the Add New Band page, but behind the scenes, the PHP code is performing different tasks.
  • when the user submits the form on this page, the browser sends the form data to the Process Edit Band script

Scripts

Several "invisible" scripts perform important tasks in this application, but are never visible as web pages in the user's browser, so user's never see them directly.

Process New Band (process_create.php)

  • this script is requested by the browser once the user clicks submit from the Add New Band page.
  • this script retrieves the data the user entered into the form using PHP's built-in $_POST variable, packages it up into a nice MySQL INSERT query, and sends the query to the MySQL database to tell it to store the data as a new row.
  • once the new row has been added, the script then instructs the browser to return to the Home page by sending a custom HTTP Location header to the browser.

Process Edit Band (process_update.php)

  • this script is requested by the browser once the user clicks submit from the Edit Band page.
  • this script retrieves the data the user entered into the form using PHP's built-in $_POST variable, packages it up into a nice MySQL UPDATE query, and sends the query to the MySQL database to tell it to update an existing row in the table.
  • once the row has been updated, the script then instructs the browser to return to the Home page by sending a custom HTTP Location header to the browser.

Process Delete Band (process_delete.php)

  • this script is requested by the browser once the user clicks a delete icon next to any band name on the Home page.
  • the delete icon links to this page each contain a query string in their URLs with the id number of the band row to delete
  • this script retrieves that band id number using PHP's built-in $_GET variable, packages it up into a nice MySQL INSERT query, and sends the query to the MySQL database to tell it to delete the row from the table.
  • once the row has been deleted, the script then instructs the browser to return to the Home page by sending a custom HTTP Location header to the browser.

The code

Home page (read.php)

 1 <?php
 2 
 3 //DEBUGGING: try to force the server to output PHP errors... some servers need this
 4 error_reporting(E_ALL);
 5 ini_set("display_errors", 1);
 6 
 7 //connect to the database server
 8 $cxn = new mysqli("<database server>", "<username>", "<password>", "<database name>");
 9 
10 //DEBUGGING: output any connection error
11 print("Connection error: " . $cxn->connect_error);
12  
13 //assemble the query
14 $query = "SELECT * FROM favorite_viking_metal_bands ORDER BY band ASC";
15 
16 //execute the query. 
17 $result = $cxn->query($query); 
18 
19 //DEBUGGING: output any query error
20 print("Query error: " . $cxn->error);
21 
22 ?>
23 <!DOCTYPE html>
24 <html>
25 	<head>
26 		<title>Read</title>
27 		<meta charset="utf-8" />
28 		<style>
29 img.delete {
30 	width: 10px;
31 	margin-left: 10px;
32 }		
33 		</style>
34 	</head>
35 	<body>
36 		<h1>Read from table</h1>
37 		<p><a href="create.php">Click here</a> to add a new viking metal band</p>
38 
39 		<ul>
40 <?php while($row = $result->fetch_assoc()) : ?>
41 			<li>
42 				<?php echo $row["band"]; ?>
43 			 	-
44 			 	<?php echo $row["origin"]; ?>
45 			 	-
46 			 	<?php echo $row["formed"]; ?>
47 
48 			 	<a href="edit.php?band_id=<?php echo $row['id'];?>">
49 				 	<img class='delete' src='images/edit.png' />
50 				</a>
51 
52 			 	<a href="process_delete.php?band_id=<?php echo $row['id'];?>">
53 				 	<img class='delete' src='images/delete.png' />
54 				</a>
55 			 </li>
56 <?php endwhile; ?>
57 		</ul>
58 	</body>
59 </html>


Add New Band page (read.php)

 1  
 2 <!DOCTYPE html>
 3 <html>
 4 	<head>
 5 		<title>Create</title>
 6 		<meta charset="utf-8" />
 7 	</head>
 8 	<body>
 9 		<h1>Create a row</h1>
10 
11 		<form method="POST" action="process_create.php">
12 			<label>band name: </label>
13 			<input name="band" type="text" />
14 			<br />
15 
16 			<label>country: </label>
17 			<input name="origin" type="text" />
18 			<br />
19 
20 			<label>year formed: </label>
21 			<input name="formed" type="text" />
22 			<br />
23 
24 			<input type="submit" value="Save" />
25 
26 		</form>
27 	</body>
28 </html>


Edit Band page (read.php)

 1  
 2 <?php
 3 
 4 //DEBUGGING: try to force the server to output PHP errors... some servers need this
 5 error_reporting(E_ALL);
 6 ini_set("display_errors", 1);
 7 
 8 //get the data from the URL
 9 $bandId = $_GET['band_id'];
10 
11 //connect to the database server
12 $cxn = new mysqli("<database server>", "<username>", "<password>", "<database name>");
13 
14 //DEBUGGING: output any connection error
15 print("Connection error: " . $cxn->connect_error);
16 
17 //assemble the query
18 //with all the variables inserted, your query should look something like:
19 //SELECT * FROM favorite_viking_metal_bands WHERE id=15
20 $query = "SELECT * FROM favorite_viking_metal_bands WHERE id={$bandId}";
21 
22 //DEBUGGING: print out the query with the variables inserted to make sure it looks valid
23 //print("Query: " . $query);
24 
25 //execute the query. 
26 $result = $cxn->query($query); 
27 
28 //DEBUGGING: output any query error
29 print("Query error: " . $cxn->error);
30 
31 //get the one row from the result set
32 $row = $result->fetch_assoc();
33 
34 ?>
35 <!DOCTYPE html>
36 <html>
37 	<head>
38 		<title>Create</title>
39 		<meta charset="utf-8" />
40 	</head>
41 	<body>
42 		<h1>Edit a row</h1>
43 
44 		<form method="POST" action="process_update.php">
45 			<input type="hidden" name="band_id" value="<?php echo $row['id']; ?>" />
46 			
47 			<label>band name: </label>
48 			<input name="band" type="text" value="<?php echo $row['band']; ?>" />
49 			<br />
50 
51 			<label>country: </label>
52 			<input name="origin" type="text" value="<?php echo $row['origin']; ?>"  />
53 			<br />
54 
55 			<label>year formed: </label>
56 			<input name="formed" type="text" value="<?php echo $row['formed']; ?>"  />
57 			<br />
58 
59 			<input type="submit" value="Save" />
60 
61 		</form>
62 	</body>
63 </html>


Process New Band script (process_create.php)

 1  
 2 <?php
 3 
 4 //DEBUGGING: try to force the server to output PHP errors... some servers need this
 5 error_reporting(E_ALL);
 6 ini_set("display_errors", 1);
 7 
 8 //debugging
 9 //print_r($_POST);
10 
11 //get the data the user entered into the form
12 $band = $_POST['band'];
13 $origin = $_POST['origin'];
14 $formed = $_POST['formed'];
15 
16 //connect to the database server
17 $cxn = new mysqli("<database server>", "<username>", "<password>", "<database name>");
18 
19 //DEBUGGING: output any connection error
20 print("Connection error: " . $cxn->connect_error);
21 
22 //assemble the query
23 //with all the variables inserted, your query should look something like:
24 //INSERT INTO favorite_viking_metal_bands (band, origin, formed) VALUES ('Nordic Plague', 'Norway', 1986)
25 $query = "INSERT into favorite_viking_metal_bands (band, origin, formed) VALUES('" . $band . "','" . $origin . "'," . $formed . ");";
26 
27 //DEBUGGING: print out the query with the variables inserted to make sure it looks valid
28 //print("Query: " . $query);
29 
30 //execute the query. 
31 $result = $cxn->query($query); 
32  
33 //DEBUGGING: output any query error
34 print("Query error: " . $cxn->error);
35 
36 //redirect the browser to the main page
37 header("Location:read.php");
38 
39 ?>


Process Edit Band script (process_update.php)

 1  
 2 <?php
 3 
 4 //DEBUGGING: try to force the server to output PHP errors... some servers need this
 5 error_reporting(E_ALL);
 6 ini_set("display_errors", 1);
 7 
 8 //debugging
 9 //print_r($_POST);
10 
11 //get the data the user entered into the form
12 $bandId = $_POST['band_id'];
13 $band = $_POST['band'];
14 $origin = $_POST['origin'];
15 $formed = $_POST['formed'];
16 
17 //connect to the database server
18 $cxn = new mysqli("<database server>", "<username>", "<password>", "<database name>");
19 
20 //DEBUGGING: output any connection error
21 //print("Connection error: " . $cxn->connect_error);
22 
23 //assemble the query
24 //with all the variables inserted, your query should look something like:
25 //UPDATE favorite_viking_metal_bands SET band='Nordic Plague', origin='Norway', formed=1986 WHERE id=15
26 $query = "UPDATE favorite_viking_metal_bands SET band='{$band}', origin='{$origin}', formed={$formed} WHERE id={$bandId}";
27 
28 //DEBUGGING: print out the query with the variables inserted to make sure it looks valid
29 //print("Query: " . $query);
30 
31 //execute the query. 
32 $result = $cxn->query($query); 
33 
34 //DEBUGGING: output any query error
35 print("Query error: " . $cxn->error);
36 
37 //redirect the browser to the main page
38 header("Location:read.php");
39 
40 ?>

Process Delete Band script (process_delete.php)

 1  
 2 <?php
 3 
 4 //DEBUGGING: try to force the server to output PHP errors... some servers need this
 5 error_reporting(E_ALL);
 6 ini_set("display_errors", 1);
 7 
 8 //get the data from the link that the user clicked
 9 $bandId = $_GET['band_id'];
10 
11 //connect to the database server
12 $cxn = new mysqli("<database server>", "<username>", "<password>", "<database name>");
13 
14 //DEBUGGING: output any connection error
15 print("Connection error: " . $cxn->connect_error);
16 
17 //assemble the query
18 //with all the variables inserted, your query should look something like:
19 //DELETE FROM favorite_viking_metal_bands WHERE id=15
20 $query = "DELETE FROM favorite_viking_metal_bands WHERE id=" . $bandId;
21 
22 //DEBUGGING: print out the query with the variables inserted to make sure it looks valid
23 //print("Query: " . $query);
24 
25 //execute the query. 
26 $result = $cxn->query($query); 
27 
28 //DEBUGGING: output any query error
29 print("Query error: " . $cxn->error);
30 
31 //redirect the browser to the main page
32 header("Location:read.php");
33 
34 ?>

Images

You will need two images in a subfolder named images:

edit.png
delete.png


What links here