homepage Welcome to WebmasterWorld Guest from 54.161.185.244
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
MYSQL Advance Dual Search (error)
MySQL PHP Dual Search Database
going2college



 
Msg#: 4151761 posted 7:43 pm on Jun 12, 2010 (gmt 0)

I am trying to have an advance search where people can type in their zipcode and then select a type of business. The results display a defined search based upon the prefrences that were selected.

I am having a problem with this script working. It keeps displaying "no results for zipcode """ even though the information is in the database. If anyone could please point me into the right direction. I have spent over 40 hours trying to see whats wrong. I am new to this.



<form name="Selections" method="GET" action="<?=$PHP_SELF?>" >
&nbsp; &nbsp;
Zipcode: <input type="text" name="Search">
<select name="Variety" size="1">
<option value="activities">Activities</option>
<option value="cafes and lounges">Cafes and Lounges</option>
<option value="restaurants">Hottest Restaurants</option>
<option value="fastfood">Quick Fastfood</option>
<option value="live music">Live Music</option>
<option value="local offers">Local Offers</option>
<option value="bucketlist">The Bucket List</option>
</select>
<input type="submit" name="submit" value="Search">
</form>
</div>

<div id="search_results">
<?php

include_once ('database_connection.php');

if(isset($_GET['Search'])){
$zipcode = trim($_GET['Search']) ;
$zipcode = mysqli_real_escape_string($dbc, $zipcode);
if(isset($_GET['Variety'])){
$variety = trim($_GET['Variety']) ;
$variety = mysqli_real_escape_string($dbc, $variety);


$query = "SELECT discounts.zipcode,discounts.type FROM discounts WHERE discounts.zipcode='%$zipcode%' AND discounts.type='%$variety%'";

//echo $query;
$result = mysqli_query($dbc,$query);
if($result){
if(mysqli_affected_rows($dbc)!=0){
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
echo '<div id="style"><p> <b> <small> Style: </small>'.$row['style'].
'</div></b></p> <div id="title"><small> Name: </small>'.$row['title'].
'</div><p> <div id="address"> <small> Address: </small>'.$row['address'].
'</div></b><hr>';
}
}else {
echo 'No Results for Zipcode "'.$_GET['Search'].'"';
}

}
}else {
echo 'Parameter Missing';
}
}
?>

 

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4151761 posted 9:53 pm on Jun 12, 2010 (gmt 0)

Welcome aboard going2college, try

discounts.zipcode='$zipcode' AND discounts.type like '%$variety%'";

or

discounts.zipcode like '%$zipcode%' AND discounts.type like '%$variety%'";

The wildcard doesn't work with equality operators. The first would be better, if it is what you want (exact match.)

going2college



 
Msg#: 4151761 posted 2:48 am on Jun 13, 2010 (gmt 0)

Thanks for the welcoming friend!



$query = "SELECT * FROM discounts.zipcode='$zipcode' AND discounts.type like '%$variety%'";




It's still not working. With the modified Query the "results" doesn't display anymore. I tried both of the suggestions. I appreciate your help. Anymore ideas what I might could possibly try?

My goal is an exact match too.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4151761 posted 7:22 pm on Jun 13, 2010 (gmt 0)

Sure, but it's not a database issue. :-) You made me look closer.

mysqli_affected_rows($dbc)!=0


From the the manual [php.net],

Gets the number of affected rows in a previous MySQL operation


You're not affecting any rows in a select. Update, delete, or insert affects rows. Remove that entire "if".

But now you need something in the "else," so try


// Put this somewhere near the top to quiet undefined
// variable errors on concatenation
$row_result=null;
//
$result = mysqli_query($dbc,$query) or die(mysqli_error()); // see note
if($result){
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
$row_result .= '<div id="style"><p> <b> <small> Style: </small>'.$row['style'].
'</div></b></p> <div id="title"><small> Name: </small>'.$row['title'].
'</div><p> <div id="address"> <small> Address: </small>'.$row['address'].
'</div></b><hr>';
}
if ($row_result) { echo $row_result; }
else { echo 'No Results for Zipcode "'.$_GET['Search'].'"'; }
}


