How do I display / filter search results with php / mysql

Question

I'm very new to working with php / mysql coding, and I've got a slight problem with displaying results that are linked to an sql database.

At the moment people search using the below code - which works fine - it's a drop down select box, but you can only select one option. I want it so the items are check boxes and you can select more than one item.

I've included below the code that's used for when people input the data to the database - which are check boxes - and I have tried replacing the 'drop down select code' with this but it doesn't work.

Does anybody know what code I have to use to replace the 'drop down select code' so that checkboxes are viewable and you can filter more than one item - I have also included the 'results page code', which displays the results and I'm thinking that 'ClientStage' needs adding to the 'check box code' somewhere.

Sorry about my lack of knowledge with this and would be grateful for some help?

DROP DOWN SELECT CODE

<select name="ClientStage" id="ClientStage">
            <option value=""></option>
            <?php
        include 'Easyspace.php';
        $sql = 'SELECT * FROM `clienttype`;';
        $rs = mysql_query($sql, $conn) or die ("error with sql query ".$sql);

        while ($row = mysql_fetch_array ($rs)){
            $ClienttypeID = $row["ClienttypeID"];
            $Clienttype = $row["Clienttype"];

            echo '<option value="' .$ClienttypeID. '">' .$Clienttype. '</option>'; 
        }
        ?>
          </select></span>

CHECK BOX CODE

<table width="100%" border="0" cellspacing="1">
    <?php
    $side=1;  
    $sql = 'SELECT * FROM `clienttype`;';
    $rs = mysql_query($sql, $conn) or die ("error with sql query ".$sql);

    while ($row = mysql_fetch_array ($rs)){
        $ClienttypeID = $row["ClienttypeID"];
        $Clienttype = $row["Clienttype"];

        if ($side == 1){
            $side = 2;
            echo '<tr>';
            echo '<td><span class="RPATtext"><input type="checkbox" name="Clients[]" value="' .$ClienttypeID. '"> ' .$Clienttype. '</div></td>';

        } else {
            $side = 1;
            echo '<td><span class="RPATtext"><input type="checkbox" name="Clients[]" value="' .$ClienttypeID. '"> ' .$Clienttype. '</div></td>';
            echo '<option value="' .$ClienttypeID. '">' .$Clienttype. '</option>'; 
        }

    }
    ?>
    </table>

RESULTS PAGE CODE

 <?php

    $Country = $_POST['Country'];
    $County = $_POST['County'];
    $ClientStage = $_POST['ClientStage'];
    $HealthIssues = $_POST['HealthIssues'];

    include 'Easyspace.php';
    $sql = "SELECT * FROM `therapists` WHERE ";

    if ($Country){
        $sql .= "`Country` = '$Country'";
    }

    if ($County){
        if ($Country){
            $sql .= ' AND ';    
        }
        $sql .= "`County` = '$County'";
    }

    if ($ClientStage){
        if ($Country or $County){
            $sql .= ' AND ';    
        }
        $sql .= "FIND_IN_SET('$ClientStage', Client_typeID)";
    }

    if ($HealthIssues){
        if ($Country or $County or $ClientStage){
            $sql .= ' AND ';    
        }
        $sql .= "FIND_IN_SET('$HealthIssues', IssuesID)";
    }
    // echo $sql;

    $rs = mysql_query($sql, $conn) or die ("error with sql query ".$sql);

    while ($row = mysql_fetch_array ($rs)){
        $TherapistID = $row['TherapistID'];
        $Title = $row['Title'];
        $FirstName = $row['First Name'];
        $LastName = $row['Last Name'];
        $PostCode = $row['PostCode'];

        echo '<tr>';
        echo '<td valign="middle" bgcolor="#D9E5C3" class="bodycopy">' .$Title. '</td>';
        echo '<td valign="middle" bgcolor="#D9E5C3" class="bodycopy">' .$FirstName. '</td>';
        echo '<td valign="middle" bgcolor="#D9E5C3" class="bodycopy">' .$LastName. '</td>';
        echo '<td valign="middle" bgcolor="#D9E5C3" class="bodycopy">' .$PostCode. '</td>';
        echo '<td height="34" valign="middle" bgcolor="#D9E5C3"><p class="bodycopy"><a href="Therapist.php?ID=' .$TherapistID. '">View more details</a></p></td>';
        echo '</tr>';

    }

    ?>

Please, don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial. — Kermit
– Kermit, Commented Jan 15, 2013 at 16:06

Louis XIV · Accepted Answer · 2013-01-15 16:20:32Z

1

Here's your new results code. I hope it's what you were looking for. Pay attention to your code: try to puts vars in lowercase ($Country has to be $country, try to use PDO for MySQL, etc...).

<?php

$Country = $_POST['Country'];
$County = $_POST['County'];
/* change here */
$ClientStage = $_POST['Clients'];
$HealthIssues = $_POST['HealthIssues'];

include 'Easyspace.php';
$sql = "SELECT * FROM `therapists` WHERE ";

if ($Country){
    $sql .= "`Country` = '$Country'";
}

if ($County){
    if ($Country){
        $sql .= ' AND ';    
    }
    $sql .= "`County` = '$County'";
}

if ($ClientStage){
    if ($Country or $County){
        $sql .= ' AND ';    
    }
    /* change here */
    $sql .= 'Client_typeID IN (';
    foreach($ClientStage as $i => $id) {
       if ($i !== 0)
           $sql .=  ',';
       $sql .=  "'" . $id . '"';
    }
    $sql .= ')';
}

if ($HealthIssues){
    if ($Country or $County or $ClientStage){
        $sql .= ' AND ';    
    }
    $sql .= "FIND_IN_SET('$HealthIssues', IssuesID)";
}
// echo $sql;

$rs = mysql_query($sql, $conn) or die ("error with sql query ".$sql);

while ($row = mysql_fetch_array ($rs)){
    $TherapistID = $row['TherapistID'];
    $Title = $row['Title'];
    $FirstName = $row['First Name'];
    $LastName = $row['Last Name'];
    $PostCode = $row['PostCode'];

    echo '<tr>';
    echo '<td valign="middle" bgcolor="#D9E5C3" class="bodycopy">' .$Title. '</td>';
    echo '<td valign="middle" bgcolor="#D9E5C3" class="bodycopy">' .$FirstName. '</td>';
    echo '<td valign="middle" bgcolor="#D9E5C3" class="bodycopy">' .$LastName. '</td>';
    echo '<td valign="middle" bgcolor="#D9E5C3" class="bodycopy">' .$PostCode. '</td>';
    echo '<td height="34" valign="middle" bgcolor="#D9E5C3"><p class="bodycopy"><a href="Therapist.php?ID=' .$TherapistID. '">View more details</a></p></td>';
    echo '</tr>';

}

?>

answered Jan 15, 2013 at 16:20

Louis XIV

2,22413 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1635710 Over a year ago

Thanks for the above code Louis XIV... Unfortunately it didn't work.

Louis XIV Over a year ago

try removing $sql .= "'" . $id . '"'; and replacing it with $sql .= $id; if it doesn't work, put a var_dump of $_POST please and echo your SQL query.

Collectives™ on Stack Overflow

How do I display / filter search results with php / mysql

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related