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

CountryGirl · February 5, 2010

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 · February 5, 2010

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 · February 5, 2010

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 · February 5, 2010

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 · February 5, 2010

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

CountryGirl · February 6, 2010

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 · February 8, 2010

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 · February 8, 2010

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

CountryGirl · February 8, 2010

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

Awesome, thanks so much!

Qadoshyah

jl5501 · February 8, 2010

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 · February 8, 2010

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 · February 8, 2010

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 · February 9, 2010

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 · February 9, 2010

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 · February 9, 2010

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

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 · February 9, 2010

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

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 · February 9, 2010

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 · February 9, 2010

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 · February 9, 2010

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

CountryGirl · February 9, 2010

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 · February 9, 2010

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

CountryGirl · February 9, 2010

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.

:confused:

Qadoshyah

jl5501 · February 9, 2010

Hi

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

darkfreaks · February 9, 2010

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 · February 9, 2010

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

Sign In

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

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information