0

Ok I am not getting any other errors in php or mysql but I keep getting returned Query was empty in my table. Any help is appreciated, if anybody needs more info on the code just let me know. This is basically a multi search tool for a huge database to reduce down search results based on user input in the form. I have been beating my head on the desk for about 8 hours now, I just need some more opinions on what is possibly wrong here.

echo '<table>
                <form action="index2.php" method="POST" name="form1" id="form1">
                <tr>
                <td colspan="2"><span style="color:#ff0000;">SEARCH FEATURE IS BEING WORKED ON</span></td>
                </tr>

                <tr>
                <td>City:</td>
                <td><input type="text" name="city" value="'.$_REQUEST['city'].'"></td>
                <td>State:</td>
                <td><select name="state"><option value="">Choose State</option>';

                $sql2 = "SELECT * FROM property GROUP BY state ORDER BY state ASC";
                $sql_result2 = mysql_query($sql2) or die(mysql_error());
                while ($row2 = mysql_fetch_assoc($sql_result2)) {
                echo "<option value='".$row2["state"]."'".($row2["state"]==$_REQUEST["state"] ? " selected" : "").">".$row2["state"]."</option>";
                }

                echo '</select></td>
                <td>County:</td>
                <td><input type="text" name="county" value="'.$_REQUEST['county'].'"></td>
                </tr>

                <tr>
                <td>Status:</td>
                <td><input type="text" name="status" value="'.$_REQUEST['status'].'"></td>
                <td>Project #:</td>
                <td><input type="text" name="pid" value="'.$_REQUEST['pid'].'"></td> 
                <td>Bank Officer:</td>
                <td><select name="bank_officer"><option value="">Choose Bank Officer';

                //$get_bank_officer = "Bank Officer";
                $sql3 = "SELECT * FROM property GROUP BY bank_officer ORDER BY bank_officer ASC";
                $sql_result3 = mysql_query($sql3) or die(mysql_error());
                while ($row3 = mysql_fetch_assoc($sql_result3)) {
                echo "<option value='".$row3["bank_officer"]."'".($row3["bank_officer"]==$_REQUEST["bank_officer"] ? " selected" : "").">".$row3["bank_officer"]."</option>";
                }

                echo '</select></td>
                </tr>

                <tr>
                <td colspan="6" align="right"><input type="reset" value="Clear Filters" onClick="clear_filters()"><input type="submit" name="submit" value="Search"></td>
                </form>
                </table>';

                echo '</div>';

    echo '<div style="overflow:auto; margin-bottom:20px;"><table class="sortable">
            <tr>
            <!--<th><input type="checkbox" name="check" class="checkall" /></th>-->
            <th>Property Name</th>
            <th>Address</th>
            <th>Project Name</th>
            <th>Project Number</th>
            <th>Project Description</th>
            <th>City</th>
            <th>State</th>
            <th>County</th>
            <th>Zipcode</th>
            <th>Status</th>
            <th>Current List Price</th>
            <th>Original List Price</th>
            <th>Listing Expiration</th>
            <th>Agent</th>
            <th>Bank Officer</th>
            <th>Pricing History 1</th>
            <th>Pricing History 2</th>
            <th>Pricing History 3</th>
            </tr>';

    if ($_REQUEST["city"]<>"") {
        $sqla = " AND (city LIKE '%".mysql_real_escape_string($_REQUEST["city"])."%')"; 
    }
    else if ($_REQUEST["state"]<>"") {
        $sqlb = " AND state='".mysql_real_escape_string($_REQUEST['state'])."'";    
    }
    else if ($_REQUEST["county"]<>"") {
        $sqlc = " AND (county LIKE '%".mysql_real_escape_string($_REQUEST["county"])."%')"; 
    }
    else if ($_REQUEST["status"]<>"") {
        $sqld = " AND (status LIKE '%".mysql_real_escape_string($_REQUEST["status"])."%')"; 
    }
    else if ($_REQUEST["pid"]<>"") {
        $sqle = " AND (asset_number LIKE '%".mysql_real_escape_string($_REQUEST["pid"])."%')";  
    }
    else if ($_REQUEST["bank_officer"]<>"") {
        $sqlf = " AND bank_officer='".mysql_real_escape_string($_REQUEST["bank_officer"])."'";  
    }
    else {
        $sql = "SELECT * FROM property WHERE id>0".$sqla.$sqlb.$sqlc.$sqld.$sqle.$sqlf;
    }

    $sql_result = mysql_query($sql) or die(mysql_error());
    if (mysql_num_rows($sql_result) > 0) {
    while ($row4 = mysql_fetch_assoc($sql_result)) {

            echo '<tr>
            <!--<td><input type="checkbox" name="check" /></td>-->
            <td><a href="overview.php?property_id='.$row4['property_id'].'">'.$row4['property_name'].'</a></td>
            <td>'.$row4['address'].'</td>
            <td>'.$row4['project_name'].'</td>
            <td>'.$row4['asset_number'].'</td>
            <td>'.$row4['project_description'].'</td>
            <td>'.$row4['city'].'</td>
            <td>'.$row4['state'].'</td>
            <td>'.$row4['county'].'</td>
            <td>'.$row4['zip'].'</td>
            <td>'.$row4['status'].'</td>
            <td>$'.$row4['listing_price'].'</td>
            <td>$'.$row4['original_price'].'</td>
            <td>'.$row4['listing_expires'].'</td>
            <td>'.$row4['agent'].'</td>
            <td>'.$row4['bank_officer'].'</td>
            <td>$'.$row4['listing_price1'].' | '.$row4['listing_price1_date'].'</td>
            <td>$'.$row4['listing_price2'].' | '.$row4['listing_price2_date'].'</td>
            <td>$'.$row4['listing_price3'].' | '.$row4['listing_price3_date'].'</td>
            </tr>';      
        }
    } 

    else {      
    echo '<tr><td colspan="19" style="background-color:#ff0000;"><span style="color:#fff;"><strong>No results found for your search criteria, please refine your search parameters and try again.</strong></span></td>';
    }   
    echo '</table></div>';
