...

View Full Version : pagination



destas
03-09-2013, 05:04 PM
Hello, can anyone help me with pagination?
How it must work: I need to count movies where one of the actor have acted in this movie.


i have 3 tables:


INSERT INTO `movies` (`id`, `title`, `year`) VALUES
(1, 'The Lorax', 2012),
(2, 'The test', 2013);

INSERT INTO `actors` (`actor_id`, `actor_name`) VALUES
(1, 'john ugu'),
(2, 'emily fun');

INSERT INTO `movieactors` (`movie_id`, `actor_id`) VALUES
(1, 1),
(2, 1),
(1, 2);




$page_query = mysql_query("SELECT m.id,
GROUP_CONCAT(DISTINCT a.actor_name ORDER BY a.actor_name) AS actors
FROM movies m
INNER JOIN movieactors mg ON (mg.actor_id = m.id)
INNER JOIN actors g ON (g.actor_id = mg.genre_id)
INNER JOIN
(
SELECT actor_name FROM movieactors
WHERE actor_name = '$actor_name'
) as act ON ma.actor_name = act.actor_name
WHERE a.actor_name <> '$actor_name'
GROUP BY m.id
ORDER BY COUNT(DISTINCT a.actor_name) DESC, m.id");
$pages = ceil(mysql_result($page_query, 0) / $MovieListPerPage);
$page = (isSet($_GET['page']) AND (int)$_GET['page'] > 0 ) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $MovieListPerPage;

Old Pedant
03-11-2013, 01:15 AM
Please show us what RESULT you want from the query, using the data you have shown.

I see you want

'The Lorax', 'emily fun, john ugu'
'The Test', 'john ugu'

from the first part, but I am not sure what you want from the second part.

destas
03-11-2013, 01:23 PM
I will give you an example:

I click on actor name john ugu (http://www.../john ugu) and on this page i need to get/count all movies where actor john ugu was acted.

Old Pedant
03-11-2013, 06:30 PM
That is trivial:


SELECT COUNT(*)
FROM movieactors, actors
WHERE movieactors.actor_id = actors.actor_id
AND actors.actor_name = 'john ugu'

destas
03-12-2013, 01:50 AM
Can you explain for me why when i use WHERE a.actor_name = 'john ugu' all movies from list have only one actor (john ugu)? :D How to get movies where john ugu was acted and all genres/actors?



SELECT m.id, m.title, m.year,
GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,
GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.genre_id = mg.genre_id)
INNER JOIN movieactors ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.actor_id = ma.actor_id)
WHERE a.actor_name = '%s'
LIMIT %d, %d"

Old Pedant
03-12-2013, 02:50 AM
You asked that question before and I thought I told you.

When you use WHERE a.actor_name = 'John Ugu' then *OF COURSE* you can not get any records where a.actor_name is ANYTHING ELSE.

I *THINK* what you are after is this:


SELECT m.id, m.title, m.year,
GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,
GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.genre_id = mg.genre_id)
INNER JOIN movieactors ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.actor_id = ma.actor_id)
INNER JOIN (
movieactors AS ma2 INNER JOIN actors AS a2
ON ma2.actor_id = a2.actor_id
AND a2.actor_name = 'John Ugu' ) AS X
ON X.movie_id = m.id
LIMIT %d, %d

You see that? You must have a *SEPARATE QUERY* (or at least a separate *part* to the query) that gets you all the movie id's where 'John Ugu' acted and *THEN* you can do your main query.

And alternative form of that which might be easier to understand:


SELECT m.id, m.title, m.year,
GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,
GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.genre_id = mg.genre_id)
INNER JOIN movieactors ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.actor_id = ma.actor_id)
WHERE m.id IN (
SELECT ma2.movie_id
FROM movieactors AS ma2 INNER JOIN actors AS a2
ON ma2.actor_id = a2.actor_id
AND a2.actor_name = 'John Ugu' )
LIMIT %d, %d

but *probably* the first form will execute faster in MySQL. (SQL Server would see the two as equivalent.)

destas
03-12-2013, 08:20 AM
Doesn't work with this query.


SELECT m.id, m.title, m.year,
GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,
GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.genre_id = mg.genre_id)
INNER JOIN movieactors ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.actor_id = ma.actor_id)
INNER JOIN (
movieactors AS ma2 INNER JOIN actors AS a2
ON ma2.actor_id = a2.actor_id
AND a2.actor_name = 'John Ugu' ) AS X
ON X.movie_id = m.id
LIMIT %d, %d



