How To Insert And Search From Mysql Database Using PHP HTML Forms And SQL
How To Insert And Search From Mysql Database Using PHP HTML Forms And SQL
This tutorial shows how to insert students data to mysql database using html forms and php server side programming language. Inserted data is also retrieved and displayed on html table using sql queries.
INDEX.PHP
<!DOCTYPE html> <html> <head> <!-- Page Title --> <title>UNIVERSITY PORTAL</title> </head> <body> <!-- Page Header --> <h1>UNIVERSITY PORTAL</h1> <!-- Add student to database page link --> <a href="Add_student.php">Add Student</a><br><br> <!-- search student from database page link --> <a href="Search_student_grade.php">Search Student</a> </body> </html>
Add_student.PHP
<!DOCTYPE html> <html> <head> <title>Add/Insert Student To Mysql Database</title> </head> <body> <!-- HTML Form --> <form action="/SearchAddAStudentPHPPHPMYADMINMYSQL/action_page.php"> <fieldset> <legend> Student information: </legend> Student Name: <!-- Student Name Input Field --> <input type="text" name="name" value="" required><br><br> Email: <!-- Email Input Field --> <input type="email" name="email"value="" required><br><br> Password: <!-- Password Input Field --> <input type="password" name="password"value="" required><br><br> Re-type Password: <!-- Verify Password Input Field --> <input type="password" name="passwordVerify"value="" required><br> <br> <!-- Submit Button Field --> <input type="submit" name="submit" value="Submit"> </fieldset> </form> </body> </html>
action_page.PHP
<?php
// Mysql Database connection
// Server name , username, password, Database name
$db_conn = mysqli_connect("localhost","root","","studentscoursesteachers");
// 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['submit'])) {
// Store data into the variables
$f_name = $_GET['name'];
$em = $_GET['email'];
$pass = $_GET['password'];
$passV = $_GET['passwordVerify'];
if ($pass != $passV) {
# code...
die("Passwords Don't Match. Try Again");
exit();
}
// Storing data into the variables and Escaping special characters.
$name = mysqli_real_escape_string($db_conn, $f_name);
$email = mysqli_real_escape_string($db_conn, $em);
$password = mysqli_real_escape_string($db_conn, $pass);
// Insert query
$sql = "INSERT INTO `students` (name, email, password) VALUES ('$name', '$email', '$password')";
// Inserting data into users table
$insert_data = mysqli_query($db_conn, $sql);
// Check if data inserted
if($insert_data){
echo "Data inserted successfully.";
}
// else data not inserted
else{
echo "Something error occurred";
}
} else //If submit button is clicked
if (isset($_GET['search'])) {
// Store data into the variables
$f_name = $_GET['name'];
// Storing data into the variables and Escaping special characters.
$searchValue = mysqli_real_escape_string($db_conn, $f_name);
//Using joins to query from multiple tables.
$sql = "SELECT students.name AS sname, courses.name AS cname, grades.grade FROM students JOIN grades ON students.id = grades.student_id JOIN courses ON grades.course_id = courses.id WHERE students.name LIKE '%$searchValue%'";
$result = $db_conn->query($sql);
echo "<table >"; //start a table tag in the HTML
echo "<tr><th>" . "Name" . "</th><th>" . "Grade" . "</th><th>" . "Course" . "</th></tr>"; //Table Header.
while ($row = $result->fetch_assoc()) {
echo "<tr><td>" . $row['sname'] . "</td><td>" . $row['grade'] . "</td><td>" . $row['cname'] . "</td></tr>"; //$row['index'] the index here is a field name
}
echo "</table>"; //Close the table in HTML
}
?>
Search_student_grade.PHP
<!DOCTYPE html> <html> <head> <title>Search</title> </head> <body> <!-- HTML Form --> <form action="/SearchAddAStudentPHPPHPMYADMINMYSQL/action_page.php"> <fieldset> <legend> Student information: </legend> Student Name: <!-- Name Input Field --> <input type="text" name="name" value="" required><br><br> <!-- Submit button field --> <input type="submit" name="search" value="Search"> </fieldset> </form> </body> </html>
DATABASE DESIGN
phpMyAdmin SQL Dump
-- phpMyAdmin SQL Dump -- version 5.0.4 -- https://www.phpmyadmin.net/ -- -- Host: 127.0.0.1 -- Generation Time: May 24, 2021 at 09:25 PM -- Server version: 10.4.17-MariaDB -- PHP Version: 8.0.1 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: `studentscoursesteachers` -- CREATE DATABASE IF NOT EXISTS `studentscoursesteachers` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; USE `studentscoursesteachers`; -- -------------------------------------------------------- -- -- Table structure for table `courses` -- CREATE TABLE `courses` ( `id` int(10) NOT NULL, `name` varchar(30) NOT NULL, `teacher_id` int(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `courses` -- INSERT INTO `courses` (`id`, `name`, `teacher_id`) VALUES(10001, 'Computer Science', 1); -- -------------------------------------------------------- -- -- Table structure for table `grades` -- CREATE TABLE `grades` ( `student_id` int(10) NOT NULL, `course_id` int(10) NOT NULL, `grade` varchar(10) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `grades` -- INSERT INTO `grades` (`student_id`, `course_id`, `grade`) VALUES(1, 10001, 'A'); -- -------------------------------------------------------- -- -- Table structure for table `students` -- CREATE TABLE `students` ( `id` int(10) NOT NULL, `name` varchar(30) NOT NULL, `email` varchar(30) NOT NULL, `password` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `students` -- INSERT INTO `students` (`id`, `name`, `email`, `password`) VALUES(1, 'juju', 'vghjgj@gmail.com', '123'); INSERT INTO `students` (`id`, `name`, `email`, `password`) VALUES(3, 'Jane', 'jane@gmail.com', '12345'); -- -------------------------------------------------------- -- -- Table structure for table `teachers` -- CREATE TABLE `teachers` ( `id` int(10) NOT NULL, `name` varchar(30) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; -- -- Dumping data for table `teachers` -- INSERT INTO `teachers` (`id`, `name`) VALUES(1, 'Jane'); -- -- Indexes for dumped tables -- -- -- Indexes for table `courses` -- ALTER TABLE `courses` ADD PRIMARY KEY (`id`), ADD KEY `Constraint_Teacher` (`teacher_id`); -- -- Indexes for table `grades` -- ALTER TABLE `grades` ADD KEY `Constraint_Student` (`student_id`), ADD KEY `Constraint_Course` (`course_id`); -- -- Indexes for table `students` -- ALTER TABLE `students` ADD PRIMARY KEY (`id`); -- -- Indexes for table `teachers` -- ALTER TABLE `teachers` ADD PRIMARY KEY (`id`); -- -- AUTO_INCREMENT for dumped tables -- -- -- AUTO_INCREMENT for table `students` -- ALTER TABLE `students` MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=4; -- -- AUTO_INCREMENT for table `teachers` -- ALTER TABLE `teachers` MODIFY `id` int(10) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=2; -- -- Constraints for dumped tables -- -- -- Constraints for table `courses` -- ALTER TABLE `courses` ADD CONSTRAINT `Constraint_Teacher` FOREIGN KEY (`teacher_id`) REFERENCES `teachers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE; -- -- Constraints for table `grades` -- ALTER TABLE `grades` ADD CONSTRAINT `Constraint_Course` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE, ADD CONSTRAINT `Constraint_Student` FOREIGN KEY (`student_id`) REFERENCES `students` (`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 */;
WEB APPLICATION AND MYSQL DATABASE SCREENSHOTS
