code for doing a search from a database

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

budabug
Forum Newbie
Posts: 8
Joined: Fri Sep 03, 2004 10:32 am

code for doing a search from a database

Post 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
User avatar
nigma
DevNet Resident
Posts: 1094
Joined: Sat Jan 25, 2003 1:49 am

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

Post by feyd »

searching would have reveiled something like this: [devnet]+multiple +search +form[/devnet]
budabug
Forum Newbie
Posts: 8
Joined: Fri Sep 03, 2004 10:32 am

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

Post 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.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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
}
budabug
Forum Newbie
Posts: 8
Joined: Fri Sep 03, 2004 10:32 am

Post by budabug »

I can't see what you posted there the screen is blank
Draco_03
Forum Regular
Posts: 577
Joined: Fri Aug 15, 2003 12:25 pm
Location: Montreal, Canada

Post 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 ?
budabug
Forum Newbie
Posts: 8
Joined: Fri Sep 03, 2004 10:32 am

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

Post by feyd »

Read the manual: [php_man]strtoupper[/php_man] and [php_man]preg_replace[/php_man]
budabug
Forum Newbie
Posts: 8
Joined: Fri Sep 03, 2004 10:32 am

Post by budabug »

thanks for the read--but whats with the '/^a-zA-Z/', and the '/^0-9\-/',
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

they are regular expression search strings.. however, they look errored...
budabug
Forum Newbie
Posts: 8
Joined: Fri Sep 03, 2004 10:32 am

nothing is working

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

?>
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

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

Post 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.
Post Reply