Page 1 of 1

php form to do multiple searches mysql

Posted: Mon Sep 16, 2002 12:44 pm
by publicx2001
Hello,

Im new to this and I need a bit of help. Maybe a lot....

I have a database with one table having 25,000 records and 9 fields for each record.

I want to make a form using php on my site to allow users to search my database.

For instance - Allow users to see results of all records when searching by a particular state, country, or city...

Or show a record when searching by an exact name.

Any help is most appreciated.

THX :roll:

Code for basic search, not exactly what you want but ...

Posted: Mon Sep 16, 2002 9:02 pm
by os2al
I'm rather new at this myself, but I have gotten some good help here, so let me try to offer you some help.

I have a working search below for you that searches a MySQL database. (I don't think you said what database you are using, but I'm guessing it's MySQL.) Of course, the answer is in two parts. Below, you'll find code for a PHP search of a MySQL database. It's pretty easy to follow, just fill in the places where you have to by following the name, like YourDataBaseNameHere or YourTableNameHere.

This is the PHP side of things that searches and retrieves the data from the HTML form, and it includes a small bit of HTML code, including some error checks. Call it "Search.php":

- - -

<html>
<head>
<title>Search Results for MySQL Database</title>
</head>
<body>

<h2>Search Result</h2>

<?
if (!$searchtype || !$searchterm)
{
echo "You have not entered search details. Please go back and try again.";
exit;
}

$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);

@ $db = mysql_pconnect("localhost", "YourDatabaseNameHere", "YourPassword");

if (!$db)
{
echo "Error: Could not connect to database. Please try again.";
exit;
}

mysql_select_db("YourDataBaseNameHere");
$query = "select * from YourTableNameHere where ".$searchtype." like '%".$searchterm."%'";
$result = mysql_query($query);

$num_results = mysql_num_rows($result);

echo "<p>Number found: ".$num_results."</p>";

for ($i=0; $i <$num_results; $i++)
{
$row = mysql_fetch_array($result);
echo "<p><strong>".($i+1).". OrganizationName: ";
echo stripslashes($row["OrganizationNameField"]);
echo "<br></strong><strong>-- Address: </strong>";
echo stripslashes($row["AddressField"]);
echo "<br><strong>-- City: </strong>";
echo stripslashes($row["CityField"]);
echo "<br><strong>-- State: </strong>";
echo stripslashes($row["StateField"]);
echo "<br><strong>-- Zip: </strong>";
echo stripslashes($row["ZipField"]);
echo "<br><strong>-- Country: </strong>";
echo stripslashes($row["CountryField"]);
echo "<br><strong>-- Phone: </strong>";
echo stripslashes($row["PhoneField"]);
echo "<br><strong>-- Fax: </strong>";
echo stripslashes($row["FaxField"]);
echo "<br><strong>-- Email: </strong>";
echo stripslashes($row["EmailField"]);
echo "</p>";

}

?>
<P>

</p>
<br>

<h3><strong>Some Message Here At Bottom of PHP Search Page</strong></h3>
<br>
</body>
</html>

- - -

Then you need the HTML form, where one inputs a name to search for. Something like this:

<HTML>
<HEAD>
<TITLE>HTML Form for Search Page</TITLE>
</HEAD>
<BODY>

<br>
<form action="search.php" method="post">
<b>Choose a Search Category From the Pull-Down Menu:</b></font><br>
<br>
<select name="searchtype">
<option value="OrganizationNameField">Organization Name
<option value="CityField">City
<option value="CountryField">County
</select>
<br>
<br>
Enter the Search Term in the Window<br>
<input name="searchterm" type=text>
<br>
<input type=submit value="Search">
</form><br>

<br>
</font>

</BODY>
</HTML>

- - -
I hope I got it all here. Of course, you can add whatever fields you want to the pull-down menu. I hope this helps. Best of luck.

Al

Posted: Mon Sep 16, 2002 9:27 pm
by publicx2001
Al-
Thanks a million. I will give it a whirl in the morning. I really appreciate your helpfulness.

Thanks,

Erik

Posted: Tue Sep 17, 2002 1:10 am
by Takuma
You could change the code so it looks nicer:-

Code: Select all

&lt;?php
&lt;html&gt; 
&lt;head&gt; 
&lt;title&gt;Search Results for MySQL Database&lt;/title&gt; 
&lt;/head&gt; 
&lt;body&gt; 

&lt;h2&gt;Search Result&lt;/h2&gt; 

