Page 1 of 1

database search

Posted: Tue Aug 17, 2004 8:12 am
by sguy
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..

Code: Select all

<?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++;
    }
?>

Code: Select all

<?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>";
    }
?>

Code: Select all

<?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++;
?>
Image

Posted: Tue Aug 17, 2004 10:39 am
by feyd
you'll need to run through the fields in your form, checking their value along the way to see if they need adding to a search string that you build.. Additionally, your code you posted is designed for having register globals on, and it is not escaped, so sql injection may be possible, depending on a few settings..

Posted: Tue Aug 17, 2004 12:01 pm
by John Cartwright
heres an example

Code: Select all

<?php

$sql = "SELECT * FROM `table` WHERE ";

if (!empty($_POST["age"]))
{
    $sql .= "`age` > ".$_POST["lowage"]." && `age` < ".$_POST["highage"];
}

?>
Hope you get the idea

Posted: Wed Aug 18, 2004 12:39 am
by sguy
thanx....
search with multiple text boxes are ok oredi,
but i hv no idea with the check box... :?:

Posted: Thu Aug 19, 2004 12:45 am
by sguy
can do something like this?
if the check box checked, then show the result with the photo, if unchecked, then show the result without photo

thanks....

Image

Posted: Thu Aug 19, 2004 7:07 am
by sguy
Phenom wrote:heres an example

Code: Select all

<?php

$sql = "SELECT * FROM `table` WHERE ";

if (!empty($_POST["age"]))
{
    $sql .= "`age` > ".$_POST["lowage"]." && `age` < ".$_POST["highage"];
}

?>
Hope you get the idea
i'm not really understand your code :wink:
why can't use this code??

Code: Select all

mysql_query("SELECT * from Staff WHERE StaffID like '%$search%' and name like'%$name%' and state like'%$state%' and gender like'$gender%' and age like '%$lowage%' and age like '%$highage%'");

Posted: Thu Aug 19, 2004 9:51 am
by feyd
blindly adding those restrictions won't help. And the usage of

Code: Select all

age like '%$lowage%' and age like '%$highage%'"
will pretty much always fail to find any results unless they are nearly the same or the same. Try something more like Phenom's example for it...

Posted: Fri Aug 20, 2004 6:14 am
by sguy
i've no idea with this code... :wink:
can u teach me how to correct it? :lol:
thanks...