Solved

Next & Prev image

Posted on 2009-05-10
19
312 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
19 Comments
 
LVL 14

Expert Comment

by:racek
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:Shopies
Comment Utility
@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
Comment Utility
@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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility

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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:Shopies
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
This article discusses four methods for overlaying images in a container on a web page
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

763 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now