With this query show only one movie.. But i'm not sure that problem is in query.


SELECT m.id, m.title, m.year,
GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,
GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.genre_id = mg.genre_id)
INNER JOIN movieactors ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.actor_id = ma.actor_id)
WHERE m.id IN (
SELECT ma2.movie_id
FROM movieactors AS ma2 INNER JOIN actors AS a2
ON ma2.actor_id = a2.actor_id
AND a2.actor_name = 'John Ugu' )
LIMIT %d, %d



My previous DB was in one table

(`id`, `title`, `year`, `genres`, `actors`)
(1, 'The Lorax', 2012, 'comedy, fantasy', 'john ugu, emily fun' ),
(2, 'The test', 2013, 'horror', 'rembo');

For pagination i use

SELECT COUNT(actors) from `moviedb` WHERE `actors` LIKE '$actor_name'

For movie list of current actor (john ugu)

SELECT * FROM moviedb WHERE actors LIKE '%s' LIMIT %d, %d;

BubikolRamios
03-12-2013, 07:42 PM
My previous DB was in one table[/B]

(`id`, `title`, `year`, `genres`, `actors`)
(1, 'The Lorax', 2012, 'comedy, fantasy', 'john ugu, emily fun' ),
(2, 'The test', 2013, 'horror', 'rembo');

For pagination i use

SELECT COUNT(actors) from `moviedb` WHERE `actors` LIKE '$actor_name'

For movie list of current actor (john ugu)

SELECT * FROM moviedb WHERE actors LIKE '%s' LIMIT %d, %d;

What does this have with anything ? It is depreceated, not used, as I see ?

Google for SQL_CALC_FOUND_ROWS, that is used to get total number of records regardles of limit.

Old Pedant
03-12-2013, 09:17 PM
Okay...I actually create a set of DB tables to test this. Here's a dump of all my tables:


mysql> select * from movies;
+----+--------------------+------+------------+------+---------------------+
| id | title | year | premiere | imdb | added |
+----+--------------------+------+------------+------+---------------------+
| 1 | kink kong | 1939 | 0000-00-00 | xyz2 | 2013-02-17 16:00:52 |
| 2 | 2001 space odyssey | 1967 | 0000-00-00 | abc3 | 2013-02-17 16:01:51 |
| 3 | zombies | 2012 | 0000-00-00 | jjj8 | 2013-02-17 16:01:51 |
+----+--------------------+------+------------+------+---------------------+
3 rows in set (0.00 sec)

mysql> select * from actors;
+----------+----------------+
| actor_id | actor |
+----------+----------------+
| 1 | Alan Arkin |
| 2 | Bela Lugosi |
| 3 | Carmen Miranda |
| 4 | Don Knotts |
+----------+----------------+
4 rows in set (0.00 sec)

mysql> select * from genres;
+----+---------+
| id | genre |
+----+---------+
| 1 | comedy |
| 2 | fantasy |
| 3 | horror |
+----+---------+
3 rows in set (0.00 sec)

mysql> select * from movieactors;
+----------+----------+
| movie_id | actor_id |
+----------+----------+
| 1 | 1 |
| 1 | 4 |
| 2 | 2 |
| 3 | 2 |
| 3 | 3 |
| 3 | 4 |
+----------+----------+
6 rows in set (0.00 sec)

mysql> select * from moviegenres;
+----------+----------+
| movie_id | genre_id |
+----------+----------+
| 1 | 1 |
| 1 | 2 |
| 2 | 3 |
| 3 | 3 |
+----------+----------+
4 rows in set (0.00 sec)


And then I used this query:

SELECT m.id, m.title, m.year,
GROUP_CONCAT(DISTINCT g.genre SEPARATOR ', ') AS genres,
GROUP_CONCAT(DISTINCT a.actor SEPARATOR ', ') AS actors
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.id = mg.genre_id)
INNER JOIN movieactors ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.actor_id = ma.actor_id)
WHERE m.id IN (
SELECT ma2.movie_id
FROM movieactors AS ma2 INNER JOIN actors AS a2
ON ma2.actor_id = a2.actor_id
AND a2.actor = 'bela lugosi')
GROUP BY m.id, m.title, m.year;

