[Resolved] Search Results Display

usernot

I have two parts to my question...

Part I:
I have a very basic search page (search.php) that pulls information from a MySQL database and displays it (results.php). If I enter something to search for...it works just fine and only displays the information requested. However, the problem arises if someone just hits "SEARCH" on the search without entering any information as then the results.php page shows EVERY entry in my database. Any ideas to fix that?

Here is what I am using...

Apache/1.3.31 (Win32)
PHP/5.0.0RC3
MySQL/3.23.38

And here is the code...

<?
mysql_connect("localhost","username","password");
mysql_select_db("database");

$search=$_GET["search"];
$result = mysql_query("SELECT * FROM table WHERE business LIKE '%$search%'");

while($r=mysql_fetch_array($result))
{

$business=$r["business"];
$address=$r["address"];
$city=$r["city"];
$state=$r["state"];
$zip=$r["zip"];

echo "$business Address: $address, $city, $state $zip ";
}
?>

Part II:
Right now, if there is a business without an address in my database, the results will still show "Address:" and a blank space where the address information should be. If there in no database information for address, is there a way to remove any mention of it in the search.php page?

Thanks in advance!

dfr574

A small change in your query and a if statement should do it.

It might need to be tweaked.

<? 
mysql_connect("localhost","username","password"); 
mysql_select_db("database"); 

$search=$_GET["search"];


$result = mysql_query("SELECT * FROM table WHERE business LIKE '%$search%' and address <> ''"); 

if(!isset($search)){
echo "Please supply something to search for.";
}
else{
while($r=mysql_fetch_array($result)) 
{ 

$business=$r["business"]; 
$address=$r["address"]; 
$city=$r["city"]; 
$state=$r["state"]; 
$zip=$r["zip"]; 

echo "$business<br> <u>Address</u>: $address, $city, $state $zip<br><br>"; 

} 
}
?>

usernot

dfr574,

I tried the suggestions you mentioned and had the following problems. The [ if(!isset($search)){ ] command I entered in the script worked fine if I opened up just the results.php page as it would prompt "Please supply something to search for" which before it would show the entire database. However, if I opened the search.php (where my search form is) and clicked SEARCH without entering anything in the text box, it still showed all the database entries on the results.php page. Any ideas on that?

Also, the second part of my original question...when I entered the new line in the script [ $result = mysql_query("SELECT * FROM table WHERE business LIKE '%$search%' and address <> ''"); ] the results.php page ONLY shows entries that have an address and doesn't show the other entries in the database that ONLY have a business name. What I would like to do is display every entry but if it doesn't have an address, it won't show the "Address: " text in the results.php page as the the information is blank in the database.

Example...

Database would look something like this:
BUSINESS ADDRESS
Ace Hardware 1111 N Street
Ace's Toys
Act Crafts 2222 S Street

With the original script, I would enter "AC" as a business name keyword on the search.php and and it would give me the results below on the results.php page as all entries have "AC" in the business name:

Ace Hardware
Address: 1111 N Street

Ace's Toys
Address:

Act Crafts
Address: 2222 S Street

What I would like to do is get a results.php page looks like this:

Ace Hardware
Address: 1111 N Street

Ace's Toys

Act Crafts
Address: 2222 S Street

Since there is not entry for address in the databae for Ace's Toys, it wouldn't show it at all on the results.php page. Any help is appreciated!

dfr574

I'm sorry I didn't realize you were working with 2 different scripts.

Something like this should work.
Another if statement will solve your second problem.

search.php

<?php
print "<html>\n";
print "<form method=\"post\" action=\"results.php\">\n";
print "<input type=\"text\" name=\"search\">\n";
print "<input type=\"submit\">\n";
print "</form>\n";
print "</html>\n";
?>

results.php

<? 
mysql_connect("localhost","username","password"); 
mysql_select_db("database"); 

$search=$_POST["search"];


$result = mysql_query("SELECT * FROM table WHERE business LIKE '%$search%'"); 

if(!isset($search)){
echo "Please supply something to search for.";
}
else{
while($r=mysql_fetch_array($result)) 
{ 

$business=$r["business"]; 
$address=$r["address"]; 
$city=$r["city"]; 
$state=$r["state"]; 
$zip=$r["zip"]; 

if(!isset($address) || $address == ""){ //This might do it
echo "$business<br><br\n";
}
else{

echo "$business<br> <u>Address</u>: $address, $city, $state $zip<br><br>"; 
}
} 
}
?>

usernot

Part I
OK...I used the script you just provided and when on the search.php I click "Submit Query" without entering anything, i still get the results.php with the entire database listed instead of "Please supply something to search for."

Part II
Here is what happens:

Database would look something like this:
BUSINESS ADDRESS
Ace Hardware 1111 N Street
Ace's Toys
Act Crafts 2222 S Street

Here are the results I get with the new script you provided:

Ace Hardware
Address: 1111 N Street

Ace's Toys

Address: 2222 S Street

So the script worked correctly in that it didn't print "Address: " for Ace's Toys because that field is empty but then it left out the business name for the next entry in the database.

I think I should change the code a bit too...
Change this:

echo "$business Address: $address, $city, $state $zip ";

To This:

echo "$business Address: $address City: $city State: $state Zip: $zip ";

So...ultimately what I would like is if there is no information in the database for a particular field it wouldn't show that particular field on the results.php page.