467,132 Members | 1,159 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,132 developers. It's quick & easy.

How to search the mysql database using php

100+
thjis is my search form...

Expand|Select|Wrap|Line Numbers
  1. <form name="form" action="search.php" method="get">
  2.   <input type="text" name="q" />
  3.   <input type="submit" name="Submit" value="Search" />
  4. </form>
  5.  
this is my search.php

Expand|Select|Wrap|Line Numbers
  1. <?php
  2.  
  3. include('lock.php');
  4. include("config.php");
  5.  
  6.  
  7.  
  8.   $var = @$_GET['q'] ;
  9.   $trimmed = trim($var); 
  10.  
  11.  
  12.  
  13.  
  14.  
  15. // rows to return
  16.  
  17.  
  18.  
  19.  
  20. if ($trimmed == "")
  21.   {
  22.   echo "<p>Please enter a search...</p>";
  23.   exit;
  24.   }
  25.  
  26.  
  27. /*if (!isset($var))
  28.   {
  29.   echo "<p>We dont seem to have a search parameter!</p>";
  30.   exit;
  31.   }*/
  32.  
  33. $query = "select * from candidate where cname like \"%$trimmed%\" ||  skills like \"%$trimmed%\" ||   exp like \"%$trimmed%\" || indus like \"%$trimmed%\" || qual like \"%$trimmed%\" ORDER BY cid ";
  34.  
  35. $result=mysql_query($query);
  36. $num=mysql_numrows($result);
  37.  
  38.  
  39.  
  40. if ($num == 0)
  41.   {
  42.   echo "<h4>Results</h4>";
  43.   echo "<p>Sorry, your search: &quot;" . $trimmed . "&quot; returned zero results</p>";
  44.  
  45.  }
  46.  
  47.  
  48. echo "<p>You searched for: &quot;" . $var . "&quot;</p>";
  49. echo "Results";
  50. print "<table width='100%' border='1'>";
  51. print "<tr><th align='center'>Name</th><th align='center'>Email</th><th align='center'>Qualification</th><th align='center'>Skills</th><th align='center'>Resume</th></tr>";
  52. $i=0;
  53. while ($i < $num) {
  54. $cname=mysql_result($result,$i,"cname");
  55. $email=mysql_result($result,$i,"email");
  56. $skills=stripslashes(mysql_result($result,$i,"skills"));
  57. $qual=mysql_result($result,$i,"qual");
  58. $id=mysql_result($result,$i,"cid");
  59. $filename=mysql_result($result,$i,"res_title");
  60. print "<tr>
  61. <td>$cname</td>
  62. <td>$email</td>
  63. <td>$qual</td>
  64. <td>$skills</td>
  65. <td><img src='download.gif'>&nbsp;<a href='download.php?id=$filename'>$filename</a></td>
  66. <td><a href='mailto:'>Forward</a></td>
  67. <td><a href='updatestatus.php'>Update</a></td>
  68. </tr>";
  69. $i++;
  70. }
  71. print "</table>";
  72. ?>
  73.  
i want search multiple values from the table. for example,

if i enter a search like " be java", then,

i have to search the db for 'be' key word and 'java' keyword seperately. then the result should be the candidates who having qualification 'be' and skills 'java' only to be displayed... how? help...
Jul 30 '10 #1
  • viewed: 2528
Share:
6 Replies
code green
Expert 1GB
You can use implode() or strtok() to split the words into array elements.
Then loop through the array creating a query with that part as the search string.
Jul 30 '10 #2
Atli
Expert 4TB
If you want to search for multiple values in a single field, a simple method is to use regular expressions. Note, this is not the most efficient method, but it does work.

For example, if I wanted to search for both "test" and "example" in a field I could do:
Expand|Select|Wrap|Line Numbers
  1. SELECT `stuff` FROM `myTable`
  2. WHERE `myCol` REGEXP '[[:<:]](test|example)[[:>:]]';
  3.  
This would return any field that had those two words anywhere within. The "[[:<:]]" and "[[:>:]]" parts will prevent it from matching the words within other words. If you don't want that, simply remove them.

(See 11.5.2. Regular Expressions for more info on how to construct more complex regular expressions)

If your search queries are coming in as space separated keywords, then all you would have to do is replace the space with a (|) and you put it into the query. The str_replace function could help you with that.

P.S.
Be sure to secure the input before using it though!
Aug 1 '10 #3
100+
i want to search multiple words in multiple fields in the table....
Aug 2 '10 #4
Atli
Expert 4TB
What I posted was an example. I showed you how you can use a regular expression to search a column. That should make it easy for you to build the query you need, based on what you already have.

I'm not going to write the whole thing for you.
Aug 2 '10 #5
100+
i dont want your code............. go
Aug 3 '10 #6
try "exploring" on php.net the php ... explode = " " function. This will seperate words that are placed together. This is what my PHP for Dummies says in Ch. 13explode ('sep", "string"): Creates an array of strings in wich each item i a substring of strings, separated by sep. For example, explode(" ",$string) creates an array in wich each wrd in $string is a separate value. This is similar to split in Perl.
Nov 10 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Ginoboy | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.