NEED HELP with search and results forms

Question

brow51 0 Newbie Poster

16 Years Ago

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();
?>

php

3 Contributors
20 Replies
86 Views
4 Days Discussion Span
Latest Post 16 Years Ago Latest Post by brow51

archangel_025 0 Newbie Poster

16 Years Ago

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

archangel_025 0 Newbie Poster

16 Years Ago

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

archangel_025 0 Newbie Poster

16 Years Ago

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

archangel_025 0 Newbie Poster

16 Years Ago

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

archangel_025 0 Newbie Poster

16 Years Ago

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...

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

brow51 0 Newbie Poster · Answer 1 · 2008-05-07T12:47:25+00:00

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.

brow51 0 Newbie Poster · Answer 2 · 2008-05-07T14:28:10+00:00

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

brow51 0 Newbie Poster · Answer 3 · 2008-05-08T13:41:45+00:00

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?

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 4 · 2008-05-08T14:29:30+00:00

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 !

brow51 0 Newbie Poster · Answer 5 · 2008-05-08T14:31:47+00:00

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

brow51 0 Newbie Poster · Answer 6 · 2008-05-09T10:54:44+00:00

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..

archangel_025 0 Newbie Poster · Answer 7 · 2008-05-09T11:19:31+00:00

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"/>

brow51 0 Newbie Poster · Answer 8 · 2008-05-09T11:32:59+00:00

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?

brow51 0 Newbie Poster · Answer 9 · 2008-05-09T11:50:48+00:00

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

archangel_025 0 Newbie Poster · Answer 10 · 2008-05-09T14:10:09+00:00

um..can you post the codes, I guess there might be something in your query,

brow51 0 Newbie Poster · Answer 11 · 2008-05-09T14:34:45+00:00

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'

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 12 · 2008-05-09T14:45:34+00:00

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 ?

brow51 0 Newbie Poster · Answer 13 · 2008-05-10T13:08:48+00:00

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

nav33n 472 Purple hazed! Team Colleague Featured Poster · Answer 14 · 2008-05-11T06:40:53+00:00

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. :)

brow51 0 Newbie Poster · Answer 15 · 2008-05-12T02:05:19+00:00

I got it to work now. thans guys for your help.