Do I need a database for movies?

ddrueding · Jan 8, 2008

In order to have different links to different searches, I'll need to do the hyperlinks based on the tokenized results? I haven't felt this stupid in a long time.

blakerwry · Jan 8, 2008

In order to accomplish #2, I would:

A) Create a new function that reads the tag column for a given index
B) Tokenizes them into individual tags
c) Builds a string of html pointing back to the search e.g.

Code:

<a href='./search.php?search=tag1'>tag1</a> <a href='./search.php?search=tag2'>tag2</a>....

D) Returns the string so you can call it/insert the result in each table row

ex:

PHP:

// 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>".makeTags($row['index'])."</td></tr>\n";
        }
        echo "</table>\n";
}

function makeTags($id)
{

// Lookup
$result = select tags from movies where movies.index = '$id';
$result = mysql_fetch_row($result);

//tokenize
... 

// Loop through tokens
for each ...
{
    // Build string of html
    $html .= "...";
}

return $html;
}

This brings us to the main difference between GET and POST methods... GET uses the url to pass variables, and consequently limited to certain characters and sizes.

POST is passed behind the scenes a bit and has less limitations.

Since you're using a link to pass information to a form via URL, to make it easy on yourself, at this point I would change your form and php to use the GET method... it's only 2 lines if I remember correctly...

blakerwry · Jan 8, 2008

btw, index is a reserved word in my version of MySQL (5.x)... perhaps id would be a better choice of column name so that you do not have to prepend the table name when performing queries...

blakerwry · Jan 8, 2008

ddrueding said:
Got #3 working the was I wanted by un-doing a bunch of Blake's work...sorry about that.

You undid very little, don't sweat it

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 )

This pageexplains a bit about firefox's built-in limitations and how to properly link to local files.
http://kb.mozillazine.org/Links_to_local_pages_don't_work

Also you might want to read up on php/mysql security, including use of the root user for a php app like this and use of the mysql_real_escape_string() php function to guard against unintended input characters.

timwhit · Jan 8, 2008

blakerwry said:
Also you might want to read up on php/mysql security, including use of the root user for a php app like this and use of the mysql_real_escape_string() php function to guard against unintended input characters.

If he is only planning on running this on a local network, does he really need to worry about SQL injection? Not that it's a bad thing to know about, but for this application, I doubt it's necessary.

Though, this cartoon is great.

Handruin · Jan 8, 2008

That's one of my favorite xkcd comics. Fits perfectly here.

ddrueding · Jan 8, 2008

Awesome Blake. That is what I was just coming back to ask. I'm glad I hit "refresh" before typing out my post.

timwhit · Jan 8, 2008

Handruin said:
That's one of my favorite xkcd comics. Fits perfectly here.

I found it just browsing after Merc posted the link to their site. I printed it out and left it around work for the other developers to read.

ddrueding · Jan 8, 2008

I'm having some issues (no kidding) with the makeTags function.

PHP:

function makeTags($id)
{

// Lookup
$result = "SELECT tags FROM movies WHERE movies.index = '$id'";
$result = mysql_fetch_row($result);

// Tokenize
$tokens = explode(" ", $result);

// Loop through tokens
foreach($tokens as $token);
{
    // Build string of HTML
    $html .= "<a href='./movies.php?search="$token"'>"$token"</a> ";
}

return $html;
}

Syntax error line 15. And I don't know if it will work, anyway.

ddrueding · Jan 8, 2008

blakerwry said:
btw, index is a reserved word in my version of MySQL (5.x)... perhaps id would be a better choice of column name so that you do not have to prepend the table name when performing queries...

Great idea. What is a logical choice?

blakerwry · Jan 8, 2008

Great idea. What is a logical choice?

I usually use the name 'id', sometimes I prepend some info about the id (especially when the same column is used in multiple tables). In this case, 'movieid' or 'id' would be my first picks.

You had a couple minor glitches... 1 that I introduced in my psuedo code. But very close.

PHP:

1: function makeTags($id)
2: {
3:
4: // Lookup
5: $result = "SELECT tags FROM movies WHERE movies.index = '$id'";
6: $result = mysql_fetch_row($result);
7:
8: // Tokenize
9: $tokens = explode(" ", $result);
10:
11: // Loop through tokens
12: foreach($tokens as $token);
13: {
14:    // Build string of HTML
15:    $html .= "<a href='./movies.php?search="$token"'>"$token"</a> ";
16: }
17:
18: return $html;
19:}