And got these results:

+----+--------------------+------+--------+-----------------------------------------+
| id | title | year | genres | actors |
+----+--------------------+------+--------+-----------------------------------------+
| 2 | 2001 space odyssey | 1967 | horror | Bela Lugosi |
| 3 | zombies | 2012 | horror | Don Knotts, Carmen Miranda, Bela Lugosi |
+----+--------------------+------+--------+-----------------------------------------+
Which are correct.

So I think the only thing wrong with that code is you are missing the GROUP BY! That's why you only got 1 movie.

Old Pedant
03-12-2013, 09:19 PM
In other words, use this:


SELECT m.id, m.title, m.year,
GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,
GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.genre_id = mg.genre_id)
INNER JOIN movieactors ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.actor_id = ma.actor_id)
WHERE m.id IN (
SELECT ma2.movie_id
FROM movieactors AS ma2 INNER JOIN actors AS a2
ON ma2.actor_id = a2.actor_id
AND a2.actor_name = 'John Ugu' )
GROUP BY m.id, m.title, m.year
LIMIT %d, %d

destas
03-15-2013, 01:19 AM
Forget about it :D Ok i have a last question.

Trying to make search. How to get FROM 3 tables ?


"SELECT * FROM `movies` WHERE `title` = '%s'



OR `actor_name` = '%s' OR `genre_name` = '%s' LIMIT %d, %d;"

Old Pedant
03-15-2013, 07:01 AM
Just make the query even uglier.


SELECT m.id, m.title, m.year,
GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,
GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.genre_id = mg.genre_id)
INNER JOIN movieactors ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.actor_id = ma.actor_id)
WHERE m.id IN (
SELECT ma2.movie_id
FROM movieactors AS ma2 INNER JOIN actors AS a2
ON ma2.actor_id = a2.actor_id
AND a2.actor_name = '%s' )
OR m.id IN (
SELECT ma2.movie_id
FROM moviegenres AS mg2 INNER JOIN genres AS g2
ON mg2.genre_id = g2.gener_id
AND g2.genre_name = '%s' )
GROUP BY m.id, m.title, m.year
LIMIT %d, %d

destas
03-15-2013, 01:52 PM
tried to make pagination for search.

From:


SELECT COUNT(*)
FROM movieactors, actors
WHERE movieactors.actor_id = actors.actor_id
AND actors.actor_name = '$actor_name'


To:


SELECT COUNT(*)
FROM movies, movieactors, actors, moviegenres, genres
WHERE movies.title = '$movie_name'
OR movieactors.actor_id = actors.actor_id
AND actors.actor_name = '$actor_name'
OR moviegenres.genre_id = genres.genre_id
AND genres.genre_name = '$genre_name'


And i get some error :D
Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 1251362 bytes)

BubikolRamios
03-15-2013, 02:48 PM
restart php editor, that should help.

destas
03-15-2013, 06:07 PM
same...

BubikolRamios
03-15-2013, 08:37 PM
Ok, I'm on java, hence there are two processes, java and jawaw or something like that (one from net beans and one from tomcat). In case like yours I have to kill them both. Then restart editor.

as last thing, if that does not work, restart computer.

destas
03-15-2013, 10:43 PM
I use ini_set('memory_limit', '-1'); and now i see tons of pages :D wrong query.



SELECT COUNT(*)
FROM movies, movieactors, actors, moviegenres, genres
WHERE movies.title = '$movie_name'
OR movieactors.actor_id = actors.actor_id
AND actors.actor_name = '$actor_name'
OR moviegenres.genre_id = genres.genre_id
AND genres.genre_name = '$genre_name'




SELECT COUNT(*)
FROM movies, movieactors, actors, moviegenres, genres


How to count only from one table?
if movies.title = '$movie_name' then count from movies table.
if actors.actor_name = '$actor_name' count from movieactors and actors.


I try with UNION. When i type movie name i see pagination but when i type genre or actor name doesn't show pagination...


SELECT COUNT(*) FROM movies
WHERE title = '$movie_name'
UNION
SELECT COUNT(*) FROM movieactors, actors
WHERE movieactors.actor_id = actors.actor_id
AND actors.actor_name = '$actor_name'
UNION
SELECT COUNT(*) FROM moviegenres, genres
WHERE moviegenres.genre_id = genres.genre_id
AND genres.genre_name = '$genre_name'

