Jump to content

search a MySql table using PHP


Go to solution Solved by Barand,

Recommended Posts

Hello everyone,

 

I'm trying create search box to search a MySql DB table using one term regarding two columns in the table (jobtitle and location).
(the table in the DB is similar to that of front-end in the attached image).
I spent much time unfortunately I couldn't generate the desired results. the result however should be plotted as the original table with table-headers along with only records matched.

I could do it but not as the same table in the image.

 

thanx in advance,

 

 

jobs.php

<?php
session_start();
require_once("common/DAO.php");
require_once("./common/config.php");
require_once("classes/class.jobs.php");
require_once("services/MemberService.php");

$objUser = new Jobs();
$location = "";
$Jqualify = "";

$userList = $objUser->getJobs($searchCondition);
?>

<!DOCTYPE html>
<html lang="en">
  <head>
     <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Integrationsdienste</title>
<link href="css/style.css" rel="stylesheet" type="text/css" />
    <meta name="description" content="">
    <meta name="author" content="templatemo">
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <link href="css/templatemo_justified.css" rel="stylesheet">
    
	<style>
	.btnjoin {
		padding: 5px 20px;
		border-radius: 4px;
		margin-bottom: 10px;
		color: #F0F8FF;
		background-color: #3CB6CB;
		border: none;
		cursor: pointer;
	}

    .table-striped>tbody>tr:nth-child(odd)>td,
.table-striped>tbody>tr:nth-child(odd)>th {
	background-color: #DDDDDD;
}

.table-striped>tbody>tr:nth-child(odd)>th {

	background-color: #bbb;
}

.space5 {
    margin-top: 5px;
}

p.test {
    width: 27em; 
    
    word-wrap: break-word;
}

	</style>

  </head>

  <body>

    <div id="container" class="container">


</ul>

<form  method="post" action="search.php?go"  id="searchform">
<div class="row space30"> <!-- row 2 begins -->
	<div  class="col-md-12 text-center">
      
	  <label for="search">Search:</label>
      <input type="search" name="search" id="search">
	 <input  type="submit" name="submit" value="Search">
      
	</div>
</div>
<?php
if(!empty($userList)) {
?>
      	<div class="row space5"> <!-- row 3 begins -->
		<div  class="col-md-12">
            <table class="table table-striped">
            <tr>
			<th style="width: 10%;">Firmenname</th>
			<th style="width: 10%;">Job Title</th>
			<th style="width: 30%;">Job Beschreibung</th>
			<th style="width: 10%;">Erfahrung</th>
			<th style="width: 10%;">Kanton</th>
			<th style="width: 10%;">Branche</th>
			<th style="width: 10%;">Vertragsart</th>
			<th style="width: 10%;">Gehalt/ Stunde</th></tr>
<?php

    foreach($userList as $i => $value) {
		if(is_numeric($i)) {
?>
<tr>
    <td><?php echo $userList[$i]["comp_name"]; ?></td>
	<td><?php echo $userList[$i]["jobtitle"]; ?></td>
	<td><?php echo nl2br($userList[$i]["job_description"], "<br/>\n"); ?></td>
    <td><?php echo $userList[$i]["minexp"]; ?> - <?php echo $userList[$i]["maxexp"]; ?></td>
	<td><?php echo $userList[$i]["location"]; ?></td>
	<td><?php echo $userList[$i]["Industry"]; ?></td>
    <td><?php echo $userList[$i]["JContract"]; ?></td>
	<td><?php echo $userList[$i]["minsal"]; ?> - <?php echo $userList[$i]["maxsal"]; ?></td>

</tr>
<?php
		}
	}//for close
?>
		</table>
		</div>
		</div>

<?php if(!empty($userList["perpage"])) { ?>
	<div class="pagination center"><?php echo $userList["perpage"]; ?></div>
<?php } ?>
<?php
}//if close
?>
</form>
  </body>
</html>

search.php

<!DOCTYPE html>
<html lang="en">
  <head>
     <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Integrationsdienste</title>
<link href="css/style.css" rel="stylesheet" type="text/css" />
    <meta name="description" content="">
    <meta name="author" content="templatemo">
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <link href="css/templatemo_justified.css" rel="stylesheet">
    <!-- HTML5 shim and Respond.js IE8 support of HTML5 elements and media queries -->
    <!--[if lt IE 9]>
      <script src="js/html5shiv.js"></script>
      <script src="js/respond.min.js"></script>
    <![endif]-->
	<style>
	.btnjoin {
		padding: 5px 20px;
		border-radius: 4px;
		margin-bottom: 10px;
		color: #F0F8FF;
		background-color: #3CB6CB;
		border: none;
		cursor: pointer;
	}

    .table-striped>tbody>tr:nth-child(odd)>td,
.table-striped>tbody>tr:nth-child(odd)>th {
	background-color: #DDDDDD;
}

