Dynamic multiselect search

<?php

define("HOST", "localhost");

// Database user

define("DBUSER", "root");

// Database password

define("PASS", "password");

// Database name

define("DB", "csvdb");

// Database Error - User Message

define("DB_MSG_ERROR", 'Could not connect!<br />Please contact the site\'s administrator.');

############## Make the mysql connection ###########

$conn = mysql_connect(HOST, DBUSER, PASS) or die(DB_MSG_ERROR);

$db = mysql_select_db(DB) or die(DB_MSG_ERROR);

$query = mysql_query("

SELECT * 

FROM csvtb 

WHERE project_id, phase, building='".$_POST['value']."'

");

echo '<table>';

while ($data = mysql_fetch_array($query)) {

echo '

<tr>

<td style="font-size:18px;">'.$data["project_id"].'</td>

<td style="font-size:18px;">'.$data["phase"].'</td>

</tr>';

}

echo '</table>';

?> // thats search.php
<!DOCTYPE html>

<?php

$con = mysql_connect("localhost","root","password");

$db = mysql_select_db("csvdb",$con);

$get=mysql_query("SELECT * FROM csvtb WHERE sold = 'no'");

$option = '';

while($row = mysql_fetch_assoc($get))

{

$option1 .= '<option value = "'.$row['project_id'].'">'.$row['project_id'].'</option>';

$option2 .= '<option value = "'.$row['phase'].'">'.$row['phase'].'</option>';

$option3 .= '<option value = "'.$row['building'].'">'.$row['building'].'</option>';

$option4 .= '<option value = "'.$row['apartment_type'].'">'.$row['apartment_type'].'</option>';

$option5 .= '<option value = "'.$row['level'].'">'.$row['level'].'</option>';

$option6 .= '<option value = "'.$row['garden'].'">'.$row['garden'].'</option>';

}

?>

<html>

<head>

<meta charset="utf-8">

<title>Search our database</title>

<script type="text/javascript">

var GB_ROOT_DIR = "greybox/";

</script>

<link rel="stylesheet" type="text/css" href="css.css" media="screen">

<script type="text/javascript" src="greybox/AJS.js"></script>

<script type="text/javascript" src="greybox/AJS_fx.js"></script>

<script type="text/javascript" src="greybox/gb_scripts.js"></script>

<link href="greybox/gb_styles.css" rel="stylesheet" type="text/css" />

<!-- Load JQuery         

<script type="text/javascript" src="js/jquery/jquery.min.js"></script> -->

<!-- Load JQuery UI 

<script type="text/javascript" src="js/jquery/jquery-ui.min.js"></script> -->        

<!-- JQUERY FROM GOOGLE API -->

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

<script type="text/javascript">

$(function() {

$("#lets_search").bind('submit',function() {

var value = $('#str').val();

$.post('search.php',{value:value}, function(data){

$("#search_results").html(data);

});

return false;

});

});

</script>

</head>

<body>

<div id="main-left">

<div id="logo-left">

<div id="main-logo">

<img src="logo2.jpg" height="100%" width="100%"/>

<!--<div id="underlogo-text">SELECTOR</div>-->

</div>

</div>

<form role="form" action="" method="post">

<div id="form-left">

<select name="project_id">

<option disabled selected> Select a project </option>

<?php echo $option1; ?>

</select>

<select name="phase">

<option disabled selected> Select a phase</option>

<?php echo $option2; ?>

</select>

<select name="building">

<option disabled selected> Select a buidling </option>

<?php echo $option3; ?>

</select>

<select name="apartment_type">

<option disabled selected> Select an apartment </option>    

<?php echo $option4; ?>         

?>

</select>

<select name="level">

<option disabled selected> select a Level </option>

<?php echo $option5; ?>

</select>

<select name="garden">

<option disabled selected> select garden options </option>

<?php echo $option6; ?>

</select>

<div id="input-size">

<p>Size</p>

<input type="text" name="size1" class="input"/>

<input type="text" name="size2" class="input"/>

</div>

<div id="input-size">

<p>Bedrooms</p>

<input type="text" name="size1" class="input"/>

<input type="text" name="size2" class="input"/>

</div>

<div id="input-size">

<p>&nbsp;</p>

<input type="submit" value="send" name="send" id="send">

</div>

</form>

</div>

</div>

<div id="main-right">

<div id="right-result-container">

<div id="result">

<div id="span-container"><span>Results:</span><div style="width:100%;">&nbsp;</div></div>

<table id="results-table">

<thead>

<tr>

</tr>

</thead>

<tbody>

<div id="search_results"></div>

</tbody>

</table>

</div>

</div>

</div>

</body>

</html> // that's index.php

know i should use pdo and can be easily injected, will secure it later, for now, how to get the value so i can search it, through post, to be completely honest i haven’t slept since 2 days, i cant’ see what i’m doing wrong, hell if you tell me to write python in there and tell me to test it i will, can’t see nothing, the quality of my questions are usually better but today i’m tired and would love to get any help i can
get.

This clause doesnt make sense to a mysql database, for the record.

Reference your post values by the name attribute of the form element. ie: $_POST[‘level’] for the thing selected as the level, etc.

Yes and building your options from a loop of all records is going to have many duplicates. Also attempting to make a single query with unique results for all fields would be difficult to say the least. You can build arrays for each of these fields and use array_unique to sort out duplicates, then build select options. You have duplicate names for size1 and size2 used for size and I assume bathroom. I went ahead and changed form options to bedroom and bathroom on the sample I made. You also mentioned PDO and because some WHERE conditions would not use = for example bedrooms >= 2 I built an array that will convert POST names to the correct DB field and comparison operator, build sql query and also bind parameter value.

Anyway, this is just a single page sample that doesn’t use your JS request.
Also remember I am using fields bedrooms and bathrooms in this example.

<?php

define("HOST", "localhost");

// Database user
define("DBUSER", "root2");

// Database password
define("PASS", "password");

// Database name
define("DB", "csvdb");

$db = new PDO("mysql:host=".HOST.";dbname=".DB, DBUSER, PASS);

/*
echo "<pre>";
print_r($_POST);
echo "</pre>";
*/

//Search
$search_results = ''; 
$search = array(); 
$allowed = array(
      "project_id" => array("field" => "project_id", "comp" => " = ")
    , "phase" => array("field" => "phase", "comp" => " = ")
    , "building" => array("field" => "building", "comp" => " = ")
    , "apartment_type" => array("field" => "apartment_type", "comp" => " = ")
    , "level" => array("field" => "level", "comp" => " = ")
    , "garden" => array("field" => "garden", "comp" => " = ")
    , "bedrooms_a" => array("field" => "bedrooms", "comp" => " >= ")
    , "bedrooms_b" => array("field" => "bedrooms", "comp" => " <= ")
    , "bathrooms_a" => array("field" => "bathrooms", "comp" => " >= ")
    , "bathooms_b" => array("field" => "bathrooms", "comp" => " <= ")
    );
if(isset($_POST['send'])):
    $sql = "SELECT project_id, phase
    FROM csvtb";  
         
    foreach ($_POST as $key => &$value):
        if(array_key_exists($key,$allowed) && !empty($value)):
            $search[] = $allowed[$key]['field'] . $allowed[$key]['comp'] . ':' . $key;
        endif;
    endforeach;
    
    if(!empty($search)):
        $sql .= " WHERE ";
        $sql .= implode(" AND ",$search);
    endif;
    //echo $sql; 
    
    $query = $db->prepare($sql);
    
    foreach ($_POST as $key => &$value) { 
        if(array_key_exists($key,$allowed) && !empty($value)):
            $query->bindParam(':' . $key, $value);
        endif;
    }
    
    $query->execute();       
    
    $search_results .= '<table>'."\r";
    
    while($row = $query->fetch(PDO::FETCH_ASSOC)){
    
        $search_results .= '
        <tr>
        
            <td style="font-size:18px;">'.$row['project_id'].'</td>
            
            <td style="font-size:18px;">'.$row['phase'].'</td>
        
        </tr>'."\r";
    
    }
    
    $search_results .= '</table>';
endif;

//Grab data for form options
$project_ids = array();
$phases = array();
$buildings = array();
$apartment_types = array();
$levels = array();
$gardens = array();
$bedrooms = array();
$bathrooms = array();

$sql = "SELECT 
      project_id
    , phase
    , building
    , apartment_type
    , level
    , garden
    , bedrooms
    , bathrooms 
    FROM csvtb 
    WHERE sold = 'no'";


$query = $db->prepare($sql);
$query->execute();
while($row = $query->fetch(PDO::FETCH_ASSOC)){
    $project_ids[] = $row['project_id'];
    $phases[] = $row['phase'];
    $buildings[] = $row['building'];
    $apartment_types[] = $row['apartment_type'];
    $levels[] = $row['level'];
    $gardens[] = $row['garden'];
    $bedrooms[] = $row['bedrooms'];
    $bathrooms[] = $row['bathrooms'];
}
    $Qproject_ids = array_unique($project_ids);
    $Qphases = array_unique($phases);
    $Qbuildings = array_unique($buildings);
    $Qapartment_types = array_unique($apartment_types);
    $Qlevels = array_unique($levels);
    $Qgardens = array_unique($gardens);
    $Qbedrooms = array_unique($bedrooms);
    $Qbathrooms = array_unique($bathrooms);
    
    $project_ids = '';
    $phases = '';
    $buildings = '';
    $apartment_types = '';
    $levels = '';
    $gardens = '';
    $bedrooms_a = '';
    $bedrooms_b = '';
    $bathrooms_a = '';
    $bathrooms_b = '';

    foreach($Qproject_ids as $project_id):
        $selectedproject_id =(isset($_POST['project_id']) && $_POST['project_id'] == $project_id ? ' selected="selected"' : '');
        $project_ids .= '<option value = "'.$project_id.'"'.$selectedproject_id.'>'.$project_id.'</option>';
    endforeach;

    foreach($Qphases as $phase):                                                                    
        $selectedphase =(isset($_POST['phase']) && $_POST['phase'] == $phase ? ' selected="selected"' : '');
        $phases .= '<option value = "'.$phase.'"'.$selectedphase.'>'.$phase.'</option>'; 
    endforeach;
                    
    foreach($Qbuildings as $building):                                                                
        $selectedbuilding =(isset($_POST['building']) && $_POST['building'] == $building ? ' selected="selected"' : '');
        $buildings .= '<option value = "'.$building.'"'.$selectedbuilding.'>'.$building.'</option>'; 
    endforeach;
                    
    foreach($Qapartment_types as $apartment_type):                                                                    
        $selectedapartment_type =(isset($_POST['apartment_type']) && $_POST['apartment_type'] == $apartment_type ? ' selected="selected"' : '');
        $apartment_types .= '<option value = "'.$apartment_type.'"'.$selectedapartment_type.'>'.$apartment_type.'</option>'; 
    endforeach;
                    
    foreach($Qlevels as $level):                                                           
        $selectedlevel =(isset($_POST['level']) && $_POST['level'] == $level ? ' selected="selected"' : '');
        $levels .= '<option value = "'.$level.'"'.$selectedlevel.'>'.$level.'</option>';     
    endforeach;
                    
    foreach($Qgardens as $garden):                                                             
        $selectedgarden =(isset($_POST['garden']) && $_POST['garden'] == $garden ? ' selected="selected"' : '');
        $gardens .= '<option value = "'.$garden.'"'.$selectedgarden.'>'.$garden.'</option>'; 
    endforeach;
                        
    foreach($Qbedrooms as $bedroom):                                                             
        $selectedbedrooms_a =(isset($_POST['bedrooms_a']) && $_POST['bedrooms_a'] == $bedroom ? ' selected="selected"' : '');
        $bedrooms_a .= '<option value = "'.$bedroom.'"'.$selectedbedrooms_a.'>'.$bedroom.'</option>';    
    endforeach; 
                     
    foreach($Qbedrooms as $bedroom):                                                             
        $selectedbedrooms_b =(isset($_POST['bedrooms_b']) && $_POST['bedrooms_b'] == $bedroom ? ' selected="selected"' : '');
        $bedrooms_b .= '<option value = "'.$bedroom.'"'.$selectedbedrooms_b.'>'.$bedroom.'</option>';    
    endforeach; 
                    
    foreach($Qbathrooms as $bathroom):                                                                   
        $selectedbathrooms_a =(isset($_POST['bathrooms_a']) && $_POST['bathrooms_a'] == $bathroom ? ' selected="selected"' : '');
        $bathrooms_a .= '<option value = "'.$bathroom.'"'.$selectedbathrooms_a.'>'.$bathroom.'</option>'; 
    endforeach; 
                    
    foreach($Qbathrooms as $bathroom):                                                                   
        $selectedbathrooms_b =(isset($_POST['bathrooms_b']) && $_POST['bathrooms_b'] == $bathroom ? ' selected="selected"' : '');
        $bathrooms_b .= '<option value = "'.$bathroom.'"'.$selectedbathrooms_b.'>'.$bathroom.'</option>'; 
    endforeach;


?>
<!DOCTYPE html>
<html>

<head>

<meta charset="utf-8">

<title>Search our database</title>

<script type="text/javascript">

var GB_ROOT_DIR = "greybox/";

</script>

<link rel="stylesheet" type="text/css" href="css.css" media="screen">

<script type="text/javascript" src="greybox/AJS.js"></script>

<script type="text/javascript" src="greybox/AJS_fx.js"></script>

<script type="text/javascript" src="greybox/gb_scripts.js"></script>

<link href="greybox/gb_styles.css" rel="stylesheet" type="text/css" />

<!-- Load JQuery         

<script type="text/javascript" src="js/jquery/jquery.min.js"></script> -->

<!-- Load JQuery UI 

<script type="text/javascript" src="js/jquery/jquery-ui.min.js"></script> -->        

<!-- JQUERY FROM GOOGLE API -->

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>

<script type="text/javascript">

$(function() {

$("#lets_search").bind('submit',function() {

var value = $('#str').val();

$.post('search.php',{value:value}, function(data){

$("#search_results").html(data);

});

return false;

});

});

</script>

</head>

<body>

<div id="main-left">

<div id="logo-left">

    <div id="main-logo">
    
    <!-- <img src="logo2.jpg" height="100%" width="100%"/> -->
    
    <!--<div id="underlogo-text">SELECTOR</div>-->
    
    </div>

</div>

<form role="form" action="" method="post">

    <div id="form-left">
    
        <select name="project_id">
            <option value=""> Select a project </option>
            <?php echo $project_ids; ?>
        </select>
        
        <select name="phase">
            <option value=""> Select a phase</option>
            <?php echo $phases; ?>
        </select>
        
        <select name="building">
            <option value=""> Select a buidling </option>
            <?php echo $buildings; ?>
        </select>
        
        <select name="apartment_type">
            <option value=""> Select an apartment </option>    
            <?php echo $apartment_types; ?>
        </select>
        
        <select name="level">
            <option value=""> select a Level </option>    
            <?php echo $levels; ?>
        </select>
        
        <select name="garden">
            <option value=""> select garden options </option>    
            <?php echo $gardens; ?>
        </select>
        
        <div id="input-size">
        
            <p>Bedrooms</p>
            
            <select name="bedrooms_a">    
                <option value=""> min </option>        
                <?php echo $bedrooms_a; ?>    
            </select>
            
            <select name="bedrooms_b">    
                <option value=""> max </option>    
                <?php echo $bedrooms_b; ?>    
            </select>
        
        
            <p>Bathrooms</p>
            
            <select name="bathrooms_a">
            
            <option value=""> min </option>
            
            <?php echo $bathrooms_a; ?>
            
            </select>
            <select name="bathooms_b">
            
            <option value=""> max </option>
            
            <?php echo $bathrooms_b; ?>
            
            </select>
        
        
            <p>&nbsp;</p>
            
            <input type="submit" value="send" name="send" id="send">
        
        </div>
    </div>

</form>

</div>

<div id="main-right">

<div id="right-result-container">

<div id="result">

<div id="span-container"><span>Results:</span><div style="width:100%;">&nbsp;</div></div>



<div id="search_results"><?php echo $search_results;?></div>


</div>

</div>

</div>

</body>

</html>

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.