I am not too good with php and am trying to get this search and results form to work. I am trying to get the user to be able to search by shape, color, price range, etc. I was only able to let them search for one color and one lab, However, there is more than one shape and lab to choose from. I know i need to use an array for these variable but don't know how to retrieve them in my query. Can anyone help me?

the search form:

<form action="template_search2.php" method="post">
      <table width="84%" border="0" align="center" cellspacing="2">
        <tr>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgA.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Round</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <label>
                      <input type="checkbox" name="searchshape[]" value="Round" class="text_B"/>
                      </label>
                  </div></td>
                </tr>
              </table>
          </div></td>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgB.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Princess</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <input type="checkbox" name="searchshape[]" value="Princess"class="text_B" />
                  </div></td>
                </tr>
              </table>
          </div></td>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgC.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Emerald</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <input type="checkbox" name="searchshape[]" value="Emerald" class="text_B" />
                  </div></td>
                </tr>
              </table>
          </div></td>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgD.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Radiant</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <input type="checkbox" name="searchshape[]" value="Radiant" class="text_B"/>
                  </div></td>
                </tr>
              </table>
          </div></td>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgE.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Asscher</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <input type="checkbox" name="searchshape[]" value="Asscher"class="text_B" />
                  </div></td>
                </tr>
              </table>
          </div></td>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgF.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Oval</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <input type="checkbox" name="searchshape[]" value="Oval" class="text_B"/>
                  </div></td>
                </tr>
              </table>
          </div></td>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgG.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Marquise</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <input type="checkbox" name="searchshape[]" value="Marquise" class="text_B"/>
                  </div></td>
                </tr>
              </table>
          </div></td>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgH.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Heart</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <input type="checkbox" name="searchshape[]" value="Heart" class="text_B"/>
                  </div></td>
                </tr>
              </table>
          </div></td>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgI.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Pear</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <input type="checkbox" name="searchshape[]" value="Pear" class="text_B"/>
                  </div></td>
                </tr>
              </table>
          </div></td>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgJ.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Trillian</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <input type="checkbox" name="searchshape[]" value="Trillian" class="text_B"/>
                  </div></td>
                </tr>
              </table>
          </div></td>
          <td class="diamond_search_border"><div align="center">
              <table width="98%" border="0" align="center" cellspacing="2">
                <tr>
                  <td><div align="center"><img src="images/imgK.gif" width="30" height="30" /></div></td>
                </tr>
                <tr>
                  <td><div align="center">Cushion</div></td>
                </tr>
                <tr>
                  <td><div align="center">
                      <input type="checkbox" name="searchshape[]" value="Cushion" class="text_B"/>
                  </div></td>
                </tr>
              </table>
          </div></td>
        </tr>
      </table>
      <hr align="center" width="200" noshade="noshade" color="#003333" />
      <table width="94%" border="0" align="center" cellspacing="2">
        <tr>
          <td width="28%" class="text_A"><div align="center">Price Range: </div></td>
          <td width="28%" class="text_A"><div align="center">Carats</div></td>
          <td width="23%" class="text_A"><div align="center">Color</div></td>
          <td width="21%" class="text_A"><div align="center">Clarity</div></td>
        </tr>
        <tr>
          <td><div align="center">
              <label>
              <input name="searchpricefrom" type="text" size="6" class="text_B"/>
              </label>
            to
            <input name="searchpriceto" type="text" size="6" class="text_B"/>
          </div></td>
          <td><div align="center">
              <label>
              <input name="searchcaratsfrom" type="text" size="6" class="text_B"/>
              </label>
            to
            <input name="searchcaratsfrom" type="text" size="6" class="text_B"/>
          </div></td>
          <td><div align="center">
              <select name="colorfrom" class="text_B">
                <option>D</option>
                <option>E</option>
                <option>F</option>
                <option>G</option>
                <option>H</option>
                <option>I</option>
                <option>J</option>
                <option>K</option>
                <option>L</option>
                <option>M</option>
                <option>N</option>
              </select>
            to
            <select name="colorto" class="text_B">
              <option>D</option>
              <option>E</option>
              <option>F</option>
              <option>G</option>
              <option>H</option>
              <option>I</option>
              <option>J</option>
              <option>K</option>
              <option>L</option>
              <option>M</option>
              <option>N</option>
            </select>
          </div></td>
          <td><div align="center">
              <select name="searchclarityfrom" class="text_B">
                <option>FL</option>
                <option>IF</option>
                <option>VVS1</option>
                <option>VVS2</option>
                <option>VS1</option>
                <option>VS2</option>
                <option>SI1</option>
                <option>SI2</option>
                <option>SI3</option>
                <option>I1</option>
                <option>I2</option>
                <option>I3</option>
              </select>
            to
            <select name="searchclarityto" class="text_B">
              <option>FL</option>
              <option>IF</option>
              <option>VVS1</option>
              <option>VVS2</option>
              <option>VS1</option>
              <option>VS2</option>
              <option>SI1</option>
              <option>SI2</option>
              <option>SI3</option>
              <option>I1</option>
              <option>I2</option>
              <option>I3</option>
            </select>
          </div></td>
        </tr>
      </table>
      <hr align="center" width="200" noshade="noshade" color="#003333" />
      <table width="489" border="0" align="center" cellspacing="2">
        <tr>
          <td width="34" class="text_A"><div align="right">Lab: </div></td>
          <td width="64" class="diamond_search_border"><div align="center">
              <input type="checkbox" name="searchlab" value="EGL" class="text_B" id="lab"/>
            EGL</div></td>
          <td width="66" class="diamond_search_border"><div align="center">
              <input type="checkbox" name="searchlab" value="AGS" class="text_B" id="lab"/>
            AGS</div></td>
          <td width="77" class="diamond_search_border"><div align="center">
              <input type="checkbox" name="searchlab" value="GIA" class="text_B" id="lab"/>
            GIA</div></td>
          <td width="74" class="diamond_search_border"><div align="center">
              <input type="checkbox" name="searchlab" value="IGI" class="text_B" id="lab"/>
            IGI</div></td>
          <td width="74" class="diamond_search_border"><div align="center">
              <input type="checkbox" name="searchlab" value="HRD" class="text_B" id="lab"/>
            HRD</div></td>
          <td width="70" class="diamond_search_border"><div align="center">
              <input type="checkbox" name="searchlab" value="Own" class="text_B" id="lab" />
            Own</div></td>
        </tr>
      </table>
      <p>
        <input name="submit" type="submit" class="text_A" value="Search" />
      </p>
    </form>

