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.
