database search

skyloon · #1 17-Aug-2004, 07:21

i just know 1 text box database search, can anyone help for many text boxes database search?
for example, i can key in name in text box, check box and list box, and then press the search button to search from database.
the scripts below are just search from 1 text box only...
can someone teach me for multiple search?
thank you..

PHP Code:


<?php
    $connection = mysql_connect("localhost","root","");
    mysql_select_db("help", $connection);
    if(!$connection)
    {
       echo "Couldn't make a connection!!!";
       exit;
    }
    $db = mysql_select_db("help",$connection);
    if(!$db)
    {
      echo "The database disapeared!";
      mysql_close($connection);
      exit;
    }
    $max = 0;
    $bmax = mysql_query("SELECT * from Staff WHERE StaffID like '%$search%'");
    while ($number_of_array = mysql_fetch_array($bmax))
    {
      $max++;
    }
?>

PHP Code:


<?php
    echo "<center><form action=\"search.php\" method=\"get\"><input type=\"text\" name=\"search\"
          value=\"$search\" style=\"border:1 solid #003366\"> <input type=\"submit\" value=\"Search\" style=\"border:1 solid #000066\">";
    echo "</td><td height=\"12\" align=\"right\" bgcolor=\"aliceblue\" width=\"63%\">";
    echo "<center>Database Search returned<font color = blue> $max </font>
          items containing \"<i><font color = blue>$search</font></i>\".</center>";
    echo "</td></tr></form></table><br>";
    $maxresult = 10;
    $sql_text = ("SELECT * from Staff WHERE StaffID like '%$search%'");

    if (!$page) {
    $page = 1;
    } 
    $backpage = $page - 1;
    $nextpage = $page + 1;
    $query = mysql_query($sql_text);
    $start = ($maxresult * $page) - $maxresult; 
    $num_rows = mysql_num_rows($query); 
    if ($num_rows <= $maxresult) {
    $num_pages = 1; 
    } else if (($num_rows % $maxresult) == 0) {
      $num_pages = ($num_rows / $maxresult);
    } else {
    $num_pages = ($num_rows / $maxresult) + 1;
    } 
    $num_pages = (int) $num_pages;
    if (($page > $num_pages) || ($page < 0)) {
    error("You have specified an invalid page number");
    }
    $sql_text = $sql_text . " LIMIT $start, $maxresult";
    $query = mysql_query($sql_text);
    
    if ($max>$maxresult)
    {
    echo "<center>- ";
    if ($backpage) { 
    echo "<a href=\"search.php?search=$search&page=$backpage\">Prev</a>";
    } else {
    echo "Prev";
    }
    for ($i = 1; $i <= $num_pages; $i++) {
    if ($i != $page) { 
    echo " <a href=\"search.php?search=$search&page=$i\">$i</a> ";
    } else { 
    echo " $i "; 
    } 
    }
    if ($page != $num_pages) {
    echo "<a href=\"search.php?search=$search&page=$nextpage\">Next</a> -";
    } else {
    echo "Next -";
    }
    echo "</center>";
    }
?>

PHP Code:


