?
Solved

Next & Prev image

Posted on 2009-05-10
19
Medium Priority
?
326 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 

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 2000 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

Back Up Your Microsoft Windows Server®

Back up 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.

Question has a verified solution.

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

3 proven steps to speed up Magento powered sites. The article focus is on optimizing time to first byte (TTFB), full page caching and configuring server for optimal performance.
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
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 look for a specific file type in a local or remote server directory using PHP.
Suggested Courses

777 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