Note: You should have gotten a result, note the die, it will reveal if there's an error with the select statement.

Since I looked, a few PHP-ish things, one more database thing:

<form name="Selections" method="GET" action="<?=$PHP_SELF?>" >

Google for PHP_SELF vulnerabilities to see why not to use the global PHP_SELF. Hard code it or put it in a constant or variable.

While get "works," you'll probably see an ugly long query string in your address bar as a result of the get method. You can leave the query string behind by using post. All you'll have to do is change all instances of $_GET to $_POST:

$myscript = 'some-script.php';
<form name="Selections" method="post" action="<?php=$myscript?>">

or

define('MYSCRIPT','some-script.php');
<form name="Selections" method="post" action="<?php=MYSCRIPT?>" >

<? is deprecated, use ?php

The last is one of expandability and speed, but it will probably work without doing this, just not as fast.

You have a textual select list, in HTML, which you are pairing up with a database value. While it will likely work, it may grow to be hard to maintain.

What you **should** do, is have the option list generated directly from the database.

id|title
1|Activities
2|Cafes and Lounges
3|Hottest Restaurants
4|Quick Fastfood
5|Live Music
6|Local Offers
7|The Bucket List

So now your select list will have values like so

....
<option value="3">Hottest Restaurants</option>
....

And your database will contain a numeric value, which is easier to cleanse on input,

if (is_numeric($_POST['Variety']) and ($_POST['Variety'] > 0)) {
$variety = $_POST['Variety'];
}
else { die("Invalid input in Variety"); }

And will make your select easier and faster. Numeric searches are always faster.

.... discounts.zipcode='$zipcode' AND discounts.type='$variety'";

Should you decide to stick with the textual values, and want an exact match on what's in the select, either make sure your select list **is** an exact match (you have lower case in your input values) or stick with the like operator, which is case-insensitive. So if you're sure "cafes and lounges" is the exact value in the database, the previous where statement with simple equality operators will work.

What you need to consider with "like" and wild cards on either end,

input: "widgets"

database values:

green widgets
widgetometer
widgets galore

will all match on

field like '%widget%'

Regexps will work too, and could be more more accurate, be may be even slower than like.

The previous reveals another issue with matching on text values (when you probably don't need to.) This looking like what I think it is, some kind of city or venue guide, I suggest option a, put those Varieties in another table so you can use numeric values, as speed is going to become very important if it grows at all.

going2college



 
Msg#: 4151761 posted 10:58 pm on Jun 13, 2010 (gmt 0)

I am trying so hard to figure this out. If you do not mind could you please modify the script below to exactly what you are describing. I am better at visually seeing everything you are talking about. I appreciate all of your help seriously. I have changed the database and everything to what you are instructing.

*Can you also show me how you would change the "action" in the form to global please.

The name of the page is "index.php"


I am very very appreciative of what your doing. Thank you so much! I have gone to so many people for help and have been turned away
countless times.

--------------------------------------------------------------------




<form name="Selections" method="POST" action="<?=$PHP_SELF?>" >
&nbsp; &nbsp;
Zipcode: <input type="text" name="Search">
<select name="Variety" size="1">
<option value="1">Activities</option>
<option value="2">Cafes and Lounges</option>
<option value="3">Hottest Restaurants</option>
<option value="4">Quick Fastfood</option>
<option value="5">Live Music</option>
<option value="6">Local Offers</option>
<option value="7">The Bucket List</option>
</select>
<input type="submit" name="submit" value="Search">
</form>
</div>

<div id="search_results">
<?php

include_once ('database_connection.php');

if(isset($_POST['Search'])){
$zipcode = trim($_POST['Search']) ;
$zipcode = mysqli_real_escape_string($dbc, $zipcode);
if(isset($_POST['Variety'])){
$variety = trim($_POST['Variety']) ;
$variety = mysqli_real_escape_string($dbc, $variety);


$query = "SELECT * FROM discounts WHERE zipcode='%$zipcode%' AND type='%$variety%'";

// Put this somewhere near the top to quiet undefined
// variable errors on concatenation
$row_result=null;
//
$result = mysqli_query($dbc,$query) or die(mysqli_error()); // see note
if($result){
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
$row_result .= '<div id="style"><p> <b> <small> Style: </small>'.$row['style'].
'</div></b></p> <div id="title"><small> Name: </small>'.$row['title'].
'</div><p> <div id="address"> <small> Address: </small>'.$row['address'].
'</div></b><hr>';
}
if ($row_result) { echo $row_result; }
else { echo 'No Results for Zipcode "'.$_POST['Search'].'"'; }
}

}
}else {
echo 'Parameter Missing';
}