destas
03-17-2013, 07:08 PM
up:rolleyes:

Old Pedant
03-17-2013, 10:15 PM
What does "pagination" mean to you?

To me, it means you are getting hundreds of results from your query and you only want to see ONE PAGE at a time.

destas
03-18-2013, 09:21 AM
Yes, but now i'm getting hundreds of results and hundreds of pages. I will make some image when i back to home.

destas
03-18-2013, 07:10 PM
Now just loading window 2 hours and nothing.

if i type in to search panel movie name, query must select and count only from movies table...



SELECT COUNT(*)
FROM movies, movieactors, actors, moviegenres, genres
WHERE movies.title = '$movie_name'
OR movieactors.actor_id = actors.actor_id
AND actors.actor_name = '$actor_name'
OR moviegenres.genre_id = genres.genre_id
AND genres.genre_name = '$genre_name'


Do i need 3 query? :D


SELECT COUNT(*)
FROM movies
WHERE movies.title = '$movie_name'

And why (if movie name STOLEN) and i type STOL doesn't show results?

Old Pedant
03-18-2013, 08:51 PM
Easy question first:
> why (if movie name STOLEN) and i type STOL doesn't show results?

Because you are using WHERE movies.title = 'STOL' and = means "exactly the same".

You would need to use WHERE movies.title LIKE '%STOL%' to match *partial* results.

*********

Anyway, your SELECT COUNT(*) is totally bogus. You don't understand how database JOINs work.

Your query needs to be like this:


SELECT COUNT( DISTINCT X.id ) AS movieCount FROM
( SELECT id FROM movies
WHERE movies.title = '$movie_name'
UNION
SELECT movie_id FROM
moviegenres, genres
WHERE moviegenres.genre_id = genres.genre_id
AND genres.genre_name = '$genre_name'
UNION
SELECT movie_id FROM
movieactors, actors
WHERE movieactors.actor_id = actors.actor_id
AND actors.actor_name = '$actor_name'
) AS X

destas
03-19-2013, 07:22 PM
Error from OR m.id IN

Invalid query: Unknown column 'ma2.movie_id' in 'field list'.



SELECT m.id, m.title, m.year,
GROUP_CONCAT(DISTINCT g.genre_name SEPARATOR ', ') AS genres,
GROUP_CONCAT(DISTINCT a.actor_name SEPARATOR ', ') AS actors
FROM movies m
INNER JOIN moviegenres mg ON (mg.movie_id = m.id)
INNER JOIN genres g ON (g.genre_id = mg.genre_id)
INNER JOIN movieactors ma ON (ma.movie_id = m.id)
INNER JOIN actors a ON (a.actor_id = ma.actor_id)
WHERE m.title LIKE '%%%s%%'
OR m.year = '%s'
OR m.id IN (
SELECT ma2.movie_id
FROM movieactors AS ma2 INNER JOIN actors AS a2
ON ma2.actor_id = a2.actor_id
AND a2.actor_name LIKE '%%%s%%' )
OR m.id IN (
SELECT ma2.movie_id
FROM moviegenres AS mg2 INNER JOIN genres AS g2
ON mg2.genre_id = g2.gener_id
AND g2.genre_name LIKE '%%%s%%' )
GROUP BY m.id, m.title, m.year
LIMIT %d, %d

Old Pedant
03-19-2013, 09:10 PM
Oh for cryin' out loud.

Obviously a simple typo. Needs to be

OR m.id IN (
SELECT mg2.movie_id
FROM moviegenres AS mg2 INNER JOIN genres AS g2

You need to do *SOME* debugging for yourself. It's tough to try and debug your database when I don't have a copy of it.

BubikolRamios
03-19-2013, 09:18 PM
Oh for cryin' out loud.


Man, I was observing your patience & providing tons of code and wondered when will this pop out :)

Old Pedant
03-19-2013, 09:42 PM
LOL! Yeah, I guess I should apologize. But he keeps hopping back and forth from one query to the other, without ever resolving either one completely. If he'd stick to *ONE* until it works, it would be much easier to handle.

djm0219
03-19-2013, 09:43 PM
Man, I was observing your patience & providing tons of code and wondered when will this pop out :)

That was the family friendly version I'm sure :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum