Do I need a database for movies?

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
We've been building quite a movie collection, and I keep all of them on my server, for easy access from anywhere in the house. Now the biggest problem is deciding what to watch. What I really want is to tag the .avi files with the names of the actors, directors, genres, awards, etc so I can view and sort them more easily. Since Vista's tags only work with picture files (and I may be kicking Vista soon anyway), it looks like I need to create it myself.

Requirements:

Output in web browser
Links to the actual files for playing

Ideally it would be able to create records by scanning the directory the movies are in, and populate some tags by looking at IMDB or similar.

I am not a DBA, I can barely manage a SQL server or Access file, but I want this to be a learning experience. What are the tools I should learn to do this right? Bonus points for only using open-source stuff.

Thanks,
~David
 

timwhit

Hairy Aussie
Joined
Jan 23, 2002
Messages
5,278
Location
Chicago, IL
How about MySQL with a PHP frontend? I'm sure you can find plenty of tutorials for this.
 

Handruin

Administrator
Joined
Jan 13, 2002
Messages
13,476
Location
USA
It's fun and beneficial to learn how to install them, but if you simply want to get going, there is always xampp.

You could also try IIS with C# asp.net and a sql server express DB. Microsoft has the Visual C# Express edition that you can build with. I'm sure all of which are way overkill for a home app, but fun to learn no less.
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
So. For this project I was thinking of doing it one of two ways:

1. Defined fields per movie:
Code:
Title
Year
Genre1
Genre2
Director1
Director2
Writer1
Writer2
Photography
Music
Actor1
Actor2
Actor3
Actor4
Actor5
Rating
Time

But I think I can be better served and with easier coding just using a tag schema.

Does anyone have an opinion?
 

Handruin

Administrator
Joined
Jan 13, 2002
Messages
13,476
Location
USA
The fields look fine, but how flexible do you want this tool to be? Will you ever want the option to add more actors, or more directors? Is this going to be simply for you? Do you ever plan to share it? Are you looking to get this done the simplest way possible and the shortest amount of time? Or are you looking to have some fun and dabble into some things?
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
I've pretty much decided that I want to use a tagging method. A really, really simple one. A single table, with 5 fields:

Index (do I need one?)
Title (varchar)
Tags (text)
FileLocation1 (varchar)
FileLocation2 (varchar)

Now all I need to be able to do is fulltextsearch the tags field, insert additional tags without clearing the field, insert entire records, and a bunch of other stuff.
 

timwhit

Hairy Aussie
Joined
Jan 23, 2002
Messages
5,278
Location
Chicago, IL
I have now, and it looks good, thanks. I'll still be fighting my way through my own implementation though, I still want to learn this stuff.

Well, I guess if you get stuck or decide it is too much work then you can go the IMDB route.

BTW, what format do you rip your movies in?
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
I rip them using RipIt4Me and compress in AutoGK into a 1.85GB XviD file. That way I can fit them evenly onto thumbdrives for trips. The HD-DVDs I rip using AnyDVD and store whole on the drive (~30GB each).
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
I'm remembering why I hate coding...

The syntax on these is wrong, and I don't know how.

Code:
$query = "SELECT * FROM movies WHERE title=$_POST['titlesearch'] ";
Code:
$query = "SELECT * FROM movies WHERE MATCH ( tags ) AGAINST ($tagsearch) ";
'titlesearch' and 'tagsearch' are brought in from an HTML page with some simple forms on it:

Code:
<form action="titlesearch.php" method="post">
    <p>Title Search: <input type="text" name="titlesearch" /><input type="submit" /></p>
    </form>
<form action="tagsearch.php" method="post">
    <p>Tag Search: <input type="text" name="tagsearch" /><input type="submit" /></p>
    </form>
I've been googling for hours, but it seems no one bothers to document the really easy stuff.
 

Handruin

Administrator
Joined
Jan 13, 2002
Messages
13,476
Location
USA
I'm remembering why I hate coding...

The syntax on these is wrong, and I don't know how.

