PHP/MySql search - Dev Articles

Dev Articles Community Forums Sponsor:

Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here

August 11th, 2006, 12:39 PM

john128

Registered User

Join Date: Aug 2006

Posts: 12

Time spent in forums: 3 h 8 m 45 sec
Reputation Power: 0

PHP/MySql search

Hi, im new to this so please be gentle.

I need to search a list of locations in the MySQL database.

This is my code:

Code:

 $connection = mysql_connect($host,$user,$password)    #13
       or die ("couldn't connect to server");
  $db = mysql_select_db($database,$connection)          #15
       or die ("Couldn't select database");

  $sql = "SELECT * FROM location";    
  $res = mysql_query($sql) or die(mysql_error());  while ($rec = mysql_fetch_assoc($res)) $location[] = $rec;

echo '<DIV class=sBox id=search>';
echo "<form action='searchresults.php' method='post'>\n"; 
echo "<p>Property Search</p>";
echo '<P><SPAN class=label>Location:</SPAN><BR><SPAN class=field>';
   echo '<SELECT name="interest">'; 
   echo '<OPTION value = >No Preference</OPTION>    ';
   foreach ($location as $l)    
   	echo "<OPTION>{$l['location']}</OPTION>\n";  
   echo '</SELECT>';

it works fine apart from the higlighted part. What is the code so that the No Preference option will show results for all the locations.

Thanks for your help. John

August 11th, 2006, 01:22 PM

MadCowDzz

I'm Internet Famous

Dev Articles Frequenter (2500 - 2999 posts)

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 4 m 48 sec
Reputation Power: 8

Change that line to:
echo '<OPTION value="no_pref">No Preference</OPTION> ';

Then have your code on searchresults.php figure out if $_POST['interest'] is equal to no_pref or not... If it is, show all locations, otherwise, just show one.

Does this work for you?

__________________
Daryl's Homepage | My Blogroll | My Profile | Firefox supporter!
DevArticles Forum Moderator

"The net is a waste of time, and that's exactly what's right about it." -- William Gibson

August 11th, 2006, 01:33 PM

john128

Registered User

Join Date: Aug 2006

Posts: 12

Time spent in forums: 3 h 8 m 45 sec
Reputation Power: 0

Sorry, i really am very new to this.

This is my code in the searchresults.php:

Code:

 $connection = mysql_connect($host,$user,$password)
       or die ("couldn't connect to server");
  $db = mysql_select_db($database,$connection)
       or die ("Couldn't select database");
 
  /* Select pets of the given type */
  $query = "SELECT * FROM propertydetailstable 
               WHERE Location_Look_up=\"{$_POST['interest']}\" AND Dwelling=\"{$_POST['type']}\" AND Beds >= \"{$_POST[minbeds]}\" AND Eurocal >= \"{$_POST['minprice']}\" AND Eurocal <= \"{$_POST['maxprice']}\"";  
  $result = mysql_query($query)
       or die ("Couldn't execute query.");

How do I ad the if statement?

Thanks, John

August 11th, 2006, 01:56 PM

MadCowDzz

I'm Internet Famous

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 4 m 48 sec
Reputation Power: 8

I think you could do:

PHP Code:


		
			
/* Select pets of the given type */

  $query = "SELECT * FROM propertydetailstable ";

  if ($_POST['interest'] != 'no_pref') {

     $query .= "WHERE Location_Look_up=\"{$_POST['interest']}\" AND Dwelling=\"{$_POST['type']}\" AND Beds >= \"{$_POST[minbeds]}\" AND Eurocal >= \"{$_POST['minprice']}\" AND Eurocal <= \"{$_POST['maxprice']}\"";  

  }

August 12th, 2006, 05:01 AM

john128

Registered User

Join Date: Aug 2006

Posts: 12

Time spent in forums: 3 h 8 m 45 sec
Reputation Power: 0

Hi I have tried the code but i get this error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /home/.bootsy/********/test/searchresults.php on line 115

What does it mean?

August 14th, 2006, 08:14 AM

