php/mysql syntax to search for 3 user options

[pabzzmike]

hello all,

i need help with php/mysql syntax that will search a mysql dbase using 3 user-provided (from web form) options. All options are contained in drop-down menus.

the mysql database has an auto column which is the primary key allowing for duplicate entries which are neccesary

the table has 2 more columns (date & details) which are not visible through the web form but should appear in the search results

the search results should show ALL houses matching the 3 criteria plus data contained in their other 2 columns in the database

error message when NOT FOUND

help much appreciated here!

a link to the sample form:
http://www.musyamichael.com/search.html

[blocblue]

Where is your code so far?

[HITMANOF44th]

<?php

/**
 * @author Jeremy Boron
 */
$i = 0;
//==================================================
// grab check db for items
//=================================================
		$query = "SELECT * FROM house WHERE (`house`.`vendor` = '".$one."' AND `house`.`name` = '".$two."' AND `house`.`part` = '".$three."')"; 
 $result = mysql_query($query) or die(mysql_error());
//===================================================
// throw items from db into variables
//=====================================================
while($row = mysql_fetch_array($result, MYSQL_BOTH)){
$vendor[$i] = $row[0];
$part[$i] = $row[1];
$name[$i] = $row[2];
//echo $vendor[$ij];
$i = $i + 1;
//echo $ij;
}
$i = $i - 1;
//====================================================================
// check to see if there is something in the first varable
//=====================================================================
	if ($vendor[0]) {
			echo "found something";
		}ELSE{
	
		echo "found nothing";	
			}
?>

maybe you are trying to do some thing like that ?

[BzzBee]

you can use your three fields in where clause like above

[pabzzmike]

Where is your code so far?

i have this bit but it displays all dbase records.
could i filter the results?

<?php

$con = mysql_connect("localhost","michael_michael","mr.p@bl0");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("michael_property", $con);

$query=mysql_query ("SELECT type, NRBhouses, housesize, details, date FROM nhouses" );
$result=mysql_query($query);

while ($row = mysql_fetch_array($query))

{
    echo 
	      "Type : {$row['type']} <br>" .
	      "Nairobi Houses : {$row['NRBhouses']} <br>" .
          "House Size : {$row['housesize']} <br>" .
		  "Details : {$row['details']} <br>" .
          "Date : {$row['date']} <br><br>";
		  
}

mysql_close($con);
?>

[pabzzmike]

<?php

$con = mysql_connect("localhost","michael_michael","mr.p@bl0");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("michael_property", $con);

/**
 * @author Jeremy Boron

 */

$i = 0;

//==================================================

// grab check db for items

//=================================================

$query = "SELECT * FROM nhouses WHERE (`nhouses`.`type` = '".$type."' AND `nhouses`.`NRBhouses` = '".$NRBhouses."' AND `nhouses`.`housesize` = '".$housesize."')"; 

$result = mysql_query($query) or die(mysql_error());

//===================================================

// throw items from db into variables

//=====================================================

while($row = mysql_fetch_array($result, MYSQL_BOTH)){

$type[$i] = $row[0];

$NRBhouses[$i] = $row[1];

$housesize[$i] = $row[2];

//echo $vendor[$ij];

$i = $i + 1;

//echo $ij;

}

$i = $i - 1;

//====================================================================

// check to see if there is something in the first varable

//=====================================================================

	if ($type[0]) {

			echo "found something";

		}ELSE{

		echo "found nothing";	

			}
mysql_close($con);
?>

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

Notice: Undefined variable: type in D:\wamp\www\statewide.com\houseMagician2.php on line 24

Notice: Undefined variable: NRBhouses in D:\wamp\www\statewide.com\houseMagician2.php on line 24

Notice: Undefined variable: housesize in D:\wamp\www\statewide.com\houseMagician2.php on line 24

Notice: Undefined variable: type in D:\wamp\www\statewide.com\houseMagician2.php on line 58
found nothing

[pabzzmike]

<?php

/**
 * @author Jeremy Boron
 */
$i = 0;
//==================================================
// grab check db for items
//=================================================
		$query = "SELECT * FROM house WHERE (`house`.`vendor` = '".$one."' AND `house`.`name` = '".$two."' AND `house`.`part` = '".$three."')"; 
 $result = mysql_query($query) or die(mysql_error());
//===================================================
// throw items from db into variables
//=====================================================
while($row = mysql_fetch_array($result, MYSQL_BOTH)){
$vendor[$i] = $row[0];
$part[$i] = $row[1];
$name[$i] = $row[2];
//echo $vendor[$ij];
$i = $i + 1;
//echo $ij;
}
$i = $i - 1;
//====================================================================
// check to see if there is something in the first varable
//=====================================================================
	if ($vendor[0]) {
			echo "found something";
		}ELSE{
	
		echo "found nothing";	
			}