3
Contributors
5
Replies
9
Views
5 Years
Discussion Span
Last Post by smantscheff
0

I have written line, 72 to 92 again , you should not if else structure.

        <?php
        if ($_REQUEST["city"]<>"") {
                $sqla = " AND (city LIKE '%".mysql_real_escape_string($_REQUEST["city"])."%')"; 
            }
            if ($_REQUEST["state"]<>"") {
                $sqlb = " AND state='".mysql_real_escape_string($_REQUEST['state'])."'";    
            }
            if ($_REQUEST["county"]<>"") {
                $sqlc = " AND (county LIKE '%".mysql_real_escape_string($_REQUEST["county"])."%')"; 
            }
            if ($_REQUEST["status"]<>"") {
                $sqld = " AND (status LIKE '%".mysql_real_escape_string($_REQUEST["status"])."%')"; 
            }
            if ($_REQUEST["pid"]<>"") {
                $sqle = " AND (asset_number LIKE '%".mysql_real_escape_string($_REQUEST["pid"])."%')";  
            }
            if ($_REQUEST["bank_officer"]<>"") {
                $sqlf = " AND bank_officer='".mysql_real_escape_string($_REQUEST["bank_officer"])."'";  
            }

            $sql = "SELECT * FROM property WHERE id>0".$sqla.$sqlb.$sqlc.$sqld.$sqle.$sqlf;
    ?>
0

All I got to say is you the man! Worked like a charm thank you tons!

0

You don't even need any IF for your condition, because like '%%' matches just all content, so you don't have to test for empty variables:

$sql = sprintf( "SELECT * FROM property WHERE id > 0 
and city like '%%%s%%'
and state like '%%%s%%'
and county like '%%%s%%'
and status like '%%%s%%'
and bank_officer like '%%%s%%'"
, mysql_real_escape_string($_REQUEST["city"])
, mysql_real_escape_string($_REQUEST["state"])
, mysql_real_escape_string($_REQUEST["county"])
, mysql_real_escape_string($_REQUEST["status"])
, mysql_real_escape_string($_REQUEST["bank_officer"])
)
0

to smartscheff, but that may query unnessary fields. like if city is empty, but still it will search city with %.
So its better to check which columns are needed in search.

0

@urtrivedi: It's a question of coding efficiency versus runtime efficiency. In small projects I prefer the former. So I do not claim that my suggestion is better than yours, but it has an other focus.

This question has already been answered. 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.