2
Contributors
14
Replies
34
Views
4 Years
Discussion Span
Last Post by pritaeas
0

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);
}

Edited by pritaeas

0

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?

Edited by TMacFarlane

0

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.

Edited by pritaeas

0

This is super awesome! Thanks so much!

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

0

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.

0

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?

0

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';
}
0

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.

0

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

0

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.

0

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

1

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.