This article explains how to connect to a database and display the data in a table. MYSQL is used as the DBMS and the PHP script connects through mysqli functions to display data.
Relational databases are the primary choice for data storage in the Web. A DBMS provides a database along with a set of tools to manage the data in the database. One example for DBMS is MYSQL. More than half of the websites in the Internet are created using PHP and the data store as MYSQL.
The steps required to access data from a table in a database provided by MYSQL can be summarized as follows:
- Establish or open a connection to the MYSQL server.
- Select a database.
- Execute the query against the database.
- Process the result returned by the server.
- Close the connection
To work with MYSQL databases, PHP provides in-built support in the form of predefined functions. To establish a connection with the MYSQL server, use the function mysql_connect which accepts three optional parameters.
First parameter is the server name, second parameter is the MYSQL server’s user name and the third parameter is the password for MYSQL server. If the connection to MYSQL fails, the function returns false. This function is generally used in conjunction with die function which can be used to print errors using the function mysql_error and terminate the execution of the script.
After opening a connection to the database, a database must be selected to execute the SQL queries. A database can be selected by using the function mysql_select_db which accepts a single string parameter, the name of the database.
After selecting a database, the next step is to specify the query which is generally stored as a string in a variable. This variable will be passed as a parameter to the function mysql_query which executes the query against the database.
The result of execution of the query can be stored in a variable. The result can be parsed row by row as an array by using the function mysql_fetch_array.
Below is a list of functions provides by PHP to work with MYSQL databases:
Below example demonstrates user validation against the details stored in a database using PHP and MYSQL:
//HTML Code
<html>
<head>
<title>Login Form</title>
</head>
<body>
<form action="getdb.php" method="get">
<label>Username: </label>
<input type="text" name="user" /><br />
<label>Password: </label>
<input type="password" name="pass" /><br />
<input type="submit" value="Submit" />
<input type="reset" value="Clear" />
</form>
</body>
</html>
//PHP Code - getdb.php
<?php
$utext = $_REQUEST["user"];
$ptext = $_REQUEST["pass"];
$flag = false;
$hostname = "localhost";
$username = "root";
$password = "123456";
$con = mysqli_connect($hostname, $username, $password) or die(mysql_error());
mysqli_select_db($con, "myapp") or die(mysql_error());
$result = mysqli_query($con, "select * from users") or die(mysql_error());
while($x = mysqli_fetch_array($result))
{
if($utext == $x["uname"] && $ptext == $x["pwd"])
$flag = true;
}
if($flag)
echo "Valid user!";
else
echo "Invalid username or password!";
?>
Suryateja Pericherla, at present is a Research Scholar (full-time Ph.D.) in the Dept. of Computer Science & Systems Engineering at Andhra University, Visakhapatnam. Previously worked as an Associate Professor in the Dept. of CSE at Vishnu Institute of Technology, India.
He has 11+ years of teaching experience and is an individual researcher whose research interests are Cloud Computing, Internet of Things, Computer Security, Network Security and Blockchain.
He is a member of professional societies like IEEE, ACM, CSI and ISCA. He published several research papers which are indexed by SCIE, WoS, Scopus, Springer and others.
Leave a Reply