Session, $_GET/$_POST or some other issue?

kilbey1

I am having some problems with a page which contains 2 forms.
Form 1: a simple field that searches on an integer in a database, included in the master PHP document (and thus appears on ALL pages). Input is validated, or redirected to a page upon success.
Form 2: listingsCriteria.php - displays results of a real estate search, based on specific criteria

Here is my flow and how it's breaking:

User selects form fields from a page previous to listingsCriteria, and hits submit (works fine)
User arrives at listingsCriteria.php - has either found some listings, or 0 (works fine)
User then goes to form 1 (search field) from within listingsCriteria.php, and enters in a 5-digit number.
[INDENT]a. Form1 is validated. This validates to false, as a valid number is 6 digits. An error is displayed. (this works fine)
[/INDENT]
[INDENT]b. listingsCriteria.php is meant to be re-displayed (or whatever page the user has searched from) - THIS BREAKS
[/INDENT]

In step 3 (listingsCriteria.php), I have set up something along these lines within a function (very shortened):

if(isset($_POST['submit'])) {
     $_SESSION['priceMax'] = $_REQUEST['priceMax'];
	$errmsg = array();
     if ($_POST['priceMax'] == '' && empty($_POST['priceMax'])) {
				$errmsg[] = "<li class=\"error\">Please enter a maximum price.<BR />";
			} elseif (!Validate::isInteger($_POST['priceMax'])) {
					$priceMax=preg_replace("/[^0-9]/", "", $_POST['priceMax']);
			} elseif ($_POST['priceMax'] < $_POST['priceMin']) {
				$errmsg[] = "<li class=\"error\">Maximum price must be greater than the minimum price.<br />";
			} else {
				$priceMax = $_POST['priceMax'];
			}	
} else {

	$priceMax = $_SESSION['priceMax'];

}

In step 3, when I echo out the sql, $priceMax is stripped of everything but digits.
In step 4, the sql echoes out that $priceMax is NOT stripped of everything but digits.

EG, Step 3:
SELECT ListNum, Bedrooms, BathsFull, BathsPartial, Price, Area, AddressNum, AddressDirection, Street FROM listingsRES WHERE (Bedrooms >= 0 OR Bedrooms != NULL) AND BathsFull >= 0 AND Price BETWEEN 100 AND 150000

Step 4:
SELECT ListNum, Bedrooms, BathsFull, BathsPartial, Price, Area, AddressNum, AddressDirection, Street FROM listingsRES WHERE (Bedrooms >= 0 OR Bedrooms != NULL) AND BathsFull >= 0 AND Price BETWEEN 100 AND 150,000

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '000' at line 1

What's really happening here?

ClarkF1

How is the price stored in the database. If it's an int, it won't have the comma in it in the database and therefore you don't need it in the query. It's this comma that's causing the SQL error.

kilbey1

The price in the database is indeed an int. However, on user input, I'm predicting someone's going to want to input "150,000" since 150000 isn't totally 'natural' to write. Considering this, I opted to have all characters except for digits stripped from the SQL statement, which is generated dynamically.

ClarkF1

Okay I can see the code where the non-digits get stripped out but can you post all the code between this and the queries that you are echoing out including the queries.

kilbey1

I'm going to attach here the 2 main files that are involved:

mlsprocess.php - how the validation for the search field on each page is processed
listingsCriteria.php - probably the one you actually want to look at

Here's is the requested code:

if(isset($_POST['submit'])) {

	$_SESSION = array();

<..snip..>
		$_SESSION['priceMin'] = $_REQUEST['priceMin'];
		$_SESSION['priceMax'] = $_REQUEST['priceMax'];
<..snip..>				
		$errmsg = array();

	/* ***************
	Min Price textbox validation
	**************** */	

		if ($_POST['priceMin'] == '' && empty($_POST['priceMin'])) {
			$errmsg[] = "<li class=\"error\">Please enter a minimum price.<BR />";
		} else { //if priceMin is not empty, do this...	
			//check they entered all integers
			if (!Validate::isInteger($_POST['priceMin'])) {
				$priceMin=preg_replace("/[^0-9]/", "", $_POST['priceMin']);
				//$errmsg[] = "<li class=\"error\">Please enter all digits for the minimum price.<br />";
			} else {
				$priceMin = $_POST['priceMin'];
			}
		}

	/* ***************
	Max Price textbox validation
	**************** */	

		if ($_POST['priceMax'] == '' && empty($_POST['priceMax'])) {
			$errmsg[] = "<li class=\"error\">Please enter a maximum price.<BR />";
		} elseif (!Validate::isInteger($_POST['priceMax'])) {
				$priceMax=preg_replace("/[^0-9]/", "", $_POST['priceMax']);
				//$errmsg[] = "<li class=\"error\">Please enter all digits for the maximum price.<br />";
		} elseif ($_POST['priceMax'] < $_POST['priceMin']) {
			$errmsg[] = "<li class=\"error\">Maximum price must be greater than the minimum price.<br />";
		} else {
			$priceMax = $_POST['priceMax'];
		}	

<..snip..>

} else {


	$PropertyType = $_SESSION['PropertyType'];
	$bathrooms = $_SESSION['bathrooms'];
	$bedrooms = $_SESSION['bedrooms'];
	$priceMin = $_SESSION['priceMin'];
	$priceMax = $_SESSION['priceMax'];
	$neighborhoods = $_SESSION['neighborhoods'];
	$firstfloorbedroom = $_SESSION['firstfloorbedroom'];
	$acreageSize = $_SESSION['acreageSize'];
	$schoolDistrict = $_SESSION['schoolDistrict'];
	$zipcode = $_SESSION['zipcode'];
	$fireplace = $_SESSION['fireplace'];
	$pool = $_SESSION['pool'];

}

