Display the result of large select_form with many conditions

Adib · December 12, 2011

Hi,

I'm using a form which contains many select boxes.

The select boxes looks like this:

<select name="locatie" id="locatie">

<option value="" selected>-----</option>

<?php

require_once ('config.php');

$query = "select ID, locatie from locatie";

$result = mysql_query ($query);

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

extract($row);

echo '<option value="'.$ID.'">'.$locatie.'</option>';

}

?>

</select>

<select name="prijsVan" id="prijsVan">

<option value="" selected>-----</option>

<?php

$query = "select ID, prijs from prijs";

$result = mysql_query ($query);

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

extract($row);

echo '<option value="'.$ID.'">€ '.$prijs.'</option>';

}

?>

</select>

<select name="prijsTot" id="prijsTot">

<option value="" selected>-----</option>

<?php

$query = "select ID, prijs from prijs";

$result = mysql_query ($query);

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

extract($row);

echo '<option value="'.$ID.'">€ '.$prijs.'</option>';

}

?>

</select>

<select name="ligging" id="ligging">

<option value="" selected>-----</option>

<?php

$query = "select ID, ligging from ligging";

$result = mysql_query ($query);

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

extract($row);

echo '<option value="'.$ID.'">'.$ligging.'</option>';

}

?>

</select>

<select name="oppervlakte" id="oppervlakte">

<option value="" selected>-----</option>

<?php

$query = "select ID, oppervlakte from oppervlakte";

$result = mysql_query ($query);

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

extract($row);

echo '<option value="'.$ID.'">'.$oppervlakte.' m²</option>';

}

?>

</select>

<select name="kamers" id="kamers">

<option value="" selected>-----</option>

<?php

$query = "select ID, kamers from kamers";

$result = mysql_query ($query);

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

extract($row);

echo '<option value="'.$ID.'">'.$kamers.'</option>';

}

?>

</select>

<select name="woning" id="woning">

<option value="" selected>-----</option>

<?php

$query = "select ID, type from woning";

$result = mysql_query ($query);

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

extract($row);

echo '<option value="'.$ID.'">'.$type.'</option>';

}

?>

</select>

<input type="submit" name="submit" value="Zoeken">

To display the result i'm using a PHP processing page which contains the flowing code:

<?php

......

.....

$locatie = $_POST['locatie'];

$prijsVan = $_POST['prijsVan'];

$prijsTot = $_POST['prijsTot'];

$ligging = $_POST['ligging'];

$oppervlakte = $_POST['oppervlakte'];

$kamers = $_POST['kamers'];

$woning = $_POST['woning'];

$eigenschappen = $_POST['eigenschappen'];

// SHOW LOCATIE

if($locatie !='' && $prijsVan =='' && $prijsTot =='' && $ligging =='' && $oppervlakte =='' && $kamers =='' && $woning =='' && $eigenschappen =='') {

$query .= " AND locatie.ID = '" . $locatie. "' ORDER BY prijs.ID";

}

// SHOW LOCATIE_PRIJSVAN

if($prijsVan !='' && $locatie !='' && $prijsTot =='' && $woning =='' && $ligging =='' && $oppervlakte =='' && $kamers =='' && $eigenschappen =='') {

$query .= " AND locatie.ID = '" . $locatie . "' AND prijs.ID >= '" . $prijsVan . "' ORDER BY prijs.ID";

}

// SHOW LOCATIE_PRIJSTOT

if($prijsTot !='' && $locatie !='' && $prijsVan =='' && $woning =='' && $ligging =='' && $oppervlakte =='' && $kamers =='' && $eigenschappen =='') {

$query .= " AND locatie.ID = '" . $locatie . "' AND prijs.ID <= '" . $prijsTot . "' ORDER BY prijs.ID";

}

// SHOW PRIJS VAN

if($prijsVan !='' && $locatie =='' && $prijsTot =='' && $ligging =='' && $oppervlakte =='' && $kamers =='' && $woning =='' && $eigenschappen =='') {

$query .= " AND prijs.ID >= '" . $prijsVan . "' ORDER BY prijs.ID";

}

// SHOW PRIJS TOT

if($prijsTot !='' && $locatie =='' && $prijsVan =='' && $ligging =='' && $oppervlakte =='' && $kamers =='' && $woning =='' && $eigenschappen =='') {

$query .= " AND prijs.ID <= '" . $prijsTot . "' ORDER BY prijs.ID";

}

// SHOW PRIJS_VAN_PRIJS_TOT

if($prijsVan !='' && $prijsTot !='' && $locatie =='' && $woning =='' && $ligging =='' && $oppervlakte =='' && $kamers =='' && $eigenschappen =='') {

$query .= " AND prijs.ID BETWEEN '" . $prijsVan . "' AND '" . $prijsTot . "' ORDER BY prijs.ID";

}

// SHOW LIGGING

if($ligging !='' && $prijsVan =='' && $prijsTot =='' && $locatie =='' && $oppervlakte =='' && $kamers =='' && $woning =='' && $eigenschappen =='') {

$query .= " AND ligging.ID = '" . $ligging. "' ORDER BY prijs.ID";

}

...................... and so on.........

// Error: indien geen keuze gemaakt show deze error

if($prijsVan =='' && $prijsTot =='' && $locatie =='' && $woning =='' && $ligging =='' && $oppervlakte =='' && $kamers =='' && $eigenschappen =='') {

?> <center><font color="#666"> <?php postError(); ?></font></center><?php

}

else{

//Execute query

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

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

echo $row['image_id'] ;

echo $row['locatie'] ;

echo $row['prijs'];

echo $row['type'];

echo $row['kamers'];

echo $row['oppervlakte'] . ' m²';

echo $row['ligging'];

echo $row['eigenschappen'];

}

mysql_close($con);

?>

As you see i have to use many IF condition to display the result of every select combinations.

Is there any way to do it in better way and using less coding?

Thanks.

Psycho · December 12, 2011

You should definitely start considering to learn how to write modular code. I.e. write code that can be re-purposed when you have to do the same thing multiple times. It would also be a good idea to separate the logic (PHP) from the display (HTML) instead of intermixing the two.

Here is one possibility for creating the select options in a more logical format. I would create all the fields as an array to make the processing easier.

<?php
//Function to generate select options from a query
function querySelectOptions($query)
{
    //Create default option
    $optionsHTML = "<option value=''>-----</option>\n";
    //Run query
    $result = mysql_query $query);
    if(!$result) { return $optionsHTML; }
    //Process results
    while($row = mysql_fetch_array($result))
    {
        $optionsHTML .= "<option value='{$row[0]}'>{$row[1]}</option>\n";
    }
    return $optionsHTML;
}

//Create locatie options
$locatieOptions = querySelectOptions("SELECT ID, locatie FROM locatie");
//Create prijsVan options
$prijsVanOptions = querySelectOptions("SELECT ID, prijs FROM prijs");
//Create prijsTot options
$prijsTotOptions = querySelectOptions("SELECT ID, prijs FROM prijs");
//Create ligging options
$liggingOptions = querySelectOptions("SELECT ID, ligging FROM ligging");
//Create oppervlakte options
$oppervlakteOptions = querySelectOptions("SELECT ID, oppervlakte from oppervlakte");
//Create kamers options
$kamersOptions = querySelectOptions("SELECT ID, kamers from kamers");
//Create woning options
$woningOptions = querySelectOptions("SELECT ID, type from woning");

?>

<select name="condition[locatie]" id="locatie">
    <?php echo $locatieOptions; ?>
</select>

<select name="condition[prijsVan]" id="prijsVan">
    <?php echo $locatieOptions; ?>
</select>

<select name="condition[prijsTot]" id="prijsTot">
    <?php echo $prijsTotOptions; ?>
</select>

<select name="condition[ligging]" id="ligging">
    <?php echo $liggingOptions; ?>
</select>

<select name="condition[oppervlakte]" id="oppervlakte">
    <?php echo $oppervlakteOptions; ?>
</select>

<select name="condition[kamers]" id="kamers">
    <?php echo $kamersOptions; ?>
</select>

<select name="condition[woning]" id="woning">
    <?php echo $woningOptions; ?>
</select>

Then the processing code

function processConditions($postConditions)
{
    $conditionsAry = array();
    foreach($postConditions as $field => $value)
    {
        $value = trim($value);
        if(!empty($value))
        {
            $value = mysql_real_escape_string($value);
            $conditionsAry[] = "`{$field}` = '{$value}'"
        }
    }
    return $conditionsAry;
}

