[SOLVED] Automated search

GreenSmurf · June 20, 2008

Is there a relatively easy way to create an automated search in PHP? I was thinking of a drop down menu or even radio buttons but I wanted a couple of opinions. Basically, I want to replace a search box with something that will automatically input the string searched for but I also want it to only search certain catagories based on each option.

Radio Buttons

If (isset(Option A)){
 $searchstring='db_col1= ABC';
}
else if (isset(Option B)){
 $searchstring='db_col2= BCD';
}
else {
 $searchstring='';
}
sql ="SELECT * FROM database WHERE $searchstring";

Drop Down Menu

If ($_REQUEST(Option A)!=''){
 $searchstring='db_col1= ABC';
}
else if ($_REQUEST(Option B)!=''){
 $searchstring='db_col2= BCD';
}
else{
 $searchstring='';
}
sql ="SELECT * FROM database WHERE $searchstring";

Something like that- not too sure if those are accurate examples. I kind of just made them up. I guess I just want to know what you would consider efficient and also practical. Perhaps some examples of code to make your case would help too although definitly not necesary. Thank you.

-Brandon

GreenSmurf · June 20, 2008

Actually I just had the idea that it should be checkboxes and each one should be passes as a search under a certain catagory. Same idea but a little more complicated. If anyone has some advice please let me know.

-Brandon

andrewgarn · June 20, 2008

Drop down menu populated from a mysql table?

these options could then be used in a query once the user selects option from menu, and clicks submit

GreenSmurf · June 20, 2008

Well, the options are not necesarily stored in the DB. The way its looking now- I guess I would need some advice on creating a string from a series of checkboxes. Something like:

if (isset(Option A)){
  $searchstring .='db_col1= yes';
}
else if (!isset(Option A)){
  $searchstring .='db_col1= no';
}
if (isset(Option B)){
  $searchstring .='db_col1= yes';
}
else if (!isset(Option B)){
  $searchstring .='db_col2= no';
}
sql ="SELECT * FROM database WHERE $searchstring";

That way the string is adding the proper search parameters depending on which boxes are selected.

-Brandon

GreenSmurf · June 20, 2008

