PHP Connecting to MYSQL Database (creating the simple search box with database)

PHP Connecting to Database

In this tutorial, we are working with PHP and MySQL database. We are going to create a simple PHP page that will search for the name entered by the user in the database.

NOTE: Full code is in the last of the post.

 In order to work with the PHP and MySQL database, you should have installed both in your machine. 
check my post for installing the PHP into the Machine.

I hope you have installed PHP and MySQL.

without wasting much time let's dive to create the page.

First, create a file named searchpage.php into your local server directory. 
local server directory is a specific folder, which can be accessed by the web server locally or globally.

Create the HTML schema which is 

And create a form in the body tag. you can try doing this your own.


In the form create one input field with the name attribute "name" and create one submit button using the input tag with the name of "submit" and Value of "Search".

now HTML work is almost complete.

let's start working with the PHP.

to work with the database we have to use a simple strategy, which is splitted into the 2 steps.

1. connect to the database server and to the particular database.
2. query your tables, and get results.

ok, first we write some code to connect to the database.

paste this code on the top of the file.

<?php 
$mysql_host = "localhost";
$mysql_user = "root"; // for you this value can be different
$mysql_password = "";  // password can be something else for you
$mysql_db = "test";
// Create connection
$conn = mysqli_connect($mysql_host, $mysql_user, $mysql_password);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
else{
echo "connected Successfully";
}

this code will ensure that we are connected to the database server.

now try to connect to the our particular dataabse which is test.

mysqli_select_db($conn,$mysql_db);

now our database are selected

now lets try to handle the form input from the user.
to handle data from Forms PHP has two methods one is GET and another is POST.
both the methods have their roles in the different sections, as GET are used in the searchs or where we can expose our data into the url. 
And POST are used into the login and secure search and where we don't want to expose our data in the link (url).

Enough knowledge let's work.

$show = false;
if(isset($_POST['submit'])){
$name = $_POST['name']; // taking the value of name input into the variable.
$query = "SELECT * FROM actors WHERE name='$name'";
$result = mysqli_query($conn, $query )or die( "Query failed: ". mysqli_error($conn) );
$show = true;
}
?>

This variable $show is to stop to show the result for the refresh of the page, you can try to work without the $show variable and see the magic.
NOTE: if you are removing the $show variable make sure to remove all its usages.

Now finally we have queried the database this is the time to show the extracted data.

<?php
if($show){
if (mysqli_num_rows($result) > 0) {
 echo "Search Result found in the database <br><br>";
 while($row = mysqli_fetch_assoc($result)) {
        echo "Name: " . $row["name"]. " " . $row["last_name"]. "<br><br>";
    }
}else{
 echo "Nothing found!";
}
 mysqli_free_result( $result ); } ?>

in the given code first, we are checking for the number of results returned, and if the results are greater than zero than then print them otherwise, echo nothing found.
DATABASE Details

database name = test

table name = actors

these are the fields.

id,name,last_name;

this is the all we need to design what we are wanted to do here are the sample run of the websites.

and the second one

FULL CODE IN THE ORDER.

<?php 
$mysql_host = "localhost";
$mysql_user = "root"; 
$mysql_password = ""; 
$mysql_db = "test";
// Create connection
$conn = mysqli_connect($mysql_host, $mysql_user, $mysql_password);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}
else{
echo "connected Successfully";
}
mysqli_select_db($conn,$mysql_db);

$show = false;
if(isset($_POST['submit'])){
$name = $_POST['name']; // taking the value of name input into the variable.
$query = "SELECT * FROM actors WHERE name='$name'";
$result = mysqli_query($conn, $query )or die( "Query failed: ". mysqli_error($conn) );
$show = true;
}

?>
<!DOCTYPE html>
<html>
<head>
 <title>Search user</title>
</head>
<body>
 <form method="post" action="searchpage.php">
  <label for="name">Enter a name to search: </label>
  <input type="text" name="name" id="name">
  <input type="submit" name="submit" value="Search">
 </form>
</body>
</html>

<?php
if($show){
if (mysqli_num_rows($result) > 0) {
 echo "Search Result found in the database <br><br>";
 while($row = mysqli_fetch_assoc($result)) {
       echo "Name: " . $row["name"]. " " . $row["last_name"]. "<br><br>";
    }
}else{
 echo "Nothing found!";
}
// free result
mysqli_free_result( $result );
}
?>

Thanks for being here.

Are you working on the  Web development or trying to create a school project or working on the simple website, and having trouble with learning the basics of the PHP, Java, Python, C#, C++, C. You are at the right place...

if you likable this post please share this along with your friends.