Member Avatar for marcmm

Ok, I have this dilemma reguarding the building of querries.

I have a website that browses hotels. The user can select the region and the quality of the hotel via some dropdown boxes and then he is offered weather the search results should include certain options that the hotels should have... like "parking space" or "swimming pool".

I use checkboxes for each of these options. Now my problem is that I don't have the slightest idea how to handle the building of the querries in order to include these checkboxe options.

Each checkbox, when checked, is supposed to add an AND () statement to the querry's condition parameters. But if the checkboxes aren't selected that condition should not exist.

the inefficient way would be to build an extensive IF structure that would encompas all possible combinations of the checkboxes. but if you have, for example, 9 checkboxes that would result in 81 posible combinations of the querry. if you also combine that with the extra combinations added from the dropdown boxes you get madness...

There has to be an easyer way!

The querry structure when ALL the checkboxes are unselected looks something like this:

$sql_querry="SELECT DISTINCT tables.fields
		        FROM tables
			WHERE ((condition )
			AND (condition))
			ORDER BY something ASC";

now I would like to do something like this:

$sql_querry="SELECT DISTINCT tables.fields
		        FROM tables
			WHERE ((condition )
                        IF ( checkbox1 is set )
                               {
                                AND ( condition )
                                }
                         IF ( checkbox2 is set )
                               {
                                AND ( condition )
                                }
                          etc.
			AND (condition))
			ORDER BY something ASC";

Now obviously just sticking the IF statements in the querry's parameters won't work.

I also thought of adding the IF statement inside the AND structure like this:

$sql_querry="SELECT DISTINCT tables.fields
		        FROM tables
			WHERE ((condition )
                        AND (
                        IF ( checkbox1 is set )
                               {
                                 condition 
                                }
                                )
			AND (condition))
			ORDER BY something ASC";

Obviously that didn't work either.

Can anybody give me some ideas reguarding what I could do about this? there has to be a way easyer then just doing 100 combinations for each combination of the checkboxes.

  • 2 Contributors
  • 2 Replies
  • 101 Views
  • 3 Days Discussion Span
  • Latest Post Latest Post by marcmm
Member Avatar for Rhyan

Yes, and you have reached the solution by yourself, but you just don't see it, or there is something that stops you from doing it this way.
Suppose you have a form with many check boxes. The form submits data either via post or via get method.
In order to know whether the checkbox is checked or not, you should validate it with some code like "if (isset($_POST[yourvar]))". This is where you will add your AND() statement to the query string. If your post value is set, then you will add AND() otherwise you do nothing.

So, you can build you querry like this

$initial_string = 'SELECT DISTINCT tables.fields
		          FROM tables
			  WHERE (condition)';
$limit_and_order_string = 'ORDER BY SOMETHING ASC LIMIT (0, 10)';

Now, you should know that PHP allows you to concatenate one variable with some new value and store the result in the same variable. This allows you to do the following

if (isset(some checkbox))
  {
  $initial_string .= 'AND FIELD=VALUE FROM CHECKBOX';
  }

You can do it this way for each checkbox. At the end you should build your querry together by doing it this way:

$query=$initial_string.$limit_and_order_string;

And that's it...
It works for me, I have implemented this method on several websites for real estates, that are very similar to hotel searching websites.

Member Avatar for marcmm

Ok, thank you very much for the repply and indeed, your solution works like a charm. And it's as simple as it's efficient. I didn't think about it maybe because I was considering the querry string as a line of parameters rather then just a normal string... I mean they only turn in to parameters when you run the line that executes the querry.

So anyway what I did was like you sugested:

$sql_querry="SELECT DISTINCT tables.names
		     FROM tables
	             WHERE ((condition )
		     AND (condition)";

$sql_end=") 
	        ORDER BY localitate.den_localitate ASC";

Wanted to point out that if one is using multyple conditions it's crucial to add the ")" to the start of the end string.

Then I just implemented if statements for each checkbox:

IF ( ISSET($_POST[checkbox name]) )
	{
	$sql_querry .= "AND ( condition atatched to that checkbox )";
	}

and lastly just concatenate the two strings and execute:

$sql_querry .= $sql_end;
        $rez_querry = mysql_query($sql_querry) OR DIE (mysql_error());

also I did not know about ".=" or "isset" so this also saved me a lot of truble. I was going to look if the checkbox was set using $_REQUEST and check if the checkbox was different from 0, like I check if the user selected something other then the default value in the dropdown boes.

ANyways, thanks again for the tips, problem solved.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.