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";
}
}
}
?>