$eigenschappen = $_POST['eigenschappen'];

//Process the conditions that were posted
$conditionsArray = processConditions($_POST['conditions']);

// Error: indien geen keuze gemaakt show deze error
if(count($conditionsArray)==0)
{
   echo "<center><font color='#666'> " . postError() . "</font></center>\n";
}
else
{
    //Execute query
    $query .= " AND " . implode(' AND ', processConditions($_POST['conditions']))
    $query .= "  ORDER BY prijs.ID";
    $result = mysql_query($query) or die(mysql_error());
         
         while($row = mysql_fetch_array($result)){
           
         echo  $row['image_id'] ;
         echo  $row['locatie'] ;
         echo  $row['prijs'];
         echo  $row['type'];
         echo  $row['kamers'];
         echo  $row['oppervlakte'] . ' m²';
         echo  $row['ligging'];
         echo  $row['eigenschappen'];
      }
}

mysql_close($con);

None of this was tested, but is just an example of how you could make that code more efficient/modular

Adib · December 16, 2011

Thanx this works like a charm.

But now I have an other problem.

In the selectbox for:

<?php echo $prijsVanOptions; ?>

</select>

I want to add the euro sign. How do i do that?

Psycho · December 16, 2011

Well, there are several ways:

1. You could change the function to take an array. Then for all the select lists you would run your query and preprocess the data as needed before calling the function. This probably makes the msot sense from the standpoint of keeping the code functionally separated.

2. You could change the function to take an additional parameter (e.g. the euro sign). Then change the logic in the function to use that parameter when generating the output.

3. Simply change the query to put the euro sign into the results. This would only require one simple change

$prijsVanOptions = querySelectOptions("SELECT ID, CONCAT('€', prijs) FROM prijs");

This will return the same results but the euro sign will be concatenated to the front of the prijs values

Adib · December 16, 2011

Thanx again for this great solution.

Here i need for the last time your help.

Ass you see i have two select boxes where i can select the price:

1. prijsVan. This means price from.

2. prijsTot. This means price to.

I have one table for the prices with column names ID as primary key(auto_increment) and prijs for the amount of price(e. 50000)

How to make this combination possible.

imagine i choose 50000 as prijsVan and 100000 as prijsTot. The script then should show the result of the price between 50000 and 100000 and if i only select something from one of these select boxes then the script should show only the prices from the chosen option.

This will be the last thing i ask

Appreciate your help

Psycho · December 16, 2011

OK, I *think* I understand you. If so, then the current code for creating the options shouldn't change. You should just need to change the code that creates the conditions.

But, there's something to consider. What if the user selects a FROM value but no TO value (or vice versa). I would handle that by creating a condition such as "WHERE value > FROM" or "WHERE value < TO".

Anyway, I did put all of the POST data into an array to be processed in a loop. If ALL the fields were going to be processed the exact same way that makes sense. If not, then it doesn't. So . . . this is just a rough idea (not tested)

<?php

function createCondition(&$conditionsAry, $field, $value, $condition)
{
    if(!isset($_POST[$field])) { return; }
    $value = trim($value);
    if(empty($value)) { return; }

    $value = mysql_real_escape_string($value);
    $conditionsAry[] = "`{$field}` {$condition} '{$value}'"
    return;
}

$eigenschappen = $_POST['eigenschappen'];

//Process the conditions that were posted and populate temp array
$conditionsAry = array();
createCondition($conditionsAry, 'locatie', '=');
createCondition($conditionsAry, 'prijsVan', '>=');
createCondition($conditionsAry, 'prijsTot', '<=');
createCondition($conditionsAry, 'ligging', '=');
createCondition($conditionsAry, 'oppervlakte', '=');
createCondition($conditionsAry, 'kamers', '=');
createCondition($conditionsAry, 'woning', '=');

// Error: indien geen keuze gemaakt show deze error
if(count($conditionsArray)==0)
{
   echo "<center><font color='#666'> " . postError() . "</font></center>\n";
}
else
{
    //Create / Execute query
    $query .= " AND " . implode(' AND ', processConditions($_POST['conditions']))
    $query .= "  ORDER BY prijs.ID";
    $result = mysql_query($query) or die(mysql_error());
         
         while($row = mysql_fetch_array($result)){
           
         echo  $row['image_id'] ;
         echo  $row['locatie'] ;
         echo  $row['prijs'];
         echo  $row['type'];
         echo  $row['kamers'];
         echo  $row['oppervlakte'] . ' m²';
         echo  $row['ligging'];
         echo  $row['eigenschappen'];
      }
}

