Solved

Next & Prev image

Posted on 2009-05-10
19
324 Views
Last Modified: 2012-05-06
Hi Experts,
What's the best way to get the next and previouse image to the current one?!! I'm using a long quesries now but I think there is a special function in MySQL to get the previouse/next record for you. !!

Best regards,
0
Comment
Question by:Shopies
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
19 Comments
 
LVL 14

Expert Comment

by:racek
ID: 24351792
if you have a numeric column as primary key:

select b.before, a.id current , c.after
from yourtabel a where id = '???'
left join
(select MAX(id) before
from yourtabel WHERE id < '???') AS b
left join
(select MIN(id) after
from yourtabel WHERE id > '???') AS c
0
 
LVL 4

Expert Comment

by:Finec
ID: 24351895
Or use the LIMIT modifier. LIMIT offset, pieces.
For example:
"YOUR QUERY" LIMIT 10,1; (it returns 1 row starts from the 10th row)
0
 
LVL 14

Expert Comment

by:racek
ID: 24351934
good idea but you need to find order nr:
select count(*) - 1 from yourtabel a where id = '???'
returns 55
then
select * from yourtabel limit 55,3

but it will be 3 rows - with my suggestion is it only one row :-)
0
Stressed Out?

Watch some penguins on the livecam!

 

Author Comment

by:Shopies
ID: 24352041
@racek Your query gave me an error:
Warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'left join (select MAX(id) before from pics WHERE id < '831')' at line 3 in
 
Any clue?
0
 

Author Comment

by:Shopies
ID: 24352046
@Finec Although your solution is very simple, In your case I will need to do 3 queries. I will use it in case the previouse one failed.
 
Regards,
0
 

Author Comment

by:Shopies
ID: 24352127
@Finec
I tried your way but it returned nothing although there are:
Query [6] -- [SELECT id,title,thump FROM pics WHERE id < '827' AND catid='14' LIMIT 827,1]
0
 

Author Comment

by:Shopies
ID: 24352147
Sorry Finec but this way needs that the IDs be sorted seriesly and there should be enough number before the current picture ID or it will not work. For example, The last image ID is 827 when I use LIMIT 827,1 there MUST be a 826 records before the ID 827 or it will not work. The latest ID I have is 827 while I just have 145 records in the DB.
Any solution for that?
0
 
LVL 19

Expert Comment

by:NerdsOfTech
ID: 24352321
Shopie, do us experts a favor and output your table structure (table name and field names / field types)

racek is guru on this; I'll help if he won't answer it. Chances are he'll have the answer.

Thanks
0
 
LVL 14

Expert Comment

by:racek
ID: 24352942

SELECT (select MAX(id) from yourtabel WHERE id < '???') AS before,
       (select a.id from yourtabel a where id = '???' ) AS current,
       (select MIN(id) from yourtabel WHERE id > '???') AS after;

Open in new window

0
 

Author Comment

by:Shopies
ID: 24354642
OKay this is my table structure and I didn't try racek second query. I'll try it and let you know results.

