Insert Delete Read Data From Mysql Table Using PHP MySQL HTML Xampp Localhost(phpMyAdmin)

Insert Delete Read Data From Mysql Table Using PHP MySQL HTML Xampp Localhost(phpMyAdmin)

DEMO VIDEO

SOURCE CODE

index.php

<!DOCTYPE html>
<html>
	<head>
		<!-- Page Title -->
		<title>MOVIES AND ACTORS</title>
		<link rel="stylesheet" href="mystyle.css">
	</head>
	<body>
		<!-- Page Header -->
		<h1>MOVIES AND ACTORS HTML PHP MYSQL DATABASE</h1>
		<!-- Add Movie to database page link -->
		<a href="insertMovie.php">Add Movie</a><br><br>
		<!-- Add Actor to database page link -->
		<a href="insertActor.php">Add Actor</a><br><br>
		<!-- Add Actor to database page link -->
		<a href="insertActorToAMovie.php">Add Actor To A Movie</a><br><br>
		<!-- View Movie Actors -->
		<a href="viewMovieActors.php">View Movie Actors</a>
	</body>
</html>

insertMovie.php

<!DOCTYPE html>
<html>
	<head>
		<title>Movie</title>
		<link rel="stylesheet" href="mystyle.css">
	</head>
	<body>
		<!-- HTML Form -->
		<form action="/MoviesActorsPHPMySQLDatabase/actionPage.php"> 
			<fieldset> 
				<legend>
					Movie information:  
				</legend>
				Movie Name:
				<!-- Name Input Field  -->
				<input type="text" name="name" value="" required><br><br>
				<!-- Submit button field -->
				<input type="submit" name="submitMovie" class="button" value="submit"> 
			</fieldset> 
			
		</form>
	</body>
</html>

insertActor.php

<!DOCTYPE html>
<html>
	<head>
		<title>Actor</title>
		<link rel="stylesheet" href="mystyle.css">
	</head>
	<body>
		<!-- HTML Form -->
		<form action="/MoviesActorsPHPMySQLDatabase/actionPage.php"> 
			<fieldset> 
				<legend>
					Actor information:  
				</legend>
				Actor Name:
				<!-- Name Input Field  -->
				<input type="text" name="name" value="" required><br><br>
				<!-- Submit button field -->
				<input type="submit" name="submitActor" class="button" value="submit"> 
			</fieldset> 
			
		</form>
	</body>
</html>

insertActorToAMovie.php

<?php
	// Mysql Database connection
	// Server name , username, password, Database name
	$databaseConnection = mysqli_connect("localhost","root","","moviesactors");
	
	// Check database connection status
	if (mysqli_connect_errno()){
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
	}
?>
<!DOCTYPE html>
<html>
	<head>
		<title>Actor</title>
		<link rel="stylesheet" href="mystyle.css">
	</head>
	<body>
		<!-- HTML Form -->
		<form action="/MoviesActorsPHPMySQLDatabase/actionPage.php">
			<fieldset>
				<legend>
					Actor/Movie information:
				</legend>
				Actor Name:
				<!-- Name select Input Field -->
				<?php
					$query = "SELECT * FROM `actors` WHERE 1";
					$res = mysqli_query($databaseConnection, $query);
					echo "<select name = 'nameA'>";
					while (($row = mysqli_fetch_array($res)) != null)
					{
						echo "<option value = '{$row['id']}'";
						echo ">{$row['name']}</option>";
					}
					echo "</select><br><br>";
				?>
				Movie Name:
				<!-- Actors select Input Field -->
				
				<?php
					$query = "SELECT * FROM `movies` WHERE 1";
					$res = mysqli_query($databaseConnection, $query);
					echo "<select name = 'nameM'>";
					while (($row = mysqli_fetch_array($res)) != null)
					{
						
						echo "<option value = '{$row['id']}'";
						echo ">{$row['name']}</option>";
					}
					echo "</select><br><br>";
				?>
				
				<!-- Submit button field -->
				<input type="submit" name="submitActorMovie" class="button" value="submit">
			</fieldset>
			
		</form>
	</body>
</html>		

viewMovieActors.php

<?php
	// Mysql Database connection
	// Server name , username, password, Database name
	$databaseConnection = mysqli_connect("localhost","root","","moviesactors");
	
	// Check database connection status
	if (mysqli_connect_errno()){
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
	}
