SELECT statement help

[jeffgman]

I think I might have gone over my experience level. I am hoping somebody can point me in the correct direction.

I have a page that when you first go into, it outputs many lines from a MySQL database. Not a problem so far. It has five columns in it. Each column is a hyperlink so the user can sort of one of those columns. Still not a problem. I have that working perfectly. But, here is my problem. At the top of the page, I have a form with three options. They are drop down lists so the user can filter the main list down. I don't have a problem if the user only selects one of the drop down lists. But, if they filter down the list by using two or three of the drop downs, I am not sure how I would do the select statement. Here is some additional information.

The sorting method is using a variable called sort which is added to the url and I am using a $GET statement to get the result. I am getting the results of the three drop down lists by using $POST statements. The three variables are called $stores, $make9, and $type9.

Here is some of the code I have so far with the select statement:

$result = "SELECT * FROM used"; 
if($sort == "store") {
	if($stores != "" or $type9 != "" or $make9 != "") {
		$result .= " WHERE";
		if($stores != "") {
			$result .= " store = {$stores}";
		}
		if($type9 != "") {
			$result .= " AND type = {$type9}";
		}
		if($make9 != "") {
			$result .= " AND make = {$make9}";
		}

As you can tell my code won't work. If the user only uses the drop down for $type9 or $make9, the AND statement probably won't work. Can somebody direct me how I can make this work?

Thanks for the help.

Jeff

[maxpup979]

the quick and dirty way I always do this is to throw in a known true statement in the where...
something like:

 $result = "SELECT * FROM used"; 
if($sort == "store") {
    if($stores != "" or $type9 != "" or $make9 != "") {
        $result .= " WHERE PrimaryKeyField is not null";
        if($stores != "") {
            $result .= " AND store = {$stores}";
        }
        if($type9 != "") {
            $result .= " AND type = {$type9}";
        }
        if($make9 != "") {
            $result .= " AND make = {$make9}";
        }  

I think you could probably just use a where 1, but I havent tested that...so I dont know for certain

[jeffgman]

maxpup979 wrote:

the quick and dirty way I always do this is to throw in a known true statement in the where...
something like:

 $result = "SELECT * FROM used"; 
if($sort == "store") {
    if($stores != "" or $type9 != "" or $make9 != "") {
        $result .= " WHERE PrimaryKeyField is not null";
        if($stores != "") {
            $result .= " AND store = {$stores}";
        }
        if($type9 != "") {
            $result .= " AND type = {$type9}";
        }
        if($make9 != "") {
            $result .= " AND make = {$make9}";
        }  

I think you could probably just use a where 1, but I havent tested that...so I dont know for certain

Thanks for the information. It will definitely help me move in the right direction.

Jeff

[jeffgman]

Okay, I have it working now. But, I have run into one more problem. Here is the code I am using for the select statement:

$result = "SELECT * FROM used"; 
if($sort == "store") {
	if(($stores != "") or ($type9 != "") or ($make9 != "")) {
		$result .= " WHERE id is not null";
		if($stores != "") {
			$result .= " AND store = '{$stores}'";
		}
		if($type9 != "") {
			$result .= " AND type = '{$type9}'";
		}
		if($make9 != "") {
			$result .= " AND make = '{$make9}'";
		}
	}
	$result .= " order by store";
} elseif ($sort == "desc") {
	if(($stores != "") or ($type9 != "") or ($make9 != "")) {
		$result .= " WHERE id is not null";
		if($stores != "") {
			$result .= " AND store = '{$stores}'";
		}
		if($type9 != "") {
			$result .= " AND type = '{$type9}'";
		}
		if($make9 != "") {
			$result .= " AND make = '{$make9}'";
		}
	}
	$result .= " order by make, model";
} elseif ($sort == "condition") {
	if(($stores != "") or ($type9 != "") or ($make9 != "")) {
		$result .= " WHERE id is not null";
		if($stores != "") {
			$result .= " AND store = '{$stores}'";
		}
		if($type9 != "") {
			$result .= " AND type = '{$type9}'";
		}
		if($make9 != "") {
			$result .= " AND make = '{$make9}'";
		}
	}
	$result .= " order by condition";
} elseif ($sort == "type") {
	if(($stores != "") or ($type9 != "") or ($make9 != "")) {
		$result .= " WHERE id is not null";
		if($stores != "") {
			$result .= " AND store = '{$stores}'";
		}
		if($type9 != "") {
			$result .= " AND type = '{$type9}'";
		}
		if($make9 != "") {
			$result .= " AND make = '{$make9}'";
		}
	}
	$result .= " order by type2";
} elseif ($sort == "price") {
	if(($stores != "") or ($type9 != "") or ($make9 != "")) {
		$result .= " WHERE id is not null";
		if($stores != "") {
			$result .= " AND store = '{$stores}'";
		}
		if($type9 != "") {
			$result .= " AND type = '{$type9}'";
		}
		if($make9 != "") {
			$result .= " AND make = '{$make9}'";
		}
	}
	$result .= " order by price";
} else {
	if($stores != "" or $type9 != "" or $make9 != "") {
		$result .= " WHERE id is not null";
		if($stores != "") {
			$result .= " AND store = '{$stores}'";
		}
		if($type9 != "") {
			$result .= " AND type = '{$type9}'";
		}
		if($make9 != "") {
			$result .= " AND make = '{$make9}'";
		}
	}
}
$qry=mysql_query($result);

Now, if the user selects one of the dropdowns in the form and filters the output, how do I keep that filter in place when they click on one of the column headings to sort the output?

Thanks,
Jeff

[cretaceous]

assuming your column heads are hrefs, echo the filter to the href like:
echo '<a href='.$mypage.'?thisvar='.$xyz.'&myfilter='.$filt;

or if it is a form stick them in the html as hidden fields
<input type="hidden" name="myfilter" value="<?php echo $filt ;?>">

[jeffgman]

Thanks for all of the help you guys have offered.