Feature Request: Remote Database and/or Movie List Export (4 Viewers)

LRFalk01 · January 16, 2009

rolls1400 said:
Does anyone know where i could download a sample sqlite db file? I dont currently have one(at work) and id like to get started on the .net project for it. I was unable to find one online so maybe someone could post one of there moving pictures file if its not too much hassle?

This is a copy of my sqlite db. I will be removing this from my site in about 2 hours.

http://echoes1.game-host.org/newmovies/movingpictures.db3

I just found this SQLite add-in for Firefox:
https://addons.mozilla.org/en-US/firefox/addon/5817

After it is installed you can go to tools and open the program.

rolls1400 · January 16, 2009

LRFalk01 said:
rolls1400 said:

Does anyone know where i could download a sample sqlite db file? I dont currently have one(at work) and id like to get started on the .net project for it. I was unable to find one online so maybe someone could post one of there moving pictures file if its not too much hassle?

Click to expand...

This is a copy of my sqlite db. I will be removing this from my site in about 2 hours.

http://echoes1.game-host.org/newmovies/movingpictures.db3

I just found this SQLite add-in for Firefox:
https://addons.mozilla.org/en-US/firefox/addon/5817

After it is installed you can go to tools and open the program.

Ok thanks, good to go

shackrock · January 16, 2009

Rolls - no clue here, sorry!
LRFalk - Amazing, both things. I'm going with the sorttable one (2nd link), because it reminds me of AJAX and I think that is pretty cool not having to refresh! Plus, easy as pie.

I'm still trying to get that "my rating" field working, but I'm getting close I think, check this out:

$queryStatement = 'SELECT user_rating FROM user_movie_settings';

That statement will successfully get my ratings from the database. However, if I use that it will ONLY fetch that data, and not the rest of the data, since it's from a seperate master table. How can I fetch from two different master tables and display it on the same chart/table?

LRFalk01 · January 16, 2009

shackrock said:
Rolls - no clue here, sorry!
LRFalk - Amazing, both things. I'm going with the sorttable one (2nd link), because it reminds me of AJAX and I think that is pretty cool not having to refresh! Plus, easy as pie.

I'm still trying to get that "my rating" field working, but I'm getting close I think, check this out:

$queryStatement = 'SELECT user_rating FROM user_movie_settings';

That statement will successfully get my ratings from the database. However, if I use that it will ONLY fetch that data, and not the rest of the data, since it's from a seperate master table. How can I fetch from two different master tables and display it on the same chart/table?

'SELECT T1.id, T1.sortby, T1.year, T1.genres, T1.score, T1.imdb_id, T1.certification, T1.runtime T2.user_rating FROM movie_info T1, user_movie_settings T2 WHERE T1.id = T2.id';

This will grab all of the original data and the user_rating information. That table sorting is just javascript. Ajax is when javascript does a server side call. One thing that was pointed out to be yesterday is that the sorting of the title does not work right.

-LRFalk01

shackrock · January 16, 2009

hm, still no luck here...take a look:

Code:

<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html;charset=iso-8859-1" />
		<script type="text/javascript" src="javascript/mootools1.2.1.js"></script>
		<script type="text/javascript" src="javascript/movies.js"></script>
		<script type="text/javascript" src="javascript/sorttable.js"></script>
		<link rel="stylesheet" href="css/main.css" type="text/css" media="screen" charset="utf-8" />
		<link rel="SHORTCUT ICON" HREF="http://echoes1.game-host.org/Movies/img/logo.ico" />

		<title>HTPC Movies</title>

	</head>
	<body>

<center>The HTPC's Flicks...</center><p>


<?php

/* 
"SELECT T1.title FROM movie_info T1, user_movie_settings T2 WHERE T1.ID = T2.ID AND T2.watched > 0"

This select statement should give you a list of movies that have been watched. You could add an 'ORDER BY T2.watched DESC' at the end of that and it will also give you a top down from most watched to least watched movie.
*/

try {
    /*** connect to SQLite database ***/
    $dbh = new PDO("sqlite:movingpictures.db3");
    }
catch(PDOException $e){
    echo $e->getMessage();
    }

$queryStatement = 'SELECT T1.id, T1.sortby, T1.year, T1.genres, T1.score, T1.imdb_id, T1.certification, T1.runtime, T2.user_rating FROM movie_info T1, user_movie_settings T2 WHERE T1.id = T2.id';