Code:
$query = "SELECT * FROM movies WHERE title=$_POST['titlesearch'] ";
Code:
$query = "SELECT * FROM movies WHERE MATCH ( tags ) AGAINST ($tagsearch) ";
'titlesearch' and 'tagsearch' are brought in from an HTML page with some simple forms on it:

Code:
<form action="titlesearch.php" method="post">
    <p>Title Search: <input type="text" name="titlesearch" /><input type="submit" /></p>
    </form>
<form action="tagsearch.php" method="post">
    <p>Tag Search: <input type="text" name="tagsearch" /><input type="submit" /></p>
    </form>
I've been googling for hours, but it seems no one bothers to document the really easy stuff.

Start first by issuing a query by hand. Take a known value for "title" and build the query by hand and see if it returns anything. If it does, then move into the php code. For example, lets say you wrote out "SELECT * FROM movies WHERE title='gone with the wind';" and see if anything is returned (obviously replace it with a movie you know is in the DB.

You may also want to try SELECT * FROM movies WHERE title LIKE '%$keyword%';

You also have to make sure you've added fulltext search capabilities on your table.

Code:
ALTER TABLE movies ADD FULLTEXT(title);

If you have other fields that need fulltext, just comman seperate them in the above statement.

Also, just as an FYI for future projects you may work on that might be more than personal. It's very bad practice to place the value of $POST or $GET into any SQL query directly. For this project you are doing, it isn't a big deal, but if you work on stuff in the future, don't do this.

If you don't know the reason why, it's because of a SQL Injection type attack that could destroy your database. Rule of thumb, never ever trust the data coming from HTML forms. Always restrict the possibilities in your code. You can also help this by using the php method for addslashes() around the value. This way the string will be taken literal into the database.

Change the code to something like this:

PHP:
$query = "SELECT * FROM movies WHERE title=addslashes($_POST['titlesearch'])";

Another tip you might want is to change the "action" part of your form to use
PHP:
<? echo $_SERVER['PHP_SELF']; ?>
. This will tell PHP to use the form to itself. If you ever rename your file from "tagsearch.php" you won't need to edit the code because PHP will inherit the new name of the file. This assumes that your form posts to itself. If not, don't change your action field.

Another tip when writing your SQL statements, try to avoid using "SELECT *" for them and rather, specify every column you need even if you want all of them. If you use "*" and rely on the order of the returned fields and one day alter your database...you might end up breaking your code. So if you specify the field order every time and get into this habit, you won't break your code down the road. Also, if you don't need all the data, there is no point in selecting it to waste time on the DB engine.
 

Handruin

Administrator
Joined
Jan 13, 2002
Messages
13,476
Location
USA
I've pretty much decided that I want to use a tagging method. A really, really simple one. A single table, with 5 fields:

Index (do I need one?)
Title (varchar)
Tags (text)
FileLocation1 (varchar)
FileLocation2 (varchar)

Now all I need to be able to do is fulltextsearch the tags field, insert additional tags without clearing the field, insert entire records, and a bunch of other stuff.


How are you using the index? I usually create a unique auto-incrementing field so that each record is unique.

Make sure for the fields holding VARCHAR that you allocate enough space for paths since you seem to be holding directory locations.
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
Wow, thank you so much for the in-depth assistance and tips. I'm off to give them a shot, and I'll report back my progress.
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
Start first by issuing a query by hand. Take a known value for "title" and build the query by hand and see if it returns anything. If it does, then move into the php code. For example, lets say you wrote out "SELECT * FROM movies WHERE title='gone with the wind';" and see if anything is returned (obviously replace it with a movie you know is in the DB.

Yup, I tried this and it works fine:

Code:
...WHERE title='Cinderella'";
You may also want to try SELECT * FROM movies WHERE title LIKE '%$keyword%';

And this works fine, too:

Code:
...WHERE title LIKE 'Cinderella'";
That leads me to believe that it is the syntax of the variable from the form that I got wrong.

You also have to make sure you've added fulltext search capabilities on your table.

Code:
ALTER TABLE movies ADD FULLTEXT(title);
If you have other fields that need fulltext, just comman seperate them in the above statement.

Awesome. I knew I had to, but I hadn't found out how yet. Thanks.

Also, just as an FYI for future projects you may work on that might be more than personal. It's very bad practice to place the value of $POST or $GET into any SQL query directly. For this project you are doing, it isn't a big deal, but if you work on stuff in the future, don't do this.

If you don't know the reason why, it's because of a SQL Injection type attack that could destroy your database. Rule of thumb, never ever trust the data coming from HTML forms. Always restrict the possibilities in your code. You can also help this by using the php method for addslashes() around the value. This way the string will be taken literal into the database.

Change the code to something like this:

PHP:
$query = "SELECT * FROM movies WHERE title=addslashes($_POST['titlesearch'])";

Yup, I knew about this, but was skipping it for this project. I was actually using the htmlspecialchars() command, will that also work?

Another tip you might want is to change the "action" part of your form to use
PHP:
<? echo $_SERVER['PHP_SELF']; ?>
. This will tell PHP to use the form to itself. If you ever rename your file from "tagsearch.php" you won't need to edit the code because PHP will inherit the new name of the file. This assumes that your form posts to itself. If not, don't change your action field.

I tried this, but it errored out. What would the syntax of the rest of the line be? I tried the following without success.

PHP:
<form action="<? echo $_SERVER['PHP_SELF']; ?>" method="post">
Another tip when writing your SQL statements, try to avoid using "SELECT *" for them and rather, specify every column you need even if you want all of them. If you use "*" and rely on the order of the returned fields and one day alter your database...you might end up breaking your code. So if you specify the field order every time and get into this habit, you won't break your code down the road. Also, if you don't need all the data, there is no point in selecting it to waste time on the DB engine.

Another good tip. Thanks Handruin!
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
How are you using the index? I usually create a unique auto-incrementing field so that each record is unique.

Make sure for the fields holding VARCHAR that you allocate enough space for paths since you seem to be holding directory locations.

I created a unique auto-incrementing index, and my VARCHAR fields are length 50, that should be enough.
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
I feel bad posting this code, because I know it is complete crap. But if you guys are going to continue spending your time helping me, you should see the whole thing. So here 'goes:

PHP:
<?php
//Connects to database
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("movies") or die(mysql_error());
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" " method="get">
    Search: <input type="text" name="search" /><input type="submit" />
</form>

<br>
You have searched for: <?php echo ($_GET['search']) ?>
<br>

<?php
//Search results
     /* Perform SQL query */
     $query = "SELECT title,tags FROM movies WHERE title LIKE 'Cinderella' OR tags LIKE 'action'";
     $result = mysql_query($query)
         or die("Query failed : " . mysql_error());
     /* Print results in HTML */
     print "<table BORDER=1 FRAME=2 WIDTH=80%>\n";
     print "<th>Title</th><th>Tags</th>";
     while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { 
        print "\t<tr>\n"; 
        foreach ($line as $col_value) { 
            print "\t\t<td>$col_value</td>\n"; 
        } 
        print "\t</tr>\n"; 
    } 
    print "</table>\n"; 
    mysql_free_result($result); 
?>
This all works EXCEPT that the query itself doesn't use the input from the form.
 

Handruin

Administrator
Joined
Jan 13, 2002
Messages
13,476
Location
USA
I feel bad posting this code, because I know it is complete crap. But if you guys are going to continue spending your time helping me, you should see the whole thing. So here 'goes:

PHP:
<?php
//Connects to database
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("movies") or die(mysql_error());
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" " method="get">
    Search: <input type="text" name="search" /><input type="submit" />
</form>

<br>
You have searched for: <?php echo ($_GET['search']) ?>
<br>

<?php
//Search results
     /* Perform SQL query */
     $query = "SELECT title,tags FROM movies WHERE title LIKE 'Cinderella' OR tags LIKE 'action'";
     $result = mysql_query($query)
         or die("Query failed : " . mysql_error());
     /* Print results in HTML */
     print "<table BORDER=1 FRAME=2 WIDTH=80%>\n";
     print "<th>Title</th><th>Tags</th>";
     while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) { 
        print "\t<tr>\n"; 
        foreach ($line as $col_value) { 
            print "\t\t<td>$col_value</td>\n"; 
        } 
        print "\t</tr>\n"; 
    } 
    print "</table>\n"; 
    mysql_free_result($result); 
?>
This all works EXCEPT that the query itself doesn't use the input from the form.


Try using "post" for now in both the form and in your PHP code. When you use "get", PHP will pull down the variables in the URL vs a POST comes from within the form itself.

Lets say you setup your application to use the following URL:

http://localhost/titlesearch.php?searchfield=value1

When you call the global:

PHP:
$_GET["searchfield"];

It will contain a string for "value1".

Where as if you setup your form to use "post", and then access it from using $_POST in php, your form will hide the data when it posts it back. There are pros and cons to both methods, but I don't have time this morning to go into them. I will try when I get home from work.


Also, when you open your script in a web browser and the page is generated, can you view the HTML source and see what was generated for this line, specifically what is shown in bold. Also, it looks like in your code you have an extra quotation mark in there...try removing that and see if it helps any.


Code:
<form action="[B][COLOR="Red"]<?php echo $_SERVER['PHP_SELF']; ?>" [COLOR="Green"]"[/COLOR][/COLOR][/B] method="get">
    Search: <input type="text" name="search" /><input type="submit" />
</form>
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
Thanks again Handruin. The HTML looks much cleaner with that quote removed, the backslashes in the form removed, and changing back to post didn't break anything. Now for what should be a simple question:

PHP:
     $query = "SELECT title,tags FROM movies WHERE title LIKE 'Cinderella' OR tags LIKE 'action'";

What should the formatting be to replace 'Cinderella' and 'action' with the test from the form? There are so many quotes and brackets that I am quite lost.
 

Handruin

Administrator
Joined
Jan 13, 2002
Messages
13,476
Location
USA
Thanks again Handruin. The HTML looks much cleaner with that quote removed, the backslashes in the form removed, and changing back to post didn't break anything. Now for what should be a simple question:

PHP:
     $query = "SELECT title,tags FROM movies WHERE title LIKE 'Cinderella' OR tags LIKE 'action'";

What should the formatting be to replace 'Cinderella' and 'action' with the test from the form? There are so many quotes and brackets that I am quite lost.


There are several ways you can approach a solution, but first I need to understand the conditions of the problem. How do you plan to enter search strings into your HTML form (give me a basic use case)? Will you have them separated by spaces, returns, or something else (if anything). Will you want to have searches to include longer strings by using quotes for multiple words, etc? Basically, how do you want to use your own search? Don't limit yourself based on your knowledge of the current technology you're trying to implement. Think about how you actually want to use this tool and then we can think of a way to solve that use case.

The reason I ask that determines how you handle this in the back end code. I see you are searching two different fields for values (titles and tags). Is your HTML form going to differentiate between the two types or will there only be the one that I see currently in your code? I'm guessing from what I see that you will enter into the search for a title, but that same value will get placed into the tags...but somehow this doesn't seem right to me.

Going by the one form field you have right now, you can access the contents of it using the $_POST['search'] global array inside PHP (which I know you've already been trying). This array should return you the contents of that form field as a text string. If you enter more than a single word into that field, your code will have to handle this. This is where there can be several ways of handling your results.

You need to alter the HTML form to go back to a "POST" rather than a "GET" for this to work. Change method="get" to method="post" and then echo out the contents of $_POST:

PHP:
echo $_POST['search'];


Another quick tip would be to write a simple function to help you clean up the query code, but this isn't exactly the right answer just yet...but might be fun to play with:

PHP:
function queryMovie(title, tags)
{
	$query =	"SELECT title, tags " .
				"FROM movies " .
				"WHERE title LIKE '$title' " .
				"OR tags LIKE '$tags'";
	
	return $query;
}

Then you only have to call this function and pass your string parameters:

PHP:
$result = mysql_query( queryMovie("your title", "your tag") );

Again, that's just a very simplistic way of dealing with this until I know more about your requirements. If you want to deal with multiple tags, then we can begin to add looping constructs inside that function to handle that and automatically build onto that query dynamically.

pseudo code:
For the number of tags I have, add "OR tags LIKE '$tags" to the query.
 

Handruin

Administrator
Joined
Jan 13, 2002
Messages
13,476
Location
USA
I created a unique auto-incrementing index, and my VARCHAR fields are length 50, that should be enough.

That means the total sum of all characters in the "path" string cannot exceed a value of 50. Are you sure that's enough given the location of where you'll store your movies?

For example, lets say this is a location where you might store your movies:
"C:\movies\drama\g\gone with the wind.avi"

That's going to consume 40 of the 50 available characters in the field. If you have a path longer than that, you'll get an error trying to insert it into the database because it won't allow for more than 50 characters.

This is the trick with VARCHAR is that you don't want to reserve too much or too little space. In earlier version of MySQL this field type is limited to a maximum of 255 characters, so keep than in mind while using this type of field. Which version of MySQL are you using? If you are using 5.0.3 or later, you can hold between 0 to 65,535 characters in that field.

There are (again) pros and cons to using the different field types. Meaning, why you would use VARCHAR vs CHAR for storing data. Since CHAR is a fixed length field, it might be a bit faster for searching, but will waste more space in the DB. The reverse is true for VARCHAR, but I'm not an expert when it comes to selecting one over the other, so maybe someone else can shed some light on it.
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
Handruin,

What I want in an ideal world...alright, you asked for it ;)

A single search box that will return the records of anything similar to the title or similar to any one of the tags in the tag field. Titles may contain spaces, but tags are alphanumeric and separated with spaces. It would be great to be able to enter multiple items (+action +1999 -animated)

Here is an example database without location fields:

Code:
    1     The Matrix              KeanuReeves 1999 action adventure     
    
    2     Over the Hedge          animated 2006 BruceWillis GarryShandling action comedy     
    
    3     Cinderella              1950 animated IleneWoods disney romance     
    
    4     Cinderella Returns      action
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
Alright, I'm changing the location fields to length 100, that ought to do it. I am running MySQL 5.0.38, so that isn't an issue.
 

Handruin

Administrator
Joined
Jan 13, 2002
Messages
13,476
Location
USA
That sounds reasonable; one field to search them all. Now for a title such as "The Matrix", would you expect to enter that with quotes into your search, or just as "The" and "Matrix" as separate words?

Why did you use "+" and "-" near your tags? Is that for something special? I'm guessing that a "+" means you want everything with this, and a "-" means not to yield results with that tag?

So in your example, you want action and 1999 but exclude results that are animated? If that's the case and you want that as a way to use your tool, the + and - can be incorporated as a feature.
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
That sounds reasonable; one field to search them all. Now for a title such as "The Matrix", would you expect to enter that with quotes into your search, or just as "The" and "Matrix" as separate words?

Why did you use "+" and "-" near your tags? Is that for something special? I'm guessing that a "+" means you want everything with this, and a "-" means not to yield results with that tag?

So in your example, you want action and 1999 but exclude results that are animated? If that's the case and you want that as a way to use your tool, the + and - can be incorporated as a feature.

Perfect. If I could search for "The Matrix" by just typing "matrix", that would be ideal. Bringing up some additional results is fine, this will mostly be a way to surf between titles.
 

blakerwry

Storage? I am Storage!
Joined
Oct 12, 2002
Messages
4,203
Location
Kansas City, USA
Website
justblake.com
Might try this out... only pulls up positive matches, not negative.


I used the following table structure
Code:
movies  CREATE TABLE `movies` (                                  
          `index` int(10) unsigned NOT NULL auto_increment,      
          `title` varchar(100) default NULL,                     
          `tags` text,                                           
          `location1` varchar(100) default NULL,                 
          `location2` varchar(100) default NULL,                 
          PRIMARY KEY  (`index`)                                 
        )
Code:
<?php

// Check value of submit to see if the form was submitted
if($_POST['submit'] == "")
        form();
else
        process();


// Nothing has been submitted, display search form
function form()
{
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
    Search: <input type="text" name="search" /><input type="submit" name="submit" />
</form>

<?
}


// Something was submitted, let's do something
function process()
{

        // Connect to DB
        mysql_connect("localhost", "movies", "movies") or die(mysql_error());
        mysql_select_db("movies") or die(mysql_error());

        // Clean user input of escape characters
        $search = mysql_real_escape_string($_POST['search']);


        // Tokenize search query
        $tokens = explode(" ", $search);


        // Build search query from tokens
        $query = "SELECT * FROM movies WHERE ";
        foreach($tokens as $token)
        {

                $query .= "title LIKE '%$token%' OR tags LIKE '%$token%' OR ";
        }
        $query .= "title LIKE ''";

        // Debugging
        //echo $query;

        // Get search results
        $results = queryMovie($query);

        // Output html results
        displayMovies($results);
        
        // Display Search form
        form();

}

// Queries DB for movie listings, returns false if query fails or no results;
function queryMovie($query)
{
    $results = mysql_query($query);

    if(mysql_num_rows($results) == 0)
        return FALSE;
    else
        return $results;
}  

// Outputs HTML results in a table, given a mysql object
function displayMovies($listings)
{
        echo "<table width='100%' cellspacing='0' cellpadding='0' border='1'>
                <tr><td><b>Title</b></td><td><b>Tags</b></td><td><b>Link</b></td></tr>\n";
        //Loop through results
        while($row = mysql_fetch_array($listings))
        {
                echo "<tr><td>".$row['title']."</td><td>".$row['tags']."</td><td><a href='".$row['location1']."'>".$row['location1']."</a></td></tr>\n";
        }
        echo "</table>\n";
}

?>
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
Damn. You guys are awesome. It will take me a while to chew through all that and learn what it means.

Thanks muchly.
 

blakerwry

Storage? I am Storage!
Joined
Oct 12, 2002
Messages
4,203
Location
Kansas City, USA
Website
justblake.com
http://blakehudson.net/dd/search.php .... left the debugging output in....

allows the use of - and + variables, all critera in the search has to match (use of AND condition) ...


I'll leave the cleaning of the HTML up to you...

Code:
<?php

// Check value of submit to see if the form was submitted
if($_POST['submit'] == "")
        form();
else
        process();


// Nothing has been submitted, display search form
function form()
{
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
    Search: <input type="text" name="search" /><input type="submit" name="submit" />
</form>

<?
}


// Something was submitted, let's do something
function process()
{

        // Connect to DB
        mysql_connect("localhost", "movies", "movies") or die(mysql_error());
        mysql_select_db("movies") or die(mysql_error());

        // Clean user input of escape characters
        $search = mysql_real_escape_string($_POST['search']);


        // Tokenize search query
        $tokens = explode(" ", $search);


        // Build search query from tokens
        $query = "SELECT * FROM movies WHERE ";
        $query .= "movies.index > 0 ";
        foreach($tokens as $token)
        {
                if(substr($token,0,1) == "+") // Token contains +
                {
                        //Strip +
                        $token = substr($token, 1, strlen($token));
                        $query .= "AND (tags LIKE '%$token%')";
                }
                elseif(substr($token,0,1) == "-") // Token contains -
                {
                        //Strip -
                        $token = substr($token, 1, strlen($token));
                        $query .= "AND (tags NOT LIKE '%$token%')";
                }
                else // Bare token
                {
                        $query .= "AND (title LIKE '%$token%' OR tags LIKE '%$token%') ";
                }
        }

        // Debugging
        echo $query;

        // Get search results
        $results = queryMovie($query);

        // Output html results
        displayMovies($results);
        
        // Display Search form
        form();

}

// Queries DB for movie listings, returns false if query fails or no results;
function queryMovie($query)
{
    $results = mysql_query($query);

    if(mysql_num_rows($results) == 0)
        return FALSE;
    else
        return $results;
}  

// Outputs HTML results in a table, given a mysql object
function displayMovies($listings)
{
        echo "<table width='100%' cellspacing='0' cellpadding='0' border='1'>
                <tr><td><b>Title</b></td><td><b>Tags</b></td><td><b>Link</b></td></tr>\n";
        //Loop through results
        while($row = mysql_fetch_array($listings))
        {
                echo "<tr><td>".$row['title']."</td><td>".$row['tags']."</td><td><a href='".$row['location1']."'>".$row['location1']."</a></td></tr>\n";
        }
        echo "</table>\n";
}

?>
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
Awesome. Thank you so much. The things left on my wish list are all HTML (I think) so I hope I can figure them out:

1. Hyperlink all the titles to launch the movie
2. Hyperlink all the tags to run a new search on that tag.
3. Display all records when no search or an empty search is run.
 

blakerwry

Storage? I am Storage!
Joined
Oct 12, 2002
Messages
4,203
Location
Kansas City, USA
Website
justblake.com
I'll leave those as an exercise for you then. #3 is already done, #1 shouldn't be too hard, let me know if you want some ideas on how to implement #2.

-Blake
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
Ideas are always welcome ;)