?>

maybe you are trying to do some thing like that ?

yes i am, i tried modifying the syntax you provided but got the following errors:
Notice: Undefined variable: type in D:\wamp\www\statewide.com\houseMagician2.php on line 24

Notice: Undefined variable: NRBhouses in D:\wamp\www\statewide.com\houseMagician2.php on line 24

Notice: Undefined variable: housesize in D:\wamp\www\statewide.com\houseMagician2.php on line 24

Notice: Undefined variable: type in D:\wamp\www\statewide.com\houseMagician2.php on line 58
found nothing

here is the modified code, please help me out:

<?php

       
   
      $con = mysql_connect("localhost","michael_michael","mr.p@bl0");
   
      if (!$con)
   
      {
   
      die('Could not connect: ' . mysql_error());
   
      }
   
       
   
      mysql_select_db("michael_property", $con);
  
       
  
      /**
  
       * @author Jeremy Boron
  
       
  
       */
  
       
  
      $i = 0;
  
       
  
      //==================================================
  
       
  
      // grab check db for items
  
       
  
      //=================================================
  
       
  
      $query = "SELECT * FROM nhouses WHERE (`nhouses`.`type` = '".$type."' AND `nhouses`.`NRBhouses` = '".$NRBhouses."' AND `nhouses`.`housesize` = '".$housesize."')";
  
       
  
      $result = mysql_query($query) or die(mysql_error());
  
       
  
      //===================================================
  
       
  
      // throw items from db into variables
  
       
  
      //=====================================================
  
       
  
      while($row = mysql_fetch_array($result, MYSQL_BOTH)){
  
       
  
      $type[$i] = $row[0];
  
       
  
      $NRBhouses[$i] = $row[1];
  
       
  
      $housesize[$i] = $row[2];
  
       
  
      //echo $vendor[$ij];
  
       
  
      $i = $i + 1;
  
       
  
      //echo $ij;
  
       
  
      }
  
       
  
      $i = $i - 1;
  
       
  
      //====================================================================
  
       
  
      // check to see if there is something in the first varable
  
       
  
      //=====================================================================
  
       
  
      if ($type[0]) {
  
       
  
      echo "found something";
  
       
  
      }ELSE{
  
       
  
      echo "found nothing";
  
       
  
    }
  
      mysql_close($con);
  
      ?>

[pabzzmike]

<?php

/**
 * @author Jeremy Boron
 */
$i = 0;
//==================================================
// grab check db for items
//=================================================
		$query = "SELECT * FROM house WHERE (`house`.`vendor` = '".$one."' AND `house`.`name` = '".$two."' AND `house`.`part` = '".$three."')"; 
 $result = mysql_query($query) or die(mysql_error());
//===================================================
// throw items from db into variables
//=====================================================
while($row = mysql_fetch_array($result, MYSQL_BOTH)){
$vendor[$i] = $row[0];
$part[$i] = $row[1];
$name[$i] = $row[2];
//echo $vendor[$ij];
$i = $i + 1;
//echo $ij;
}
$i = $i - 1;
//====================================================================
// check to see if there is something in the first varable
//=====================================================================
	if ($vendor[0]) {
			echo "found something";
		}ELSE{
	
		echo "found nothing";	
			}
?>

maybe you are trying to do some thing like that ?

yes i am, i tried modifying the syntax you provided but got the following errors:
Notice: Undefined variable: type in D:\wamp\www\statewide.com\houseMagician2.php on line 24

Notice: Undefined variable: NRBhouses in D:\wamp\www\statewide.com\houseMagician2.php on line 24

Notice: Undefined variable: housesize in D:\wamp\www\statewide.com\houseMagician2.php on line 24

Notice: Undefined variable: type in D:\wamp\www\statewide.com\houseMagician2.php on line 58
found nothing

here is the modified code, please help me out:

<?php

$con = mysql_connect("localhost","michael_michael","mr.p@bl0");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("michael_property", $con);

/**
 * @author Jeremy Boron

 */

$i = 0;

//==================================================

// grab check db for items

//=================================================

$query = "SELECT * FROM nhouses WHERE (`nhouses`.`type` = '".$type."' AND `nhouses`.`NRBhouses` = '".$NRBhouses."' AND `nhouses`.`housesize` = '".$housesize."')"; 

$result = mysql_query($query) or die(mysql_error());

//===================================================

// throw items from db into variables

//=====================================================

while($row = mysql_fetch_array($result, MYSQL_BOTH)){

$type[$i] = $row[0];

$NRBhouses[$i] = $row[1];

$housesize[$i] = $row[2];

//echo $vendor[$ij];

$i = $i + 1;

//echo $ij;

}

