How to tie in a multiple choice search form to my PHP code?

[CountryGirl]

I'm trying to make a search form with multiple choices to be searched from. I want it to still return the same results from the same tables, just have someone be able to choose which criteria they want to search by. So, my question is, how do I tie in the multiple choices in the search form with the PHP code? I assume it would be put somewhere in the WHERE clause, but I'm not completely sure.

 

This is my search form code:

 

<h1>Search Records</h1>
	<form method="post" action="searchjoin3.php">
		<table width=90% align=center>
			<tr><td><select name="selectName" size="3" multiple="multiple">
						<option selected="selected" value="one">Account #</option>
						<option selected="selected" value="two">Name</option>
						<option selected="selected" value="three">Legal</option>
					</select></td><td><input type=text name='search' size=60 maxlength=255></td></tr>
			<td></td>
			<td><input type=submit value="Search Records"></td>

 

And my PHP search query:

 

"SELECT asmnt_parcel.Account, asmnt_parcel.OwnersName, asmnt_parcel.ParcelID, asmnt_parcel.Township, asmnt_parcel.Range, asmnt_parcel.Section, asmnt_parcel.LotSize, asmnt_parcel.LotSizeType, asmnt_parcel.TaxAreaCode, asmnt_parcel.TotalValue, asmnt_legal.Legal, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_situs.Situs, appr_resident.TotalArea, appr_resident.YearBuilt, appr_miscimpr.Description, appr_miscimpr.Year, appr_miscimpr.Size, appr_miscimpr.Value, sale_parcel.SaleDate
			 FROM asmnt_parcel
			 INNER JOIN asmnt_legal 
			 ON asmnt_parcel.Account=asmnt_legal.Account
			 INNER JOIN cmn_name
			 ON asmnt_parcel.OwnersName=cmn_name.OwnersName
			 INNER JOIN asmnt_situs
			 ON asmnt_parcel.Account=asmnt_situs.Account
			 INNER JOIN appr_resident
			 ON asmnt_parcel.Account=appr_resident.Account
			 INNER JOIN appr_miscimpr
			 ON asmnt_parcel.Account=appr_miscimpr.Account
			 INNER JOIN sale_parcel
			 ON asmnt_parcel.Account=sale_parcel.Account
			 WHERE asmnt_parcel.Account LIKE '{$search}'
             ORDER BY asmnt_parcel.Account ASC";
   $result = mysql_query($query, $con) or die(mysql_error().": $query");

 

As you can see right now I just have the one search option in the WHERE clause. But, I need to change that obviously.

 

Any tips and ideas on how to do it would be great!!

 

Thank you!

Qadoshyah

[jl5501]

Hi

 

Firstly your form needs to look like this

 

<tr><td><select name="selectName[]" size="3" multiple="multiple">
                     <option value="one">Account #</option>
                     <option value="two">Name</option>
                     <option value="three">Legal</option>
                  </select></td><td><input type=text name='search' size=60 maxlength=255></td></tr>

 

 

and that will mean that all the selected values of selectName will be passed in an array called

$_POST['selectName']

 

so you can process those values in your query

 

it probably needs to be something like this for the where clause in your query

foreach($_POST['selectName'] as $selectedName)
(
      $whereclause .= 'OR myfieldname = '.$seletedname;
)

 

you will need to add a sub phrase for each value in that array and separate with OR

 

That is not particularly clear, but you should get the idea.

 

I have some code from a project that could post snippets from

 

 

[CountryGirl]

Hi

 

Firstly your form needs to look like this

 

<tr><td><select name="selectName[]" size="3" multiple="multiple">
                     <option value="one">Account #</option>
                     <option value="two">Name</option>
                     <option value="three">Legal</option>
                  </select></td><td><input type=text name='search' size=60 maxlength=255></td></tr>

 

 

and that will mean that all the selected values of selectName will be passed in an array called

$_POST['selectName']

 

so you can process those values in your query

 

it probably needs to be something like this for the where clause in your query

foreach($_POST['selectName'] as $selectedName)
(
      $whereclause .= 'OR myfieldname = '.$seletedname;
)

 

you will need to add a sub phrase for each value in that array and separate with OR

 

