[hockey97]

Hi, I need some help. Is there a better way to have the mysql querys to have parameters to be optional?


Ok, what I have is a online selling shop. I have items being sold in other locations. I want to display these images
images with information like price and shipping cost and a order button.

So, the problem is that with Mysql 5.0 my query that I pass like

SELECT * FROM ITEMS WHERE country=$country, state= $state , city = $city , zip_code= $zip_code

When I write something like that if any of the where fields are blank it will only grab the items that have those same blank spots.

For example, lest say I selected a country and a state but didn't put a zip code nor a city name. I then expect the results to grab every item being sold in that country and only in that state I selected. Yet, the query dosen't act like this. If the city and zip code are left blank the the query will only grab the items that dosen't have a city name or a zipcode in the data row.

Yet, I need to have it where if those are blank then it means to grab the items based on what was given meaning the country and state.

The only requirement is the countries name. Everything else should be optional. Sometimes if like the city names is blank or null.. then I get a sql query error. I just need to have the query have optional arguments for the where parts or conditional.

My search by default only requires the user to search for items in a country. I have state and region and provance to be optional so that users can narrow down their search towards their local area. If they want to buy something in their local area. Yet, they don't have to always narrow down their searches if they don't want too.

currently I just written a bunch of different SQL queries based on different conditions. yet, this did prevent the query from resulting into an error message but never fixed the search to exactly how I want it to be.

If I had country and state given to the query code. Yet, left city name or zip code to null or blank. then the items listed better have the city name or zip code to null or blank in order for that item to show up in the query.

Yet, I don't want that to happen. I want it where if they left out zip_Code or city name but gave a country name and a state name. Then only return all items being sold in that country under that state. so if it was the U.S and state was Florida and that is what we sent to the query code. Then I expect to see all items being sold in the U.S in the state of Florida to be inside the query or results.

I also want city name and zip code to be optional too. so you don't have to give a zip code or the city name all the time. you can give it the city name or zip code or a zip code range to grab items being cold in that specific area.

[Dormilich]

I am not aware that there are optional WHERE clauses in SQL. so the only choice I see is building the query with only the clauses you have.

and string values in SQL need to be quoted.

[no2pencil]

If I understand what you are trying to do, couldn't you logically build the sql query, based only on the variables that you have value for?

$sql="SELECT * FROM ITEMS WHERE ";
if($city) $sql.="city=\"$city\" ";

Of course you would want a fall out so at least one selection should be used as a default, & have a valid sql statement from the get-go.

[e_i_pi]

Yeah there is a way, you just gotta give your SQL server some sweet sweet lovin':

SELECT *
FROM ITEMS
WHERE (country = $country OR $country IS NULL OR $country = '')
AND (state = $state OR $state IS NULL OR $state = '')
AND (city = $city OR $city IS NULL OR $city = '')
AND (zip_code = $zip_code OR $zip_code IS NULL OR $zip_code = '')

[Atli]

It would probably be better to build the query based on the input data, though, like no2pencil and Dormilich suggested.

I would suggest a more dynamic way of handling that though, by compiling a list of WHERE clauses based on the user input and then adding it to the query before executing it.

For example, starting with this:

// A list of the WHERE conditions that will be used in
// the SQL query.
$conditions = array();

You could then go on to fetch all the required fields (city, in your case) and adding them to the list of conditions. (I'm assuming you're getting data from POST?)

// A list of required input fields that must be added
// to the query.
$required = array("country");

// Go through the required fields, and for each one that
// is set in the $_POST array, add a condition to the
// $conditions list. Exit the script if any are missing.
foreach ($fields as $field) {
	if (isset($_POST[$field])) {
		$conditions[] = sprintf("`%s` = '%s'", $field,
			mysql_real_escape_string($_POST[$field]));
	}
	else {
		die("Required field '{$field}' is missing.");
	}
}

You could do the exact same thing with the optional fields:

// A list of optional input fields that can be added
// to the query, but won't be if they are missing.
$optional = array("state", "zip", "city");

// Go through the optional fields, and for each one that
// is set in the $_POST array, add a condition to the
// $conditions list.
foreach ($fields as $field) {
	if (isset($_POST[$field])) {
		$conditions[] = sprintf("`%s` = '%s'", $field,
			mysql_real_escape_string($_POST[$field]));
	}
}

Then all you have to do is compile the query, and add all the conditions to it:

$sql = "SELECT stuff FROM table WHERE ". implode(" AND ", $conditions);

[e_i_pi]

I agree that code generated SQL would be better in this case, but there are times when you don't really have that option easily available. A good example is in my workplace, where I need to generate reports in SSRS. It is possible to have code-generated SQL, but it's not always the best way to go (in fact my boss suggests I avoid it except when it's the only option). Debugging code-generated SQL when you don't have a variable dumping method option is very very painful.

But where you have control over the SQL query itself, you're right Atli, generating the SQL is the top option.

This post has been edited by e_i_pi: 06 February 2012 - 02:37 AM

[hockey97]

Atli, on 06 February 2012 - 03:19 AM, said:

It would probably be better to build the query based on the input data, though, like no2pencil and Dormilich suggested.

I would suggest a more dynamic way of handling that though, by compiling a list of WHERE clauses based on the user input and then adding it to the query before executing it.

For example, starting with this:

// A list of the WHERE conditions that will be used in
// the SQL query.
$conditions = array();

You could then go on to fetch all the required fields (city, in your case) and adding them to the list of conditions. (I'm assuming you're getting data from POST?)

// A list of required input fields that must be added
// to the query.
$required = array("country");

// Go through the required fields, and for each one that
// is set in the $_POST array, add a condition to the
// $conditions list. Exit the script if any are missing.
foreach ($fields as $field) {
	if (isset($_POST[$field])) {
		$conditions[] = sprintf("`%s` = '%s'", $field,
			mysql_real_escape_string($_POST[$field]));
	}
	else {
		die("Required field '{$field}' is missing.");
	}
}

You could do the exact same thing with the optional fields:

// A list of optional input fields that can be added
// to the query, but won't be if they are missing.
$optional = array("state", "zip", "city");

// Go through the optional fields, and for each one that
// is set in the $_POST array, add a condition to the
// $conditions list.
foreach ($fields as $field) {
	if (isset($_POST[$field])) {
		$conditions[] = sprintf("`%s` = '%s'", $field,
			mysql_real_escape_string($_POST[$field]));
	}
}

Then all you have to do is compile the query, and add all the conditions to it:

$sql = "SELECT stuff FROM table WHERE ". implode(" AND ", $conditions);

Thanks, I am doing something similar. Just was told by another programmer on here that Mysql SQL has a built in method to handle optional arguments in the WHERE clause. So I asked here to learn about it if it exists and if not what are others doing.

Just trying to fine the most efficient way of doing this. I am currently dynamically generating the query statement.
Just how most suggested here. Just thought there is a better more efficient way to do it.