?>

going2college



 
Msg#: 4151761 posted 12:06 am on Jun 14, 2010 (gmt 0)

The script will run, but it keeps displaying "no results for zipcode" when there is. Why is that?


*Script Revised a little bit toward what you asked*

-------------------------------------------------------------------------

<?php include_once"flash_header.php"; ?>
</div>
<div id="content_slider">
<?php include_once"slider_content.php"; ?>
</div>
<div id="search_discounts">
<form name="Selections" method="POST" action="<?=$PHP_SELF?>" >
&nbsp; &nbsp;
Zipcode: <input type="text" name="Search">
<select name="Variety" size="1">
<option value="1">Activities</option>
<option value="2">Cafes and Lounges</option>
<option value="3">Hottest Restaurants</option>
<option value="4">Quick Fastfood</option>
<option value="5">Live Music</option>
<option value="6">Local Offers</option>
<option value="7">The Bucket List</option>
</select>
<input type="submit" name="submit" value="Search">
</form>
</div>

<div id="search_results">
<?php

include_once ('database_connection.php');

if(isset($_POST['Search'])){
$zipcode = trim($_POST['Search']) ;
$zipcode = mysqli_real_escape_string($dbc, $zipcode);
if (is_numeric($_POST['Variety']) and ($_POST['Variety'] > 0)) {
$variety = $_POST['Variety'];
}
else { die("Invalid input in Variety"); }


$query = "SELECT * FROM discounts WHERE zipcode='%$zipcode%' AND type='%$variety%'";

// Put this somewhere near the top to quiet undefined
// variable errors on concatenation
$row_result=null;
//
$result = mysqli_query($dbc,$query) or die(mysqli_error()); // see note
if($result){
while($row = mysqli_fetch_array($result,MYSQLI_ASSOC)){
$row_result .= '<div id="style"><p> <b> <small> Style: </small>'.$row['style'].
'</div></b></p> <div id="title"><small> Name: </small>'.$row['title'].
'</div><p> <div id="address"> <small> Address: </small>'.$row['address'].
'</div></b><hr>';
}
if ($row_result) { echo $row_result; }
else { echo 'No Results for Zipcode "'.$_POST['Search'].'"'; }
}

}
else {
echo 'Parameter Missing';
}

?>

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4151761 posted 4:10 pm on Jun 14, 2010 (gmt 0)

The script will run, but it keeps displaying "no results for zipcode" when there is. Why is that?


It looks like you almost have it, **if** you've verified your data DOES match what's in your select list.

See post # 2:

$query = "SELECT * FROM discounts WHERE zipcode='%$zipcode%' AND type='%$variety%'";

The wild cards will not work with an equality operator. Use one of these two:

$query = "SELECT * FROM discounts WHERE zipcode='$zipcode' AND type='$variety'";

OR

$query = "SELECT * FROM discounts WHERE zipcode like '%$zipcode%' AND type like '%$variety%'";

The first will match **only** on an exact match, the second will match if results are found with $zipcode and $variety anywhere in those fields.

going2college



 
Msg#: 4151761 posted 4:18 pm on Jun 14, 2010 (gmt 0)

IT WORKS!

Is there anything I can do for you in return for your help? I have stuff coming up in the near future I might be able to help you with for your generosity.

rocknbil

WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member



 
Msg#: 4151761 posted 6:15 pm on Jun 15, 2010 (gmt 0)

Ever heard of Suntory 18 year or Patrone? :-)

There is only one serious answer: you're welcome, just pay it forward.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved