MySQL Search with Multiple Select Menus & Text Search

[TMacFarlane]

This is a very complicated problem. I have an existing web page with multiple drop-down fields-- now I want to add a keyword search text field. I have managed to create a page with the text input only, and that works fine. The trouble comes when I try to add it to the other fields.

The live page is here (sorry, the development page is behind a login):

http://www.technologistsinc.com/Projects/

The original page was developed through DreamWeaver using the Recordset function. Once I learned a bit more about databases, I upgraded it to mysqli. Here is the simplified code:

//database connection
<?php require_once('../Connections/projectConn.php'); ?>

//Main database query
$query_projTbl = "SELECT *, DATE_FORMAT(project_descriptions.proj_date_final, '%M %Y') AS us_format FROM project_descriptions 
                JOIN ...";
//Bunch of irrelevant JOIN commands to other tables

//Set variable for WHERE clause
$where = false;

//Basic loop that all dropdowns go through. Imagine repeating
$loc='project_descriptions.locat_id';
//If the location field contains a variable
if(isset($_GET['loc'])) {
    $loc = $_GET['loc'];
    $query_projTbl .= " WHERE project_locations.locat_id=$loc";
    $where = true;
}

$client='project_owner.proj_owner_id';
//If the client field contains a variable
if(isset($_GET['client'])) {
    $client = $_GET['client'];
    if ($where) {
        $query_projTbl .= " AND ";
    } else {
        $query_projTbl .= " WHERE ";
        $where = true;
    }
    $query_projTbl .= "project_owner.proj_owner_id=$client";
}
// ... Here is the Text field
if(isset($_GET['txt_search'])) {
    $txt_search=$_GET['txt_search'];
    if ($where) {
        $query_projTbl .= " AND ";
    } else {
        $query_projTbl .= " WHERE ";
        $where = true;
    }
    $query_projTbl .= "MATCH (project_descriptions.proj_desc) AGAINST ('%$txt_search%' IN BOOLEAN MODE)";
}

//Tidy up
$query_projTbl .= " ORDER BY proj_date_final DESC";
$projTbl = mysqli_query($projectConn, $query_projTbl) or die(mysqli_error($projectConn));
$row_projTbl = mysqli_fetch_assoc($projTbl);
$totalRows_projTbl = mysqli_num_rows($projTbl);

The text input is named "txt_search" of course.

Basically, two kinds of weird things happen:

1. The fields don't work together. I get a result only if I enter something into the text field.

2. I changed "AND" in the text field if... statement to "OR", and then the drop-downs worked only, or the text field worked only, but not both. Changing all of the if... statements to "OR" just made it send all items for every search.

I cannot even think of what track to go in--any suggestions would be heavily appreciated (not to mention any advice on writing this whole application better.)

[pritaeas]

Debug. Output your variables and the resulting query. Then you can test with phpMyAdmin against your database. It's quite likely something small you've overlooked.

Apart from that, I prefer a construct like this:

$where = array ();

if (isset($_GET['loc'])) 
{
    $loc = $_GET['loc'];
    $where[] = "project_locations.locat_id = $loc";
}

if (isset($_GET['client'])) 
{
    $client = $_GET['client'];
    $where[] = "project_owner.proj_owner_id = $client";
}

if (isset($_GET['txt_search'])) 
{
    $txt_search = $_GET['txt_search'];
    $where[] = "MATCH (project_descriptions.proj_desc) AGAINST ('%$txt_search%' IN BOOLEAN MODE)";
}

if (count($where) > 0)
{
    $query_projTbl .= ' WHERE ' . implode(' AND ', $where);
}

[TMacFarlane]

This is very elegant! I will do this. Unfortunately, I learned PHP/MySQL by following in a book about DreamWeaver, and I know I learned eerything the wrong way. I've tried a couple of small debugging practices from tuorials, but could never make much sense of it. Could you possibly point me at some resources to learn more about making debugging a more constant part of my workflow?

[pritaeas]

Old style debugging is just echo'ing out the variables and such at key parts of the code. In my MySQLi code snippet you can see how I'd use a variable to enable additional debug information (something that should be disabled in production code).

Next to that, always make sure all error features are turned on in your PHP.INI file while debugging. Show all notices and warnings, and don't forget to log to file too.

An easier way is to use a decent IDE which will provide integrated debugging. Then you can just step through the code and inspect all variables.

Unfortunately, I do not know any decent resources about PHP debugging in general.

This thread shows some available editors.

[TMacFarlane]

This is super awesome! Thanks so much!

You mentioned that I could do something in PHPMyAdmin-- can you elaborate?

[pritaeas]

Well, if you output the query after it's been built in code, you can copy it, then open phpMyAdmin (assuming you have it), copy it there and execute it. Then you can see quickly what happens. Any errors, or the data that should be returned.

[TMacFarlane]

I did this in myPHPAdmin lasts night, and it worked fine. Haven't done the debugging from you code snippet method yet, but I suspect it will turn out the same way. The queries themselves seem fine.

Theoretically, it SHOULD work; practically, no. My current theory is that the text input field is still sending a value, even when it is empty. The text search algorithm in MySQL is throwing back a "no results" because the number is too low (I don't know the exact terminology.) So I am pretty sure it is most likely a PHP problem.

Is there a way I can script in a default value if the text input field is not used?

[pritaeas]

the text input field is still sending a value, even when it is empty.

Yes it does.

if (empty($txt_search)) {
    $txt_search = 'whatever you like';
}

[TMacFarlane]

No... I tried something similar before: $txt_search = '';

Declaring that variable would give it a value until it was over-ridden by the form data. In this case, a miss is as good as a mile. Having nothing in there is the same as if it was never there, but I cnnot put something in there, unless it is a 3-letter word that exists in every single table entry.

I am beginning to believe it cannot be done. That may be why I have never encountered a database search form containing both drop-down lists and text searches.

[pritaeas]

Am positive it can be done. There must be something missing. The other two, loc and client are those integers or strings?

[TMacFarlane]

The other two, loc and client are those integers or strings?

Not quite know how to answer that. I believe the form variables are all strings (I seem to remember the tutorial said all the variables had to be strings) but they are integers in that they reference the foreign key (the Db is MyISAM).

It's way more complicated than that, though. Two drop-down fields are, well, special. There is a date range variable for the "date" drop-down, and a cost range variable from the "cost" drop-down. Getting the cost field straight was also a very trying excersize. I am going to hav to post the whole thing here:

<?php require_once('/Db/projectConn.php'); ?>

<?php
//Formats the date
date_default_timezone_set('America/New_York');
$todays_date = date("Y-m-d H:i:s");

//Main database query
$query_projTbl = "SELECT *, DATE_FORMAT(project_descriptions.proj_date_final, '%M %Y') AS us_format FROM project_descriptions 
                JOIN project_locations ON project_descriptions.locat_id=project_locations.locat_id 
                JOIN project_owner ON project_descriptions.owner_id=project_owner.proj_owner_id 
                JOIN proj_scope ON project_descriptions.scope_id=proj_scope.proj_scope_id 
                JOIN proj_type ON project_descriptions.type_id=proj_type.proj_type_id";

//Set variable for WHERE clause
$where = array();

//If the location field contains a variable
if(isset($_GET['loc'])) {
    $loc = $_GET['loc'];
    $where[] .= "project_locations.locat_id=$loc";
}

//If the client field contains a variable
if(isset($_GET['client'])) {
    $client = $_GET['client'];
    $where[] .= "project_owner.proj_owner_id=$client";
}

//If the type field contains a variable
if(isset($_GET['type'])) {
    $type = $_GET['type'];
    $where[] .= "proj_type.proj_type_id=$type";
}

//Grabs the date from the dropdown list
if(isset($_GET['dates'])) {
    $dates=$_GET['dates'];
    $where[] .= "project_descriptions.proj_date_final BETWEEN $dates";
}

//Grabs the cost from the dropdown list
if(isset($_GET['cost'])) {
    $cost=$_GET['cost'];
    $where[] .= "project_descriptions.proj_cost BETWEEN $cost";
}

//Grabs the content from the search box
if(isset($_GET['txt_search'])) {
    $txt_search=$_GET['txt_search'];
    $where[] .= "MATCH (project_descriptions.proj_desc) AGAINST ('%$txt_search%' IN BOOLEAN MODE)";
}

//Tidy up
if (count($where) > 0) {
    $query_projTbl .= " WHERE " . implode(" AND ", $where);
}
$query_projTbl .= " ORDER BY proj_date_final DESC";
$projTbl = mysqli_query($projectConn, $query_projTbl) or die(mysqli_error($projectConn));
$row_projTbl = mysqli_fetch_assoc($projTbl);
$totalRows_projTbl = mysqli_num_rows($projTbl);

//Location dropdown menu
mysqli_select_db($projectConn, $database_projectConn);
$query_projLocat = "SELECT * FROM project_locations ORDER BY project_locations.locat_location ASC";
$projLocat = mysqli_query($projectConn, $query_projLocat) or die(mysqli_error($projectConn));
$row_projLocat = mysqli_fetch_assoc($projLocat);
$totalRows_projLocat = mysqli_num_rows($projLocat);

//Client dropdown menu
mysqli_select_db($projectConn, $database_projectConn);
$query_projOwner = "SELECT * FROM project_owner ORDER BY project_owner.proj_owner_name ASC";
$projOwner = mysqli_query($projectConn, $query_projOwner) or die(mysqli_error($projectConn));
$row_projOwner = mysqli_fetch_assoc($projOwner);
$totalRows_projOwner = mysqli_num_rows($projOwner);

//Scope dropdown menu
mysqli_select_db($projectConn, $database_projectConn);
$query_projScope = "SELECT * FROM proj_scope";
$projScope = mysqli_query($projectConn, $query_projScope) or die(mysqli_error($projectConn));
$row_projScope = mysqli_fetch_assoc($projScope);
$totalRows_projScope = mysqli_num_rows($projScope);

//Type Dropdown Menu
mysqli_select_db($projectConn, $database_projectConn);
$query_projType = "SELECT * FROM proj_type";
$projType = mysqli_query($projectConn, $query_projType) or die(mysqli_error($projectConn));
$row_projType = mysqli_fetch_assoc($projType);
$totalRows_projType = mysqli_num_rows($projType);

?>

Now, here is the form itself:

          <form action='<?php echo $_SERVER['../PHP_SELF']; ?>' method='get' name='form_filter' id="searchForm"> 

                <div class="selectBox">
                    <label for="loc">Location</label>
                    <select name="loc"> 
                        <option value="project_descriptions.locat_id">All Locations</option>
                        <?php do { ?>
                        <option value="<?php echo $row_projLocat['locat_id']; ?>"><?php echo $row_projLocat['locat_location']; ?></option> 
                        <?php } while ($row_projLocat = mysqli_fetch_assoc($projLocat)); ?>
                    </select>
                </div>

                <div class="selectBox">
                    <label for="dates">Date Completed</label>
                    <select name="dates"> 
                        <option value="'2007-01-01' AND '2013-12-31'">All Dates</option>
                        <option value="'2007-01-01' AND '2007-12-31'">2007</option>
                        <option value="'2008-01-01' AND '2008-12-31'">2008</option>
                        <option value="'2009-01-01' AND '2009-12-31'">2009</option>
                        <option value="'2010-01-01' AND '2010-12-31'">2010</option>
                        <option value="'2011-01-01' AND '2011-12-31'">2011</option>
                        <option value="'2012-01-01' AND '2012-08-01'">2012</option>
                        <option value="'2012-08-02' AND '2013-12-31'">Ongoing</option>
                      </select>
                </div>

                <div class="selectBox">
                    <label for="type">Type</label>
                    <select name="type"> 
                            <option value="proj_type.proj_type_id">All Types</option>
                        <?php do { ?>
                            <option value="<?php echo $row_projType['proj_type_id']; ?>"><?php echo $row_projType['proj_type']; ?></option> 
                        <?php } while ($row_projType = mysqli_fetch_assoc($projType)); ?>
                      </select>
                </div>

                <div class="selectBox">
                    <label for="client">Client</label>
                    <select name="client"> 
                        <option value="project_owner.proj_owner_id">All Clients</option>
                        <?php do { ?>
                        <option value="<?php echo $row_projOwner['proj_owner_id']; ?>"><?php echo $row_projOwner['proj_owner_name']; ?></option> 
                        <?php } while ($row_projOwner = mysqli_fetch_assoc($projOwner)); ?>
                    </select>
                </div>

                 <div class="selectBox">
                    <label for="cost">Cost</label>
                    <select name="cost"> 
                        <option value="'0.00' AND '100000000.00'">All Costs</option>
                        <option value="'0.00' AND '1000000.00'">Below $1,000,000.000</option>
                        <option value="'1000000.00' AND '3000000.00'">$1,000,000&ndash;$3,000,000</option>
                        <option value="'3000000.00' AND '5000000.00'">$3,000,000&ndash;$5,000,000</option>
                        <option value="'5000000.00' AND '10000000.00'">$5,000,000&ndash;$10,000,000</option>
                        <option value="'10000000.00' AND '15000000.00'">$10,000,000&ndash;$15,000,000</option>
                        <option value="'15000000.00' AND '20000000.00'">$15,000,000&ndash;$20,000,000</option>
                        <option value="'20000000.00' AND '40000000.00'">$20,000,000&ndash;$40,000,000</option>
                        <option value="'60000000.00' AND '100000000.00'">$40,000,000+</option>
                    </select>
                </div>

                <label for="txt_search">Keyword Search</label>
                <input name="txt_search" type="text" value="" class="txtSearch">

                <input type='submit' value = 'Search' class="formButn"> <input type="reset" value="Reset" onClick="window.location='/Projects/index.php'" class="formButn"> 

           </form>

You can probaly see how complicated the "cost" range is. Managing the quotation marks was the key to getting this thing working in the first place.

[pritaeas]

It looks fine, am not seeing it (without testing). The only issue could be single quotes in the text search field.

[TMacFarlane]

Not resolved yet after all.

[TMacFarlane]

This did it:

//Grabs the content from the search box
if(empty($_GET['txt_search'])) {
    unset($where['txt_search']) ;
} elseif(isset($_GET['txt_search'])) {
    $txt_search=$_GET['txt_search'];
    $where[] .= "MATCH (project_descriptions.proj_desc) AGAINST ('%$txt_search%' IN BOOLEAN MODE)";
}

This removes the text field from the array if it is empty. Everything works now (although the boolean search is not a great as I'd like it to be ;)

Thanks so very much for hanging in there with me through this. I really appreciate your feedback & guidance.

[pritaeas]

Glad you got it working.