the results page:

<?php

/*set varibles from form */

$searchshape[] = trim ($searchshape);
$searchshapetwo = trim ($searchshapetwo);
$searchlab = trim ($searchlab);

$searchshape = array("Round","Princess");
#



/* connects to database */
@ $dbconn = new mysqli('MYSQL01', 'websightprojects', 'brow51', 'websightprojects');
if (mysqli_connect_errno())
{
echo 'Error: Could not connect to database.  Please try again later.';
exit;
}
/*query the database*/
$query = "select * from diamond where shape like '%".$searchshape."%' AND lab like '%".$searchlab."%' ";
$result = $dbconn->query($query);


echo "<table width='98%' border='0' cellspacing='0' bordercolor='#000000' class='main'>
  <tr class='results_brder'><td width='8%' class='text_A'>ID#</td>";
echo "<td width='8%' class='text_A'>Shape</td>";
echo "<td width='8%' class='text_A'>Carat</td>";
echo "<td width='8%' class='text_A'>Color</td>";
echo "<td width='8%' class='text_A'>Clarity</td>";
echo "<td class='text_A'><div align='center'>Measurements</div></td>";
echo "<td width='8%' class='text_A'><div align='center'>Lab</div></td>";
echo "<td width='8%' class='text_A'>Price/ct</td>";
echo "<td width='8%' class='text_A'>Price</td>";
echo "<td width='5%' class='text_A'>%Rap</td>";
echo "<td width='8%' class='text_A'><div align='right'>Details</div></td> </tr><br>";


 

//echo '<p>There are :'.$num_results.' results.</p>';/*number of rows found*/
$num_results = $result->num_rows;
/*loops through results*/
for ($i=0; $i <$num_results; $i++)
{
$num_found = $i + 1;
$row = $result->fetch_assoc();

echo "<tr class ='text_B'><td>".($row['diamondid'])."</td>";
echo "<td>".($row['shape'])."</td>";
echo "<td><div align='center'>".($row['carat'])."</div></td>";
echo "<td><div align='center'>".($row['color'])."</div></td>";
echo "<td><div align='center'>".($row['clarity'])."</div></td>";
echo "<td>".($row['measurements'])."</span>-<span>".($row['mone'])."</span>x<span>".($row['mtwo'])."</span>mm</td>";
echo "<td><div align='center'>".($row['lab'])."</div></td>";
echo "<td><div align='center'>".($row['priceCT'])."</div></td>";
echo "<td><div align='center'>".($row['price'])."</div></td>";
echo "<td><div align='center'>-".($row['rap'])."</div></td>";
echo "<td><div align='right'><span class='style3'><a href='diamondspec.php?diamondid=".$row['diamondid']."' class='a'>View</a> <br></div></td></tr>";

}


/*free database*/

$dbconn->close();
?>

I had the same case as yours and I used the switch case capability of PHP, you can also use If else and nested If but in my case i can make several options for search but had to make it in different forms, each having at most two options only;

//declare variables
$dbhost = 'localhost';
$dbuser = 'root';
$dbpass = 'dost';
$dbname = 'db_agreements';