line 5 - my code - should have mysql_query() around the query
line 12 - no ; needed
line 15 - when including variables in strings there are a few php shortcuts... strings with double quotes (") are parsed and variables replaced, strings with single quotes(') are not... when concatenating strings, use the . operator.

ex:
The following are the same

PHP:

echo "My name is $name and I am $age";
echo 'My name is '.$name.' and I am '.$age;
echo "My name is $name and I am ".$age;

I find when using arrays it's easier to use the concatenation method rather than escaping the special characters...

Anyway, after writing the function I realized we already had the tags pulled out of the db and an additional lookup per movie seemed redundant. So here's the revised app.

PHP:

<?php

        form();
        process();


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

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

<?
}


// Perform the search
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($_GET['search']);


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

        if($results) // If there were results, output them
        {
                // Output html results
                displayMovies($results);
        }
        else // Report Nothing found
        {
                echo "\n<br>Nothing Found. Please try again. <br><br>\n";
        }
        
}

// Queries DB for movie listings, returns false if query fails or no results;
function queryMovie($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 "<code>$query</code>\n";

        $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></tr>\n";
        //Loop through results
        while($row = mysql_fetch_array($listings))
        {
                echo "<tr><td><a href='".$row['location1']."'>".$row['title']."</a></td><td>".makeTags($row['tags'])."</td></tr>\n";
        }
        echo "</table>\n";
}

// Tokenizes a string of tags and returned returns them as HTML formatted links
function makeTags($tags)
{
        // Tokenize
        $tokens = explode(" ", $tags);

        $html = "";
        // Loop through tokens
        foreach($tokens as $token)
        {
                // Build string of HTML
                $html .= "<a href='./movies.php?search=$token'>$token</a> ";
        }

        return $html;
}  
?>

ddrueding · Jan 8, 2008

Awesome. Working code and I feel like I almost know it. I understand the basic concept behind every line of code, I just need to go read up on the syntax.

Thanks again.

blakerwry · Jan 8, 2008

no problem dd, now you just have to work on the app that fills the db with useful info...

One thought, use a different tag separator (such as a comma, semicolon, etc) so that you can tokenize tags by something other than whitespace.

If you need help on syntax, check out php.net (use the function list search in the upper right). The well documented and user assisted API of PHP is what drew me to it as a programming language for both web and local apps.

ddrueding · Jan 8, 2008

Thanks Blake. You've been a great help. I'll need to put this on hold for the day so I can go make some money, but I'll be back.

timwhit · Jan 8, 2008

If you have phpMyAdmin installed you could just enter all the data into a CSV file and import with phpMyAdmin.

ddrueding · Jan 8, 2008

timwhit said:
If you have phpMyAdmin installed you could just enter all the data into a CSV file and import with phpMyAdmin.

This is likely what I'll end up doing. But I'm doing some research into Zotero. Perhaps it can get me some of the information I want.

blakerwry · Jan 9, 2008

If you haven't already found it, sqlyog is a great tool to visually manage a mysql db...

zotero looks likes something I could use rather than my collection of bookmarks to random informational sites and pages. Nice find.

ddrueding · Jan 9, 2008

Thanks. My sister's friend wrote it (I think) she's a grad student at Cornell.

ddrueding · Jan 15, 2008

I just found IMDBPHP. It looks to do exactly what I want. I just hope the code isn't too far over my head (I'm sure it is).

Handruin · Jan 15, 2008

Try it, and if/when you have questions, post them so we can help. Their API seems to be Object Oriented and reasonably documented. How much have you done with that be it PHP or any other language? It does look interesting, but I see it as a tool to query the IMBD database, not your own.

What are thoughts with using this tool/API? I guess you could make it more like your own IMDB and then have it pull in real data about each movie. You could extend their API and have it search locally, but your local sight would have to work similar to IMDB. It might be a fun project if you're interested.

I can already think of some fun things to add, like your own "view" counters when you click the "watch this movie" button (which also shows you the last time you watched the movie), or your own
random suggest" based on moods, or even a que of movies you want to watch, yet it remembers them for you with e-mail reminders...weird things like that.

ddrueding · Jan 15, 2008

Looks like the author of that also wrote phpVideoPro. Looking into it more.

Handruin · Jan 15, 2008

Also, when you get to a point where you want to make PHP development easier, try a demo of the Zend Studio and see if you like it. You might not appreciate it just yet, but the earlier versions were the best PHP development tool I could find. Things may have changed by now, but I'm sure they still make a decent product.

ddrueding · Jan 15, 2008

Handruin said:
Try it, and if/when you have questions, post them so we can help. Their API seems to be Object Oriented and reasonably documented. How much have you done with that be it PHP or any other language? It does look interesting, but I see it as a tool to query the IMBD database, not your own.

What are thoughts with using this tool/API? I guess you could make it more like your own IMDB and then have it pull in real data about each movie. You could extend their API and have it search locally, but your local sight would have to work similar to IMDB. It might be a fun project if you're interested.

I can already think of some fun things to add, like your own "view" counters when you click the "watch this movie" button (which also shows you the last time you watched the movie), or your own
random suggest" based on moods, or even a que of movies you want to watch, yet it remembers them for you with e-mail reminders...weird things like that.

My thoughts were to grab some of the more relevant data and populate the "tags" with it. Things like writer, director, genre, top actors, etc. I already have the location and title info in a spreadsheet.

Handruin · Jan 15, 2008

ddrueding said:
Looks like the author of that also wrote phpVideoPro. Looking into it more.

I honestly just saw that 30 seconds ago and you posted it. That seems more practical.

ddrueding · Jan 15, 2008

So it looks like what I need to do is:

1. Get the API.
2. Use the "Title" field to find IMDB's movie ID
3. Pull the fields that I want to use for that ID
4. Separate any arrays into elements
5. Add them to the (now) comma separated tag field

Based on my quick playing around, step #2 can't be completely automated. There are too many returns, and the target isn't necessarily the first hit (try Cinderella).

If I added a field for the IMDB number, and retrieved these manually, the rest could be automated.

Still chewing....

ddrueding · Jan 15, 2008

Handruin said:
I honestly just saw that 30 seconds ago and you posted it. That seems more practical.

It maintains an inventory, and provides the information, but offers no more than what "My IMDB" offers. What I am looking for is something that is self-referencing, where anytime you click on a link it shows more films from within the collection. Perhaps having a separate link to launch IMDB's site would be nice, but I want the default "click on something" function to be another search of the database.

Planned use scenario:

Last night we watched High Fidelity, and I liked John Cusack in it. Searching for High Fidelity and clicking on "John Cusack" in the tags should bring up other movies in the collection with him in them. In that list would be Grosse Pointe Blank, which I also like but we watched recently. But in there is Dan Aykroyd, which brings up Spies like Us. The tags for that film show John Landis and Chevy Chase, so we decide to watch it.

Going outside the collection (to IMDB) to do this may lead to films that aren't immediately available. That is a good thing when I'm surfing Amazon for new movies, but not when we are sitting on the couch looking for something to watch NOW.

ddrueding · Jan 15, 2008

Adding the IMDB IDs now, worked out a system, only ~15 seconds each.

ddrueding · Jan 15, 2008

OK, I've got the API to spit out all the information I want for a given IMDB ID.

Now I'm looking into breaking that information into tags.

ddrueding · Jan 15, 2008

Agghhhh! Help please...

For now I'm just trying to display all the tags as I want them. In a single line, separated by commas without spaces.

Here is the code:

PHP:

  echo $movie->title().',';
  echo $movie->year().',';
  $gen = $movie->genres();
  for ($i = 0; $i + 1 < count($gen); $i++) {
    echo $gen[$i].',';
  }
  $cast = $movie->cast();
  for ($i = 0; $i + 1 < count($cast); $i++) {
    echo $cast[$i].',';
  }
  $director = $movie->director();
  for ($i = 0; $i + 1 < count($director); $i++) {
    echo $director[$i].',';
  }
  $write = $movie->writing();
  for ($i = 0; $i + 1 < count($write); $i++) {
    echo $write[$i].',';
  }
  $produce = $movie->producer();
  for ($i = 0; $i + 1 < count($produce); $i++) {
    echo $produce[$i].',';
  }
  $compose = $movie->composer();
  for ($i = 0; $i < count($compose); $i++) {
    echo $compose[$i].',';
  }
  foreach ( $movie->alsoknow() as $ak){
    echo $ak["title"];
  }

And here is the results:

HTML:

Ice Age,2002,Adventure,Animation,Family,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,The Ice Age

As you can see, Title, Year, Genres, and (at the end) "AKAs" all work fine, but the rest are failing. I don't understand what the difference is between Genres and the other arrays.

Ideas?

ddrueding · Jan 15, 2008

This is cleaner and produces the same error...is it still correct?

PHP:

$movie = new imdb ($_GET["mid"]);

  $movieid = $_GET["mid"];
  $movie->setid ($movieid);


  echo $movie->title().',';
  echo $movie->year().',';
  $gen = $movie->genres();
  for ($i = 0; $i + 1 < count($gen); $i++) {
    echo $gen[$i].',';
  }
  foreach ( $movie->cast() as $cas){
    echo $cas.',';
  }
  foreach ( $movie->director() as $dir){
    echo $dir.',';
  }
  foreach ( $movie->writing() as $wri){
    echo $wri.',';
  }
  foreach ( $movie->producer() as $pro){
    echo $pro.',';
  }
  foreach ( $movie->composer() as $com){
    echo $com.',';
  }
  foreach ( $movie->alsoknow() as $ak){
    echo $ak["title"];
  }

ddrueding · Jan 15, 2008

Alright. Sorry for all the crap. Last one before I go to bed,

Complete david.html

HTML:

<HTML>
    <HEAD><TITLE>Enter IMDB Movie ID:</TITLE></HEAD>

    <BODY>
        <FORM ACTION="david.php" METHOD=get>
            <INPUT TYPE="text" NAME="imdbid" SIZE=30 MAXLENGTH=50>
            <INPUT TYPE="submit" VALUE="Submit">
        </FORM>
    </BODY>

</HTML>

Complete david.php

PHP:

<?php

require ("imdb.class.php");

$movie = new imdb ($_GET["imdbid"]);

  $movieid = $_GET["imdbid"];
  $movie->setid ($movieid);

  echo $movie->year().',';
  foreach ( $movie->genres() as $gen){
    echo $gen.',';
  }
  foreach( $movie->cast() as $cas){
    echo $cas.',';
  }
  foreach( $movie->director() as $dir){
    echo $dir.',';
  }
  foreach( $movie->writing() as $wri){
    echo $wri.',';
  }
  foreach( $movie->producer() as $pro){
    echo $pro.',';
  }
  foreach( $movie->composer() as $com){
    echo $com.',';
  }
  foreach( $movie->alsoknow() as $ak){
    echo $ak["title"];
  }
?>

Complete output when searching for 0118548

HTML:

1997,Thriller,Crime,Drama,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,Array,

LunarMist · Jan 15, 2008

Probably not, but when did practical get in the way of technology?

Handruin · Jan 15, 2008

try adding another foreach() inside the items that show "Array" as a result. It looks like a multi-dimensional array.

PHP:

foreach( $movie->cast() as $cas){
	foreach($cas as $castItem) {
	     echo $castItem.',';
	}
}

ddrueding · Jan 15, 2008

Handruin said:
try adding another foreach() inside the items that show "Array" as a result. It looks like a multi-dimensional array.

PHP:

foreach( $movie->cast() as $cas){ foreach($cas as $castItem) { echo $castItem.','; } }