mysql_close($con);

?>

Adib · December 18, 2011

I've tried your last script but didn't work

I use this script and everything works fine except when i select something for $prijsVanOptions and $prijsTotOptions this script shows the result of $prijsTotOption. It has to show the result between $prijsVanOptions and $prijsTotOptions.

<?php

// Search.php

include ('config.php');

//Function to generate select options from a query

function querySelectOptions($query)

{

//Create default option

$optionsHTML = "<option value=''>-----</option>\n";

//Run query

$result = mysql_query ($query);

if(!$result) { return $optionsHTML; }

//Process results

while($row = mysql_fetch_array($result))

{

$optionsHTML .= "<option value='{$row[1]}'>{$row[1]}</option>\n";

}

return $optionsHTML;

}

//Create locatie options

$locatieOptions = querySelectOptions("SELECT ID, locatie FROM locatie");

//Create prijsVan options

$prijsVanOptions = querySelectOptions("SELECT ID, prijs FROM prijs where prijs >= prijs");

//Create prijsTot options

$prijsTotOptions = querySelectOptions("SELECT ID, prijs FROM prijs where prijs <= prijs");

//Create ligging options

$liggingOptions = querySelectOptions("SELECT ID, ligging FROM ligging");

//Create oppervlakte options

$oppervlakteOptions = querySelectOptions("SELECT ID, oppervlakte from oppervlakte");

//Create kamers options

$kamersOptions = querySelectOptions("SELECT ID, kamers from kamers");

//Create woning options

$woningOptions = querySelectOptions("SELECT ID, type from woning");

?>

<?php echo $locatieOptions; ?>

</select><br>

<?php echo $prijsVanOptions; ?>

</select><br>

<?php echo $prijsTotOptions; ?>

</select><br>

<?php echo $liggingOptions; ?>

</select><br>

<?php echo $oppervlakteOptions; ?>

</select><br>

<?php echo $kamersOptions; ?>

</select><br>

<?php echo $woningOptions; ?>

</select><br>

<?php

//Result.php

require_once ('config.php');

function postError(){

print '<br><br><p> <h1>Je hebt geen keuze gemaakt!</h1>';

print '<h2>Maak een keuze!</h2>';

}

$query = "SELECT DISTINCT * FROM huur, prijs, locatie, ligging, kamers, woning, oppervlakte, eigenschappen where huur.prijs_id = prijs.ID AND huur.locatie_id = locatie.ID AND huur.ligging_id = ligging.ID AND huur.kamers_id = kamers.ID AND huur.type_id = woning.ID AND huur.oppervlakte_id = oppervlakte.ID AND huur.eigenschappen_id = eigenschappen.ID ";

function processConditions($postConditions)

{

$conditionsAry = array();

foreach($postConditions as $field => $value)

{

$value = trim($value);

if(!empty($value))

{

$value = mysql_real_escape_string($value);

$conditionsAry[] = "`{$field}` = '{$value}'";

}

return $conditionsAry;

}

//Process the conditions that were posted

$conditionsArray = processConditions($_POST['condition']);

// Error: indien geen keuze gemaakt show deze error

if(count($conditionsArray)==0)

{

echo "<center><font color='#666'> " . postError() . "</font></center>\n";

}

else

{

//Create / Execute query

$query .= " AND " . implode(' AND ', processConditions($_POST['condition']));

$query .= " ORDER BY prijs.ID";

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

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

echo $row['image_id'];

echo $row['locatie'];

echo $row['prijs'];

echo $row['type'];

echo $row['kamers'];

echo $row['oppervlakte'] . ' m²';

echo $row['ligging'];

echo $row['eigenschappen'];

}

?>

Sign In

Display the result of large select_form with many conditions

Recommended Posts

Adib

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Adib

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Adib

Link to comment

Share on other sites

Psycho

Link to comment

Share on other sites

Adib

Link to comment

Share on other sites

Join the conversation

Browse

Activity

Important Information