//connect to mysql
$conn = mysql_connect($dbhost, $dbuser, $dbpass) or die ('Error connecting to mysql');

//select database
mysql_select_db($dbname);

//variables for search

$Type=$_POST["Type"];
$searchID=$_POST["txtID"];
$searchTitle=$_POST["txtTitle"];




switch ($Type) { 
	
case "Agreements":
		$result = mysql_query("SELECT agreement_id, country, title, signed, ratified,  status, signatories, mode, areas, validity, effectivity, activity FROM tbl_agreement WHERE title LIKE '%$searchTitle%' "); 
		while($row=mysql_fetch_array($result))
			{	
   				//change date format
			$d_signed="{$row['signed']}";
			$d_rat="{$row['ratified']}";
			$d_eff="{$row['effectivity']}";
			
			$d_signed = strftime("%b %d, %Y" ,$d_signed);
			$d_rat = strftime("%b %d, %Y" ,$d_rat);
			$d_eff = strftime("%b %d, %Y" ,$d_eff);
			
			
			
	echo "<b>Agreement ID :</b>{$row['agreements_id']} <br>" .
   		"<b>Country :</b>{$row['country']} <br>" .
   	        "<b>Title :</b>{$row['title']} <br>" .
       		"<b>Date Signed :</b>  {$row['signed']}<br>" .
		"<b>Date Ratified :</b> {$row['ratified']} <br>" .
		 "<b>Effectivity :</b> {$row['effectivity']} <br>" .
		"<b>Status :</b>{$row['status']} <br>" .
		"<b>Signatories :</b> {$row['signatories']} <br>" .
		"<b>modes of Cooperation :</b> {$row['modes']} <br>" .
		"<b>Areas of Concern :</b> {$row['areas']} <br>" .
		"<b>Validity :</b> {$row['validity']} <br>" .
		"<b>Activities :</b> {$row['activity']} <br><br>";
			}
	//}
break;

case "Protocols":
	$searchID=$_POST["txtID"];
		
		$result = mysql_query("SELECT agreement, protocol_id, title, country, modes_coop, areas_con, agreement_id FROM tbl_protocols WHERE title LIKE '%$searchTitle%'  "); 
		while($row=mysql_fetch_array($result))
			{	
   				
 
		echo "<b>Agreement Number :</b>{$row['agreement_id']} <br>" .
			"<b>Agreement :</b>{$row['agreement']} <br>" .
			"<b>Country :</b>{$row['country']} <br>" .
   			"<b>Protocol Number :</b>{$row['protocol_id']} <br>" .
        		"<b>Title :</b> {$row['title']} <br>" .
			"<b>Modes of Cooperation :</b> {$row['mode_coop']} <br>" .
			"<b>Areas of Concern :</b> {$row['areas_con']} <br><br>";
			}
	//}
break;

case "Projects":
	$searchID=$_POST["txtID"];
		
		$result = mysql_query("SELECT project_id, title, description, rp_budget, ctrpart_budget, activities, remarks, country, protocol_id, agencies FROM tbl_projects WHERE title LIKE '%$searchTitle%' "); 
		
		while($row=mysql_fetch_array($result))
			{	
   				$agreement_id=$row["agreement_id"];
 
		echo "<b>Country :</b>{$row['country']} <br>" .
   			 "<b>Protocol ID :</b>{$row['protocol_id']} <br>" .
			 "<b>Project ID :</b>{$row['project_id']} <br>" .
        		 "<b>Title :</b> {$row['title']} <br>" .
			 "<b>Description :</b> {$row['description']} <br>" .
			 "<b>RP Budget :</b>{$row['rp_budget']} <br>" .
			 "<b>Counter Part Budget :</b>{$row['ctrpart_budget']} <br>" .
			 "<b>Activities :</b>{$row['activities']} <br>" .
			 "<b>Agencies :</b>{$row['agencies']} <br>" .
			 "<b>Remarks :</b> {$row['remarks']} <br><br>";
			}
	//}
break;
default:
$searchID=$_POST["txtSearchID"];
//	else
		echo "Enter Search";
}


 ?>

Good thing the people in our company only needs two searches for now, since i havn't learned in making more than two queries in a single form, but I guess what i did is just the same principle if your going to add more options to your search, im still trying to finish four additional options on top of the two. to the experts, any suggestions. please :D

Thanks, but how do set set it so i can have it read the other fields?
when i modify the query to read other inputs for other fields it returns nothing.

I only used a single page and to read the fields you can use this as a sample

$Type=$_POST["Type"];

This means your calling the data in your textbox named Type and placing it inside the variable $Type,

make sure you typed everything correctly, from queries to variables.

Then in your queries you can use "LIKE" or "=.

also some variables in the queries requires "%" before and after the variable name like:

