Show 1 Movie frm DBase and give page links Page 1 Page 2 Page 3

[noobtopro]

Quesion: Show each movie in the database on its own page, and give the user links in a "page 1, Page 2, Page 3" - type navigation system. Hint: Use LIMIT to control which movie is on which page. I have provided 3 files: 1st: configure DB, 2nd: insert data, 3rd: my code for the question. I would appreciate the help. I am a noob by the way.

 

First set up everything for DB:

 

<?php

//connect to MySQL

$db = mysql_connect('localhost', 'root', '000') or

    die ('Unable to connect. Check your connection parameters.');

 

//create the main database if it doesn't already exist

$query = 'CREATE DATABASE IF NOT EXISTS moviesite';

mysql_query($query, $db) or die(mysql_error($db));

 

//make sure our recently created database is the active one

mysql_select_db('moviesite', $db) or die(mysql_error($db));

 

//create the movie table

$query = 'CREATE TABLE movie (

        movie_id        INTEGER UNSIGNED  NOT NULL AUTO_INCREMENT,

        movie_name      VARCHAR(255)      NOT NULL,

        movie_type      TINYINT          NOT NULL DEFAULT 0,

        movie_year      SMALLINT UNSIGNED NOT NULL DEFAULT 0,

        movie_leadactor INTEGER UNSIGNED  NOT NULL DEFAULT 0,

        movie_director  INTEGER UNSIGNED  NOT NULL DEFAULT 0,

 

        PRIMARY KEY (movie_id),

        KEY movie_type (movie_type, movie_year)

    )

    ENGINE=MyISAM';

mysql_query($query, $db) or die (mysql_error($db));

 

//create the movietype table

$query = 'CREATE TABLE movietype (

        movietype_id    TINYINT UNSIGNED NOT NULL AUTO_INCREMENT,

        movietype_label VARCHAR(100)    NOT NULL,

        PRIMARY KEY (movietype_id)

    )

    ENGINE=MyISAM';

mysql_query($query, $db) or die(mysql_error($db));

 

//create the people table

$query = 'CREATE TABLE people (

        people_id        INTEGER UNSIGNED    NOT NULL AUTO_INCREMENT,

        people_fullname  VARCHAR(255)        NOT NULL,

        people_isactor    TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,

        people_isdirector TINYINT(1) UNSIGNED NOT NULL DEFAULT 0,

 

        PRIMARY KEY (people_id)

    )

    ENGINE=MyISAM';

mysql_query($query, $db) or die(mysql_error($db));

 

echo 'Movie database successfully created!';

?>

 

 

 

********************************************************************

***********************************************************************

 

second file to load info into DB:

 

 

 

<?php

// connect to MySQL

$db = mysql_connect('localhost', 'root', '000') or

    die ('Unable to connect. Check your connection parameters.');

 

//make sure you're using the correct database

mysql_select_db('moviesite', $db) or die(mysql_error($db));

 

// insert data into the movie table

$query = 'INSERT INTO movie

        (movie_id, movie_name, movie_type, movie_year, movie_leadactor,

        movie_director)

    VALUES

        (1, "Bruce Almighty", 5, 2003, 1, 2),

        (2, "Office Space", 5, 1999, 5, 6),

        (3, "Grand Canyon", 2, 1991, 4, 3)';

mysql_query($query, $db) or die(mysql_error($db));

 

// insert data into the movietype table

$query = 'INSERT INTO movietype

        (movietype_id, movietype_label)

    VALUES

        (1,"Sci Fi"),

        (2, "Drama"),

        (3, "Adventure"),

        (4, "War"),

        (5, "Comedy"),

        (6, "Horror"),

        (7, "Action"),

        (8, "Kids")';

mysql_query($query, $db) or die(mysql_error($db));

 

// insert data into the people table

$query  = 'INSERT INTO people

        (people_id, people_fullname, people_isactor, people_isdirector)

    VALUES

        (1, "Jim Carrey", 1, 0),

        (2, "Tom Shadyac", 0, 1),

        (3, "Lawrence Kasdan", 0, 1),

        (4, "Kevin Kline", 1, 0),

        (5, "Ron Livingston", 1, 0),

        (6, "Mike Judge", 0, 1)';

mysql_query($query, $db) or die(mysql_error($db));

 

echo 'Data inserted successfully!';

?>

 

 

 

**************************************************************

****************************************************************

 

MY CODE FOR THE QUESTION:

 

 

<?php

$db = mysql_connect('localhost', 'root', '000') or

die ('Unable to connect. Check your connection parameters.');

 

mysql_select_db('moviesite', $db) or die(mysql_error($db));

 

//get our starting point for the query from the URL

if (isset($_GET['offset'])) {

$offset = $_GET['offset'];

} else {

$offset = 0;

}

 

//get the movie

$query  = 'SELECT

movie_name, movie_year

FROM

movie

ORDER BY

movie_name

LIMIT ' . $offset . ' , 1';

$result = mysql_query($query, $db) or die(mysql_error($db));

$row = mysql_fetch_assoc($result);

?>

<html>

<head>

<title><?php echo $row['movie_name']; ?></title>

</head>

<body>

<table border = "1">

<tr>

<th>Movie Name</th>

<th>Year</th>

</tr><tr>

<td><?php echo $row['movie_name']; ?></td>

<td><?php echo $row['movie_year']; ?></td>

</tr>

</table>

<p>

<a href="page.php?offset=0">Page 1</a>,

<a href="page.php?offset=1">Page 2</a>,

<a href="page.php?offset=2">Page 3</a>

</p>

</body>

</html>

 

 

[Psycho]

#1: Please enclose your code in [ code ] tags for readability.

 

#2: What is your question? This is obviously a homework assignment and you have attempted the problem on your own. Way to go! Most people just try and pass their homework as a "real" problem and try to get someone to write it all for them. Are you getting any errors or is the code doing something other than you expect it to?

[noobtopro]

Sorry about that, I will attach files next time. Actually I just solved it, too much coffee. I didn't the page in the anchor links (thought I did) ooops. Problem solved. Thank you for the support.