database search

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
User avatar
sguy
Forum Commoner
Posts: 61
Joined: Sun Aug 10, 2003 2:44 am

database search

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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
User avatar
sguy
Forum Commoner
Posts: 61
Joined: Sun Aug 10, 2003 2:44 am

Post by sguy »

thanx....
search with multiple text boxes are ok oredi,
but i hv no idea with the check box... :?:
User avatar
sguy
Forum Commoner
Posts: 61
Joined: Sun Aug 10, 2003 2:44 am

Post 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
User avatar
sguy
Forum Commoner
Posts: 61
Joined: Sun Aug 10, 2003 2:44 am

Post 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%'");
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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...
User avatar
sguy
Forum Commoner
Posts: 61
Joined: Sun Aug 10, 2003 2:44 am

Post by sguy »

i've no idea with this code... :wink:
can u teach me how to correct it? :lol:
thanks...
Post Reply