<?php
    $a = $start + 1;
    print"<TABLE CELLSPACING=\"0\" CELLPADDING=\"0\" BORDER=\"1\" align=center width=\"60%\">";
           print "  <TR><TD align=center>Staff ID</TD><TD align=center>Name</TD><TD align=center>IC No</TD></TR>\n";

    while ($number_of_array = mysql_fetch_array($query)) {
    echo "<tr>\n"; 
    echo "<TD align=center><font size=\"2\">$number_of_array[StaffID]</TD>\n"; 
    echo "<TD align=center><font size=\"2\">$number_of_array[Name]</TD>\n"; 
    echo "<TD align=center><font size=\"2\">$number_of_array[ICNO]</TD>\n";
    $a++;
?>

http://img.photobucket.com/albums/v2...oon/search.jpg

JasonMichael · #2 21-Aug-2004, 15:25

Hmmm, I was really stumped for awhile, when I first read this post, but maybe it was because I had just got out of the hospital and was still under the effects of the sedation. (don't worry folks! nothing too serious! Just a had to go thru a little test!)

Now I seem to read this post in a whole new light!!! In fact, I just figured out a GREAT way of doing this that I might even implement in my own code!

In order to conduct the search from "several" fields in your form, you're going to have to get those multiple $_POST variables together - lets concactentate them, and seperate them with a space:

PHP Code:


$search_string=$_POST['StaffID']." ".$_POST['Name']." ".$_POST['ICNO'];

Hmm, that looks pretty good.

Next....

Lets explode this $search_string into an array:

PHP Code:


$search_string_array = explode(" ",$search_string);

Hmm, we're getting there...

Now lets create our query string. We're going to use the power of REGEXP and some MySQL magic here. We're basically going to be searching for words in the MySQL database.

In fact, I decided to just go ahead and write you a complete function:
Here's the code to call the function, including some of the code mentioned above:

PHP Code:


$select = "news_id,news_headline"; // the MySQL fields you want to select for your results
  $table = "tblnews"; // the table you're selecting from
  $mysql_fields = "news_date news_headline news_body_textarea"; // the fields you're going to search in

  $search_string=$_POST['StaffID']." ".$_POST['Name']." ".$_POST['ICNO'];  // get your search data together

  $search_string_array = explode(" ",$search_string);
  
  $query = create_find_words_mysql_query($select,$table,$mysql_fields,$search_string_array);

// now use your code to do your query with the $query string ... below this line...

Here is our create_find_words_mysql_query function... tested.... I even added it to my own library of functions at my site. I love this thing!!! You'll love it too!

PHP Code:


function create_find_words_mysql_query($select="*",$table,$mysql_fields,$search_string_array,$extra_where="") {

/*

Function name: create_find_words_mysql_query
Date:  08-21-2004
Author:  Jason Michael of [url]http://www.jmrtechnet.com[/url]
Purpose: to create a query string to be used to search multiple fields for multiple values of a mysql table.
Inputs Parameters:
$select - the mysql fields being selected
$mysql_fields - the mysql fields to be searched
$search_string_array - an array of words to be searched for
$extra_where - used for additional MySQL 'where' operations that may be needed, and is appeneded to result string.

Output:  $result - a string with the MySQL query to be performed

*/

  $open_pattern="[[:<:]]";
  $close_pattern="[[:>:]]";
  // watch out for the differences  in the ` and the '   characters used below.
  // Note: we're converting to uppercase to make sure we don't have 'case' problems finding stuff.
  
  $mysql_fields_array = explode(" ",$mysql_fields);
  // we have to build the mysql_fields_string
  foreach ($mysql_fields_array as $value) { 
                  // for each field we need to search in. build up our $tmp string for MySQL
                  $tmp.="`$value`,' ',";
                  
  }
  // we have to take off the extra ,' ', on the end.
  $tmp = substr($tmp,0,strlen($tmp)-5);
  
  // now we have a good mysql_field_string function for our MySQL query
  $mysql_fields_string="UCASE(CONCAT(".$tmp."))";
  
  
  foreach($search_string_array as $value) {
       // be careful typing/copying this!  make sure you're using a space at the beginning of the quoted query
  
       $tmp_where = " (".$mysql_fields_string." REGEXP \"".$open_pattern.STRTOUPPER($value).$close_pattern."\") OR ";      
       $querywhere .=$tmp_where;
  }
  // now to take off the extra space and 'OR' off the end of the string
  $querywhere = trim(substr($querywhere,0,strlen($querywhere)-3));
  
  // now to create the final query
  $query = "SELECT $select FROM $table WHERE ".$querywhere." ".$extra_where;
  return $query;
}

I hope this answers your question and helps you out a bit.... enjoy.

skyloon · #3 22-Aug-2004, 08:51

wah!!!
very complicated code, nice....
but i'm not very understand....

it's ok for me to do the text box searching, but i don't know how to do the age & check box searching...

JasonMichael · #4 25-Aug-2004, 18:24

Ok, I've looked at your form. Give me a little time and I'll come up with an eloquent solution to your problem here... you still need it solved, right?

skyloon · #5 25-Aug-2004, 22:05

thanks......

skyloon · #6 27-Aug-2004, 00:47

i've tried this way, but dunno how to show the age and the checkbox..

i have 2 list menus, named lowage & highage in search.php, i want to find the age between 2 list menu...

i don't store the age into database, i use current year to minus year of birth of someone, when i do searching, i can't use ("select age from `table` where age >= '$lowage' and age <= '$highage'");

how to solve it???

thanks...

PHP Code:


<?php
echo"<table border=0 cellspacing=0 width=80% align=center>";
    echo "</td><td height=\"12\" align=\"right\" bgcolor=\"lightcyan\" width=\"100%\"><font size=2>";

    echo "<center>Database record returned<font color = blue> $max </font>
          items.</center>";
    echo "</td></tr></form></table><br>";

    $maxresult = 10;
    $sql_text = ("SELECT * from ".$DBprefix."signup WHERE username like '%$search%' and fullname like'%$name%' and state like'%$state%' and gender like'$gender%' and '$age' >= '%$lowage%' and '$age' >= '%$highage%'");

    if (!$page) {
    $page = 1;
    } 
    $backpage = $page - 1;
    $nextpage = $page + 1;
    $query = mysql_query($sql_text);
    $start = ($maxresult * $page) - $maxresult; 
    $num_rows = mysql_num_rows($query); 
    if ($num_rows <= $maxresult) {
    $num_pages = 1; 
    } else if (($num_rows % $maxresult) == 0) {
      $num_pages = ($num_rows / $maxresult);
    } else {
    $num_pages = ($num_rows / $maxresult) + 1;
    } 
    $num_pages = (int) $num_pages;
    if (($page > $num_pages) || ($page < 0)) {
    error("You have specified an invalid page number");
    }
    $sql_text = $sql_text . " LIMIT $start, $maxresult";
    $query = mysql_query($sql_text);
    
    if ($max>$maxresult)
    {
    echo "<center>- ";
    if ($backpage) { 
    echo "<a href=\"searching.php?search=$search&page=$backpage\">Prev</a>";
    } else {
    echo "Prev";
    }
    for ($i = 1; $i <= $num_pages; $i++) {
    if ($i != $page) { 
    echo " <a href=\"searching.php?search=$search&page=$i\">$i</a> ";
    } else { 
    echo " $i "; 
    } 
    }
    if ($page != $num_pages) {
    echo "<a href=\"searching.php?search=$search&page=$nextpage\">Next</a> -";
    } else {
    echo "Next -";
    }
    echo "</center>";
    }
    ?>
<?php 
$a = $start + 1; 
    print"<TABLE CELLSPACING=\"2\" CELLPADDING=\"2\" BORDER=\"0\" align=center width=\"80%\">"; 
    print "  <TR bgcolor=slateblue><TD font size=2 align=center height=\"26\"><font font size=2 color=white><b>Nickname</TD><TD align=center><font size=2 color=white><b>Gender</TD><TD align=center><font size=2 color=white><b>Age</TD><TD align=center><font size=2 color=white><b>Location</TD><TD align=center><font size=2 color=white><b>Country</TD><TD align=center><font size=2 color=white><b>Headline</TD></TR>\n"; 

$res = mysql_query("SELECT * FROM ".$DBprefix."signup WHERE username like '%$search%' and fullname like'%$name%' and state like'%$state%' and gender like'$gender%' and '.$age.' >= '%$lowage%' and '.$age.' >= '%$highage%'");  

if(mysql_num_rows($res) > 0) {  
   while($row = mysql_fetch_array($res)) {  
      echo"<tr>"; 
     $pic = strtolower(substr($row['gender'], 0, 1)); 
        $color = ($pic == 'm') ? 'aliceblue' : '#fff0ff';
           echo '<td bgcolor="'.$color.'"><font size=2>&nbsp;&nbsp;<b><a class=hq href="viewuser.php?name='.$row['username'].'" target="_blank">'.$row['username'].'</a>';  
         if($row['image'] == 1) {   
         echo '&nbsp;&nbsp;<img src="image/camera.gif" alt="camera" />';  
         }  
          echo '</td>'."\n";            
      $pic = strtolower(substr($row['gender'], 0, 1)); 
         $color = ($pic == 'm') ? 'aliceblue' : '#fff0ff';
          "$row[gender]\n";
          
      $currentyear=strftime("%Y");
      $currentdate=strftime("%d-%m-%Y");
      $age = $currentyear - $row[dobyear];

        $pic = strtolower(substr($row['gender'], 0, 1)); 
           $color = ($pic == 'm') ? 'aliceblue' : '#fff0ff';
        echo'<td align=center bgcolor="'.$color.'">';
            "$row[gender]\n";
        if($row['gender'] == 'male') {
        echo '<img src="image/'.substr($row['gender'], 0, 1).'.jpg" alt="'.$row['gender'].'" />';
        }
        else {
        echo '<img src="image/'.substr($row['gender'], 0, 1).'.jpg" alt="'.$row['gender'].'" />';
        }        

$pic = strtolower(substr($row['gender'], 0, 1)); 
           $color = ($pic == 'm') ? 'aliceblue' : '#fff0ff';
        echo'<td align=center bgcolor="'.$color.'">';
        echo"<font size=2>$age";
            "$row[gender]\n";

$pic = strtolower(substr($row['gender'], 0, 1)); 
           $color = ($pic == 'm') ? 'aliceblue' : '#fff0ff';
        echo'<td align=center bgcolor="'.$color.'">';
        echo"<font size=2>$row[location]";
            "$row[gender]\n";

$pic = strtolower(substr($row['gender'], 0, 1)); 
           $color = ($pic == 'm') ? 'aliceblue' : '#fff0ff';
        echo'<td align=center bgcolor="'.$color.'">';
        echo"<font size=2>$row[country]";
            "$row[gender]\n";
            
$pic1 = strtolower(substr($row['gender'], 0, 1)); 
           $color1 = ($pic1 == 'm') ? 'blue' : 'red';
        echo'<font color="'.$color1.'">';
$pic = strtolower(substr($row['gender'], 0, 1)); 
           $color = ($pic == 'm') ? 'aliceblue' : '#fff0ff';
        $color1 = ($pic1 == 'm') ? 'blue' : 'red';
        echo'<td bgcolor="'.$color.'"><font color="'.$color1.'">';
        echo"<font size=2>$row[headline]";
            "$row[gender]\n";
$a++; 
   }  
}  
else {  
   echo 'No users found';  
} 
?>

ararat · #7 04-Jun-2006, 12:09

I firsly I apollogize my bad english I maked cv a web pages but I can not searching multiple criterias I saw your php codes in up may you can help me I maked this web pages on my localhost and my database is ararat and cv tables name is ararat_cv
and criterias are
-city
-male or female
-age
-interested occupation
can you hepl me please I mnot good programer
please

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Rate This Thread
Rate This Thread:

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Read a .html file, check that file for links	salemite	C Programming Language	10	17-Jan-2008 07:56
Multiple Database search	misunderstood	MySQL / PHP Forum	11	04-Jun-2004 12:21
How a search engine really works (In english)	jrobbio	Open Discussion Forum	0	06-Jul-2003 17:13
Search Engine Positioning 101 and 201 "How To" Tips...	000	Search Engine Optimization Forum	0	29-May-2003 10:34
[class] 404 search function code	jrobbio	MySQL / PHP Forum	6	22-Apr-2003 09:32

database search

Re: database search