Page 1 of 2

code for doing a search from a database

Posted: Thu Sep 09, 2004 11:54 am
by budabug
I am feeling clueless on this. I have a form that has multiple items you can do a search from. I do not understand how to write the code so that you can choose from just one or all of the different fields. Can anyone give me a code to follow or a link to a tutorial that might clue me in to what to do?

Thanks
MM

Posted: Thu Sep 09, 2004 2:32 pm
by nigma
Maybe you could elaborate a bit? I don't quite understand what you're asking, and am assuming that the reason no one else has replied yet is because of the same reason.

Posted: Thu Sep 09, 2004 2:41 pm
by feyd
searching would have reveiled something like this: [devnet]+multiple +search +form[/devnet]

Posted: Thu Sep 09, 2004 2:51 pm
by budabug
feyd | Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]


here is the form (html page):

Code: Select all

<html>
<head>
<title>Time Tracking System</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<form name="FormName" action="time_info.php" method="post" enctype="application/x-www-form-urlencoded">
<table width="581" border="0" align="center" cellpadding="5" cellspacing="0">
  <tr>
    <td colspan="2"><div align="center"><font size="3" face="Arial, Helvetica, sans-serif"><strong>Time
            Tracking Search</strong></font></div></td>
    </tr>
  <tr>
    <td width="188"><font face="Arial, Helvetica, sans-serif"> </font></td>
    <td width="373"><font face="Arial, Helvetica, sans-serif"> </font></td>
  </tr>
  <tr valign="top">
    <td><font size="2" face="Arial, Helvetica, sans-serif">Name:</font></td>
    <td><font face="Arial, Helvetica, sans-serif">
      <select name="name">
        <option value="all_employees" selected>All
        <option value="name1">name1
        <option value="name2">name2
    </font></td>
  </tr>
  <tr valign="top">
    <td><font size="2" face="Arial, Helvetica, sans-serif">Association:</font></td>
    <td><font face="Arial, Helvetica, sans-serif">
      <select name="association">
      <option selected>Choose Association
      <option value="association name">Association Name
      
      </select>
    </font></td>
  </tr>
  <tr valign="top">
    <td><font size="2" face="Arial, Helvetica, sans-serif">Start Date (MM/DD/YYYY):</font></td>
    <td><font face="Arial, Helvetica, sans-serif">
      <select name="month">
        <option selected>Month</option>
        <option value="01">01</option>
        <option value="02">02</option>
        <option value="03">03</option>
        <option value="04">04</option>
        <option value="05">05</option>
        <option value="06">06</option>
        <option value="07">07</option>
        <option value="08">08</option>
        <option value="09">09</option>
        <option value="10">10</option>
        <option value="11">11</option>
        <option value="12">12</option>
        </select>
    /
    <select name="day">
      <option selected>Day</option>
      <option value="01">01</option>
      <option value="02">02</option>
      <option value="03">03</option>
      <option value="04">04</option>
      <option value="05">05</option>
      <option value="06">06</option>
      <option value="07">07</option>
      <option value="08">08</option>
      <option value="09">09</option>
      <option value="10">10</option>
      <option value="11">11</option>
      <option value="12">12</option>
      <option value="13">13</option>
      <option value="14">14</option>
      <option value="15">15</option>
      <option value="16">16</option>
      <option value="17">17</option>
      <option value="18">18</option>
      <option value="19">19</option>
      <option value="20">20</option>
      <option value="21">21</option>
      <option value="22">22</option>
      <option value="23">23</option>
      <option value="24">24</option>
      <option value="25">25</option>
      <option value="26">26</option>
      <option value="27">27</option>
      <option value="28">28</option>
      <option value="29">29</option>
      <option value="30">30</option>
      <option value="31">31</option>
    </select>
    /
    <select name="year">
      <option selected>Year</option>
      <option value="2004">2004</option>
      <option value="2005">2005</option>
      <option value="2006">2006</option>
    </select>
    </font></td>
  </tr>
  <tr valign="top">
    <td><font size="2" face="Arial, Helvetica, sans-serif">End Date (MM/DD/YYYY):</font></td>
    <td><font face="Arial, Helvetica, sans-serif">
      <select name="month_end">
        <option selected>Month</option>
        <option value="01">01</option>
        <option value="02">02</option>
        <option value="03">03</option>
        <option value="04">04</option>
        <option value="05">05</option>
        <option value="06">06</option>
        <option value="07">07</option>
        <option value="08">08</option>
        <option value="09">09</option>
        <option value="10">10</option>
        <option value="11">11</option>
        <option value="12">12</option>
      </select>