$errors = count($errmsg);

if ($errors > 0) {

	echo "<br clear=\"all\" />";
	echo "<ul>";
	foreach($errmsg as $key) {
		echo $key;
	}
	echo "</ul>";
	detailedForm();
	echo "<br /><br />";

} else {

	if($PropertyType == 'RES' || $PropertyType == 'FAL' || $PropertyType == 'MUL') {
		$default_sort = 'Address'; //set default sort ordering scheme 
		$allowed_order = array ('Address', 'Price', 'Bathrooms', 'Bedrooms'); //these are the allowable fields for ordering 
	} elseif ($PropertyType == 'CIB') {
		$default_sort = 'Address'; //set default sort ordering scheme 
		$allowed_order = array('Address', 'Price');
	}


	/* if order and limit are not set, or it is not in the allowed 
	* list, then set it to a default value. Otherwise, 
	* set it to what was passed in. */ 		
	if (!isset ($_GET['order']) || !in_array ($_GET['order'], $allowed_order)) { 
		$order = $default_sort; 
		$sort = DESC; 
	} else { 
		$order = $_GET['order']; 
		$sort = $_GET['sort']; 
	}

	$PaginateIt->SetItemsPerPage(10);
	$PaginateIt->SetLinksToDisplay(20);


	if($bedrooms == "No Minimum") { 
		$bedrooms = 0; 
	}
	if($bathrooms == "No Minimum") { 
		$bathrooms = 0; 
	}

	if($acreageSize == "lessthanone") {
		$acreageSize = "< 1";
	} elseif($acreageSize == "onetwo") {
		$acreageSize = "BETWEEN 1 and 2";
	} elseif($acreageSize == "twothree") {
		$acreageSize = "BETWEEN 2 and 3";
	} elseif($acreageSize == "threefour") {
		$acreageSize = "BETWEEN 3 and 4";
	} elseif($acreageSize == "fiveplus") {
		$acreageSize = "> 5";
	}

	//set up SELECT statement
	$aSelect = array(); 
	$aSelect[] = "ListNum";
	if($PropertyType == 'RES' || $PropertyType == 'FAL') {	
		$aSelect[] = "Bedrooms";
	} elseif($PropertyType == 'MUL') {
		$aSelect[] = "CAST(FirstNumBeds + SecondNumBeds + ThirdNumBeds + FourthNumBeds AS SIGNED) AS Bedrooms";
	}

	if($PropertyType == 'RES' || $PropertyType == 'FAL') {						
		$aSelect[] = "BathsFull";
	} elseif($PropertyType == 'MUL') {
		$aSelect[] = "CAST(FirstNumFullBaths + SecondNumFullBaths + ThirdNumFullBaths + FourthNumFullBaths AS SIGNED) AS BathsFull";
	}
	if($PropertyType == 'RES' || $PropertyType == 'FAL') {	
		$aSelect[] = "BathsPartial";
	} elseif($PropertyType == 'MUL') {
		$aSelect[] = "CAST(FirstNumPartialBaths + SecondNumPartialBaths + ThirdNumPartialBaths + FourthNumPartialBaths AS SIGNED) AS BathsPartial";
	}	

	$aSelect[] = "Price";						
	$aSelect[] = "Area";						
	$aSelect[] = "AddressNum";
	$aSelect[] = "AddressDirection";	
	$aSelect[] = "Street";		


	if(isset($firstfloorbedroom)) {
		if($PropertyType == 'RES') {	
			$aSelect[] = "MasterBedLevel";	
			$aSelect[] = "SecBedLevel";
			$aSelect[] = "ThirdBedLevel";
			$aSelect[] = "FourthBedLevel";
			$aSelect[] = "FifthBedLevel";					
		}
	}

	if($acreageSize != "") {
		$aSelect[] = "Acres";						
	}

	if($schoolDistrict != "") {
		if($PropertyType != 'CIB') {	
			$aSelect[] = "SchoolDistrict";						
		}
	}

	if(isset($fireplace)) {
		if($PropertyType == 'RES') {
			$aSelect[] = "Fireplaces";						
		}
	}

	if(isset($pool)) {
		if($PropertyType == 'RES') {
			$aSelect[] = "Pool";
		}					
	}

	if($zipcode != "") {
		$aSelect[] = "ZipCode";						
	}

	//set up FROM statement
	$aFrom = "listings" . $PropertyType;					

	//set up WHERE statement
	$aWhere = array();
	if($PropertyType == 'RES' || $PropertyType == 'FAL') {
		$aWhere[] = "(Bedrooms >= " . $bedrooms . " OR Bedrooms != NULL)";
	} elseif($PropertyType == 'MUL') {
		$aWhere[] = "((FirstNumBeds + SecondNumBeds + ThirdNumBeds + FourthNumBeds) >= " . $bedrooms . " OR (FirstNumBeds + SecondNumBeds + ThirdNumBeds + FourthNumBeds) != NULL)";
	}

	if($PropertyType == 'RES' || $PropertyType == 'FAL') {						
		$aWhere[] = "BathsFull >= " . $bathrooms . "";
	} elseif($PropertyType == 'MUL') {
		$aWhere[] = "((FirstNumFullBaths + SecondNumFullBaths + ThirdNumFullBaths + FourthNumFullBaths) >= " . $bathrooms . " OR (FirstNumFullBaths + SecondNumFullBaths + ThirdNumFullBaths + FourthNumFullBaths) != NULL)";
	}

	$aWhere[] = "Price BETWEEN " . trim($priceMin) . " AND " . trim($priceMax);

	$query_area_search = 'SELECT a.value, a.description'
	. ' FROM areas a'
	. ' WHERE a.value > 0'
	. ' GROUP BY a.description'
	. ' ORDER BY a.description ASC';
	$result_area_search = mysql_query($query_area_search);
	$arearownum = mysql_num_rows($result_area_search);
	$areasize = sizeof($neighborhoods);


		$aWhereAreas = array();
		foreach ($neighborhoods as $key => $value) {
			$hoods = $value;
			$aWhereAreas[] = "Area = '". $hoods . "'";
		}

	if($firstfloorbedroom == "Y") {
		if($PropertyType == 'RES') {
			$aWhere[] = "(MasterBedLevel = 'M' OR SecBedLevel = 'M' OR ThirdBedLevel = 'M' OR FourthBedLevel = 'M' OR FifthBedLevel = 'M')";
		}
	}	

	if($acreageSize != "") {
		$aWhere[] = "Acres " . $acreageSize . "";
	}	

	if($schoolDistrict != "") {
		if($PropertyType != 'CIB') {	
			$aWhere[] = "SchoolDistrict = '" . $schoolDistrict . "'";
		}
	}	

	if($fireplace == "Y") {
		if($PropertyType == 'RES') {
			$aWhere[] = "(Fireplaces >= 1 OR Fireplaces != NULL)";					
		}
	}

	if($pool == "Y") {
		if($PropertyType == 'RES') {
			$aWhere[] = "(Pool = 'Y' OR Pool != NULL)";					
		}
	}
	if($zipcode != "") {
		$aWhere[] = "ZipCode = '" . $zipcode . "'";					
	}

	//build SELECT clause
	$sSelect = 'SELECT ' . implode(', ', $aSelect);
	//echo $sSelect."<BR />";

	// build FROM clause
	$sFrom = 'FROM ' . $aFrom;						

	//build where clause
	$sWhere = 'WHERE ' . implode(' AND ', $aWhere);

	//build AREA part of where clause from multiple select list
	if($areasize > 0) {
		if($areasize == '1') {
			$sWhereAreas = 'AND ' . implode(' OR ', $aWhereAreas);
		} else {
			$sWhereAreas = 'AND (' . implode(' OR ', $aWhereAreas).")";
		}
	}

	$sql = $sSelect." ".$sFrom." ".$sWhere." ".$sWhereAreas;
	$sql = trim($sql);
	echo "<br /><br />".$sql."<BR><BR>";