$i = $i - 1;

//====================================================================

// check to see if there is something in the first varable

//=====================================================================

	if ($type[0]) {

			echo "found something";

		}ELSE{

		echo "found nothing";	

			}
mysql_close($con);
?>

[pabzzmike]

HITMANOF44

here is my modified script with errors

<?php

$con = mysql_connect("localhost","michael_michael","mr.p@bl0");
if (!$con)
  {
  die('Could not connect: ' . mysql_error());
  }

mysql_select_db("michael_property", $con);

/**
 * @author Jeremy Boron

 */

$i = 0;

//==================================================

// grab check db for items

//=================================================

$query = "SELECT * FROM nhouses WHERE (`nhouses`.`type` = '".$type."' AND `nhouses`.`NRBhouses` = '".$NRBhouses."' AND `nhouses`.`housesize` = '".$housesize."')"; 

$result = mysql_query($query) or die(mysql_error());

//===================================================

// throw items from db into variables

//=====================================================

while($row = mysql_fetch_array($result, MYSQL_BOTH)){

$type[$i] = $row[0];

$NRBhouses[$i] = $row[1];

$housesize[$i] = $row[2];

//echo $vendor[$ij];

$i = $i + 1;

//echo $ij;

}

$i = $i - 1;

//====================================================================

// check to see if there is something in the first varable

//=====================================================================

	if ($type[0]) {

			echo "found something";

		}ELSE{

		echo "found nothing";	

			}
mysql_close($con);
?>

Sorry to butt in, but does the query work? You could simplify it to:

$query = "SELECT * FROM nhouses WHERE type = '{$type}' AND NRBhouses = '{$NRBhouses}' AND housesize = '{$housesize}' ";

Put the ` back over the tables/fields if you want.

$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_array($result, MYSQL_BOTH)){
   $type[] = $row[0];
   $NRBhouses[] = $row[1];
   $housesize[] = $row[2];
}

$type[2] will give first field of third record
$housesize[1] will give third field of second record

Response to PM:

form:

<select id="type" name="type" >

... options filled by php using while loop from db SELECT...
e.g. <option value="...type id from db..."> ...type name from db...</option> 

</select>

<select id="situation" name="situation">

... options filled by php using while loop from db SELECT...
e.g. <option value="...situation id from db..."> ...situation area from db...</option> 

</select>

<select id="housesize" name="housesize">

... options filled by php using while loop from db SELECT...
e.g. <option value="...housesize id from db..."> ...housesize size from db...</option> 

</select>

When this form is submitted, the form handler (separate file or same one) will then filter the choice of properties chosen from the dropdowns, like this:

$type = $_POST['type'];
$situation = $_POST['situation']; 
$housesize = $_POST['housesize'];

You may want to 'clean' the data first, e.g. using addslashes() and something like htmlentities(), although you're passing integers as data, so 'should' be ok without.

Your filter query will be something like this (I've left out error checking for clarity). Also I've changed the field names (see my table structure at the end) :

$query = "SELECT * FROM house_property WHERE type_id = '{$type}' AND situation_id = '{$situation}' AND housesize_id = ''{$housesize}";
$result = mysql_query($query);
if(mysql_num_rows($result) > 0){
   echo "<table>\n\t<thead>\n\t\t<tr>\n\t\t\t<th>Name</th>\n\t\t\t<th>Details</th>\n\t\t\t<th>Date</th>\n\t\t</tr>\n\t</thead>\n\t<tbody>";
   while($data = mysql_fetch_array($result)){
      echo "\n\t\t<tr>\n\t\t\t<td>{$data['name']}</td>\n\t\t\t<td>{date['details']}</td>\n\t\t\t<td>{$data['date']}</td>\n\t\t</tr>";

   }
   echo "\n\t</tbody>\n</table>";
}else{
   echo '<p>No properties of with these stipulations found.</p>';
}

The '\n' means new line and '\t' means tab indent - they just help to tidy up the html (they can be left out - it won't affect the functionality of the code).

The above assumes tables like the following:

TABLE PROPERTY_TYPE:
id (autonumber, tinyint 3) PRIMARY KEY
ptype (varchar, 20)

TABLE SITUATED:
id (autonumber, tinyint 3) PRIMARY KEY
area (varchar, 20)

TABLE HOUSESIZE:
id (autonumber, tinyint 3) PRIMARY KEY
housesize (varchar, 20)

TABLE HOUSE_PROPERTY:
id (autonumber, int) PRIMARY KEY
name (varchar, 50)
details (text)
date (date)
type_id (tinyint, 3) FOREIGN KEY
situation_id (tinyint, 3) FOREIGN KEY
housesize_id (tinyint, 3) FOREIGN KEY

I hope the above helps.