?>
<!DOCTYPE html>
<html>
	<head>
		<title>Movie Actors</title>
		<link rel="stylesheet" href="mystyle.css">
	</head>
	<body>
		<!-- HTML Form -->
		<form action="">
			<fieldset>
				<legend>
					Movie Actors information:
				</legend>
				Movie Name:
				<!-- Actors select Input Field -->
				
				<?php
					$query = "SELECT * FROM `movies` WHERE 1";
					$res = mysqli_query($databaseConnection, $query);
					echo "<select name = 'nameM'>";
					while (($row = mysqli_fetch_array($res)) != null)
					{
						echo "<option value = '{$row['name']}'";
						echo ">{$row['name']}</option>";
					}
					echo "</select><br><br>";
				?>
				
				<!-- Submit button field -->
				<input type="submit" name="submitShowMovieActors" class="button" value="Show Movie Actors">
			</fieldset>
			
			
		</form>
		<?php
			if (isset($_GET['submitShowMovieActors'])) {
				
				// Store data into the variables
				$m_name = $_GET['nameM'];
				
				// Storing data into the variables and Escaping special characters.
				$nameM = mysqli_real_escape_string($databaseConnection, $m_name);
				
				// query
				$selectMovieActorsQuery = "SELECT
				actors.name As Actors
				FROM movies
				JOIN moviesactors
				ON movies.id = moviesactors.movies_id
				JOIN actors
				ON actors.id = moviesactors.actors_id
				WHERE movies.name = '$nameM' ";
				// results
				$res = mysqli_query($databaseConnection, $selectMovieActorsQuery);
				if ($res) {
					# code...
					$rowcount=mysqli_num_rows($res);			
					
					
				}
				if ($rowcount > 0) {
					# code...
					echo "<ul>";
					while( ($row = mysqli_fetch_array($res))  != null )
					{
						echo "<li class=''><a href='#'>";
						echo $row['Actors'];
						echo "</a></li>";
					}
					echo "</ul>";
					
					} else {
					echo "No Actors Found For The Selected Movie!";
					exit();
				}
			}
		?>
	</body>
</html>						

actionPage.php

<?php
	
	// Mysql Database connection
	// Server name , username, password, Database name
	$databaseConnection = mysqli_connect("localhost","root","","moviesactors");
	
	// Check database connection status
	if (mysqli_connect_errno()){
		echo "Failed to connect to MySQL: " . mysqli_connect_error();
	}
	
	//If submit button is clicked
	if (isset($_GET['submitMovie'])) {
		
		// Store data into the variables
		$m_name = $_GET['name'];
		
		// Storing data into the variables and Escaping special characters.
		$name = mysqli_real_escape_string($databaseConnection, $m_name);
		
		
		// Insert query
		$sql = "INSERT INTO `movies` (name) VALUES ('$name')";
		
		// Inserting data into users table
		$insert_data = mysqli_query($databaseConnection, $sql);
		
		// Check if data inserted
		if($insert_data){
			echo "Data inserted successfully.";
		}
		// else data not inserted
		else{
			echo "Something error occurred";
		}
	} else //If submit/submitActor button is clicked
	if (isset($_GET['submitActor'])) {
		
		// Store data into the variables
		$m_name = $_GET['name'];
		
		// Storing data into the variables and Escaping special characters.
		$name = mysqli_real_escape_string($databaseConnection, $m_name);
		
		
		// Insert query
		$sql = "INSERT INTO `actors` (name) VALUES ('$name')";
		
		// Inserting data into users table
		$insert_data = mysqli_query($databaseConnection, $sql);
		
		// Check if data inserted
		if($insert_data){
			echo "Data inserted successfully.";
		}
		// else data not inserted
		else{
			echo "Something error occurred";
		}
	} else //If submit/submitActorMovie button is clicked
	if (isset($_GET['submitActorMovie'])) {
		
		// Store data into the variables
		$a_name = $_GET['nameA'];
		$m_name = $_GET['nameM'];
		
		// Storing data into the variables and Escaping special characters.
		$nameA = mysqli_real_escape_string($databaseConnection, $a_name);
		$nameM = mysqli_real_escape_string($databaseConnection, $m_name);
		
		// Check If Exists query
		$sqlCheckIfExists = "SELECT `id`, `movies_id`, `actors_id` FROM `moviesactors` WHERE `movies_id` = '$nameM' AND `actors_id` = '$nameA' ";
		// data Exists users table
		$data_Exists = mysqli_query($databaseConnection, $sqlCheckIfExists);
		if ($data_Exists) {
			# code...
			$rowcount=mysqli_num_rows($data_Exists);
			
		}
		if ($rowcount > 0) {
			# code...
			echo "That Actor Is Already Assigned To The Selected Movie!";
			exit();
			
			} else {
			# code...
			
			// Insert query
			$sql = "INSERT INTO `moviesactors` (`movies_id`, `actors_id`) VALUES ('$m_name', '$a_name')";
			
			// Inserting data into users table
			$insert_data = mysqli_query($databaseConnection, $sql);
			
			// Check if data inserted
			if($insert_data){
				echo "Data inserted successfully.";
			}
			// else data not inserted
			else{
				echo "Something error occurred";
			}
		}
	}
	
	
	
	
?>				

mystyle.css

a:link, a:visited {
  background-color: white;
  color: black;
  border: 2px solid green;
  padding: 10px 20px;
  text-align: center;
  text-decoration: none;
  display: inline-block;
}

a:hover, a:active {
  background-color: green;
  color: white;
}
.button {
  background-color: #4CAF50;
  border: none;
  color: white;
  padding: 15px 32px;
  text-align: center;
  text-decoration: none;
  display: inline-block;
  font-size: 16px;
  margin: 4px 2px;
  cursor: pointer;
}
fieldset {
  background-color: #eeeeee;
}

