PHP MySQL search problems

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
apervizi
Forum Newbie
Posts: 8
Joined: Thu Jul 24, 2008 2:24 pm

PHP MySQL search problems

Post by apervizi »

My HTML search form includes a text box (the name of the company is typed) and a drop down menu (where a US state is chosen).

My MySQL database has 11 fields, but when the search is executed I only want to display 7 fields (fld_companyName, fld_address, fld_city, fld_zip, fld_warnState, fld_numEmployees and fld_termDate).

The following code also includes the php results script.

When I hit the search button, nothing is being displayed.

Can somebody please take a look at this code.

Thanks

Alim Pervizi

Code: Select all

 
<form name="search" method="post" action="<?php echo $_SERVER['PHP_SELF'] ?>">
Search for the company: <input type="text" name="find" /> in
<select name="WarnState"> // Table Field
<option value="" selected="selected">Choose a State</option>
<option value="AL">Alabama</option>
<option value="AK">Alaska</option>
<option value="AZ">Arizona</option>
<option value="AR">Arkansas</option>
<option value="CA">California</option>
<option value="CO">Colorado</option>
<option value="CT">Connecticut</option>
<option value="DE">Delaware</option>
<option value="DC">District Of Columbia</option>
<option value="FL">Florida</option>
<option value="GA">Georgia</option>
<option value="HI">Hawaii</option>
<option value="ID">Idaho</option>
<option value="IL">Illinois</option>
<option value="IN">Indiana</option>
<option value="IA">Iowa</option>
<option value="KS">Kansas</option>
<option value="KY">Kentucky</option>
<option value="LA">Louisiana</option>
<option value="ME">Maine</option>
<option value="MD">Maryland</option>
<option value="MA">Massachusetts</option>
<option value="MI">Michigan</option>
<option value="MN">Minnesota</option>
<option value="MS">Mississippi</option>
<option value="MO">Missouri</option>
<option value="MT">Montana</option>
<option value="NE">Nebraska</option>
<option value="NV">Nevada</option>
<option value="NH">New Hampshire</option>
<option value="NJ">New Jersey</option>
<option value="NM">New Mexico</option>
<option value="NY">New York</option>
<option value="NC">North Carolina</option>
<option value="ND">North Dakota</option>
<option value="OH">Ohio</option>
<option value="OK">Oklahoma</option>
<option value="OR">Oregon</option>
<option value="PA">Pennsylvania</option>
<option value="RI">Rhode Island</option>
<option value="SC">South Carolina</option>
<option value="SD">South Dakota</option>
<option value="TN">Tennessee</option>
<option value="TX">Texas</option>
<option value="UT">Utah</option>
<option value="VT">Vermont</option>
<option value="VA">Virginia</option>
<option value="WA">Washington</option>
<option value="WV">West Virginia</option>
<option value="WI">Wisconsin</option>
<option value="WY">Wyoming</option>
</select>
<input type="hidden" name="searching" value="yes" />
<input type="submit" value="search" name="search" />
</form>
 
<?php
 
//Displayed only when the form has been submitted
if ($searching =="yes")
{
 
echo "<h2>Results</h2><p>";
 
//If no search term is typed an error is displayed
if ($find == "")
{
echo "<p>You forgot to enter a search term";
exit;
}
 
$hostname = "localhost";
$username = "dbuser";
$password = "password";
$usertable = "dbtable";
$dbName = "databasename";
 
if (!mysql_connect($hostname, $username, $password))
die("Can't connect to database");
 
if (!mysql_select_db($dbName))
die("Can't select database");
 
// Perform a bit of filtering
$find = strtoupper($find); // Change characters to UPPER case
$find = strip_tags($find); // Removes out any code that may have been entered previously
$find = trim ($find); // Removes out all the whitespace
 
//Search based on the search term, in the field the user specified
$data = mysql_query("SELECT * FROM {$usertable}");
 
//Display the results
while($result = mysql_fetch_array( $data ))
{
echo $result['fld_companyName'];
echo " ";
echo $result['fld_address'];
echo "<br>";
echo $result['fld_city'];
echo "<br>";
echo $result['fld_zip'];
echo "<br>";
echo $result['fld_warnState'];
echo "<br>";
echo $result['fld_numEmployees'];
echo "<br>";
echo $result['fld_termDate'];
echo "<br>";
echo "<br>";
 
}
 
//Counting the number or results - If there is no results, a message is displayed
$anymatches=mysql_num_rows($data);
if ($anymatches == 0)
{
echo "Sorry, but we can not find an entry to match your query<br><br>";
}
 
//And we remind them what they searched for
echo "<b>Searched For:</b> " .$find;
}
?>
 
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: PHP MySQL search problems