/
<select name="day_end">
  <option selected>Day</option>
  <option value="01">01</option>
  <option value="02">02</option>
  <option value="03">03</option>
  <option value="04">04</option>
  <option value="05">05</option>
  <option value="06">06</option>
  <option value="07">07</option>
  <option value="08">08</option>
  <option value="09">09</option>
  <option value="10">10</option>
  <option value="11">11</option>
  <option value="12">12</option>
  <option value="13">13</option>
  <option value="14">14</option>
  <option value="15">15</option>
  <option value="16">16</option>
  <option value="17">17</option>
  <option value="18">18</option>
  <option value="19">19</option>
  <option value="20">20</option>
  <option value="21">21</option>
  <option value="22">22</option>
  <option value="23">23</option>
  <option value="24">24</option>
  <option value="25">25</option>
  <option value="26">26</option>
  <option value="27">27</option>
  <option value="28">28</option>
  <option value="29">29</option>
  <option value="30">30</option>
  <option value="31">31</option>
</select>
/
<select name="year_end">
  <option selected>Year</option>
  <option value="2004">2004</option>
  <option value="2005">2005</option>
  <option value="2006">2006</option>
</select>
</font></td>
  </tr>
  <tr valign="top">
    <td> </td>
    <td><div align="right">
      <input type="submit" name="Submit" value="Submit">
    </div></td>
  </tr>
</table>



</form>
</body>
</html>


So I have Name as one option, association, date as another

I want to beable to search for name but nothing else or
I want to search for name and association but not date
or just date, or just name, etc


feyd | Please use

Code: Select all

and

Code: Select all

tags where approriate when posting code. Read:  [url=http://forums.devnetwork.net/viewtopic.php?t=21171]Posting Code in the Forums[/url][/color]

Posted: Thu Sep 09, 2004 3:01 pm
by feyd
the searching is determined by your form processor. with the right data checking, you will know that certain fields aren't selected. you then switch to differing code paths to not use those fields in the search.

Posted: Thu Sep 09, 2004 3:02 pm
by Christopher
I don't quite understand exactly what you are looking for, but let me venture a guess. Say you had a form that had the fields state and zip, you might do something like:

Code: Select all

$state = str_toupper(preg_replace('/^a-zA-Z/', '', $_REQUEST['state']));
$zip = preg_replace('/^0-9\-/', '', $_REQUEST['zip']));

$where = '';
if ($state) {
  $where .= "state="$state"";
}
if ($zip) {
  if ($where) {
    $where .= ' AND ';
  }
  $where .= "zip="$zip"";
}
if ($where) {
  $result = $db->query("SELECT * FROM address WHERE $where");
} else {
// error no input
}

Posted: Thu Sep 09, 2004 3:14 pm
by budabug
I can't see what you posted there the screen is blank

Posted: Thu Sep 09, 2004 3:22 pm
by Draco_03
$state = str_toupper(preg_replace('/^a-zA-Z/', '', $_REQUEST['state']));
$zip = preg_replace('/^0-9\-/', '', $_REQUEST['zip']));

$where = '';
if ($state) {
$where .= "state=\"$state\"";
}
if ($zip) {
if ($where) {
$where .= ' AND ';
}
$where .= "zip=\"$zip\"";
}
if ($where) {
$result = $db->query("SELECT * FROM address WHERE $where");
} else {
// error no input
}

can you see it now ?

Posted: Thu Sep 09, 2004 3:47 pm
by budabug
$state = str_toupper(preg_replace('/^a-zA-Z/', '', $_REQUEST['state']));
$zip = preg_replace('/^0-9\-/', '', $_REQUEST['zip']));


excuse ignorasnce--i am just learning-- what is str_toupper(preg_replace('/^a-zA-Z/', and preg_replace('/^0-9\-/',

part of this code?

Posted: Thu Sep 09, 2004 3:52 pm
by feyd
Read the manual: [php_man]strtoupper[/php_man] and [php_man]preg_replace[/php_man]

Posted: Thu Sep 09, 2004 4:20 pm
by budabug
thanks for the read--but whats with the '/^a-zA-Z/', and the '/^0-9\-/',

Posted: Thu Sep 09, 2004 4:33 pm
by feyd
they are regular expression search strings.. however, they look errored...

nothing is working

Posted: Fri Sep 10, 2004 10:03 am
by budabug
Nothing is working can someone take a look at this based on my html form page and tell me what I am doing wrong.

Code: Select all

<?php
	
	     
	      #Get login info
	     require("time_login_test.php");
	 
	     $all_employees = ("name");
	     $full_date_start = $month;
	     $full_date_end = $year_end."-".$month_end."-".$day_end;
	     $full_date = $year."-".$month."-".$day;
	     
	     if (!$db)
	     {
	       echo "Sorry, we're unable to connect to the database right now.  Please try again later.";
	       exit;
	     }
	     
	     mysql_select_db("dbname");
	     	     
	     $searchoption = '';


	     if ($full_date_start)
	     {
	       $searchoption = $searchoption . "AND full_date_start = '".$full_date_start."' ";
	     }
         if ($name)
         {
	       $searchoption = $searchoption . "AND name = '".$name."' ";
	     }
	    if ($full_date_end)
	     {
	       $searchoption = $searchoption . "AND full_date_end = '".$full_date_end."' ";
	     }

         #echo $searchoption;

	     #Determine how many candidates match the criteria
          
           $query = "SELECT * FROM time WHERE association = '".$association."' ".$searchoption." ORDER BY full_date DESC";
           $result = mysql_query($query);
           $num_results = mysql_num_rows($result);
           
	       if ($num_results == 0)
	       {
             echo "<b>There are no candidates that meet your criteria</b>.  Please your back button and broaden your search.";
	       }
	       elseif ($num_results == 1)
	       {
             echo "<b>There is one candidate that meets your criteria</b>.  ";
	       }
	       else 
	       {
             echo "<b>There are ".$num_results." candidates that meet your criteria</b>.  Results are sorted by submission date - the most recent submissions are at the top.";
	       }

          
 
         for ($i=0; $i <$num_results; $i++)
         {
           $row = mysql_fetch_array($result);
           $association = $row["association"];
           echo "<font face="Arial" size="2">";
           echo "<br><hr>";
           #Debug
           echo "<br><i>Match #".($i+1)." of ".$num_results." (submitted ".$row["full_date"]."):</i>";
           #echo "".$association."";
           echo "</font><font face="Arial" size="3">";
           echo "<br><b>";
           echo htmlspecialchars( stripslashes($row["association"]));
           echo " ";
          
           echo "</b></font><font face="Arial" size="2"><br>";
           echo htmlspecialchars( stripslashes($row["name"]));
           echo "<br>";
           echo "Description: ";
           echo htmlspecialchars( stripslashes($row["description"]));
      echo "<br>";
           echo "Minutes: ";
           echo htmlspecialchars( stripslashes($row["minutes"]));
           echo " ";

         }

           echo "<br><hr>";
          
	     
	 ?>

?>

Posted: Fri Sep 10, 2004 1:27 pm
by Christopher
'/^a-zA-Z/' is all characters that are not a-z or A-Z
'/^0-9\-/' is all characters that are not 0-9 or dash

The preg_replace just filters the request vars to get rid of all characters you don't allow (i.e. replaces everything you don't want with null)

Looks like you have register_globals ON, you should turn that OFF

Don't see where you are connectint to DB.

Posted: Fri Sep 10, 2004 1:32 pm
by feyd
arborint wrote:'/^a-zA-Z/' is all characters that are not a-z or A-Z
'/^0-9\-/' is all characters that are not 0-9 or dash
incorrect, that would only be if the entire regex was inside a character class.