.table-striped>tbody>tr:nth-child(odd)>th {

	background-color: #bbb;
}

.space5 {
    margin-top: 5px;
}

p.test {
    width: 27em; 
    
    word-wrap: break-word;
}

	</style>

  </head>

  <body>

    <div id="container" class="container">

<?php
  if(isset($_POST['submit'])){
  if(isset($_GET['go'])){
  if(preg_match("/^[  a-zA-Z]+/", $_POST['name'])){
  $name=$_POST['name'];
  //connect  to the database
  $db=mysql_connect  ("localhost", "MyDB",  "pass") or die ('I cannot connect to the database  because: ' . mysql_error());
  //-select  the database to use
  $mydb=mysql_select_db("eandagroup");
  //-query  the database table
  $sql="SELECT  ID, comp_name, jobtitle FROM jobs WHERE comp_name LIKE '%" . $name .  "%' OR jobtitle LIKE '%" . $name ."%'";
  //-run  the query against the mysql query function
  $result=mysql_query($sql);
  
 //-create  while loop and loop through result set

  while($row=mysql_fetch_array($result)){
	  
	  	 echo $CompName=$row['comp_name'];
         echo $JobTitle=$row['jobtitle'];
		 echo $JobDest=$row[$i]["job_description"];
		 echo $Location=$row[$i]["location"];
		 echo $Inds=$row[$i]["Industry"]; 
		 echo $Contr=$row[$i]["JContract"];
  
  }
  }
  else{
  echo  "<p>Please enter a search query</p>";
  }
  }
  }
?>
</table>
		</div>
		</div>

<?php if(!empty($result["perpage"])) { ?>
	<div class="pagination center"><?php echo $result["perpage"]; ?></div>
<?php } ?>
<?php

?>
</form>

 </div>

    </div> <!-- /container -->

    <!-- Bootstrap core JavaScript
    ================================================== -->
    <!-- Placed at the end of the document so the pages load faster -->
    </ul>


  </body>
</html>

post-179690-0-00961600-1447758071_thumb.jpg

Link to comment
https://forums.phpfreaks.com/topic/299497-search-a-mysql-table-using-php/
Share on other sites

You have to put your output inside HTML table tags (table, tr, td). Have a look at html table structure and output your data to match that

 

Example

<table>
  <tr>
    <th>Month</th>
    <th>Savings</th>
  </tr>
  <tr>
    <td>January</td>
    <td>$100</td>
  </tr>
</table>

Hi,

thanks again. I did what you suggested but still have one error by </table>

could you help please.

 

here is the code

<?php
session_start();
require_once("common/DAO.php");
require_once("./common/config.php");
require_once("classes/class.jobs.php");
require_once("services/MemberService.php");

$objUser = new Jobs();
$location = "";
$Jqualify = "";

$userList = $objUser->getJobs($searchCondition);
?>

<!DOCTYPE html>
<html lang="en">
  <head>
     <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Integrationsdienste</title>
<link href="css/style.css" rel="stylesheet" type="text/css" />
    <meta name="description" content="">
    <meta name="author" content="templatemo">
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <link href="css/templatemo_justified.css" rel="stylesheet">
    
	<style>
	.btnjoin {
		padding: 5px 20px;
		border-radius: 4px;
		margin-bottom: 10px;
		color: #F0F8FF;
		background-color: #3CB6CB;
		border: none;
		cursor: pointer;
	}

    .table-striped>tbody>tr:nth-child(odd)>td,
.table-striped>tbody>tr:nth-child(odd)>th {
	background-color: #DDDDDD;
}

.table-striped>tbody>tr:nth-child(odd)>th {

	background-color: #bbb;
}

.space5 {
    margin-top: 5px;
}

p.test {
    width: 27em; 
    
    word-wrap: break-word;
}

	</style>

  </head>

  <body>

    <div id="container" class="container">


