Category Archives: PHP

PHP & MySQL Database Insert View & Search


insert

Create Database

CREATE DATABASE alumni;
USE alumni;
CREATE TABLE `students` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Title` varchar(255) DEFAULT NULL,
  `Name` varchar(255) DEFAULT NULL,
  `Batch` varchar(255) DEFAULT NULL,
  `Contact` varchar(255) DEFAULT NULL,
  `Email` varchar(255) DEFAULT NULL,
  `FProject` varchar(255) DEFAULT NULL,
  `Designation` varchar(255) DEFAULT NULL,
  `CName` varchar(255) DEFAULT NULL,
  `Location` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

insert.php

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
Electronic Engineering Alumni</pre>
<style type="text/css"><!--
body {
	margin-left: 0px;
	margin-top: 0px;
	margin-right: 0px;
	margin-bottom: 0px;
	background-color: #CF9;
}
--></style>
<pre>

</pre>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td align="center">
<table width="50%" border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td style="font-family: Arial, Helvetica, sans-serif; size: 35px; color: #c60; font-weight: bold;" align="center"></td>
</tr>
<tr>
<td style="font-family: Arial, Helvetica, sans-serif; size: 35px; color: #c60; font-weight: bold;" align="center"><strong>Electronic Engineering Alumni</strong></td>
</tr>
<tr>
<td></td>
</tr>
<tr>
<td align="center">
<table width="50%" border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td align="center"><a href="insert.php">INSERT</a></td>
<td align="center"><a href="search.php">SERACH</a></td>
<td align="center"><a href="list.php">LIST</a></td>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td align="center"></td>
</tr>
<tr>
<td></td>
</tr>
<tr>
<td align="center"><form action="query.php" method="post">
<table width="400" border="0" cellspacing="5" cellpadding="5">
<tbody>
<tr>
<td width="130">Title:</td>
<td width="335">
<label for="Title"></label>
<select id="Title" name="Title"><option value="Mr.">Mr.</option><option value="Ms.">Ms.</option></select></td>
</tr>
<tr>
<td>Name:</td>
<td><input id="Name" type="text" name="Name" /></td>
</tr>
<tr>
<td>Batch:</td>
<td><input id="Batch" type="text" name="Batch" /></td>
</tr>
<tr>
<td>Contact:</td>
<td><input id="Contact" type="text" name="Contact" /></td>
</tr>
<tr>
<td>Email:</td>
<td><input id="Email" type="text" name="Email" /></td>
</tr>
<tr>
<td>FYP:</td>
<td><input id="FYP" type="text" name="FYP" /></td>
</tr>
<tr>
<td>Designation:</td>
<td><input id="Designation" type="text" name="Designation" /></td>
</tr>
<tr>
<td>Company Name:</td>
<td><input id="CName" type="text" name="CName" /></td>
</tr>
<tr>
<td>Location:</td>
<td><input id="Location" type="text" name="Location" /></td>
</tr>
<tr>
<td></td>
<td><input type="submit" /></td>
</tr>
</tbody>
</table>
</form></td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<pre>

query.php

<?php
$con=mysqli_connect("localhost","root","","alumni");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$sql="INSERT INTO students (Title, Name, Batch, Contact, Email, FProject, Designation, CName, Location)
VALUES
('$_POST[Title]','$_POST[Name]','$_POST[Batch]','$_POST[Contact]','$_POST[Email]','$_POST[FYP]','$_POST[Designation]','$_POST[CName]','$_POST[Location]')";

if (!mysqli_query($con,$sql))
  {
  die('Error: ' . mysqli_error());
  }
$url = 'insert.Php';
echo "1 record added";
echo '<META HTTP-EQUIV=Refresh CONTENT="5; URL='.$url.'">';
mysqli_close($con);
?>

Search
search

search.php

<!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=utf-8" />
<title>Electronic Engineering Alumni</title>
<style type="text/css">
body {
	margin-left: 0px;
	margin-top: 0px;
	margin-right: 0px;
	margin-bottom: 0px;
	background-color: #CF9;
}
</style>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td align="center"><table width="50%" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td align="center" style="font-family:Arial, Helvetica, sans-serif; size:35px; color:#C60; font-weight:bold;">&nbsp;</td>
      </tr>
      <tr>
        <td align="center" style="font-family:Arial, Helvetica, sans-serif; size:35px; color:#C60; font-weight:bold;"><strong>Electronic Engineering Alumni</strong></td>
        </tr>
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td align="center"><table width="50%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td align="center"><a href="insert.php">INSERT</a></td>
            <td align="center"><a href="search.php">SERACH</a></td>
            <td align="center"><a href="list.php">LIST</a></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td align="center">&nbsp;</td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        </tr>
      <tr>
        <td align="center"><form id="form1" name="form1" method="post" action="search-result.php">
          <table width="50%" border="0" cellspacing="0" cellpadding="0">
            <tr>
              <td width="36%" align="center">Search:</td>
              <td width="32%" align="left"><label for="search"></label>
                <input type="text" name="search" id="search" /></td>
              <td width="32%" align="left"><label for="select"></label>
                <select name="select" id="select">
<option value="Name">Name</option>
<option value="ID">ID</option>
                  <option value="Batch">Batch</option>
                  <option value="Email">Email</option>
                  <option value="Location">Location</option>
                </select></td>
            </tr>
            <tr>
              <td align="center">&nbsp;</td>
              <td align="center">&nbsp;</td>
              <td align="center">&nbsp;</td>
            </tr>
            <tr>
              <td align="center">&nbsp;</td>
              <td align="center"><input type="submit" name="submit" id="submit" value="Submit" /></td>
              <td align="center">&nbsp;</td>
            </tr>
          </table>
        </form></td>
      </tr>
    </table></td>
  </tr>
</table>
</body>
</html>

search-results.php

<!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=utf-8" />
<title>Electronic Engineering Alumni</title>
<style type="text/css">
body {
	margin-left: 0px;
	margin-top: 0px;
	margin-right: 0px;
	margin-bottom: 0px;
	background-color: #CF9;
}
</style>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td align="center"><table width="50%" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td align="center" style="font-family:Arial, Helvetica, sans-serif; size:35px; color:#C60; font-weight:bold;">&nbsp;</td>
      </tr>
      <tr>
        <td align="center" style="font-family:Arial, Helvetica, sans-serif; size:35px; color:#C60; font-weight:bold;"><strong>Electronic Engineering Alumni</strong></td>
        </tr>
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td align="center"><table width="50%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td align="center"><a href="insert.php">INSERT</a></td>
            <td align="center"><a href="search.php">SERACH</a></td>
            <td align="center"><a href="list.php">LIST</a></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td align="center">&nbsp;</td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        </tr>
      <tr>
        <td align="center"><?php
$search = mysql_real_escape_string($_POST['search']);
$select = mysql_real_escape_string($_POST['select']);
$con=mysqli_connect("localhost","root","","alumni");
// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

$data = mysqli_query($con, "SELECT * FROM students WHERE $select LIKE '$search'"); 
while($result = mysqli_fetch_array( $data )) 
 { 
 echo $result['ID']; 
 echo " "; 
 echo $result['Name']; 
 echo " "; 
 echo $result['Batch']; 
 echo "<br>"; 
 } 
 
$anymatches=mysqli_num_rows($data);
if ($anymatches == 0) 
 { 
 echo "Sorry, but we can not find an entry to match your query<br><br>"; 
 } 
 
 //And we remind them what they searched for 
 echo "<b>Searched Result For:</b> " .$search; 

mysqli_close($con);
?> </td>
      </tr>
    </table></td>
  </tr>
</table>


</body>
</html>

View
view
list.php

<!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=utf-8" />
<title>Electronic Engineering Alumni</title>
<style type="text/css">
body {
	margin-left: 0px;
	margin-top: 0px;
	margin-right: 0px;
	margin-bottom: 0px;
	background-color: #CF9;
}
</style>
</head>
<body>
<table width="100%" border="0" cellspacing="0" cellpadding="0">
  <tr>
    <td align="center"><table width="50%" border="0" cellspacing="0" cellpadding="0">
      <tr>
        <td align="center" style="font-family:Arial, Helvetica, sans-serif; size:35px; color:#C60; font-weight:bold;">&nbsp;</td>
      </tr>
      <tr>
        <td align="center" style="font-family:Arial, Helvetica, sans-serif; size:35px; color:#C60; font-weight:bold;"><strong>Electronic Engineering Alumni</strong></td>
        </tr>
      <tr>
        <td>&nbsp;</td>
      </tr>
      <tr>
        <td align="center"><table width="50%" border="0" cellspacing="0" cellpadding="0">
          <tr>
            <td align="center"><a href="insert.php">INSERT</a></td>
            <td align="center"><a href="search.php">SERACH</a></td>
            <td align="center"><a href="list.php">LIST</a></td>
          </tr>
        </table></td>
      </tr>
      <tr>
        <td align="center">&nbsp;</td>
      </tr>
      <tr>
        <td>&nbsp;</td>
        </tr>
      <tr>
        <td align="center">
        <?php
$con=mysqli_connect("localhost","root","","alumni");
// Check connection


if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }


$result = mysqli_query($con,"SELECT * FROM students");
echo "<table border='1' cellspacing='2' cellpadding='2'>
<tr>
<th>ID</th>
<th>Name</th>
<th>Batch</th>
<th>Contact</th>
<th>Email</th>
<th>FYP</th>
<th>Designation</th>
<th>Company Name</th>
<th>Location</th>
</tr>";

while($row = mysqli_fetch_array($result))
  {
  echo "<tr>";
  echo "<td>" . $row['ID'] . "</td>";
  echo "<td>" . $row['Name'] . "</td>";
  echo "<td>" . $row['Batch'] . "</td>";
  echo "<td>" . $row['Contact'] . "</td>";
  echo "<td>" . $row['Email'] . "</td>";
  echo "<td>" . $row['FProject'] . "</td>";
  echo "<td>" . $row['Designation'] . "</td>";
  echo "<td>" . $row['CName'] . "</td>";
  echo "<td>" . $row['Location'] . "</td>";
  echo "</tr>";
  }
mysqli_close($con);
?> 
        </td>
      </tr>
    </table></td>
  </tr>
</table>


</body>
</html>