These are simple search and signup PHP files that I made using mmtuts tutorials on using PHP to interact with MySQL database. They are made of several components (PHP files) that link to one another. This also requires you to have a MySQL database in order to work. I packed it in a zip file so it can be downloaded and extracted easily. In the zip file, there is an index file that links the search database php file and the registration php file. Both files are connected to another two files inside the includes folder — the php file responsible for connecting PHP to MySQL, and the php file that creates prepared statements for signup.
You can download the zip file here:
https://drive.google.com/file/d/1s4Kf-XFT2ldlKaqSaFBmP3jPrkrxQr4f/view?usp=sharing
Instruction
The zip file contains createuser.php (which is a signup page), searchdb.php (a search page that allows you to search the database), index.php (links createuser.php and searchdb.php), readme.txt (contains instruction), sql-command.txt (contains command for MySQL), an includes folder containing db_inc.php (used to connect createuser.php and searchdb.php to the database), and signup_inc.php (used by createuser.php to provide it with prepared statements).
Steps:
1. Create a database name altometa in PHPMyAdmin. Run PHPMyAdmin, select New in the Sub Menu, create database with the name “altometa”.
2. Under altometa database, use the content of sql-command.txt on PHPMyAdmin’s SQL command line.
3. Extract the zip file contents into htdocs folder for XAMMP and MAMP, and www folder for WAMP.
4. Run the index.php with XAMMP, MAMP or WAMP (I’m assuming you already know the basics of how these things work).
If you guys don’t know some things like XAMMP, MAMP or WAMP, and how to setup local server on your PC, ask Mr. Google. As for MySQL, you can get the tutorial here.
The Codes
There are multiple PHP files used for this in order to work, I’ll explain everything. This will also serve as documentation for this simple project.
Code for index.php
<!DOCTYPE html> <html> <head> <title>Index</title> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> </head> <body> <strong>Database Index Page</strong><br/> Code written by Altometa<br/> <a href="" target="_blank"></a> <br/><br/> <strong>Links</strong><br/> <a href="searchdb.php">Search Database</a><br/> <a href="createuser.php">Create New User</a> </body> </html>
This is just html so no need to explain.
Code for searchdb.php
<?php
include_once 'includes/db_inc.php';
?>
<!DOCTYPE html>
<html>
<head>
<title>Search Database</title>
<meta name="viewport" content="width=device-width, initial-scale=1.0" />
</head>
<body>
<a href="index.php"><< Back to Index Page</a><br/><br/>
<strong>Search Database</strong><br/><br/>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
Search for <input type="text" name="keyword" /> under
<select name="options" />
<option value="FirstNme">First Name</option>
<option value="LastNme">Last Name</option>
<option value="EmailAd">Email</option>
<option value="UserID">User ID</option>
<option value="Rank">Rank</option>
</select>
<input type="submit" name="submit" />
</form>
<?php
if(isset($_POST['submit'])) {
if(isset($_POST['keyword'])) {
$keyword = mysqli_real_escape_string($conn, $_POST['keyword']);
}
if(isset($_POST['options'])) {
$lookup = $_POST['options'];
if($lookup == 'FirstNme') {
$lookup = 'first_name';
}
if($lookup == 'LastNme') {
$lookup = 'last_name';
}
if($lookup == 'EmailAd') {
$lookup = 'email_ad';
}
if($lookup == 'UserID') {
$lookup = 'uid';
}
if($lookup == 'Rank') {
$lookup = 'rank';
}
}
$sql = "SELECT * FROM dbusers WHERE $lookup=?;";
$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt,$sql)){
echo "SQL Statement Failed!";
} else {
mysqli_stmt_bind_param($stmt, "s", $keyword);
mysqli_stmt_execute($stmt);
$result = mysqli_stmt_get_result($stmt);
$resultCheck = mysqli_num_rows($result);
if ($resultCheck > 0) {
echo "<br><strong>Search Results</strong><br><br>";
while ($row = mysqli_fetch_assoc($result)) {
echo $row['uid'] . " - " . $row['first_name'] . " " . $row['last_name'] . " | " . $row['email_ad'] . " | " . $row['rank'] . "<br>";
}
} else {
echo "<br>" . 'No Result Found';
}
}
// Disregarded in favor of Prepared Statement
// $result = mysqli_query($conn, $sql);
// $resultCheck = mysqli_num_rows($result);
// if ($resultCheck > 0) {
// while ($row = mysqli_fetch_assoc($result)) {
// echo "<br><strong>Search Results</strong><br><br>" . $row['uid'] . " - " . $row['first_name'] . " " . $row['last_name'] . " | " . $row['email_ad'] . " | " . $row['rank'];
// }
// } else {
// echo "<br>" . 'No Result Found';
// }
}
?>
</body>
</html>
Notice the line with include_once ‘includes/db_inc.php’? It means that it’s gonna draw a function from another separate PHP file. The rest of the code creates a page that has a simple search engine. The search engine has a search field and a dropdown list that gives you the option to search base on First Name, Last Name, Email Add, User ID or Rank. Each option as you can see in the code targets specific columns in the database. If it founds the keyword under column x (where x = column name), it will return a user account info.
At line 28, note mysqli_real_escape_string. This means that PHP will insert the search keyword as string but not run any code in a query. It acts like a filter that prevents MySQL code injection. $conn is a variable defined in inlcudes/db_inc.php.
At line 48, notice that it is a command use for MySQL. $sql is a variable that stores a query. The command says to SELECT or search all columns from dbusers database where column is ?. The “?” is a character that represents something in the prepared statement argument. $stmt is a variable that contains the prepared statement initialize command (mysqli_stmt_init), followed by the condition that if prepared statement didn’t work then show message “SQL Statement Failed!” otherwise bind parameter, “s” to keyword, where s is a variable to replace another variable. Why? So that when a user enters a keyword, that keyword stored in a variable will be hidden in another variable and thus cloak it.
At line 55, it is the command use to retrieve result using prepared statement. 56 runs a check to see if there is at least one returned result. If there is a returned result, the first condition to display the search result(s) will execute else it will display a “No Result Found” message.
Beginning at line 67 til 76, is an old code that was commented out in favor of prepared statements. This set of codes will do the same search function but without using prepared statements. At line 68, mysqli_query is the command use to search the database.
Code for createuser.php
<!DOCTYPE html> <html> <head> <title>Create User Account</title> <meta name="viewport" content="width=device-width, initial-scale=1.0" /> </head> <body> <a href="index.php"><< Back to Index Page</a><br/><br/> <strong>Create New User</strong><br/><br/> <form action="includes/signup_inc.php" method="POST"> <input type="text" name="first" placeholder="First Name" required><br> <input type="text" name="last" placeholder="Last Name" required><br> <input type="text" name="email" placeholder="E-mail" required><br> <input type="text" name="rank" placeholder="Rank" required><br> <input type="text" name="password" placeholder="Pass Word" required><br> <button type="submit" name="submit">Sign Up</button> </form> <?php if (@$_GET['create'] == 'success') echo "<br><br>User Successfully Created!" ?> </body> </html>
This is just a simple form that needs filling, nothing much to elaborate, except for the form action=”includes/signup_inc.php” part of the code. Anyway, since we are going to enter personal information into the form, the best way to make this data more secure from prying eyes is to use method=”POST”. The action= part of the form will execute another php file in the includes subfolder. This will be the one to process the input data and enter it into the database. On each field, note that we also set it to “required” so that users can’t leave these fields blank.
At line 18 is a PHP script that runs a message if the page returns a ?create=success snippet. This happens after a successful data entry process was made.
Code for db_inc.php
<?php $dbServername = "localhost"; $dbUsername = "root"; $dbPassword = ""; $dbName = "altometa"; $conn = mysqli_connect($dbServername, $dbUsername, $dbPassword, $dbName); ?>
Note that we called db_inc.php on php file searchdb.php. So what this does is connect PHP to the MySQL database. I’ll make the explanation brief..
Here we set up a variable $dbServername which is equivalent to “localhost”, since we are using localhost database for these PHP files. But this can be different depending on which server we store our database. $dbUsername = “root” is another variable that makes use of a global user. By default, there should be a global user in PHPMyAdmin called root who has all the privileges to the entire database, unless of course, this username is deleted. $dbPassword contains the password for the global username in $dbUsername. For username root, the password is empty by default. And finally, $dbName is the name of our database table. This is what our PHP files will be using.
All of these data has to be entered simultaneously every time we make use of the database, so we have to set them up in another variable. In this case, we call it variable $conn and using mysqli_connect, we enter all the information for the database to verify. Once accepted, we will be permitted to use the database.
Now all we have to do is call this PHP file whenever we need PHP to access the database and make a query.
Code for signup_inc.php
<?php
include_once 'db_inc.php';
$first = mysqli_real_escape_string($conn, $_POST['first']);
$last = mysqli_real_escape_string($conn, $_POST['last']);
$email = mysqli_real_escape_string($conn, $_POST['email']);
$rank = mysqli_real_escape_string($conn, $_POST['rank']);
$pass = mysqli_real_escape_string($conn, $_POST['password']);
$sql = "INSERT INTO dbusers (first_name, last_name, email_ad, rank, passkey)
VALUES (?, ?, ?, ?, ?);";
$stmt = mysqli_stmt_init($conn);
if(!mysqli_stmt_prepare($stmt, $sql)) {
echo "SQL error";
} else {
mysqli_stmt_bind_param($stmt, "sssss", $first, $last, $email, $rank, $pass);
mysqli_stmt_execute($stmt);
}
header("Location: ../createuser.php?create=success");
?>
Technically, this is the PHP file we use to process the entered data. What it does is it enters the input data from our form in createuser.php into a database table (note: we are calling db_inc.php for this to work).
So what we did here first is setup variables. Now, hackers and people with malicious intent may try to break our database or inject code into it by entering codes in the forms’ fields, this is what we call code injection. To prevent this, we have to filter all the entered data using mysqli_real_escape_string function, this will remove all the codes before it begins processing the input data. Next we call variable $conn to access the database and enter all the input data from the forms.
At line 10, we create variable $sql to run a mysql command to insert all the input data into the database. However, we want these data to be more secure so we added another layer of security by using Prepared Statements to hide the entered info. The entered VALUES as you may have observed is ?, not the actual variables.
To use Prepared Statements, we create another variable called $stmt (see line 12). Here we initiated Prepared Statements function by using mysqli_stmt_init and apply it to our PHP-MySQL connector variable $conn. We follow these up by setting an argument that if Prepared Statements did not work, then it will display a message “SQL error”, otherwise, it will bind the variables into the ? values in the SQL INSERT command. The “sssss” represents the 5 variables that follows the same sequence – $first, $last, $email, $rank, $pass. Our code is ended by a mysli_stmt_execute which will begin running initialization process for Prepared Statements function.
Finally, the header(“Location: ../createuser.php?create=success”); is the page it will return if the process completed. This will add the ?create=success snippet in the createuser.php url.
Content of sql-command.txt
CREATE TABLE dbusers (
uid int(11) not null PRIMARY KEY AUTO_INCREMENT,
first_name varchar(256) not null,
last_name varchar(256) not null,
email_ad varchar(256) not null,
rank varchar(256) not null,
passkey varchar(256) not null
);
INSERT INTO dbusers (first_name, last_name, email_ad, rank, passkey)
VALUES ('Anthony', 'Y', 'altometa@altometa.com', 'Administrator', 'admin');
INSERT INTO dbusers (first_name, last_name, email_ad, rank, passkey)
VALUES ('Nosfearatu', 'S', 'nosferatu@altometa.com', 'Member', 'nosfear');
INSERT INTO dbusers (first_name, last_name, email_ad, rank, passkey)
VALUES ('Dracula', 'A', 'dracula@altometa.com', 'Member', 'vlad');
This is not part of the PHP files but a code for MySQL to create new users. Note that you need to have a database table named altometa first in order for this code to work.
As to how to do it, please see mmtuts tutorial on MySQL here.
Well, that’s it for now. If you have comments, please drop me a comment. Also, do share my code to help others by using the share buttons.
Follow me at:
