[SOLVED] Automated search

[GreenSmurf]

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]

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]

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]

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]

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]

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

 

-Brandon

[DarkWater]

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

[GreenSmurf]

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]

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

 

-Brandno