Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Next & Prev image

Posted on 2009-05-10
19
Medium Priority
?
328 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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
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 …
Suggested Courses

604 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