Hey guys,

So I've been devloping a TV Guide website that will allow you to fill out a variety of different fields, then searches the database for that information, and returns the results. The way I have it right now, the user has to fill out all of the fields in order for the search to work properly. I want to have it so even if the user only enters information for one field, it will ignore all of the empty fields, and still search the database.

Here is the code for the form:

<form method="POST" action="guideresult.php">
    <table cellspacing="7" cellpadding="0" bgcolor="#ecedef" align="center">
    <tr>
    <td>DATE</td>
    <td>TIME</td>
    <td>TYPE</td>
    <td>GENRE</td>
    <td>RATING</td>
    <td>PROGRAM NAME SEARCH</td>
    <td>Submit</td>
    </tr>
    <tr>
    <td>
        <input type="date" name="date">
    </td>
    <td>
        <input type="time" name="startime">
    </td>
    <td>
        <select name="type">
        <option value="NULL"></option>
        <option value="TV Show">TV Show</option>
        <option value="Movie">Movie</option>
        </select>
    </td>
    <td>
        <select name="genre">
        <option value="NULL"></option>
        <option value="Action">Action</option>
        <option value="Animated">Animated</option>
        <option value="Comedy">Comedy</option>
        <option value="Crime">Crime</option>
        <option value="Drama">Drama</option>
        <option value="Entertainment">Entertainment</option>
        <option value="Family">Family</option>
        <option value="Fantasy">Fantasy</option>
        <option value="Horror">Horror</option>
        <option value="Musical">Musical</option>
        <option value="Reality">Reality</option>
        <option value="Romance">Romance</option>
        <option value="ScFi">ScFi</option>
        <option value="Sports">Sports</option>
        <option value="Talk Show">Talk Show</option>
        </select>
    </td>
    <td>
        <select name="rating" class="rating">
        <option value="NULL"></option>
        <option class="tvshow" value="TV-Y">TV-Y</option>
        <option class="tvshow" value="TV-Y7">TV-Y7</option>
        <option class="tvshow" value="TV-G">TV-G</option>
        <option class="tvshow" value="TV-PG">TV-PG</option>
        <option class="tvshow" value="TV-14">TV-14</option>
        <option class="tvshow" value="TV-MA">TV-MA</option>
        <option class="movie" value="G">G</option>
        <option class="movie" value="PG">PG</option>
        <option class="movie" value="PG-13">PG-13</option>
        <option class="movie" value="R">R</option>
        <option class="movie" value="NC-17">NC-17</option>
        </select>
    </td>
    <td><input type="text" name="programname" size="40"></td>
    <td><input type="submit" value="   Submit   "></td>
    </tr>
    </table>
    </form>

And the results page:

<?php
        require_once 'connect.php';
        $db_server = mysql_connect($db_hostname, $db_username, $db_password);
        if (!$db_server) die("Unable to connect to MySQL: " . mysql_error());
        mysql_select_db($db_database)
            or die("Unable to select database: " . mysql_error());


        $date = $_POST['date'];
        $time = $_POST['startime'];
        $type = $_POST['type'];
        $genre = $_POST['genre'];
        $rating = $_POST['rating'];
        $programname = $_POST['programname'];


        $query = "SELECT Schedule.Channel_Number, StationInformation.Station_Name, Schedule.Program_Name, Schedule.Date, Schedule.Time FROM Schedule INNER JOIN StationInformation ON Schedule.Channel_Number=StationInformation.Channel_Number INNER JOIN TVShows ON Schedule.Program_Name=TVShows.Program_Name WHERE Schedule.Date='$date' AND Schedule.Time='$time' AND TVShows.Genre='$genre' AND TVShows.Rating='$rating' AND TVShows.Program_Name='$programname' ORDER BY Channel_Number, Time LIMIT 50";
        $result = mysql_query($query);

        if (!$result) die ("Database access failed: " . mysql_error());
        $rows = mysql_num_rows($result);

        echo "<div id='content'>";
        echo "<table align='center' class='CSSTableGenerator' width='1035'>
        <tr>
        <th>Channel Number</th>
        <th>Station Nation</th>
        <th>Program Name</th>
        <th>Date</th>
        <th>Time</th>
        </tr>";
            for ($j = 0 ; $j < $rows ; ++$j)
            {
                $row = mysql_fetch_row($result);

                echo "<tr>";
                echo "<td><center>" . $row[0] . "</center></td>";
                echo "<td><center>" . $row[1] . "</center></td>";
                echo "<td><center>" . $row[2] . "</center></td>";
                echo "<td><center>" . date ('m/d/Y',strtotime($row[3])) . "</center></td>";
                echo "<td><center>" . date ('h:i A',strtotime($row[4])) . "</center></td>";
            }
        echo "</table>";
        echo "</div>";
        ?>

use a loop to go through posted fields and build your query in that loop
loop $_POST
where .= and field = value

You will have to check for values of each of the fields and build the query accordingly. Something like:

$query = "SELECT Schedule.Channel_Number, StationInformation.Station_Name, Schedule.Program_Name, Schedule.Date, Schedule.Time FROM Schedule INNER JOIN StationInformation ON Schedule.Channel_Number=StationInformation.Channel_Number INNER JOIN TVShows ON Schedule.Program_Name=TVShows.Program_Name WHERE 1 ";

If(isset($_POST['date']) && !empty($_POST['date']))  {
    $date = mysql_real_escape_string($_POST['date']);
    $query .= " AND Schedule.Date='$date'";
}

    If(isset($_POST['startime']) && !empty($_POST['startime']))  {
    $startime = mysql_real_escape_string($_POST['startime']);
    $query .=  " AND Schedule.Date='$startime'";
}

etc.

As You can see I also added a bit of security escaping the input. You can enhance it even more. And start thinking of dropping the old mysql extension and maybe start using PDO.

Thanks for the responses guys, I'll give them a try!

For some reason it is not picking up the if conditions, it is just displaying everything from the database.

print out your query

It does not seem to be appending to the query from the if statements.

You have marked it as solved but seems you still have problems? If you wish we can still find a solution. Post the latest version of the script here and post the contents of the $_POST array using print_r($_POST).

I wound up fooling around with the code and I figured out the issue. Broj1, your code wound up working flawlessly. I just need to alter a few things on my end. Thanks again!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.