Searching Through Database

Question

BTW8892 0 Newbie Poster

10 Years Ago

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>";
        ?>

php

Edited 10 Years Ago by BTW8892

3 Contributors
8 Replies
265 Views
22 Hours Discussion Span
Latest Post 10 Years Ago Latest Post by BTW8892

jstfsklh211 79 Light Poster

10 Years Ago

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

broj1 356 Humble servant

10 Years Ago

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.

Edited 10 Years Ago by broj1

Reply to this topic

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.

BTW8892 0 Newbie Poster · Answer 1 · 2014-05-12T19:29:05+00:00

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

BTW8892 0 Newbie Poster · Answer 2 · 2014-05-12T19:45:37+00:00

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

jstfsklh211 79 Light Poster · Answer 3 · 2014-05-12T19:47:18+00:00

jstfsklh211 79 Light Poster

10 Years Ago

print out your query

BTW8892 0 Newbie Poster · Answer 4 · 2014-05-12T20:06:01+00:00

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

broj1 356 Humble servant Featured Poster · Answer 5 · 2014-05-13T07:10:58+00:00

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).

BTW8892 0 Newbie Poster · Answer 6 · 2014-05-13T17:41:36+00:00

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!