$result = mysql_query("SELECT project_id, title, description, rp_budget, ctrpart_budget, activities, remarks, country, protocol_id, agencies FROM tbl_projects WHERE title LIKE '%$searchTitle%' ");

hope this answers your question?, if not il try to think of one, and we can always ask the experts here, hehehe

I only used a single page and to read the fields you can use this as a sample

$Type=$_POST["Type"];

This means your calling the data in your textbox named Type and placing it inside the variable $Type,

make sure you typed everything correctly, from queries to variables.

Then in your queries you can use "LIKE" or "=.

also some variables in the queries requires "%" before and after the variable name like:

$result = mysql_query("SELECT project_id, title, description, rp_budget, ctrpart_budget, activities, remarks, country, protocol_id, agencies FROM tbl_projects WHERE title LIKE '%$searchTitle%' ");

hope this answers your question?, if not il try to think of one, and we can always ask the experts here, hehehe

I appreciate the help, I'll try it out. But what about the checkboxes and droplists?

Before what i did was for check boxes you can use if else for true or false or 1 or 0, say if chkemerald; if chkemerald = 0 then (condition) or if chkemerald = true.

for the droplists, what i did was i simply checked the values in the droplists, just like my previous sample code, the one with the switch case, or you can use if else, which ever you'd like. and i checked your code and I was just wondering if you gave each checkbox a unique name?...

once each checkbox has a unique name, I would suggest that you put the values in a variable so that it would be easier for you to do whatever you'd like with the values that you've got.... :D

I think I'm getting closer, I just need help in figuring out how to query it in.
as of now I have it as:

$query = "select * from diamond where shape like '%".$searchshapeone."%' and shape like '%".$searchshapetwo."%'";

$searchshapeone and $searchshapetwo are the names of the check boxes. why doesn't this query work though? It should give me values where shape is like both variable right?

What I see is your

'%".$searchshapeone."%'

and

'%".$searchshapetwo."%'

try using this instead

'%$your_variable%'

$your_variable would be the name of your checkboxes
see what the results would be...

The above query doesn't work because, it will search for the record where shape is like "$searchshapeone" AND again, like, "$searchshapetwo". It will work only if there is a record in the database with shape like both $searchshapeone and $searchshapetwo. For example, If there is a record in the table with shape, for example, 12345, if $searchshapeone = 123 and $searchshapetwo = 345, then your query will work (because both conditions ie., shape like '%123%' and shape like '%345%' are satisfied). Try out with OR condition. It will work !

same thing... I can search by checking one box only but get nothing in return if two or more checkboxes are selected...

It still didn't work. It seems i can't individually name my check boxes and have them transfer the values on my query if they are in the same field. Now I'm really stuck. Any ideas..

You have a code

<input type="checkbox" name="searchshape[]" value="Round" class="text_B"/>

simply change the

searchshape[]

for every checkbox that you have so that each has a unique name

sample

<input type="checkbox" name="emerald" value="Round" class="text_B"/>


<input type="checkbox" name="ruby" value="Round" class="text_B"/>

Actually I already caught that yesterday, and changed it. I figured it out though... not sure why it works though. I got rid of the code where it connects to the database and added an include function to execute the database connection from a seperate file. Thanks guys for all you help, I would have gotten this far without your support. But can some tell me why it works that way and not when it's coded onto the results page?

you know what know it only works if i check all the boxes, otherwise if i check only one it returns all fields

Actually I got it so that it doesn't do that anymore. I am now able to search it. I just need to figure out how to filter it. Right now for example, if i search for a round diamond with a AGS lab i get all records that have "Round" and all records that have AGS. After I figure that out, I need to apply it so it would filter through range of prices, carats, etc.

right now query is:

$query = "select * from diamond where shape like '$searchshape' or shape like '$searchshapetwo' or shape like '$searchshapethree' or shape like '$searchshapefour' or shape like '$searchshapefive' or shape like '$searchshapesix' or shape like '$searchshapeseven' or shape like '$searchshapeeight' or shape like '$searchshapenine' or shape like '$searchshapeten' or shape like '$searchshapeeleven' or lab like '$searchlab' or lab like '$searchlabtwo' or lab like '$searchlabthree' or lab like '$searchlabfour' or lab like '$searchlabfive' or lab like '$searchlabsix' or color like '$colorfrom' or color like '$colorto' or clarity like '$searchclarityfrom' or clarity like '$searchclarityto' or price between '$searchpricefrom' and '$searchpriceto'

Umm.. Is this how you want your script to work ? The user can select different checkboxes and enter a search string next to the checkbox. Then when he clicks on search, It should return all the matching records ?

It would have been easier if I just used an array for my checkboxes huh?

Yep. An array of checkboxes. When the user submits the form, only those checkboxes which were selected, will be posted. You can then build your query depending on the checkboxes the user selects. :)

This question has already been answered. Start a new discussion instead.