legend {
  background-color: gray;
  color: white;
  padding: 5px 10px;
}

input {
  margin: 5px;
}

input[type=text], select {
  width: 100%;
  padding: 12px 20px;
  margin: 8px 0;
  display: inline-block;
  border: 1px solid #ccc;
  border-radius: 4px;
  box-sizing: border-box;
}


ul {
  counter-reset: li; 
  list-style: none; 
  padding: 0;
  text-shadow: 0 1px 0 rgba(255,255,255,.5);
}

ul a {
  position: relative;
  display: block;
  padding: .4em .4em .4em 2em;
  margin: .5em 0;
  background: #DAD2CA;
  color: #444;
  text-decoration: none;
  border-radius: .3em;
  transition: .3s ease-out;
}

ul a:hover { background: #E9E4E0; }
ul a:hover:before { transform: rotate(360deg); }
ul a:before {
  content: counter(li);
  counter-increment: li;
  position: absolute;
  left: -1.3em;
  top: 50%;
  margin-top: -1.3em;
  background: #f9dd94;
  height: 2em;
  width: 2em;
  line-height: 2em;
  border: .3em solid #fff;
  text-align: center;
  font-weight: bold;
  border-radius: 2em;
  transition: all .3s ease-out;
}

phpMyAdmin SQL Dump

-- phpMyAdmin SQL Dump
-- version 5.1.0
-- https://www.phpmyadmin.net/
--
-- Host: 127.0.0.1
-- Generation Time: May 28, 2021 at 03:44 PM
-- Server version: 10.4.19-MariaDB
-- PHP Version: 8.0.6

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;

--
-- Database: `moviesactors`
--
CREATE DATABASE IF NOT EXISTS `moviesactors` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `moviesactors`;

-- --------------------------------------------------------

--
-- Table structure for table `actors`
--

CREATE TABLE `actors` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `actors`
--

INSERT INTO `actors` (`id`, `name`) VALUES(1, 'Jason Statham');
INSERT INTO `actors` (`id`, `name`) VALUES(2, 'Josh Hartnett');
INSERT INTO `actors` (`id`, `name`) VALUES(3, 'Holt McCallany');
INSERT INTO `actors` (`id`, `name`) VALUES(6, 'Bruce Willis');

-- --------------------------------------------------------

--
-- Table structure for table `movies`
--

CREATE TABLE `movies` (
  `id` int(11) NOT NULL,
  `name` varchar(30) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `movies`
--

INSERT INTO `movies` (`id`, `name`) VALUES(1, 'Wratt Of Man');
INSERT INTO `movies` (`id`, `name`) VALUES(2, 'Green Honnet');
INSERT INTO `movies` (`id`, `name`) VALUES(3, 'Clash Of Titans');

-- --------------------------------------------------------

--
-- Table structure for table `moviesactors`
--

CREATE TABLE `moviesactors` (
  `id` int(11) NOT NULL,
  `movies_id` int(11) NOT NULL,
  `actors_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

--
-- Dumping data for table `moviesactors`
--

INSERT INTO `moviesactors` (`id`, `movies_id`, `actors_id`) VALUES(1, 1, 1);
INSERT INTO `moviesactors` (`id`, `movies_id`, `actors_id`) VALUES(2, 1, 3);
INSERT INTO `moviesactors` (`id`, `movies_id`, `actors_id`) VALUES(3, 1, 2);
INSERT INTO `moviesactors` (`id`, `movies_id`, `actors_id`) VALUES(11, 1, 6);
INSERT INTO `moviesactors` (`id`, `movies_id`, `actors_id`) VALUES(12, 2, 6);

--
-- Indexes for dumped tables
--

--
-- Indexes for table `actors`
--
ALTER TABLE `actors`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `movies`
--
ALTER TABLE `movies`
  ADD PRIMARY KEY (`id`);

--
-- Indexes for table `moviesactors`
--
ALTER TABLE `moviesactors`
  ADD PRIMARY KEY (`id`),
  ADD KEY `Constraint_FK_Movies_ID` (`movies_id`),
  ADD KEY `Constraint_FK_actors_ID` (`actors_id`);

--
-- AUTO_INCREMENT for dumped tables
--

--
-- AUTO_INCREMENT for table `actors`
--
ALTER TABLE `actors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=7;

--
-- AUTO_INCREMENT for table `movies`
--
ALTER TABLE `movies`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4;

--
-- AUTO_INCREMENT for table `moviesactors`
--
ALTER TABLE `moviesactors`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=13;

--
-- Constraints for dumped tables
--

--
-- Constraints for table `moviesactors`
--
ALTER TABLE `moviesactors`
  ADD CONSTRAINT `Constraint_FK_Movies_ID` FOREIGN KEY (`movies_id`) REFERENCES `movies` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
  ADD CONSTRAINT `Constraint_FK_actors_ID` FOREIGN KEY (`actors_id`) REFERENCES `actors` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;
COMMIT;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;

SCREENSHOTS

DATABASE DESIGN

DATABASE STRUCTURE AND TABLES

Leave a Reply

Your email address will not be published. Required fields are marked *