Half of #3 is already done, I just need it to display all records when the page is first accessed as well.

Thanks again.
 

Handruin

Administrator
Joined
Jan 13, 2002
Messages
13,476
Location
USA
For #2, you'll have to start using the $_GET variable and when you build the URL in your code, you'll need to make a variable that places the value of the tag into it.

Here would be an example tag on your page that you would have to generate in your code:
<a href="http://localhost/search.php?tagsearch=+action">action</a>

Then capture the value in your code using:

PHP:
$tagvalue = $_GET['tagsearch'];  //this returns a value of "+action" in tha above example

Pass that back into your search engine and out should come a new result. :)
 

ddrueding

Fixture
Joined
Feb 4, 2002
Messages
19,315
Location
Monterey, CA
Got #3 working the was I wanted by un-doing a bunch of Blake's work...sorry about that.

I almost have #1 working as I want; the location is in the hyperlink, but it still starts with the server's address (http://192.168.1.7/ z:\movies\animated\Cinderella.avi )

I understand the explanation provided for #2 (I think). I just need to go chew on it for a while.

PHP:
<?php

form();
process();

// Display search form
function form()
{
?>

<form action="<?php echo $_SERVER['PHP_SELF']; ?>" method="post">
    Search: <input type="text" name="search" /><input type="submit" name="submit" />
</form>

<?
}