You are correct! But as it turns out, I only want the first item (not who they played or their ID#).

ddrueding · Jan 15, 2008

This almost does it.

PHP:

<?php

require ("imdb.class.php");

$movie = new imdb ($_GET["imdbid"]);

  $movieid = $_GET["imdbid"];
  $movie->setid ($movieid);

  echo $movie->year().',';
  foreach ( $movie->genres() as $gen){
    echo $gen.',';
  }
  foreach( $movie->cast() as $cas){
    echo $cas["name"].',';
    }
  foreach( $movie->director() as $dir){
    echo $cas["name"].',';
  }
  foreach( $movie->writing() as $wri){
    echo $cas["name"].',';
  }
  foreach( $movie->producer() as $pro){
    echo $cas["name"].',';
  }
  foreach( $movie->composer() as $com){
    echo $cas["name"].',';
  }
  foreach( $movie->alsoknow() as $ak){
    echo $ak["title"];
  }
?>

ddrueding · Jan 15, 2008

Bah. Too many entries. I'd love to only return the first half-dozen important cast members, but many are billed by order of apprearance (eg. 12 Monkeys has Brad Pitt as #13).

ddrueding · Jan 15, 2008

I've reduced the amount of information pulled further. But I'm still getting strange repeats at the end.

HTML:

1995,Drama,Sci-Fi,Thriller,Joseph Melito,Bruce Willis,Jon Seda,Michael Chance,Vernon Campbell,H. Michael Walls,Bob Adrian,Simon Jones,Carol Florence,Bill Raymond,Ernest Abuba,Irma St. Paule,Madeleine Stowe,Joey Perillo,Bruce Kirkpatrick,Wilfred Williams,Rozwill Young,Brad Pitt,Nell Johnson,Frederick Strother,Rick Warner,Frank Gorshin,Anthony 'Chip' Brienza,Joilet Harris,Drucie McDaniel,John Blaisse,Louis Lippa,Stan Kang,Pat Dias,Aaron Michael Lacey,David Morse,Charles Techman,Jann Ellis,Johnnie Hobbs Jr.,Janet Zappala,Thomas Roy,Harry O'Toole,Yuri Korchenko,Chuck Jeffreys,Lisa Gay Hamilton,Felix Pire,Matt Ross,Barry Price,John Panzarella,Christopher Plummer,Larry Daly,Arthur Fennell,Karl Warren,Christopher Meloni,Paul Meshejian,Robert O'Neill,Kevin Thigpen,Lee Golden,Joseph McKenna,Jeff Tanner,Faith Potts,Michael Ryan Segal,Annie Golden,Lisa Talerico,Stephen Bridgewater,Ray Huffman,Jodi Dawson,Jack Dougherty,Lenny Daniels,Herbert C. Hauls Jr.,Charley Scalies,Carolyn Walker,Tiffany Baldwin,C.J. Byrnes,Phillip V. Caruso,Cathy D'Arcy,John Hagy,Adam Hatley,Julie Mabry,Sal Mazzotta,Roger Pratt,Allelon Ruggiero,Richard Stanley,Richard Stanley,Richard Stanley,Richard Stanley,Richard Stanley,

ddrueding · Jan 15, 2008

I'm an idiot. Fixed.

PHP:

<?php

require ("imdb.class.php");

$movie = new imdb ($_GET["imdbid"]);

  $movieid = $_GET["imdbid"];
  $movie->setid ($movieid);

  echo $movie->year().',';
  foreach ( $movie->genres() as $gen){
    echo $gen.',';
  }
  foreach( $movie->cast() as $cas){
    echo $cas["name"].',';
    }
  foreach( $movie->director() as $dir){
    echo $dir["name"].',';
  }
  foreach( $movie->writing() as $wri){
    echo $wri["name"].',';
  }
?>

Tannin · Jan 19, 2008

Hey, since when did this morph into a php/mysql thread? I don't know that I've ever opened it because I assumed it was all about movie collections (a topic I have zero interest in). Serves me right for not checking.

I can see you are getting some great advice here Dave, and building a very useful app.

(My big PHP project, by the way, is not too far off being ready to go public. The major issues I have yet to deal with are content-related and presentational - i.e., no coding task on the horizon that I shouldn't be able to manage OK, but a couple of fairly major "what the hell am I aiming to do here, and what do I want this bit to look like" problems are holding me up.)

Sing out if I can help, Dave, though it seems as though Doug and Blake are on the case and you are doing fine already.

Tea · Jan 19, 2008

ddrueding said:
foreach ( $movie->genres() as $gen){

Dave, you are getting into that object-oriented stuff. I've warned you about this stuff before. Look, it's OK if you smoke it now and then, but on no account are you to shoot it or shove it up your ars..... um .... bottom, OK? And only on weekends.

Do I need a database for movies?

Fixture

Storage? I am Storage!

Storage? I am Storage!

Storage? I am Storage!

Hairy Aussie

Administrator

Fixture

Hairy Aussie

Fixture

Fixture

Storage? I am Storage!

Fixture

Storage? I am Storage!

Fixture

Hairy Aussie

Fixture

Storage? I am Storage!

Fixture

Fixture

Administrator

Fixture

Administrator

Fixture

Administrator

Fixture

Fixture

Fixture

Fixture

Fixture

Fixture

Fixture

I can't believe I'm a Fixture

Administrator

Fixture

Fixture

Fixture

Fixture

Fixture

Storage? I am Storage!

Storage? I am Storage!