New Post has been published on WebSetNet
New Post has been published on http://websetnet.com/how-to-design-mysql-database-login-script-search-by-using-php/
How To Design MySQL Database | Login Script | Search |by Using PHP
There are many PHP guides and tutorials on the internet. They help us in the creation of MySQL databases, tables and much more. But this PHP tutorial is slightly different from others which make this MySQL database guide more unique and worthy. This MySQL database tutorial is a case study of my client in which he wants to manage his employeeâs MySQL database. During his research, he consulted my many times and we develop a great MySQL database, project of online user management system. In which we added the basic features of Insert, Update and Delete. After this, we add login sessions of MySQL database by their name or CNIC number. In this article, you will learn how to create an online MySQL database with the merger of login, logo sessions and MySQL database search engine.
For this purpose, you just need an online server or a local server of WAMP or XXAMP and need the coding that I shall mention in this article. Main features of this articles are:
How to Connect MySQL Database
For this purpose, you just need to create a php file with the name of connection.php and type <?php ?>. Now, define three variables $dbhost, $dbuser and $dbpass for hostname, database user and database password. These are used in the connection of MySQL Database. Pass these variables in the SQL query  mysql_connect and assign its value to the new variable $conn like ($conn = mysql_connect($dbhost, $dbuser, $dbpass);. Use this variable ($conn) in if condition for successful MySQL database connection. It will display a message MySQL database connected successfully or MySQL database could not connect. After this use this MySQL query mysql_close and pass this variable ($conn) like mysql_close($conn);.
This connection.php file will be look like
<html>
<head>
<title>MySQL Database Tutorial</title>
</head>
<body>
<?php
$dbhost = âlocalhostâ;
$dbuser = âubaidsheikhâ;
$dbpass = â123456789â˛;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
die(âCould not connect to the database: â . mysql_error());
echo âConnected successfully to the database<br />â;
mysql_close($conn);
?>
</body>
</html>
How to create MySQL Database
After the successful connection of MySQL database, you just need to add following lines of codes in the connection.php file. Assign a variable $sql=âdatabase nameâ; and use this variable with $conn variable under the MySQL query mysql_query like (mysql_query ($sql, $conn)). After this pass the value of this MySQL query to the new variable $retval. Use $retval variable in if else condition like if (! $retval) or if ($retval). First condition comes under negation that when the first condition is true it shows the message âdatabase could not createâ. And when this condition becomes false it will display a message âyour database has been created successfullyâ. You can see that I have used first statement of negation to verify the status of MySQL Database. You can also use the second statement to check the status of MySQL database. Now, your connection.php file will look like this after successful modification.
<html>
<head>
<title>MySQL Database Tutorial</title>
</head>
<body>
<?php
$dbhost = âlocalhostâ;
$dbuser = âubaidsheikhâ;
$dbpass = â123456789â˛;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
die(âCould not connect to the database: â . mysql_error());
echo âConnected successfully to the database<br />â;
$sql = âUsersâ;
$retval = mysql_query( $sql, $conn );
if(! $retval )
die(âCould not create database: â . mysql_error());
echo âMySQl Database USERS created successfully\nâ;
mysql_close($conn);
?>
</body>
</html>
How to create table in MySQL database by using PHP
You can also use your connection.php file and modify it with the code to create a table. But I will recommend creating another PHP file create table.php and add the lines of codes to create a table in your MySQL database. It requires a table name, field names and definition of each field. In the creation of a table in MySQL database we require at least three attributes which are as follows:
NOT NULL attribute is used when you donât want the entry of any NULL value in your MySQL database. When you assign NOT NULL attribute regarding any field it gives an error of MySQL database when any user enters the Null value. For example, User_id  INT NOT NULL AUTO_INCREMENT
AUTO_INCREMENT attribute is used when you want to add the next possible and available number to the id field. This attribute is mostly assigned to the primary keys in MySQL database. For example, User_id  INT NOT NULL AUTO_INCREMENT
PRIMARY KEY attribute is used when you want to set any column as a primary key. You can define multiple columns as a primary key with separation of commas.
User_id  INT NOT NULL AUTO_INCREMENT,
Now, your final code for the creation of table in the MySQL databases will b look like:
id INT NOT NULL AUTO_INCREMENT,
Username VARCHAR(30) NOT NULL,
Password VARCHAR(30) NOT NULL,
Now, open your createtable.php file create database connection and paste the above lines of codes before this SQL command (mysql_close($conn);).
<html>
<head>
<title>MySQL Database Tutorial</title>
</head>
<body>
<?php
$dbhost = âlocalhostâ;
$dbuser = âubaidsheikhâ;
$dbpass = â123456789â˛;
$conn = mysql_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
die(âCould not connect to the database: â . mysql_error());
echo âConnected successfully to the database<br />â;
$sql =âCREATE TABLE User_info(â. âid INT NOT NULL AUTO_INCREMENT,â.
âUser_id INT NOT NULL , â.
âUsername VARCHAR(30) NOT NULL,â.
âPassword VARCHAR(30) NOT NULL,â.
âPRIMARY KEY ( User_id ));â;
mysql_select-db(âUsersâ);
$retval = mysql_query( $sql, $conn );
if(! $retval )
die(âCould not create table: â . mysql_error());
echo âMySQl Database table created successfully\nâ;
mysql_close($conn);
?>
</body>
</html>
Now, you have successfully create MySQL database connection, database and table manually. You can also create your MySQL database automatically as I have discussed here in detail.
You can insert data in your MySQL database with the help of static SQL commands. But an SQL expert is required for this purpose to insert data or data entry. So, we need an HTML form here to insert data into our MySQL database. HTML form is easy to handle even a person with the basic knowledge of computer can insert data through HTML form into MySQL database. So, it is recommended to create an HTML form here for data entry instead of using static SQL commands every time. Now, create an HTML page like (insertuser.html) and enter the following code of HTML form in it. Here you can enter the address of specific (.php) file page where you have integrated the insert code with this HTML coding (action=âinsert.phpâ). USE this command action=â<?php $_PHP_SELF ?>â If you are using the insert coding and HTML form on the same page.
<form method=âpostâ action=âinsert.phpâ>
<table width=â600âł border=â0âł cellspacing=â1âł cellpadding=â2âł>
<tr>
<td width=â250âł>CNIC</td>
<td>
<input name=âuser_idâ type=âtextâ id=âuser_idâ>
</td>
</tr>
<tr>
<td width=â250âł>Username</td>
<td>
<input name=âusernameâ type=âtextâ id=âusernameâ>
</td>
</tr>
<tr>
<td width=â250âł>Password</td>
<td>
<input name=âpasswordâ type=âpasswordâ id=âpasswordâ>
</td>
</tr>
<tr>
<td width=â250âł> </td>
<td> </td>
</tr>
<tr>
<td width=â250âł> </td>
<td>
<input name=âaddâ type=âsubmitâ id=âaddâ value=âAdd Userâ>
</td>
</tr>
</table>
</form>
<?php
}
?>
</body>
</html>
When you enter any value in this page it will pass these values to the insert.php page where you have integrated the insert SQL command. In the file of insert.php, we have used $_post method to get values from the HTML form. And and it can enter all the values from the HTML form to the MySQL database.
// Get values from form
$name=$_POST['username'];
$pwd=$_POST['password'];
$id=$_POST[user_id'];
You can see that I have passed the values of $_POST to specific variables regarding $name, $pwd and $id. It can increase its efficiency and reduce the chances of errors. Now, you can use these variables in insert code to enter the values from HTML form to the MySQL database.
// Insert data into mysql
$sql=âINSERT INTO $tbl_name(`User_id`,`Username`, `Password`)VALUES(â$idâ,â$nameâ, â$pwdâ)â;
It can get values from the variables under (VALUES) and enters them in the specific columns of database table ($tbl_name)
<?php
$host=âlocalhostâ; // Host name
$username=âubaidsheikhâ; // Mysql username
$password=â123456789â˛; // Mysql password
$db_name=âUsersâ; // Database name
$tbl_name=âUser_infoâ; // Table name
// Connect to server and select database.
mysql_connect(â$hostâ, â$usernameâ, â$passwordâ)or die(âcannot connectâ);
mysql_select_db(â$db_nameâ)or die(âcannot select DBâ);
// Get values from form
$name=$_POST['username'];
$pwd=$_POST['password'];
$id=$_POST[user_id'];
// Insert data into mysql
$sql=âINSERT INTO $tbl_name(`User_id`,`Username`, `Password`)VALUES(â$idâ,'$nameâ, â$pwdâ)â;
$result=mysql_query($sql);
// if successfully insert data into database, displays message âSuccessfulâ.
if($result)
echo âSuccessfulâ;
else
echo âERRORâ;
?>
<?php
// close connection
mysql_close();
?>
When you want to delete any entry from MySQL database you have to use DELETE FROM SQL command and WHERE clause. Be careful while using a WHERE clause because a single mistake can delete all the records from the MySQL database table. You can use any condition while using WHERE clause and can delete records in a single table at a time. As I have mentioned that the WHERE clause is very important when you delete any entry in MySQL database. The second important thing is the $id variable that u have set as (NOT NULL AUTO_INCREMENT) and assign as PRIMARY KEY in MySQL database table.
// select record from mysql
$sql=âSELECT * FROM $tbl_nameâ;
$result=mysql_query($sql);
?>
<?php // start while loop
while($rows=mysql_fetch_array($result))
?>
<?php
// close while loop
?>
Here you need two php files:
When you open deleteuser.php file it can display data from the MySQL database by using SELECT query. And provide you an interface to delete your required user from the MySQL database.
<HTML><Title>Delete User</Title>
<?php
$host=âlocalhostâ; // Host name
$username=âubaidsheikhâ; // Mysql username
$password=â123456789â˛; // Mysql password
$db_name=âUsersâ; // Database name
$tbl_name=âUser_infoâ; // Table name
// Connect to server and select database.
mysql_connect(â$hostâ, â$usernameâ, â$passwordâ)or die(âcannot connectâ);
mysql_select_db(â$db_nameâ)or die(âcannot select DBâ);
// select record from mysql
$sql=âSELECT * FROM $tbl_nameâ;
$result=mysql_query($sql);
?>
<table width=â400âł border=â0âł cellpadding=â3âł cellspacing=â1âł bgcolor=â#CCCCCCâ>
<tr>
<td colspan=â5âł bgcolor=â#FFFFFFâ><strong>Delete data in mysql</strong> </td>
</tr>
<tr>
<td align=âcenterâ bgcolor=â#FFFFFFâ><strong>ID</strong></td>
<td align=âcenterâ bgcolor=â#FFFFFFâ><strong>Username</strong></td>
<td align=âcenterâ bgcolor=â#FFFFFFâ><strong>Password</strong></td>
<td align=âcenterâ bgcolor=â#FFFFFFâ><strong>CNIC</strong></td>
<td align=âcenterâ bgcolor=â#FFFFFFâ>Â </td>
</tr>
<?php
while($rows=mysql_fetch_array($result))
?>
<tr>
<td bgcolor=â#FFFFFFâ><? echo $rows['id']; ?></td>
<td bgcolor=â#FFFFFFâ><? echo $rows['Username']; ?></td>
<td bgcolor=â#FFFFFFâ><? echo $rows['Password']; ?></td>
<td bgcolor=â#FFFFFFâ><? echo $rows['User_id']; ?></td>
<td bgcolor=â#FFFFFFâ><a href=âdeleteuserac.php?id=<? echo $rows['id']; ?>â>delete</a></td>
</tr>
<?php
// close while loop
?>
</table>
<?php
// close connection;
mysql_close();
?>
Now, come to the deleteuserac.php file it will take order from the deleteuser.php file to delete specific user. In this file you can use these codes to complete your task.
// get value of id that sent from address bar
// Delete data in mysql from row that has this id
$sql=âDELETE FROM $tbl_name WHERE id=â$idââ;
$result=mysql_query($sql);
<?php
$host=âlocalhostâ; // Host name
$username=âubaidsheikhâ; // Mysql username
$password=â123456789â˛; // Mysql password
$db_name=âUsersâ; // Database name
$tbl_name=âUser_infoâ; // Table name
// Connect to server and select databse.
mysql_connect(â$hostâ, â$usernameâ, â$passwordâ)or die(âcannot connectâ);
mysql_select_db(â$db_nameâ)or die(âcannot select DBâ);
// get value of id that sent from address bar
$id=$_GET['id'];
// Delete data in mysql from row that has this id
$sql=âDELETE FROM $tbl_name WHERE id=â$idââ;
$result=mysql_query($sql);
// if successfully deleted
if($result)
echo âDeleted Successfullyâ;
echo â<BR>â;
echo â<a href=âdeleteuser.phpâ>Back to main page</a>â;
else
echo âERRORâ;
?>
<?php
// close connection
mysql_close();
?>
When you want to update your MySQL Database users you have to create three files to update your userâs data.
This file is used to display users data from MySQL database with the help of SELECT command of SQL and mysql_fetch_array in while loop.
$sql=âSELECT * FROM $tbl_nameâ;
$result=mysql_query($sql);
?>
mysql_fetch_array is used to fetch a result row as an associative array, a numeric array, or both.
<?php
while($rows=mysql_fetch_array($result)){
?>
<HTML><Title>Update Doctor</Title>
<?php
$host=âlocalhostâ; // Host name
$username=âubaidsheikhâ; // Mysql username
$password=â123456789â˛; // Mysql password
$db_name=âUsersâ; // Database name
$tbl_name=âUsers_infoâ; // Table name
// Connect to server and select database.
mysql_connect(â$hostâ, â$usernameâ, â$passwordâ)or die(âcannot connectâ);
mysql_select_db(â$db_nameâ)or die(âcannot select DBâ);
$sql=âSELECT * FROM $tbl_nameâ;
$result=mysql_query($sql);
?>
<table width=â400âł border=â0âł cellspacing=â1âł cellpadding=â0âł>
<tr>
<td>
<table width=â400âł border=â1âł cellspacing=â0âł cellpadding=â3âł>
<tr>
<td colspan=â4âł><strong>List data from mysql </strong> </td>
</tr>
<tr>
<td align=âcenterâ><strong>Username</strong></td>
<td align=âcenterâ><strong>Password</strong></td>
<td align=âcenterâ><strong>User ID</strong></td>
<td align=âcenterâ><strong>Update</strong></td>
</tr>
<?php
while($rows=mysql_fetch_array($result))
?>
<tr>
<td><? echo $rows['Username']; ?></td>
<td><? echo $rows['Password']; ?></td>
<td><? echo $rows['User_id']; ?></td>
<td align=âcenterâ><a href=âupdateuser.php?id=<? echo $rows['id']; ?>â>update</a></td>
</tr>
<?php
?>
</table>
</td>
</tr>
</table>
<?php
mysql_close();
?>
When you click on the update link in the file of listusers.php it will take you to the updateuser.php page. In this file, you can use GET method to get value from the listusers.php file and SELECT command to retrieve data from MySQL database. In this file, you can also use an HTML form to display values in the textbox to update them.
// get value of id that sent from address bar
// Retrieve data from MySQL database
$sql=âSELECT * FROM $tbl_name WHERE Sr=â$idââ;
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);
<HTML><Title>Update Doctor</Title>
<?php
$host=âlocalhostâ; // Host name
$username=âubaidsheikhâ; // Mysql database username
$password=â123456789â˛; // Mysql database password
$db_name=âUsersâ; //MySQL Database name
$tbl_name=âUser_infoâ; // Table name
// Connect to server and select database.
mysql_connect(â$hostâ, â$usernameâ, â$passwordâ)or die(âcannot connectâ);
mysql_select_db(â$db_nameâ)or die(âcannot select DBâ);
// get value of id that sent from address bar
$id=$_GET['id'];
// Retrieve data from database
$sql=âSELECT * FROM $tbl_name WHERE id=â$idââ;
$result=mysql_query($sql);
$rows=mysql_fetch_array($result);
?>
<tr>
<td><form name=âform1âł method=âpostâ action=âupdateuserac.phpâ>
<table width=â100%â border=â0âł cellspacing=â1âł cellpadding=â3âł>
<tr>
<td colspan=â3âł><strong>Save User nformation</strong></td>
</tr>
<tr>
<td width=â71âł>Username</td>
<td width=â6âł>:</td>
<td width=â301âł><input name=ânameâ type=âtextâ id=âusernameâ value=â<? echo $rows['Username']; ?>â></td>
</tr>
<tr>
<td>Password</td>
<td>:</td>
<td><input name=âpasswordâ type=âtextâ id=âpasswordâ value=â<? echo $rows['Password']; ?>â></td></tr>
<td>CNIC</td>
<td>:</td>
<td><input name=âuser_idâ type=âtextâ id=âuser_idâ value=â<? echo $rows['User_id']; ?>â></td></tr>
<td>
<input name=âidâ type=âhiddenâ id=âidâ value=â<? echo $rows['id']; ?>â>
</td>
<td align=âcenterâ>
<input type=âsubmitâ name=âSubmitâ value=âSubmitâ>
</td>
<td>Â </td>
</tr>
</table>
</td>
</form>
</tr>
</table>
<?php
// close connection
mysql_close();
?>
After the successful creation of listusers.php and updateuser.php you need to create updateuserac.php file that will take values from HTML form and enter the updated values in MySQL database.
<?php
$host=âlocalhostâ; // Host name
$username=âubaidsheikhâ; // Mysql username
$password=â123456789â˛; // Mysql password
$db_name=âUsersâ; // Database name
$tbl_name=âUser_infoâ; // Table name
// Connect to server and select database.
mysql_connect(â$hostâ, â$usernameâ, â$passwordâ)or die(âcannot connectâ);
mysql_select_db(â$db_nameâ)or die(âcannot select DBâ);
// update data in mysql database
$sql=âUPDATE $tbl_name SET Password=ââ.$_POST['password'].ââ,User_id=ââ.$_POST['user_id'].ââ,Username=ââ.$_POST['username'].ââ WHERE id=ââ.$_POST['id'].âââ;
$result=mysql_query($sql);
// if successfully updated.
if($result)
echo âSuccessfulâ;
echo â<BR>â;
echo â<a href=âlistusers.phpâ>View result</a>â;
else
echo âERRORâ;
?>
After the successful designing of MySQL database. Now, you are required to create login session for your users. Simply create a login form as I have mentioned below. Here you can see that I have used another PHP file in action=âchecklogin.phpâ. Login form will use to take username and passwords only and checklogin.php file to verify the userâs info in MySQL database.
<form name=âlogin-formâ action=âchecklogin.phpâ method=âpostâ>
<div>
<h1>Login Form</h1>
<span>Fill out the form below to login</span>
</div>
<div>
<input name=âusernameâ type=âtextâ placeholder=âUsernameâ />
<div></div>
<input name=âpasswordâ type=âpasswordâ placeholder=âPasswordâ />
<div></div>
</div>
<div>
<input type=âsubmitâ name=âsubmitâ value=âLoginâ />
This file is used to verify the details of the user according to the MySQL database. If the details are true it will be redirect user to the login success page. And if the user details are wrong regarding username or password it will redirect back to the login page. It will get values of username and password through login page
// username and password sent from form
$myusername=$_POST['username'];
$mypassword=$_POST['password'];
After this it will protect MySQL database from SQL injection
/ To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);
$sql=âSELECT * FROM $tbl_name WHERE Username=â$myusernameâ and Password=â$mypasswordââ;
$result=mysql_query($sql);
After successful verification, it will redirect the user to the required page. And redirect back to the login page in the case of wrong information.
$host=âlocalhostâ; // Host name
$username=âubaidsheikhâ; // Mysql username
$password=â123456789â˛; // Mysql password
$db_name=âUsersâ; // Database name
$tbl_name=âUser_infoâ; // Table name
// Connect to server and select databse.
mysql_connect(â$hostâ, â$usernameâ, â$passwordâ)or die(âcannot connectâ);
mysql_select_db(â$db_nameâ)or die(âcannot select DBâ);
// username and password sent from form
$myusername=$_POST['username'];
$mypassword=$_POST['password'];
// To protect MySQL injection (more detail about MySQL injection)
$myusername = stripslashes($myusername);
$mypassword = stripslashes($mypassword);
$myusername = mysql_real_escape_string($myusername);
$mypassword = mysql_real_escape_string($mypassword);
$sql=âSELECT * FROM $tbl_name WHERE Username=â$myusernameâ and Password=â$mypasswordââ;
$result=mysql_query($sql);
// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1)
// Register $myusername, $mypassword and redirect to file ârequired pageâ
session_register(âusernameâ);
session_register(âpasswordâ);
header(âlocation:(enter required page)â);
else
header(âlocation: login.htmlâ);
One thing you should keep in mind that donât forget to use this code in the header of every page of MySQL database. It is used to make your MySQL database more secure to prevent entry of users on secured pages without login. Let us suppose that a user copies the required URL of the page and tries to access it after the logout session. Then, It will be redirected to the login page.
<?php
session_start();
if(!session_is_registered(username))
header(âlocation:login.htmlâ);
Create a PHP file with the name of logout.php and place this code and use the hyperlink of the logout in your secured pages. When you click the logout button it will destroy the session and take you to the login page.
<?php
session_start();
if(session_destroy())
header(âLocation: login.htmlâ);
?>
White creating PHP search engine, we need some commands and attributes
Returns an array of strings, each of which is a substring of string formed by splitting it on boundaries formed by the string delimiter.
$search_exploded = explode (â â, $search);
The foreach construct provides an easy way to iterate over arrays. foreach works only on arrays and objects, and will issue an error when you try to use it on a variable with a different data type or an uninitialized variable. There are two syntaxes.
foreach($search_exploded as $search_each)
$x++;
if($x==1)
$construct .=âUser_id LIKE â%$search_each%ââ;
else
$construct .=âAND Username LIKE â%$search_each%ââ;
<input type=âtextâid=âtextâ size=â40Ⲡname=âsearchâ></br></br>
</tr>
<input type=âsubmitâ name=âsubmitâ value=âSearchâ ></br></br></br>
</center>
</form>
</body>
</html>
<?php
$button = $_GET ['submit'];
$search = $_GET ['search'];
if(!$button)
echo ââ;
else
if(strlen($search)<=1)
echo âSearch term too shortâ;
else
echo â</label>â;
echoâ</form>â;
echo âYou searched for <b>$search</b> <hr size=â1â˛></br>â;
mysql_connect(âlocalhostâ,âubaidsheikhâ,â123456789âł);
mysql_select_db(âUsersâ);
$search_exploded = explode (â â, $search);
foreach($search_exploded as $search_each)
$x++;
if($x==1)
$construct .=âUser_id LIKE â%$search_each%ââ;
else
$construct .=âAND Username LIKE â%$search_each%ââ;
$construct =âSELECT * FROM User_info WHERE $constructâ;
$run = mysql_query($construct);
$foundnum = mysql_num_rows($run);
if ($foundnum==0)
echo âSorry, there are no matching result for <b>$search</b>.</br></br>1.
Try more general words. for example: If you want to search âhow to create a websiteâ
then use general keyword like âcreateâ âwebsiteâ</br>2. Try different words with similar
meaning</br>3. Please check your spellingâ;
else
echo â$foundnum results found !<p>â;
while($runrows = mysql_fetch_assoc($run))
$title = $runrows ['id'];
$desc = $runrows ['Username'];
$add = $runrows ['Password'];
$cli = $runrows ['User_id'];
echo â<li><u>Id</u></li><br>
<b>$title</b>
<br>
<u>Username</u>
<br>
$desc<br>
<a href=â$urlâ>$url</a>
<u>Password</u>
<br>
$add<br>
<a href=â$urlâ>$url</a>
<u>User_id</u>
<br>
$cli<br>
<a href=â$urlâ>$url</a>
<p>
â;
?>
To conclude, I can say that the main purpose of this article is to provide complete knowledge about the MySQL database, Login sessions and PHP search engine. In this tutorial I have discussed every aspect of MySQL database regarding insert, update and delete with advanced settings.
