Search function using a single query

[kavionly]

I have a form that pulls out all the data from database . I would like to search using a single query, currently i am using if condition which i have shown below its fetching all the data but i want to run the query based on selection. Pls can anyone help me
Here is my form
test.php
<form method="post" action="search_action3.php" name="form3" >
<table >
<tr>
<td valign="bottom">Size</td>
<td ><select name="size" >
<?php
$result = mysql_query("SELECT DISTINCT size FROM jos_details order by size") or die(mysql_error());
?>
<option value="">All</option>
<?php
while($row = mysql_fetch_array( $result )) {?>
<Option value="<?php echo $row['size']; ?>"> <?php echo $row['size']; ?></option>
<?php
}
?>
</select>
</td>
</tr>
<td >GSM/MIC</td>
<td ><select name="gsm" >
<?php
$Result1=mysql_query("select DISTINCT gsm from jos_details order by gsm ");

?>
<option value=" ">All</option>
<?php
while($Row_Gsm = mysql_fetch_array($Result1))
{
?>
<Option value="<?php echo $Row_Gsm['gsm']; ?>"> <?php echo $Row_Gsm['gsm']; ?></option>
<?php
}
?>
</select>
</td>
</tr>
<tr>
<td >Colour</td>
<td ><select name="color" >
<option value="">All</option>
<?php

$Result3 = mysql_query("SELECT DISTINCT color FROM jos_details order by color") or die(mysql_error());
while($row3 = mysql_fetch_array( $Result3 )) {?>
<Option value="<?php echo $row3['color']; ?>" > <?php echo $row3['color']; ?></option>
<?php
}
?>
</select>
</td>
</tr>
<tr>
<td >Environmental<br /></td>
<td ><select name="envi" >
<option value="">All</option>
<?php

$Result4 = mysql_query("SELECT DISTINCT environmental FROM jos_details order by environmental") or die(mysql_error());
while($row4 = mysql_fetch_array( $Result4 )) {?>
<Option value="<?php echo $row4['environmental']; ?>" > <?php echo $row4['environmental']; ?></option>
<?php
}
?>
</select>
</td>
</tr>
<tr>
<td colspan="2" align="center" ><input type="image" name="btn_submit2" id="btn_submit2" src="submitbutton.jpg">
</td>
</tr>
</table>
</form>


search_action3.php

<?php
include "config.php";

if(!$_REQUEST['btn_submit1'])
{

$Prod_Size = $_POST['size'];

$Gsm = $_REQUEST['gsm'];

$Color = $_REQUEST['color'];

$Environment = $_REQUEST['envi'];

if($Prod_Size)
{
$where = "and size='$Prod_Size'";
}
else
{
$where = "";
}
if($Gsm)
{
$where = "and gsm='$Gsm'";
}
else
{
$where = "";

}

if($Color)
{
$where = "and color='$Color'";
}
else
{
$where = "";

}
if($Environment)
{
$where = "and environmental='$Environment'";
}
else
{
$where = "";

}

$sql_Check = "select * from jos_details where 1=1 $where group by category_id,sub_category_id";

$result_Check = mysql_query($sql_Check) or die(mysql_error());

while($Row_Check = mysql_fetch_array($result_Check))
{

echo $Row_Check['size'];
echo $Row_Check['gsm'];
}
}
?>

[Phill Pafford]

Haven't tested this but something like this maybe:

<form method="post" action="search_action3.php" name="form3">
    <table>
        <tr>
            <td valign="bottom">
                Size
            </td>
            <td>
                <select name="size">
                    <?php
                        $selectedSQL = "SELECT DISTINCT size FROM jos_details order by size";
                    ?>
                    <option value="">All</option>
                    <?php
                        echo displaySelected($selectedSQL);
                    ?>
                </select>
            </td>
        </tr>
        <td>
            GSM/MIC
        </td>
        <td>
            <select name="gsm">
                <?php
                    $selectedSQL = "select DISTINCT gsm from jos_details order by gsm ";
                ?>
                <option value=" ">All</option>
                <?php
                    echo displaySelected($selectedSQL);
                ?>
            </select>
        </td>
        </tr>
        <tr>
            <td>
                Colour
            </td>
            <td>
                <select name="color">
                    <option value="">All</option>
                    <?php
                        $selectedSQL = "SELECT DISTINCT color FROM jos_details order by color";
                        echo displaySelected($selectedSQL);
                    ?>
                </select>
            </td>
        </tr>
        <tr>
            <td>
                Environmental
                <br/>
            </td>
            <td>
                <select name="envi">
                    <option value="">All</option>
                    <?php

                        $selectedSQL = "SELECT DISTINCT environmental FROM jos_details order by environmental";
                        echo displaySelected($selectedSQL);
                    ?>
                </select>
            </td>
        </tr>
        <tr>
            <td colspan="2" align="center">
                <input type="image" name="btn_submit2" id="btn_submit2" src="submitbutton.jpg">
            </td>
        </tr>
    </table>
</form> search_action3.php
<?php
include "config.php";

if(!$_REQUEST['btn_submit1']) {
    $Prod_Size    = $_POST['size'];
    $Gsm        = $_REQUEST['gsm'];
    $Color        = $_REQUEST['color'];
    $Environment= $_REQUEST['envi'];

    if($Prod_Size) {
        $where = "and size='$Prod_Size'";
    } else {
        $where = "";
    }
    
    if($Gsm) {
        $where = "and gsm='$Gsm'";
    } else {
        $where = "";
    }
    
    if($Color) {
        $where = "and color='$Color'";
    } else {
        $where = "";
    }
    
    if($Environment) {
        $where = "and environmental='$Environment'";
    } else {
        $where = "";
    }
    
    $sql_Check = "select * from jos_details where 1=1 $where group by category_id,sub_category_id";
    $result_Check = mysql_query($sql_Check) or die(mysql_error());
    
    while($Row_Check = mysql_fetch_array($result_Check)) {
        echo $Row_Check['size'];
        echo $Row_Check['gsm'];
    }
} 

function displaySelected($selectedSQL) {
    $html = "";
    $counter = 0;
    $result = mysql_query($selectedSQL) or die("The website encountered a problem, sorry.");
    
    while($row = mysql_fetch_array($result)) {
        $html .= "<Option value='" . $row[$counter] . "'> " . $row[$counter] . "</option>" ;
        $counter++;
    }
    return $html;
}
?>

[kavionly]

thanks a lot it works fine