CREATE TABLE `pics` (
  `id` int(11) NOT NULL auto_increment,
  `title` varchar(250) default NULL,
  `thump` varchar(250) default NULL,
  `date` int(11) default NULL,
  `depart` varchar(250) NOT NULL,
  `catid` int(11) NOT NULL default '0',
  `content` text NOT NULL,
  `view` int(11) NOT NULL default '0',
  `visible` int(11) NOT NULL default '1',
  `pub` int(11) NOT NULL default '1',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=cp1256 AUTO_INCREMENT=832 ;

Open in new window

0
 
LVL 14

Expert Comment

by:racek
ID: 24355326
as one row with ids
SELECT (select MAX(id) from pics WHERE id < '???') AS before,
       (select a.id from pics a where id = '???' ) AS current,
       (select MIN(id) from pics WHERE id > '???') AS after;

Open in new window

0
 
LVL 14

Expert Comment

by:racek
ID: 24355396
all rows (row you are looking for could be first or last !!!!!
'???' replace med id number
SELECT * from pics WHERE ID = (select MAX(id) from pics WHERE id < '???') 
UNION ALL
SELECT * from pics WHERE ID = (select a.id from pics a where id = '???' )
UNION ALL
SELECT * from pics WHERE ID = (select MIN(id) from pics WHERE id > '???') 
ORDER BY id;

Open in new window

0
 

Author Comment

by:Shopies
ID: 24401524
I tried the code before the last one (you can see it in the snippet below) but it still gives me an error which says:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\AppServ\www\phpMyAdmin\shopiesCMS\album.php on line 5
I changed all the variables need to be changed as you can see.

$query = mysql_query("SELECT (select MAX(id) from pics WHERE id < '".$get_id."') AS before,
       (select a.id from pics a where id = '".$get_id."' ) AS current,
       (select MIN(id) from pics WHERE id > '".$get_id."') AS after;");
		$row = mysql_fetch_array($query);

Open in new window

0
 
LVL 14

Expert Comment

by:racek
ID: 24401608

I tried it in mysql and problem is column names. rename them. (and synonym for curr is not necessary) this works in SQL:
 
SELECT (select MAX(id) from pics WHERE id < 3) AS bef,
       (select a.id from pics a where id = 3 ) AS curr,
       (select MIN(id) from pics WHERE id > 3) AS aft;
 
for you:
 
$query = mysql_query("SELECT (select MAX(id) from pics WHERE id < '".$get_id."') AS bef,
       (select id from pics where id = '".$get_id."' ) AS curr,
       (select MIN(id) from pics WHERE id > '".$get_id."') AS aft;");
                $row = mysql_fetch_array($query);

Open in new window

0
 

Author Comment

by:Shopies
ID: 24401987
It's working GREAT now but what if I want to add more columns other than just the ID. Say I want to fetch the thump colum as an example? How to do it?
0
 
LVL 14

Expert Comment

by:racek
ID: 24402036
if not id = 3 then no return, row before or after doesn't need to be there (first - last) therefore LEFT JOIN

SELECT b.col1,b.col2..., c.col1,c.col2... a.col1, a.col2
FROM
pics c  id  
JOIN (SELECT (select MAX(id) from pics WHERE id < 3) AS bef,
                     (select a.id from pics a where     id = 3 )  AS curr,
                     (select MIN(id)  from pics WHERE id > 3)  AS aft ) AS acb
                             ON  acb.curr = c.id
LEFT JOIN pics AS b ON acb.bef   = b.id
LEFT JOIN pics AS a ON acb.aft    = a.id;
0
 
LVL 14

Expert Comment

by:racek
ID: 24402040
easier is if you want 1-3 rows as answer

SELECT * from pics where id in (
           select MAX(id) from pics WHERE id < 3
UNION select a.id from pics a where id = 3  
UNION select MIN(id) from pics WHERE id > 3 )  
0
 

Author Comment

by:Shopies
ID: 24402127
That's not what I meant. Bringing just the ID in a prev/next query is usless since other columns should be fetched as well. As long as I'm working with a pictures table then I'll need to fetch the picture column as well to show the picture. Now the first part of the query which brings the (bef) picture brings only the MAX(id) at the time I need to bring the picture column as well.!! I tried this:
MAX(id,thump) but obviously was stupid choice and it gave me an error of course. If that's what you understood previously and I couldn't understand it, could you please do it for me?!!
 
Best regards,
0
 
LVL 14

Accepted Solution

by:
racek earned 500 total points
ID: 24402209

replace xxx with yours column name. You have two possibilities to do it:
1)
SELECT b.id,b.xxx,c.id,c.xxx,a.id, a.xxx
FROM pics  c
JOIN (SELECT (select MAX(id) from pics  WHERE id < 3) AS bef,
             (select a.id from pics  a where     id = 3 )  AS curr,
             (select MIN(id)  from pics  WHERE id > 3)  AS aft ) AS acb
  ON  acb.curr = c.id
LEFT JOIN pics  AS b ON acb.bef   = b.id
LEFT JOIN pics  AS a ON acb.aft    = a.id;
 
OR try 2:
SELECT id, xxx
 from pics where id in (
           select MAX(id) from pics WHERE id < 3
UNION select a.id from pics a where id = 3  
UNION select MIN(id) from pics WHERE id > 3 )  

Open in new window

0

Featured Post

The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

689 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question