</ul>
<?php
if(isset($_POST['submit'])){
  if(isset($_GET['go'])){
  if(preg_match("/^[  a-zA-Z]+/", $_POST['search'])){
  $name=$_POST['search'];
  //connect  to the database
  $db=mysql_connect  ("localhost", "MyDB",  "pass") or die ('I cannot connect to the database  because: ' . mysql_error());
  //-select  the database to use
  $mydb=mysql_select_db("eandagroup");
  //-query  the database table
  $sql="SELECT comp_name, jobtitle, job_description, location FROM jobs WHERE comp_name LIKE '%" . $name .  "%' OR jobtitle LIKE '%" . $name ."%'";
  //-run  the query against the mysql query function
  $result=mysql_query($sql);
   ?>   
	</div>
</div>

<?php
if(!empty($result)) {
?>
      	<div class="row space5"> <!-- row 3 begins -->
		<div  class="col-md-12">
            <table class="table table-striped">
            <tr>
			<th style="width: 10%;">Firmenname</th>
			<th style="width: 10%;">Job Title</th>
			<th style="width: 30%;">Job Beschreibung</th>
			<th style="width: 10%;">Kanton</th>
			
<?php

    foreach($result as $i => $value) {
		if(is_numeric($i)) {
?>

<tr>
    <td><?php echo $result[$i]["comp_name"]; ?></td>
	<td><?php echo $result[$i]["jobtitle"]; ?></td>
	<td><?php echo nl2br($result[$i]["job_description"], "<br/>\n"); ?></td>
	<td><?php echo $result[$i]["location"]; ?></td>
	
</tr>

<?php
		}
	}//for close
?>
		</table>
		</div>
		</div>

<?php if(!empty($userList["perpage"])) { ?>
  <div class="pagination center"><?php echo $userList["perpage"]; ?></div>
<?php } ?>
<?php
}//if close
?>

// at this line it generates an error I don't why!!! what's missing

  </body>
</html>
Edited by nicedad

true, I forget it. here is the code again. still the same error.

<?php
session_start();
require_once("common/DAO.php");
require_once("./common/config.php");
require_once("classes/class.jobs.php");
require_once("services/MemberService.php");

$objUser = new Jobs();
$location = "";
$Jqualify = "";

$userList = $objUser->getJobs($searchCondition);
?>

<!DOCTYPE html>
<html lang="en">
  <head>
     <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Integrationsdienste</title>
<link href="css/style.css" rel="stylesheet" type="text/css" />
    <meta name="description" content="">
    <meta name="author" content="templatemo">
    <link href="css/bootstrap.min.css" rel="stylesheet">
    <link href="css/templatemo_justified.css" rel="stylesheet">
    
	<style>
	.btnjoin {
		padding: 5px 20px;
		border-radius: 4px;
		margin-bottom: 10px;
		color: #F0F8FF;
		background-color: #3CB6CB;
		border: none;
		cursor: pointer;
	}

    .table-striped>tbody>tr:nth-child(odd)>td,
.table-striped>tbody>tr:nth-child(odd)>th {
	background-color: #DDDDDD;
}

.table-striped>tbody>tr:nth-child(odd)>th {

	background-color: #bbb;
}

.space5 {
    margin-top: 5px;
}

p.test {
    width: 27em; 
    
    word-wrap: break-word;
}

	</style>

  </head>

  <body>

    <div id="container" class="container">
<?php
if(isset($_POST['submit'])){
  if(isset($_GET['go'])){
  if(preg_match("/^[  a-zA-Z]+/", $_POST['search'])){
  $name=$_POST['search'];
  //connect  to the database
  $db=mysql_connect  ("localhost", "MyDB",  "pass") or die ('I cannot connect to the database  because: ' . mysql_error());
  //-select  the database to use
  $mydb=mysql_select_db("eandagroup");
  //-query  the database table
  $sql="SELECT comp_name, jobtitle, job_description, location FROM jobs WHERE comp_name LIKE '%" . $name .  "%' OR jobtitle LIKE '%" . $name ."%'";
  //-run  the query against the mysql query function
  $result=mysql_query($sql);
   ?>   
	</div>
</div>

<?php
if(!empty($result)) {
?>
      	<div class="row space5"> <!-- row 3 begins -->
		<div  class="col-md-12">
            <table class="table table-striped">
            <tr>
			<th style="width: 10%;">Firmenname</th>
			<th style="width: 10%;">Job Title</th>
			<th style="width: 30%;">Job Beschreibung</th>
			<th style="width: 10%;">Erfahrung</th>
			<th style="width: 10%;">Kanton</th>
			<th style="width: 10%;">Branche</th>
			<th style="width: 10%;">Vertragsart</th>
			<th style="width: 10%;">Gehalt/ Stunde</th></tr>
<?php

     while($row=mysql_fetch_array($result)) {
		?>

<tr>
		<td><?php echo $CompName=$row['comp_name'];?></td>
         <td><?php echo $JobTitle=$row['jobtitle'];?></td>
		 <td><?php echo $JobDest=$row[$i]["job_description"];?></td>
		 <td><?php echo $Location=$row[$i]["location"];?></td>

	
</tr>

<?php
		}
	
?>
		</table>
        
		</div>
		</div>

<?php if(!empty($userList["perpage"])) { ?>
  <div class="pagination center"><?php echo $userList["perpage"]; ?></div>
<?php } ?>
<?php
}//if close
?>
// here is the error, which I don't where it come from!
 </body>
</html>

of course not, the attached image shows the bug.

furthermore you can check it out live using this link

 

http://a-integrationsdienste.ch/jobs.php

 

and search for any word in column (Firmenname or Job Title) i.e. tester or akram

you  will see the error

  • Solution

According to my IDE, the {s at the ends of these three lines have no corresponding closing }s

<?php
if(isset($_POST['submit'])){
  if(isset($_GET['go'])){
  if(preg_match("/^[  a-zA-Z]+/", $_POST['search'])){
 
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.