Here is the code I ended up with:

    <tr valign=center>
      <td><strong>Quickbooks Services: </strong></td>
      <td> </td>
  <td>
         <input type="radio" name="qb" value="qbsup" <?php if($_REQUEST['qb']=="qbsup"){echo "checked";} ?>> QB Supplied to Accountant</td>
      <td>
          <input type="radio" name="qb" value="qbent" <?php if($_REQUEST['qb']=="qbent"){echo "checked";} ?>> QB Data Entry by Accountant</td>
      <td>
         <input type="radio" name="qb" value="qbtran" <?php if($_REQUEST['qb']=="qbtran"){echo "checked";} ?>> QB Training</td>
      <td>
         <input type="radio" name="qb" value="cs" <?php if($_REQUEST['qb']=="cs"){echo "checked";} ?>> Creative Solutions</td>
      <td>
         <input type="radio" name="qb" value="tr" <?php if($_REQUEST['qb']=="tr"){echo "checked";} ?>> Tax Return</td></tr>
   <tr>
       <td> </td>
       <td> </td>
       <td>
         <input type="radio" name="qb" value="qbnone" <?php if($_REQUEST['qb']=="qbnone" OR $_REQUEST['qb']==""){echo "checked";} ?>> None</td></tr>
         <tr><td/></tr>
         <tr><td/></tr>
    <tr valign=center>
      <td><strong>Business Type: </strong></td>
      <td> </td>
  <td>
         <input type="radio" name="bus" value="personal" <?php if($_REQUEST['bus']=="personal"){echo "checked";} ?>>Personal</td>
      <td>
         <input type="radio" name="bus" value="ccorp" <?php if($_REQUEST['bus']=="ccorp"){echo "checked";} ?>>C-Corp</td>
      <td>
         <input type="radio" name="bus" value="partner" <?php if($_REQUEST['bus']=="partner"){echo "checked";} ?>>Partnership</td>
      <td>
         <input type="radio" name="bus" value="scorp" <?php if($_REQUEST['bus']=="scorp"){echo "checked";} ?>>S Corp</td>
      <td>
         <input type="radio" name="bus" value="estate" <?php if($_REQUEST['bus']=="estate"){echo "checked";} ?>>Estate & Trust</td>
   </tr>
   <tr valign=center>
      <td> </td>
      <td> </td>
      <td>
         <input type="radio" name="bus" value="non" <?php if($_REQUEST['bus']=="non"){echo "checked";} ?>> Non-Profit</td>
      <td>
         <input type="radio" name="bus" value="sole" <?php  if($_REQUEST['bus']=="sole"){echo "checked";} ?>> Sole Proprietor</td>
      <td>
         <input type="radio" name="bus" value="single" <?php  if($_REQUEST['bus']=="single"){echo "checked";} ?>> Single Member LLC</td>
         <td> </td>
         <td> </td></tr>
      <tr>
        <td> </td>
        <td> </td>
        <td>
         <input type="radio" name="bus" value="busnone" <?php if($_REQUEST['bus']=="busnone" OR $_REQUEST['bus']==""){echo "checked";} ?>> None</td></tr>
         <tr><td/></tr>
         <tr><td/></tr>
   <tr valign=center>
      <td><strong>Financial Statements: </strong></td>
      <td> </td>
  <td>
         <input type="radio" name="fin" value="month" <?php if($_REQUEST['fin']=="month"){echo "checked";} ?>> Monthly</td>
      <td>
         <input type="radio" name="fin" value="quart" <?php if($_REQUEST['fin']=="quart"){echo "checked";} ?>> Quarterly</td>
      <td>
         <input type="radio" name="fin" value="ann" <?php if($_REQUEST['fin']=="ann"){echo "checked";} ?>> Annually</td>
      <td> </td>
      <td> </td>
   </tr>
   <tr>
        <td> </td>
        <td> </td>
        <td>
         <input type="radio" name="fin" value="finnone" <?php if($_REQUEST['fin']=="finnone" OR $_REQUEST['fin']==""){echo "checked";} ?>> None</td></tr>
         <tr><td/></tr>
         <tr><td/></tr>
   <tr valign=center>
      <td><strong>Other: </strong></td>
      <td> </td>
  <td>
         <input type="radio" name="oth" value="tax" <?php if($_REQUEST['oth']=="tax"){echo "checked";} ?>> Sales Tax</td>
      <td>
         <input type="radio" name="oth" value="pay" <?php if($_REQUEST['oth']=="pay"){echo "checked";} ?>> Payroll</td>
      <td> </td>
      <td> </td>
      <td> </td>
   </tr>
   <tr>
        <td> </td>
        <td> </td>
        <td>
         <input type="radio" name="oth" value="othnone" <?php if($_REQUEST['oth']=="othnone" OR $_REQUEST['oth']==""){echo "checked";} ?>> None</td></tr>
         <tr><td/></tr>
         <tr><td/></tr>
<?

$searchstring = "";

if ($_REQUEST['qb']=="qbsup")
   $searchstring .= "qbserv = 'QB Supplied to Accountant'";
else if ($_REQUEST['qb']=="qbent")
   $searchstring .= "qbserv = 'QB Data Entry by Accountant'";
else if ($_REQUEST['qb']=="qbtran")
   $searchstring .= "qbserv = 'QB Training'";
else if ($_REQUEST['qb']=="cs")
   $searchstring .= "qbserv = 'Creative Solutions'";
else if ($_REQUEST['qb']=="tr")
   $searchstring .= "qbserv = 'Tax Return'";
   
if ($searchstring!="")
   $searchstring .=" AND ".$searchstring;
   
if ($_REQUEST['bus']=="personal")
   $searchstring .= "bustype = 'Personal'";
else if ($_REQUEST['bus']=="ccorp")
   $searchstring .= "bustype = 'C-Corp'";
else if ($_REQUEST['bus']=="partner")
   $searchstring .= "bustype = 'Partnership'";
else if ($_REQUEST['bus']=="scorp")
   $searchstring .= "bustype = 'S Corp'";
else if ($_REQUEST['bus']=="estate")
   $searchstring .= "bustype = 'Estate & Trust'";
