Php MySQL Database Connection With Example
As in our earlier discussion in PHP we have checked about Scope of the Variables in PHP . Now we are going to demonstrate Select Data from MySQL and Display in HTML Table (Grid). For same we will required to create a Database in MySQL. We have created a database name dbphpdemo.
After creating database we have created a table name tblWebsitedetails like
CREATE TABLE `dbphpdemo`.`tblWebsiteDetails` ( `intSrNo` INT NOT NULL AUTO_INCREMENT , `strWebsiteName` VARCHAR(50) NOT NULL , `strWebsiteDetails` VARCHAR(255) NOT NULL , `dtmCreationDate` DATE NOT NULL , `intRank` INT NOT NULL , PRIMARY KEY (`intSrNo`)) ;As intsrno is the Primary Key and AutoIncrement Field. Then we can added some rows using queries or by Insert in phpMyAdmin
INSERT INTO `tblWebsiteDetails` (`intSrNo`, `strWebsiteName`, `strWebsiteDetails`, `dtmCreationDate`, `intRank`) VALUES (NULL, 'https://ocjp.in', 'Website Regarding Programming Tutorials', '2015-01-01', '3');
INSERT INTO `tblWebsiteDetails` (`intSrNo`, `strWebsiteName`, `strWebsiteDetails`, `dtmCreationDate`, `intRank`) VALUES (NULL, 'https://csitpark.in', 'For Domain Registration and Hosting Services', '2010-09-21', '2');
INSERT INTO `tblWebsiteDetails` (`intSrNo`, `strWebsiteName`, `strWebsiteDetails`, `dtmCreationDate`, `intRank`) VALUES (NULL, 'http://gujjupedia.com', 'Community Website for News, Business and Other Activity', '2018-01-21', '6');Data in tblwebsitedetails will be like
Now we have to create connection using PHP Code and fetch the data from tblwebsitedetails. We have used $con=mysqli_connect($server,$user,$psw); for connecting to Server. Function mysqli_select_db($con,$dbname); for selection of Database. $qr=mysqli_query($con,$query) or die(mysqli_error($con)) ; is used for execution of query die() function in php will end the process in case of any error. Function $res=mysqli_fetch_assoc($qr) will fetch the data from mysql and will place in Assosiation Array named $res (Single Row at a time). This function is placed in While Loop to get all records.
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Selection of Data from MySQL</title>
</head>
<body>
<?php
$server="localhost";
$user="root";
$psw="";
$dbname ="dbphpdemo";
$con=mysqli_connect($server,$user,$psw);
if($con){
echo "<br/>Server Connected ";
mysqli_select_db($con,$dbname);
$query="select * from tblwebsitedetails";
$qr=mysqli_query($con,$query) or die(mysqli_error($con)) ;
?>
<table border='1'>
<tr>
<th>Serial No</th>
<th>Website Name</th>
<th>Website Details</th>
<th>Website Creation Date</th>
<th>Rank of Website</th>
</tr>
<?php
while($res=mysqli_fetch_assoc($qr)){
?>
<tr>
<td><?php echo $res["intSrNo"];?></td> <!-- Case Sensitive for Fields -->
<td><?php echo $res["strWebsiteName"];?></td>
<td><?php echo $res["strWebsiteDetails"];?></td>
<td><?php echo $res["dtmCreationDate"];?></td>
<td><?php echo $res["intRank"];?></td>
</tr>
<?php } ?>
</table>
<?php
}
else
{
echo "<br/>Error in Connecting to your Server :".$server." Please check Server confirm running and Username Password are Correct";
}
?>
</body>
</html>
Some of the Errors you may face like :
Error :No database selected :
Solution :Check Database name or username
Error :Warning: mysqli_connect(): (HY000/2002): No connection could be made because the target machine actively refused it
Solution : Check Server Name and Re Confirm Server is Running
Error :
Table ‘dbphpdemo.tblwebsitedetailsf’ doesn’t exist
Solution : Check the Table Name and Confirm it is in Same Database that you have connected.
Error :
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near
Solution : Check your SQL Query.
Error : Undefined index: ***** in selectdata.php on line
Solution : Check Column name for the same.