//Query results
$stmt = $dbh->query($queryStatement);

?>
        <table class="sortable" border='1'>
            <thead>
                <tr>
					<td>ID</td>
                    <td>Title</td>
                    <td>Year</td>
                    <td>Genres</td>
                    <td>IMDB Score</td>
					<td>My Score</td>
                    <td>Rating</td>
                    <td>Runtime</td>
                    <td>IMDB</td>
                </tr>
            </thead>
        <?php
        //loop through results to make table
while($next_row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        ?>
<tr class="movieRow">
				<td class="movieid"><?php print($next_row["T1.id"]); ?></td>
                <td class="movieTitle"><?php print($next_row["T1.sortby"]); ?></td>
                <td class="movieYear"><?php print($next_row["T1.year"]); ?></td>
                <td class="movieGenres"><?php print(str_replace('|', ' | ', $next_row["T1.genres"])); ?></td>
                <td class="movieScore"><?php print($next_row["T1.score"]); ?></td>
				<td class="movieMyScore"><?php print($next_row["T2.user_rating"]); ?></td>	
                <td class="movieRating"><?php print($next_row["T1.certification"]); ?></td>
                <td class="movieLength"><?php print($next_row["T1.runtime"]); ?></td>
                <td class="movieImdb"><a href="http://www.imdb.com/title/<?php print($next_row["T1.imdb_id"]); ?>">IMDB</a></td>    
</tr>
        <?php
        }
        ?>
        </table>

<p>
	</body>

</html>

link to the site:
HTPC Movies

rolls1400 · January 16, 2009

MovieListOutput

So i created a basic app that will take data from the movingpictures db3 file and produce a html file. It also allows you to pick which columns you want to display. It is very basic but if people want things added let me know.
Ill be gone all weekend so no replies till monday but leave me feedback if you try it.

Ok so here is the zip which contains the file and dlls(didnt know if you need them or not.). I published it in 2008 so you have to install it.

Rico · January 16, 2009

Works perfect that tool

Check weblink of my current database:

Movie List

I edited it a little in google docs but if i create a new list every time i have to edit everything to make the background color,
Table width, Font, Font Size, Ect. Ect.

This should be a great implentation so we dont have to edit the html file everytime we update our movie list,
cause your standard output of the file looks like this:

Standard output of the tool

also some future requests:

1. That you can click the link details_url that you forwarded directly on the website.
2. Also that you can direct forwarded to the imdb_id title page from the index.html.
3. Is it also possible to edit the tool that it can also create a webpage with thumbnails that should be cool

4. Editing of the html file like Color, Borderwidht of tables, Font, ect. ect.
5. I also found out that the tool not sorts the index.html on Alphabetic Order this should also be oke if it can be fixed.

Thanks Man

LRFalk01 · January 17, 2009

Code:

<tr class="movieRow">
				<td class="movieid"><?php print($next_row["T1.id"]); ?></td>
                <td class="movieTitle"><?php print($next_row["T1.sortby"]); ?></td>
                <td class="movieYear"><?php print($next_row["T1.year"]); ?></td>
                <td class="movieGenres"><?php print(str_replace('|', ' | ', $next_row["T1.genres"])); ?></td>
                <td class="movieScore"><?php print($next_row["T1.score"]); ?></td>
				<td class="movieMyScore"><?php print($next_row["T2.user_rating"]); ?></td>	
                <td class="movieRating"><?php print($next_row["T1.certification"]); ?></td>
                <td class="movieLength"><?php print($next_row["T1.runtime"]); ?></td>
                <td class="movieImdb"><a href="http://www.imdb.com/title/<?php print($next_row["T1.imdb_id"]); ?>">IMDB</a></td>

This is where it is going wrong. You do not need the table declarations here.

Code:

<tr class="movieRow">
				<td class="movieid"><?php print($next_row["id"]); ?></td>
                <td class="movieTitle"><?php print($next_row["sortby"]); ?></td>
                <td class="movieYear"><?php print($next_row["year"]); ?></td>
                <td class="movieGenres"><?php print(str_replace('|', ' | ', $next_row["genres"])); ?></td>
                <td class="movieScore"><?php print($next_row["score"]); ?></td>
				<td class="movieMyScore"><?php print($next_row["user_rating"]); ?></td>	
                <td class="movieRating"><?php print($next_row["certification"]); ?></td>
                <td class="movieLength"><?php print($next_row["runtime"]); ?></td>
                <td class="movieImdb"><a href="http://www.imdb.com/title/<?php print($next_row["imdb_id"]); ?>">IMDB</a></td>