Post by califdon »

Turn on PHP error reporting.

Code: Select all

ini_set("display_errors","2");
ERROR_REPORTING(E_ALL);
When a PHP script results in a totally blank screen (check the Source Page view in your browser), it is an indication that there is a fatal PHP syntax error somewhere in the script that prevents the web server from completing the PHP preprocessing.
apervizi
Forum Newbie
Posts: 8
Joined: Thu Jul 24, 2008 2:24 pm

Re: PHP MySQL search problems

Post by apervizi »

Thank you for responding to my post.

Very handy code.

This is the error code

Notice: Undefined variable: searching in /home/layoffse/public_html/test/search.php on line 65
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: PHP MySQL search problems

Post by califdon »

apervizi wrote:Thank you for responding to my post.

Very handy code.

This is the error code

Notice: Undefined variable: searching in /home/layoffse/public_html/test/search.php on line 65
So it should be simple to fix. Look at line 65 and figure out what variable you have that hasn't been defined earlier.
apervizi
Forum Newbie
Posts: 8
Joined: Thu Jul 24, 2008 2:24 pm

Re: PHP MySQL search problems

Post by apervizi »

Now when I hit the search button, all the table records get displayed.

How can I filter the results based on what gets typed (company name) and what gets selected from the drop down menu (US state)?

Thanks
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: PHP MySQL search problems

Post by califdon »

apervizi wrote:Now when I hit the search button, all the table records get displayed.
You were expecting something different? [Sorry, I'm just teasing you a little, I do realize that you're just learning. We've all been there.]
How can I filter the results based on what gets typed (company name) and what gets selected from the drop down menu (US state)?
Look at line 95 (or close to there) where you form your SQL string:

Code: Select all

$data = mysql_query("SELECT * FROM {$usertable}");
That translates to "select all the columns from all the rows of some table". So if you want to select only certain rows, you have a WHERE clause that specifies which records you want. You would collect the values for your variables from your form, then the SQL string would look more like this:

Code: Select all

$data = mysql_query("SELECT * FROM {$usertable} WHERE `companyName`='$coname' AND `state`='$state'");
You should notice that there are 3 different kinds of punctuation there, single quote ('), double quote (") and back-tick (`). You have to use the right ones in the right places! Back-ticks are only used around the names of tables or column names. Obviously you will have to use the correct field names and variable names for your script.
apervizi
Forum Newbie
Posts: 8
Joined: Thu Jul 24, 2008 2:24 pm

Re: PHP MySQL search problems

Post by apervizi »

Changing the code to what you suggested was a total success!

Thank you very much.

What happens if the user types in partial name of the company?
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: PHP MySQL search problems

Post by califdon »

apervizi wrote:Changing the code to what you suggested was a total success!

Thank you very much.
You're welcome and I'm glad you succeeded.
What happens if the user types in partial name of the company?
Nothing, unless you use a different comparison operator. The equal sign (=) makes a strict comparison. But instead of the equal sign you can use the LIKE operator, which works with wild cards represented by percent signs (%). For example, you could have:

Code: Select all

$data = mysql_query("SELECT * FROM {$usertable} WHERE `companyName` LIKE '%$coname%' AND `state`='$state'");
and if the user inputs "nov", it might return 103 records that contain the letters "nov" in the company name field.
apervizi
Forum Newbie
Posts: 8
Joined: Thu Jul 24, 2008 2:24 pm

Re: PHP MySQL search problems

Post by apervizi »

You have been instrumental in making this project a success. Thank you for sharing your knowledge.

Q1. Let just say that if the user does not see the company listed in the database, can a php form be added (not as a separate page, but in the same search + results page) after the line:
echo "Sorry, but we can not find an entry to match your query".

I have a tested php form ready to be implemented.

Q2. How can I validate that all the text boxes have been filled in the form?

Thanks
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Re: PHP MySQL search problems

Post by califdon »

apervizi wrote:You have been instrumental in making this project a success. Thank you for sharing your knowledge.

Q1. Let just say that if the user does not see the company listed in the database, can a php form be added (not as a separate page, but in the same search + results page) after the line:
echo "Sorry, but we can not find an entry to match your query".

I have a tested php form ready to be implemented.
I think you are saying that you are using one script to create several pages. So when a search produces no result rows, you need to branch on an IF conditional statement to whatever you want to be displayed instead of the results.
Q2. How can I validate that all the text boxes have been filled in the form?

Thanks
You can test for values in all of the Input boxes, using Javascript. Here's a simple explanation of how to do it: http://www.w3schools.com/js/js_form_validation.asp.
Post Reply