else if ($_REQUEST['bus']=="non")
   $searchstring .= "bustype = 'Non-Profit'";
else if ($_REQUEST['bus']=="sole")
   $searchstring .= "bustype = 'Sole Proprietor'";
else if ($_REQUEST['bus']=="single")
   $searchstring .= "bustype = 'Single Member LLC'";
   
if ($searchstring!="")
   $searchstring .=" AND ".$searchstring;
   
if ($_REQUEST['fin']=="month")
   $searchstring .= "fstats = 'Monthly'";
else if ($_REQUEST['fin']=="quart")
   $searchstring .= "fstats = 'Quarterly'";
else if ($_REQUEST['fin']=="partner")
   $searchstring .= "fstats = 'Annually'";
   
if ($searchstring!="")
   $searchstring .=" AND ".$searchstring;
   
if ($_REQUEST['oth']=="tax")
   $searchstring .= "stax = 'on'";
else if ($_REQUEST['oth']=="pay")
   $searchstring .= "payroll = 'on'";

echo $searchstring;
?>

My $searchstring ends up looking like this:

qbserv = 'QB Data Entry by Accountant' AND qbserv = 'QB Data Entry by Accountant'bustype = 'Personal' AND qbserv = 'QB Data Entry by Accountant' AND qbserv = 'QB Data Entry by Accountant'bustype = 'Personal' AND qbserv = 'QB Data Entry by Accountant' AND qbserv = 'QB Data Entry by Accountant'bustype = 'Personal' AND qbserv = 'QB Data Entry by Accountant' AND qbserv = 'QB Data Entry by Accountant'bustype = 'Personal'

But should look like this:

qbserv = 'QB Data Entry by Accountant' AND bustype = 'Personal'

I also get these sql errors but I think it has to do with the previous problem of assembling the $searchstring:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Program Files\Abyss Web Server\htdocs\reports.php on line 365
Warning: mysql_numrows(): supplied argument is not a valid MySQL result resource in C:\Program Files\Abyss Web Server\htdocs\reports.php on line 382

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\Abyss Web Server\htdocs\reports.php on line 447

If anyone has any idea what to do please let me know. Thank you.

-Brandon

GreenSmurf · June 21, 2008

Anyone got any suggestions? I'd really appreciate it. Thank you.

-Brandon

DarkWater · June 21, 2008

Add or die(mysql_error()) to the end of your query so you can see the error.

GreenSmurf · June 21, 2008

I tried that and it still remains the same:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'bustype = 'Personal' AND qbserv = 'QB Data Entry by Accountant' AND qbserv = 'QB' at line

It just confirms my belief of where the problem is but I just can't see why there is a problem with the code. If someone could point it out to me that'd be awesome.

This is the part of the query that is bad:

qbserv = 'QB Data Entry by Accountant' AND qbserv = 'QB Data Entry by Accountant'bustype = 'Personal' AND qbserv = 'QB Data Entry by Accountant' AND qbserv = 'QB Data Entry by Accountant'bustype = 'Personal' AND qbserv = 'QB Data Entry by Accountant' AND qbserv = 'QB Data Entry by Accountant'bustype = 'Personal' AND qbserv = 'QB Data Entry by Accountant' AND qbserv = 'QB Data Entry by Accountant'bustype = 'Personal'

It should read:

qbserv = 'QB Data Entry by Accountant' AND bustype = 'Personal'

Any ideas of what is wrong please let me know. The if statements above should only be adding the proper portions of code once not running in a loop style and tacking each thing on several times.

-Brandon

GreenSmurf · June 21, 2008

I solved it and will post the solution on Monday if anyone is curious. Thank you to anyone who helped.

-Brandno

Sign In

[SOLVED] Automated search

Recommended Posts

GreenSmurf

Link to comment

Share on other sites

GreenSmurf

Link to comment

Share on other sites

andrewgarn

Link to comment

Share on other sites

GreenSmurf

Link to comment

Share on other sites

GreenSmurf

Link to comment

Share on other sites

GreenSmurf

Link to comment

Share on other sites

DarkWater

Link to comment

Share on other sites

GreenSmurf

Link to comment

Share on other sites

GreenSmurf

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information