MadCowDzz

I'm Internet Famous

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 4 m 48 sec
Reputation Power: 8

Which line is line 115?
It probably means I missed a semicolon, or didn't escape a quote.

August 14th, 2006, 10:41 AM

john128

Registered User

Join Date: Aug 2006

Posts: 12

Time spent in forums: 3 h 8 m 45 sec
Reputation Power: 0

Line 115 is

PHP Code:


		
			
 $query = "SELECT * FROM propertydetailstable ";

Thanks

August 14th, 2006, 11:36 AM

MadCowDzz

I'm Internet Famous

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 4 m 48 sec
Reputation Power: 8

Check that the line above that has a semicolon.

I notice in your code above, the line above that $query line is:

PHP Code:


		
			
 $db = mysql_select_db($database,$connection)

       or die ("Couldn't select database");

Is there a chance you accidentally removed the "or die();" line?
I've seen that happen... make sure the statements are correct, or put a semicolon on if necessary.

August 15th, 2006, 04:56 AM

john128

Registered User

Join Date: Aug 2006

Posts: 12

Time spent in forums: 3 h 8 m 45 sec
Reputation Power: 0

From what i can see it is all there

PHP Code:


		
			
<?php

  include("access.inc");

 

  $connection = mysql_connect($host,$user,$password)

       or die ("couldn't connect to server");

  $db = mysql_select_db($database,$connection)

       or die ("Couldn't select database");

     



  /* Select pets of the given type */

  $query = "SELECT * FROM propertydetailstable ";

  if ($_POST['interest'] != 'no_pref') { 

     $query .= "WHERE Location_Look_up=\"{$_POST['interest']}\" AND Dwelling=\"{$_POST['type']}\" AND Beds >= \"{$_POST[minbeds]}\" AND Eurocal >= \"{$_POST['minprice']}\" AND Eurocal <= \"{$_POST['maxprice']}\"";   

  }

That is the code exactly, is there anything wrong here?

#10

August 15th, 2006, 08:12 AM

MadCowDzz

I'm Internet Famous

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 4 m 48 sec
Reputation Power: 8

I don't see a problem inside the code you're posting... You're positive that's line 115? And what's the line above it?
Sometimes i've had strange behaviour with the curly braces...

Desperate guess, but try this isntead:

PHP Code:


		
			
 $query .= "WHERE Location_Look_up=\"".$_POST['interest']."\" AND Dwelling=\"".$_POST['type']."\" AND Beds >= \"".$_POST[minbeds]."\" AND Eurocal >= \"".$_POST['minprice']."\" AND Eurocal <= \"".$_POST['maxprice']."\"";

All I did was remove the curly praces and make the variables append onto the string, instead of embed within it. I honestly expect no difference.

#11

August 15th, 2006, 04:09 PM

john128

Registered User

Join Date: Aug 2006

Posts: 12

Time spent in forums: 3 h 8 m 45 sec
Reputation Power: 0

Well i tried that and it stopped the error message from appearing, but it did not give the propper results so i changed this:

PHP Code:


		
			
($_POST['interest'] == 'no_pref') {

so that there are == instead of !=.

I then changed the code back to the old code with the curly brackets.

This has cured the problem.

Thank you very much!!!!!!!

#12

August 16th, 2006, 09:48 AM

MadCowDzz

I'm Internet Famous

Join Date: Jan 2003

Location: Toronto, Canada

Posts: 2,890

Time spent in forums: 1 Week 16 h 4 m 48 sec
Reputation Power: 8

Cheers... so the code uses equal, instead of not equal?
I might have mis-interpreted the SQL statement, sorry for any confusion =)

Glad you got things sorted out.

#13

August 16th, 2006, 03:11 PM

john128

Registered User

Join Date: Aug 2006

Posts: 12

Time spent in forums: 3 h 8 m 45 sec
Reputation Power: 0

Well after alot of testing I found a way of using equal and it worked.

Just one more question how do i sort the results in Assending price order? the price field is named Eurocal.

Thanks