search multiple fields from a single table in php mysql

Question

I have a table named current with several fields in that table like post, experience, company, etc.

I want to make a search having three select boxes one showing the posts from post field, 2nd showing the experience and 3rd showing the company from a single current table.

I want that if user selects from any one select field or from all it should search the respective data from the table and show the results only related to search.

For this I have written the code but its not working please help me in finding where did I went wrong, I cant understand.

It only shows data entered in post field but not in any other field.

My code goes here

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" name="">
<table>

<tr>
<?php
$ss=mysql_query("select * from current"); 

?>
<td><select name="post"><?php while($rr=mysql_fetch_array($ss))
{?><option value="<?php echo $rr['post'];?>"><?php echo $rr['post'];?></option><?php } ?></select></td>
<?php
$s11=mysql_query("select * from current"); 

?>
<td><select name="experience"><?php while($r11=mysql_fetch_array($s11))
{?><option value="<?php echo $r11['experience'];?>"><?php echo $r11['experience'];?></option><?php } ?>    </select></td>
<td>
<?php
$sss=mysql_query("select * from current"); 

?>
<select name="company"><?php while($rrr=mysql_fetch_array($sss))
{?><option value="<?php echo $rrr['cname'];?>"><?php echo $rrr['cname'];?></option><?php } ?></select></td>
<td><input type="submit" name="search" value="Search" /></td>
</tr>

</table>
    </form>

my search code goes here

   <?php
include('Admin/config.php');
    error_reporting(E_ERROR | E_WARNING | E_PARSE);
  if(isset($_REQUEST['search']))
  {
$post = $_REQUEST['post'];
$ex = $_REQUEST['experience'];
$company = $_REQUEST['company'];
$query = "select * from current where post like '%$post%' or experience like '%$ex%' or cname like '%$company%' ";
$res1 = mysql_query($query);
while($rows = mysql_fetch_array($res1))
{
?>
<tr>
   <td><?php echo $rows['date'];?></td>
  <td><?php echo $rows['post'];?></td>
  <td><?php echo $rows['qualification'];?></td>
  <td><?php echo $rows['experience'];?></td>
  <td><?php echo $rows['nop'];?></td>
   <td><?php echo $rows['noj'];?></td>
  <td><?php echo $rows['cname'];?></td>
  <td><?php echo $rows['jloc'];?></td>


   </tr><?php

  }}

  else
  {


    $s=mysql_query("SELECT * FROM current ORDER BY date DESC");



  while($rows=mysql_fetch_array($s))
  {
  ?>
   <tr>
   <td><?php echo $rows['date'];?></td>
   <td><?php echo $rows['post'];?></td>
   <td><?php echo $rows['qualification'];?></td>
   <td><?php echo $rows['experience'];?></td>
   <td><?php echo $rows['nop'];?></td>
    <td><?php echo $rows['noj'];?></td>
   <td><?php echo $rows['cname'];?></td>
   <td><?php echo $rows['jloc'];?></td>


   </tr>
   <?php
   }}
   ?>
   </table>

Can you post some example of what you are getting as result and what you expect to get ? — Prix
– Prix, Commented Jun 28, 2013 at 10:21
Your code is wide open to an SQL injection attack, you don't do any validation or sanitization of user input. You should at the very least be using mysql_real_escape_string() on the input, and would be better off using prepared statements and additional validation as well. Also, mysql_* methods are deprecated. Use mysqli or PDO instead. xkcd.com/327 — GordonM
– GordonM, Commented Jun 28, 2013 at 10:33
I am a newbee and I am learning php and mysql codes from a tutorial website. hence I have not much Idea. If you can tell me about some other tutorial website where I can learn some standard coding that will be appreciated a lot — Php developer
– Php developer, Commented Jun 29, 2013 at 5:38
what should i do if I want related search with three condition as if one field chosen then display result again if two fields chosen then show result related to those field eg. if i select in first select the post namely manager(civil) and in second text field experience 1-4 years then the result should be only manager(civil) with 1-4 years. but in above code it is showing manager(civil) 5-6 years and another result design engineer 1-4 years. what shall I do if I want to show such results otherwise display match not found. Its Important for me please reply asap if possible — Php developer
– Php developer, Commented Jun 29, 2013 at 6:10

bbldzr · Accepted Answer · 2013-06-28 11:03:15Z

<?php
include('Admin/config.php');
error_reporting(E_ERROR | E_WARNING | E_PARSE);
$ss=mysql_query("select `post`,`experience`,`cname` from current");
$filter = array();
while($rr = mysql_fetch_assoc($ss)) {
    foreach ($rr as $key => $val) {
        $filter[$key][] = $val;
    }
}
$request = array('post' => '', 'experience' => '', 'cname' => '');
if  (isset($_REQUEST['search']))
{
    $request = array(
        'post' => $_REQUEST['post'],
        'experience' => $_REQUEST['experience'],
        'cname' => $_REQUEST['cname'],
    );
}
?>
<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post" name="">
    <table>
        <tr>

            <?php
            foreach ($filter as $name => $value) {
                ?>
                <td>
                    <select name="<?=$name?>">
                        <?
                        foreach ($value as $key => $val) {
                            ?>
                            <option value="<?=$val?>"<?= $val ===  $request[$name] ?  ' selected' : ''?>><?=$val?></option>
                        <?
                        }
                        ?>
                    </select>
                </td>
            <?
            }
            ?>
            <td><input type="submit" name="search" value="Search" /></td>
        </tr>
    </table>
</form>
<table>
    <?php
    if(isset($_REQUEST['search']))
    {
        $where = '';
        foreach ($request as $key => $val) {
            if ($where !== '') {
                $where .= ' OR ';
            }
            $where .= "`".$key."` like '%".$val."%' ";
        }
        $query = "select * from current where ".$where;
    }
    else
    {
        $query = "select * from current ORDER BY date DESC";
    }
    $res1 = mysql_query($query);
    while($rows=mysql_fetch_array($res1))
    {
        ?>
        <tr>
            <td><?php echo $rows['date'];?></td>
            <td><?php echo $rows['post'];?></td>
            <td><?php echo $rows['qualification'];?></td>
            <td><?php echo $rows['experience'];?></td>
            <td><?php echo $rows['nop'];?></td>
            <td><?php echo $rows['noj'];?></td>
            <td><?php echo $rows['cname'];?></td>
            <td><?php echo $rows['jloc'];?></td>
        </tr>
    <?php
    }
    ?>
</table>

what should i do if I want related search with three condition as if one field chosen then display result again if two fields chosen then show result related to those field eg. if i select in first select the post namely manager(civil) and in second text field experience 1-4 years then the result should be only manager(civil) with 1-4 years. but in above code it is showing manager(civil) 5-6 years and another result design engineer 1-4 years. what shall I do if I want to show such results otherwise display match not found. Its Important for me please reply asap if possible

Collectives™ on Stack Overflow

search multiple fields from a single table in php mysql

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related