php form to do multiple searches mysql

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
publicx2001
Forum Newbie
Posts: 2
Joined: Mon Sep 16, 2002 12:44 pm

php form to do multiple searches mysql

Post 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:
os2al
Forum Newbie
Posts: 14
Joined: Tue Jun 25, 2002 3:26 pm

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

Post 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
publicx2001
Forum Newbie
Posts: 2
Joined: Mon Sep 16, 2002 12:44 pm

Post by publicx2001 »

Al-
Thanks a million. I will give it a whirl in the morning. I really appreciate your helpfulness.

Thanks,

Erik
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post 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;
os2al
Forum Newbie
Posts: 14
Joined: Tue Jun 25, 2002 3:26 pm

Modifying the search from a checkbox ... How?

Post 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:
User avatar
Takuma
Forum Regular
Posts: 931
Joined: Sun Aug 04, 2002 10:24 am
Location: UK
Contact:

Post by Takuma »

Can I see the HTML code please... :D
os2al
Forum Newbie
Posts: 14
Joined: Tue Jun 25, 2002 3:26 pm

Code above

Post 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
Coco
Forum Contributor
Posts: 339
Joined: Sat Sep 07, 2002 5:28 am
Location: Leeds, UK
Contact:

Post 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
os2al
Forum Newbie
Posts: 14
Joined: Tue Jun 25, 2002 3:26 pm

One more question, if you will ...

Post 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
arossphoto
Forum Newbie
Posts: 1
Joined: Mon Sep 30, 2002 10:05 am

Post 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
Post Reply