Obtaining fields from a database

[steve55]

I have a page that contains some drop down menus that I use to search a msql database (the drop downs are populated by text files, but I think this is irrelevant for this question).

I can't get a result to display if the user selects 'all..' from any of my drop downs.. it is looking for values in the database and as there isn't an entry for 'all..' in it.

i.e If a user selects 'all towns' I would like all towns from the database to be included in my results page...

The page in question is here http://www.stevesims.com/jump3.php I can post the code if required.

Help!!!!!!!!!!!!!!!!!

[eoghain]

Well the code will be needed to point out where you problem is, but I can tell you that you are building your database query wrong.

I'm guessing that you have a piece of code that looks something like this:

   $sql = "SELECT *"
        . "  FROM my_table"
        . " WHERE my_field = '" . $_POST["form_field"] . "'";

And when that $sql variable get's build it reads something like this:

SELECT *
  FROM my_table
 WHERE my_field = 'all towns'

And since my_field doesn't hold any values called 'all towns' you get no data back. But when the user selects a valid town you do get data. The simple solution is to check before you build the $sql variable to see if they have choosen 'all' and if so don't include the WHERE part.

[steve55]

my current results code is as follows:-

<?

include "config.php";

if ($Submit) // perform search only if a string was entered.
{
$db = mysql_connect ($Host, $User, $Password);
mysql_select_db ($DBName) or die ("Cannot connect to database");

$srch="%".$Submit."%"; 
$query = "select * from houses WHERE areas LIKE '$fieldOne' || towns  LIKE '$fieldTwo' || zip  LIKE '$fieldThree' "; 

$result = mysql_db_query("stevesims", $query); 

if  ($result) 
{ 
echo "Here are the results:<br><br>"; 
echo "<TABLE border = '1' cellspacing = '0' cellpadding ='0' width = '90%' align='center'><tr> 
    <td align=center bgcolor=#cccc66><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">URL</font></td> 
    <td align=center bgcolor=#cccc66><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Area</font></td> 
    <td align=center bgcolor=#cccc66><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Town</font></td> 
    <td align=center bgcolor=#cccc66><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">Post Code</font></td> 
    </tr>"; 

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

        $areas = $r["areas"]; 
        $towns = $r["towns"]; 
        $zip = $r["zip"];
		$id = $r["id"]; 
              echo "<tr> 
                    <td><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\"><A HREF=\"http://www.stevesims.com/Houses/view2c.php?id=$id\">View Property</A></font></td> 
                    <td><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">$areas</font></td> 
                    <td><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">$towns</font></td> 
                    <td><font size=\"2\" face=\"Verdana, Arial, Helvetica, sans-serif\">$zip</font></td></tr> 
            "; 
    }                  // end while 
    echo "</table>"; 
 } else { echo "problems...."; } 

} else {
echo "Search string is empty. <br> Go back and type a string to search";
}

?>

---

Expanding upon your reply, how would I change the above query to solve the problem...

[eoghain]

Simple:

if ($Submit) { // perform search only if a string was entered. 
   $db = mysql_connect ($Host, $User, $Password); 
   mysql_select_db ($DBName) or die ("Cannot connect to database"); 

// Add something like this
   if ($fieldOne == 'all') {
      $fieldOne = "%"; // % = MySQL multi character wildcard
   }

   if ($fieldTwo == 'all') {
      $fieldTwo = "%";
   }

   $srch="%".$Submit."%"; 
   $query = "select * from houses WHERE areas LIKE '$fieldOne' || towns LIKE '$fieldTwo' || zip LIKE '$fieldThree' "; 

   $result = mysql_db_query("stevesims", $query); 

// rest of code

[steve55]

Thanks for your help...

I have amended the code as you suggested and loaded it up to http://www.stevesims.com/jump3.php

One question though, the 'all' command seems to override the rest of the code, so for example if I select 'all areas' but choose a town or post code 'all' results are returned...

Is there anyway around this..?

[eoghain]

The reason for that is that your SQL is using OR statements instead of AND statements:

SELECT *
  FROM houses
 WHERE areas LIKE '%'
    OR towns LIKE '%'
    OR zip LIKE '%';

This is going to get you everything because an OR match returns the full set of values. If one of your OR's returns all of the values in the table then that's what you get. What you want to limit what is returned are ANDs:

SELECT *
  FROM houses
 WHERE areas LIKE '%'
   AND towns LIKE '%'
   AND zip LIKE '%';

This will start restricting the resutls by your values. So if you ask for a house in a specific area but in any town and any zip then you will get all houses that fit that area. But if you add a zip to that then you only get the houses that match the area and zip.

[steve55]

No way - it works.

Thank you very much, you would not believe the hassle I've had getting a solution to this (I'm new to php), posting on numerous forums etc.... it's been 6weeks or so..

cheers.

[eoghain]

Glad I could help.