That is not particularly clear, but you should get the idea.

 

I have some code from a project that could post snippets from

 

Thank you so much!! That does give me some ideas. If you could post some snippets from that code of yours that would be great. I'm fairly new to PHP, so just the simple info doesn't always make sense to me (sorry, but I'm doing all this and learning PHP at the same time, haha!).

 

Do I need multiple $_POST statements? I assume not, but just wanted to verify.

 

For the WHERE clause, do I put all that information below in for each choice?

 

foreach($_POST['selectName'] as $selectedName)
(
      $whereclause .= 'OR myfieldname = '.$seletedname;
)

 

Thanks!

Qadoshyah

[jl5501]

Ok, I hope there is something in here that will help you. There is some stuff that you will not need, as my code is getting the options list from the database first

 

<tr>
      <td class="tabletext">Country or Region of Origin:<br />
        <span class="footer">( hold CTRL/Command key to select more then 1 country or region.)</span></td>
      <td class="tabletext">
         <select name="country[]" multiple="multiple" size="5">
            <option value="0">All</option>
<?php
while($country = mysql_fetch_object($countries))
{

?>
            <option value="<?php echo $country->countryid;?>"><?php echo $country->countryname;?></option>
<?php
}
mysql_data_seek($countries,0);
while($region = mysql_fetch_object($regions))
{
?>
            <option value="R<?php echo $region->id;?>"><?php echo $region->name;?></option>
<?php
}
?>
          </select></td>
    </tr>

 

But in essence the only important thing there is the [] on the name to make an array

 

then when it gets processed I do this

 

foreach($_POST['country'] as $key => $val)
      {
         if(substr($val,0,1) == 'R')
         {
            $rid = substr($val,1);
            $crs = getList('countries',' where rid='.$rid);
            while($cr = mysql_fetch_object($crs))
            {
               if($i)
                $cond .= ' or country = '.$cr->countryid;
               else
                $cond .= ' country = '.$cr->countryid;
               $i++;
            }
         }
         else
         {
            if($i)
             $cond .= ' or country = '.$val;
            else
             $cond .= ' country = '.$val;
         }
         $i++;
      }
      $cond .= ')';

 

This is actually a far more complex example then you need as I am adding countries or regions to the query, but basically, I bulid up the where clause in the $cond variable ready to add it to the query.

 

With doing this, the main thing is that the selected options are in an array $_POST['country'] and I do a foreach on that array to extract all the countries selected and add to the where clause

[jl5501]

If you can give me a while, I will trim that down to just have the bits you need

[CountryGirl]

If you can give me a while, I will trim that down to just have the bits you need

 

Thank you so much!  That'll be great!! I won't be working on the site I need this for all weekend anyways. So, when you get it trimmed down that will be awesome! I think it'll make much more sense once it's trimmed down.

 

Thanks a ton!

Qadoshyah

[CountryGirl]

I'm a little lost, especially when it comes to the foreach statements. Anymore advice/help you have would be great!! I've read over the code you posted a few times and am just not quite sure how to implement it all to my code.

 

Thanks so much for the help!

Qadoshyah

[jl5501]

I will post a simplified version of my code, a little later

[CountryGirl]

I will post a simplified version of my code, a little later

 

Awesome, thanks so much!

 

Qadoshyah

[jl5501]

Ok the simplified code as promised

 

First the part of the form where multiple countries can be selected. Note the use of multiple="multiple" and the [] on the end of the name

This means that you can select more than 1 and that they will be passed as an array to the processing script

<tr>
      <td class="tabletext">Country of Origin:<br />
        <span class="footer">( hold CTRL/Command key to select more then 1 country.)</span></td>
      <td class="tabletext">
         <select name="country[]" multiple="multiple" size="5">
            <option value="0">All</option>
<?php
while($country = mysql_fetch_object($countries))
{

?>
            <option value="<?php echo $country->countryid;?>"><?php echo $country->countryname;?></option>
         </select></td>
    </tr>

 

Then the processing script is building up part of the whereclause for a query. You may well have other conditions that go into the eventual whereclause. This one in its live environment certainly does.

 

$cond = 'where ';
$i =0
foreach($_POST['country'] as $key => $val)
{
       if($i)
            $cond .= ' or country = '.$val;
       else
            $cond .= ' country = '.$val;
       $i++;
}

 

We see the array from the form as $_POST['country'] and go through it adding each country to the whereclause

[jl5501]

I missed off the closing of the while loop in the first block there, so will repost it here

 

<tr>
      <td class="tabletext">Country of Origin:<br />
        <span class="footer">( hold CTRL/Command key to select more then 1 country.)</span></td>
      <td class="tabletext">
         <select name="country[]" multiple="multiple" size="5">
            <option value="0">All</option>
<?php
while($country = mysql_fetch_object($countries))
{
?>
            <option value="<?php echo $country->countryid;?>"><?php echo $country->countryname;?></option>
<?php
}
?>
         </select></td>
    </tr>

[CountryGirl]

Thank you so much! I will work on my code and see how I can implement it well into my code. I think I can make it work. This will be a great reference to look at as I work on my code. I may come back with more questions tomorrow when I start working on my code, but we'll see. Maybe not .

 

Thanks again!

Qadoshyah

[CountryGirl]

I don't have any of my PHP code in an array, I think that's where my confusion/problem is. So, I'm trying to figure out the best way to write the arrays I need and then maybe the foreach and stuff you posted above will work better and make more sense.

 

This is way more confusing than I thought it would be! But, I know this will be the most efficient way for my search to work.

 

Thanks!

Qadoshyah

[darkfreaks]

if your still having trouble let us know.

 

also post the code you have currently that way i can see how to implement it for you.

 

 

otherwise feel free to press the "topic solved" button on the lower left hand corner.

[CountryGirl]

Yes, I'm still having troubles .  I'm lost in PHP world right now  .

 

I realized that for some of the code/ideas given here, I need to have an array it appears - is that right?

 

From reading up on how to create arrays, it appears I need to list everything in the database. I'm sure there's a way around this. How do I just create an array to one specific table and have the value be a wildcard or something like that?

 

I tried adding some of the coding (and putting the information I need in it) that was suggested and putting it where I thought it needed to go, but it didn't work. I'm sure it was my fault, as I probably didn't have everything written right for it.

 

This is my search form:

 

<head><title>Searching Another Test</title>
</head>
<body bgcolor="#bba86d">
	<div align="center">
		<p></p>
	</div>
	<h1>Search Records</h1>
	<form method="post" action="searchjoin3.php">
		<table width="90%" align="center">
		<tr><td><select name="selectName[]" size="3" multiple="multiple">
                     <option value="one">Account #</option>
                     <option value="two">Name</option>
                     <option value="three">Legal</option>
                  </select></td><td><input type=text name='search' size=60 maxlength=255></td></tr>
			<td><input type=submit value="Search Records"></td>
			</tr>	
		</table>
   
</form>

 

And my PHP code:

 

$search = $_POST['selectName'];

if ($search) // perform search only if a string was entered.
{
   $con = mysql_connect($dbHost, $dbUser, $dbPass) or die("Failed to connect to MySQL Server. Error: " . mysql_error());
   mysql_select_db($dbDatabase) or die("Failed to connect to database {$dbDatabase}. Error: " . mysql_error());
   
   $query = "SELECT asmnt_parcel.Account, asmnt_parcel.OwnersName, asmnt_parcel.ParcelID, asmnt_parcel.Township, asmnt_parcel.Range, asmnt_parcel.Section, asmnt_parcel.LotSize, asmnt_parcel.LotSizeType, asmnt_parcel.TaxAreaCode, asmnt_parcel.TotalValue, asmnt_legal.Legal, cmn_name.Address2, cmn_name.City, cmn_name.State, cmn_name.ZipCode, asmnt_situs.Situs, appr_resident.TotalArea, appr_resident.YearBuilt, appr_miscimpr.Description, appr_miscimpr.Year, appr_miscimpr.Size, appr_miscimpr.Value, sale_parcel.SaleDate
			 FROM asmnt_parcel
			 INNER JOIN asmnt_legal 
			 ON asmnt_parcel.Account=asmnt_legal.Account
			 INNER JOIN cmn_name
			 ON asmnt_parcel.OwnersName=cmn_name.OwnersName
			 INNER JOIN asmnt_situs
			 ON asmnt_parcel.Account=asmnt_situs.Account
			 INNER JOIN appr_resident
			 ON asmnt_parcel.Account=appr_resident.Account
			 INNER JOIN appr_miscimpr
			 ON asmnt_parcel.Account=appr_miscimpr.Account
			 INNER JOIN sale_parcel
			 ON asmnt_parcel.Account=sale_parcel.Account
             ORDER BY asmnt_parcel.Account ASC";
   $result = mysql_query($query, $con) or die(mysql_error().": $query");

 

And:

 

while ($r = mysql_fetch_array($result))
      { // Begin while
         $act = $r["Account"]; 
         $nme = $r["OwnersName"];
         $add = $r["Address2"];  
         $city = $r["City"];  
         $ste = $r["State"];  
         $zip = $r["ZipCode"]; 
         $legal = $r["Legal"];   
         $pid = $r["ParcelID"]; 
         $size = $r["LotSize"];  
         $type = $r["LotSizeType"]; 
         $sqft = $r["TotalArea"];
         $built = $r["YearBuilt"];  
         $valu = $r["TotalValue"];
         $impr = $r["Description"];
         $iyr = $r["Year"];
         $isze = $r["Size"];
         $ivlu = $r["Value"];
         $sch = $r["TaxAreaCode"];
         $sit = $r["Situs"];
         $tship = $r["Township"]; 
         $rng = $r["Range"];
         $sctn = $r["Section"];   
         $date = $r["SaleDate"]; 
         echo "<tr>
            <td>$act</td>
            <td>$nme</td>
            <td>$add</td>
            <td>$city</td>
            <td>$ste</td>
            <td>$zip</td>
            <td>$legal</td>
            <td>$pid</td>
            <td>$size</td>
            <td>$type</td>
            <td>$sqft</td>
            <td>$built</td>
            <td>$valu</td>
            <td>$impr</td>
            <td>$iyr</td>
            <td>$isze</td>
            <td>$ivlu</td>
            <td>$sch</td>
            <td>$sit</td>
            <td>$tship</td>
            <td>$rng</td>
            <td>$sctn</td>
            <td>$date</td>
            </tr>";
      } // end while
      

 

Well, looking more at my code . . . maybe I do have an array of sorts with the script above: "($r = mysql_fetch_array($result))."

 

Where would I put a foreach statement in there to help tie it in? That's where I'm lost. Sorry if these are dumb questions, but I am quite new to PHP and am more confused than I thought I would be with this!

 

Thanks for any more help from y'all!

 

Qadoshyah

[darkfreaks]

why do you need a foreach loop when you have the while loop

 

are you using his code where he completely changed your form select to country[] instead of selectname[]

 

in that case you would need a for each loop to display each country or select

 

[darkfreaks]

after looking at it he used the for each statement in replace of the while statement so you would have to cut out the while statement and add in the for each.

[CountryGirl]

Well, yeah, I was just realizing that as well with my while loop. I'd rather leave that while loop there, since it works well.

 

So, my only question is, how to be able to search from the multiple choices in the search form and return accurate results. Do I just need to add somethings to the while loop?

 

This is the page I'm working on and what I want to be able to let people select from: http://www.wagonerassessor.com/searchjoin3.php

 

Thanks!

Qadoshyah

[darkfreaks]

no his code will work its just a matter of adding it to your query

[CountryGirl]

no his code will work its just a matter of adding it to your query

 

Okay, so where would I add it in? Would I need to change anything in my code? Or just add his code and tweak it for my database?

 

Qadoshyah

[darkfreaks]

im really confused myself where he got his code as your not using countries

[CountryGirl]

im really confused myself where he got his code as your not using countries

 

I guess it was off of something he did. But, it's confusing since I don't quite get what to do, because of the countries aspect.

 

 

Qadoshyah

[jl5501]

Hi

 

Just digesting what you have posted, then will come up with some suggestions for you

[darkfreaks]

if you want to do a wildcard from your loop you can do something like

 

 

WHERE age LIKE '2%'

 

in the SELECT clause

 

which will return everything with a partial match of the value

[jl5501]

My code was from a live site of mine, trimmed down to use as an example