// Perform the search
function process()
{

        // Connect to DB
        mysql_connect("localhost", "root", "") or die(mysql_error());
        mysql_select_db("movies") or die(mysql_error());

        // Clean user input of escape characters
        $search = mysql_real_escape_string($_POST['search']);


        // Tokenize search query
        $tokens = explode(" ", $search);


        // Build search query from tokens
        $query = "SELECT * FROM movies WHERE ";
        $query .= "movies.index > 0 ";
        foreach($tokens as $token)
        {
                if(substr($token,0,1) == "+") // Token contains +
                {
                        //Strip +
                        $token = substr($token, 1, strlen($token));
                        $query .= "AND (tags LIKE '%$token%')";
                }
                elseif(substr($token,0,1) == "-") // Token contains -
                {
                        //Strip -
                        $token = substr($token, 1, strlen($token));
                        $query .= "AND (tags NOT LIKE '%$token%')";
                }
                else // Bare token
                {
                        $query .= "AND (title LIKE '%$token%' OR tags LIKE '%$token%') ";
                }
        }

        // Debugging
        echo $query;

        // Get search results
        $results = queryMovie($query);

        // Output html results
        displayMovies($results);
        
}

// Queries DB for movie listings, returns false if query fails or no results;
function queryMovie($query)
{
    $results = mysql_query($query);

    if(mysql_num_rows($results) == 0)
        return FALSE;
    else
        return $results;
}  

// Outputs HTML results in a table, given a mysql object
function displayMovies($listings)
{
        echo "<table width='60%' cellspacing='0' cellpadding='0' border='1' align=center>
                <tr><td><b>Title</b></td><td><b>Tags</b></td></tr>\n";
        //Loop through results
        while($row = mysql_fetch_array($listings))
        {
                echo "<tr><td><a href='".$row['location1']."'>".$row['title']."</a></td><td>".$row['tags']."</td></tr>\n";
        }
        echo "</table>\n";
}

?>
 
Top