&lt;? 
if (strlen(trim($searchtype)) == 0 || strlen(trim($searchterm) == 0) {
  echo "You have not entered search details. Please go back and try again.";
  exit;
}

$searchtype = addslashes($searchtype);
$searchterm = addslashes($searchterm);

mysql_connect("localhost", "YourDatabaseNameHere", "YourPassword") or die("Error: Could not connect to database. Please try again.");
mysql_select_db("YourDataBaseNameHere");

$query = "SELECT * FROM YourTableNameHere WHERE $searchtype LIKE '%$searchterm%'";
$result = mysql_query($query) or die("Error: Failed to execute query.");
$num_results = mysql_num_rows($result);

echo "&lt;p&gt;Number found: $num_results&lt;/p&gt;";

for ($i=0; $i &lt;$num_results; $i++) {
  $row = mysql_fetch_array($result);
  echo "&lt;p&gt;&lt;strong&gt;".($i+1).". OrganizationName: "; 
  echo stripslashes($row&#1111;"OrganizationNameField"]); 
  echo "&lt;br&gt;&lt;/strong&gt;&lt;strong&gt;-- Address: &lt;/strong&gt;"; 
  echo stripslashes($row&#1111;"AddressField"]); 
  echo "&lt;br&gt;&lt;strong&gt;-- City: &lt;/strong&gt;"; 
  echo stripslashes($row&#1111;"CityField"]); 
  echo "&lt;br&gt;&lt;strong&gt;-- State: &lt;/strong&gt;"; 
  echo stripslashes($row&#1111;"StateField"]); 
  echo "&lt;br&gt;&lt;strong&gt;-- Zip: &lt;/strong&gt;"; 
  echo stripslashes($row&#1111;"ZipField"]); 
  echo "&lt;br&gt;&lt;strong&gt;-- Country: &lt;/strong&gt;"; 
  echo stripslashes($row&#1111;"CountryField"]); 
  echo "&lt;br&gt;&lt;strong&gt;-- Phone: &lt;/strong&gt;"; 
  echo stripslashes($row&#1111;"PhoneField"]); 
  echo "&lt;br&gt;&lt;strong&gt;-- Fax: &lt;/strong&gt;"; 
  echo stripslashes($row&#1111;"FaxField"]); 
  echo "&lt;br&gt;&lt;strong&gt;-- Email: &lt;/strong&gt;"; 
  echo stripslashes($row&#1111;"EmailField"]); 
  echo "&lt;/p&gt;"; 
} 

?&gt; 
&lt;P&gt; 

&lt;/p&gt; 
&lt;br&gt; 

&lt;h3&gt;&lt;strong&gt;Some Message Here At Bottom of PHP Search Page&lt;/strong&gt;&lt;/h3&gt; 
&lt;br&gt; 
&lt;/body&gt; 
&lt;/html&gt; 
?&gt;

Modifying the search from a checkbox ... How?

Posted: Tue Sep 24, 2002 6:24 pm
by os2al
Takuma or Volka,

Would you be able to tell me how I could add a checkbox on my form side, allowing choice of a "Country" or some other field, to modify the search using the code above?

I'm trying to put on a checkbox to limit searches, but I'm not sure how I would get that to function using the code I have, what I would add to it on the PHP side, or actually even on the form side. Any help appreciated.

Al :roll:

Posted: Wed Sep 25, 2002 1:40 am
by Takuma
Can I see the HTML code please... :D

Code above

Posted: Wed Sep 25, 2002 2:26 pm
by os2al
I'm talking about the code that is above. It works by a pull-down menu to select a topic and a word is input into a search form. I'm wondering how I might be able to have it take input from a checkbox that limits the search, either in addition or in place of the pull-down menu.

To wit, you check a box for, say, "Country," and then type a name. It returns the list of names within that country.

Clear? Yes, no?

Al

Posted: Thu Sep 26, 2002 9:24 am
by Coco

Code: Select all

html:

<input type="radio" name="searchtype" value="orgname">Org Name<bR>
<input type="radio" name="searchtype" value="City">City<bR>
<input type="radio" name="searchtype" value="Country">Country<bR>
result is submitted and retrieved the usual way

One more question, if you will ...

Posted: Sun Sep 29, 2002 12:44 pm
by os2al
Thanks again. I have one more question, if you don't mind.

When I try to get input from a checkbox into a MySQL database, it doesn't seem to accept it. I'm not positive the problem is with the checkboxes, but I suspect that it might be.

My SQL translator, which is great, creates a tinyint(3) field in MySQL from an MS Access checkbox. I have a bunch of separate checkboxes taking input. What gets put inside this tinyint(3) field, 0 or 1?

I created the form-side HTML for each checkbox, but is there something I'm not getting here? The field name is Age in MySQL.

<input type="checkbox" value="Age" name="Age">Age<br>

Any help, hint appreciated.

Al

Posted: Mon Sep 30, 2002 10:05 am
by arossphoto
Hi,

I have a search page that basically does the same thing as the ones shown above, but I would like users to be able to select multiple categories from a menu when they search.

Is there any way to do this using the above code as an example?

Thanks,

Andrew