Try that.

Can you remove this line from your page? It is going back to my page.

Code:

<link rel="SHORTCUT ICON" HREF="http://echoes1.game-host.org/Movies/img/logo.ico" />

-LRFalk01

shackrock · January 17, 2009

nope, still the same. new code below:

Code:

<html>
	<head>
		<meta http-equiv="Content-Type" content="text/html;charset=iso-8859-1" />
		<script type="text/javascript" src="javascript/mootools1.2.1.js"></script>
		<script type="text/javascript" src="javascript/movies.js"></script>
		<script type="text/javascript" src="javascript/sorttable.js"></script>
		<link rel="stylesheet" href="css/main.css" type="text/css" media="screen" charset="utf-8" />

		<title>HTPC Movies</title>

	</head>
	<body>

<center>The HTPC's Flicks...</center><p>


<?php

/* 
"SELECT T1.title FROM movie_info T1, user_movie_settings T2 WHERE T1.ID = T2.ID AND T2.watched > 0"

This select statement should give you a list of movies that have been watched. You could add an 'ORDER BY T2.watched DESC' at the end of that and it will also give you a top down from most watched to least watched movie.
*/

try {
    /*** connect to SQLite database ***/
    $dbh = new PDO("sqlite:movingpictures.db3");
    }
catch(PDOException $e){
    echo $e->getMessage();
    }

$queryStatement = 'SELECT T1.id, T1.sortby, T1.year, T1.genres, T1.score, T1.imdb_id, T1.certification, T1.runtime, T2.user_rating FROM movie_info T1, user_movie_settings T2 WHERE T1.id = T2.id';



//Query results
$stmt = $dbh->query($queryStatement);

?>
        <table class="sortable" border='1'>
            <thead>
                <tr>
					<td>ID</td>
                    <td>Title</td>
                    <td>Year</td>
                    <td>Genres</td>
                    <td>IMDB Score</td>
					<td>My Score</td>
                    <td>Rating</td>
                    <td>Runtime</td>
                    <td>IMDB</td>
                </tr>
            </thead>
        <?php
        //loop through results to make table
while($next_row = $stmt->fetch(PDO::FETCH_ASSOC))
    {
        ?>
<tr class="movieRow">
				<td class="movieid"><?php print($next_row["id"]); ?></td>
                <td class="movieTitle"><?php print($next_row["sortby"]); ?></td>
                <td class="movieYear"><?php print($next_row["year"]); ?></td>
                <td class="movieGenres"><?php print(str_replace('|', ' | ', $next_row["genres"])); ?></td>
                <td class="movieScore"><?php print($next_row["score"]); ?></td>
				<td class="movieMyScore"><?php print($next_row["user_rating"]); ?></td>	
                <td class="movieRating"><?php print($next_row["certification"]); ?></td>
                <td class="movieLength"><?php print($next_row["runtime"]); ?></td>
                <td class="movieImdb"><a href="http://www.imdb.com/title/<?php print($next_row["imdb_id"]); ?>">IMDB</a></td>    
</tr>
        <?php
        }
        ?>
        </table>

<p>
	</body>

</html>

LRFalk01 · January 17, 2009

Your code works for me. Moving Pictures Movies

I think it is the path to your SQLite3 db. Try putting a \ or a .\ in front of the file. . . it should be trowing an exception if PHP is not connecting to it though.

Try this being your path: http://thesoundaswordmakes.com/movies/movingpictures.db3

Try something like:

Code:

$stmt = $dbh->query($queryStatement);
print_r($stmt);

while($next_row = $stmt->fetch(PDO::FETCH_ASSOC))
{
     print_r($next_row);
}

Feature Request: Remote Database and/or Movie List Export (4 Viewers)

LRFalk01

Portal Pro

rolls1400

Portal Pro

shackrock

Portal Pro

LRFalk01

Portal Pro

shackrock

Portal Pro

rolls1400

Portal Pro

Attachments

Rico

Portal Pro

LRFalk01

Portal Pro

shackrock

Portal Pro

LRFalk01

Portal Pro

Users who are viewing this thread