search a MySql table using PHP

nicedad · November 17, 2015

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>

Barand · November 17, 2015

$name=$_POST['name'];

Your search field name is "search", not "name"

$name=$_POST['search'];

nicedad · November 17, 2015

Hi Barand,

thanks for the replay.

unfortunately still giving me the result in one line or even any space.

(akrambbbbakramccakramdddakrameeakrameeeeeeeakramtesteakramtesterakramtester)

Barand · November 17, 2015

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>

nicedad · November 17, 2015

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 November 17, 2015 by nicedad

Barand · November 17, 2015

I think you are missing a closing "}" before </table>

nicedad · November 17, 2015

unfortunately not, the missing code is at the end before </body>

please guys help

Barand · November 17, 2015

What happened to the

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

which you had in you previous version?

You now have foreach ($result ... ) which is completely wrong

nicedad · November 17, 2015

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>

Barand · November 17, 2015

Would it be asking too much for you to post the error message that you are getting?

nicedad · November 17, 2015

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

Barand · November 17, 2015

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'])){

nicedad · November 17, 2015

Barand you're really life-saver

this was it thanx so much

Sign In

search a MySql table using PHP

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information