MySQL contiguous records

imagined · May 28, 2008, 9:03pm

Let’s say I have this query:

SELECT id, name, datecreated FROM mytable WHERE id = '.$_GET[‘id’]

I want to get 2 records before that and 2 after that, but the thing is, they have to be sorted by datecreated

any ideas?

kdd · May 29, 2008, 12:03am

Well, there are a couple of ways to do this…

use 2 queries
here’s the solution:


$id = $_get['id']; //user input, so beware, and check it!
select ... from ... where id='$id-1' or id='$id-2' or id='$id+1' or id='$id+2' order by datecreated desc

djheru · May 29, 2008, 5:34am

that is a good brain teaser. This is what I came up with:


$id = $_GET['id'];
$query = <<<EOT
SELECT id, name, datecreated FROM mytable
WHERE
     (datecreated >=
          (SELECT datecreated FROM mytable WHERE id = $id)
     LIMIT 3)
OR
     (datecreated <
          (SELECT datecreated FROM mytable WHERE id = $id)
     LIMIT 2)
EOT;

Please let me know if it works.

kdd · May 29, 2008, 6:24am

Even I should say Thank You to djheru! Not sure if it works, but your way is more general, whereas mine was more concrete to the problem given. (Don’t know if “concrete” word is correct, but you get the point, mine was not general way).

agnus · May 29, 2008, 9:17am

Actually, there is a simpler way:



$start_id = (int)$_GET['id'] - 2;
$end_id = (int)$_GET['id'] + 2;

$sql = "SELECT * FROM my_table WHERE id BETWEEN $start_id AND $end_id";
mysql_query($sql).....and so on

agnus · May 29, 2008, 9:38am

Well, if ID isn’t a number, you could try making a select to determine the id of the row and then run the SQL mentioned above. It all depends on what data you have and what you need to accomplish.
Oh, and btw, always remember to sanitize input data.

Charleh · May 29, 2008, 1:30pm

You should use the LIMIT and possibly OFFSET parameters if the ID is not numeric - also bear in mind that although IDs may be numeric they are not necessarily contiguous because data may have been removed so therefore djherus solution is more elegant in both cases and works even when data has been removed - this is the one to use

imagined · May 29, 2008, 2:10pm

thanks, those are all good suggestions.

this is for a photo gallery, but what if a user deletes photo with id = 65? then, if this system is based purely on id’s it wont work, because what if someone wants to see photo 64?

this is part of what i came up with, but it’s based purely on id


// QUERY TO SELECT UNIQUE RECORD
$query = 'SELECT '.$displayable.' FROM '.$tablename.' WHERE id = '.$_GET['id'];
$result = mysql_db_query($db_name, $query);
$row = mysql_fetch_array($result);

//////////////////////////////////////
// SELECT CONTIGUOUS RECORDS
////////////////////////////////////
$more = $_GET['id'] - 2;
$less = $_GET['id'] + 2;
$whereNearby = 'WHERE id >= '.$more.' AND id <= '.$less;

if(isset($_GET['categoryid'])&&is_numeric($_GET['categoryid']))
{
	$whereNearby .= ' AND (category = "'.$_GET['categoryid'].'" OR category2 = "'.$_GET['categoryid'].'")';
	// Query to display items from a specific category
	$query  = 'SELECT '.$displayable.' FROM '.$tablename.' '.$where.' ORDER BY datecreated DESC '.$limit;
}

$queryNearby = 'SELECT '.$displayable.' FROM '.$tablename.' '.$whereNearby.' ORDER BY datecreated DESC';
$resultNearby = mysql_query($queryNearby);

$return .= '<table><tr>';		
while($rowNearby = mysql_fetch_array($resultNearby))
{
	if(file_exists($imagefolder.$rowNearby['image']))
	{
		$startlink = '<a href="'.$_SERVER['PHP_SELF'].'?id='.$rowNearby['id'].'&show=1&pageno='.$_GET['pageno'].'&pageid='.$catalogpageid.'&categoryid='.$_GET['categoryid'].'">';
		$ext = '.'.end(explode('.', $rowNearby['image']));
		$tablenameCount = strlen($tablename);
		$numberChar = $tablenameCount + 14;
		$photo = substr($rowNearby['image'], 0, $numberChar).'_thumb'.$ext;
		$return .= '<td>';
		$return.=$startlink.'<img src="'.$imagefolder.$photo.'" style="float:left; margin:10px 5px; border:3px solid #FFF;" />'.$endlink;
		$return .= '</td>';
	}
}
$return .= '</tr></table>';

djheru · May 29, 2008, 2:36pm


$more = $_GET['id'] - 2;
$less = $_GET['id'] + 2;
$whereNearby = 'WHERE id >= '.$more.' AND id <= '.$less;

This won’t work as you expect. Imagine that $_GET[‘id’] = 10. You would end up with this query conditional:
WHERE id>=8 AND id<=12
Now imagine that the record with an ID of 8 and the one with the record of 9 have been deleted from the database…

If I understand your request correctly, you want to get 5 photos. One with a given ID, and the two that are immediately before and after IF you order by datecreated.

So the first thing that you need to figure out is the value of datecreated where the ID=$id. Now you could use two queries for this, or you could use a subquery. The subquery (SELECT datecreated FROM mytable WHERE id = $id) is executed first, and it stores the value of datecreated where ID=$id.

Then, you can use this value to get the two where datecreated is less than and the two that are greater than THE datecreated we selected.

I think that it would be possible to use UNION to get the proper result as well if ID refers to the auto increment value of the table, but I wasn’t sure that it did. What if ID refers to the user ID or the Gallery ID?

Charleh · May 29, 2008, 3:30pm

You don’t want to run multiple queries when there is no need. This can be done with one query…

Here’s what I’ve got in MSSQL


SELECT * from Pics WHERE ID IN 
(SELECT TOP 2 ID FROM Pics WHERE CreatedDate > (SELECT CreatedDate FROM Pics WHERE ID = '5') ORDER BY CreatedDate) 
OR ID IN 
(SELECT TOP 3 ID FROM Pics WHERE CreatedDate <= (SELECT CreatedDate FROM Pics WHERE ID = '5) ORDER BY CreatedDate DESC)

And in MySQL (not 100% but…)


SELECT * from Pics WHERE ID IN 
(SELECT ID FROM Pics WHERE CreatedDate > (SELECT CreatedDate FROM Pics WHERE ID = '5') ORDER BY CreatedDate LIMIT 2) 
OR ID IN 
(SELECT ID FROM Pics WHERE CreatedDate <= (SELECT CreatedDate FROM Pics WHERE ID = '5) ORDER BY CreatedDate DESC LIMIT 3)

Works for me on my Great Plains data I think, lemme just test a bit

Edit: Yeah works, just tweaked the > and <= to the correct way round, should be all you need

kdd · May 29, 2008, 4:02pm

Thanks for the info.

imagined · June 3, 2008, 1:29pm

I just ran Charleh query on phpMyAdmin, this is what I got:

Error
SQL query:

SELECT *
FROM projects
WHERE ID
IN (

SELECT ID
FROM projects
WHERE CreatedDate > (
SELECT CreatedDate
FROM projects
WHERE ID = ‘5’ )
ORDER BY CreatedDate
LIMIT 2
)
OR ID
IN (

SELECT ID
FROM projects
WHERE CreatedDate <= (
SELECT CreatedDate
FROM projects
WHERE ID = ‘5’ )
ORDER BY CreatedDate DESC
LIMIT 3
)
LIMIT 0 , 30

MySQL said:

#1235 - This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery’

Does anyone know any alternative to this?

Charleh · June 3, 2008, 2:34pm

Yeah, use MSSQL

But seriously, I just read up on it - MySQL doesn’t support LIMIT in subqueries used in the WHERE clause - however you can still create a subquery and JOIN to it and it should be ok!

Try the following


SELECT * 
FROM projects
INNER JOIN 
(
SELECT ID
FROM projects
INNER JOIN ( 
SELECT CreatedDate
FROM projects
WHERE ID = '5') as Dates ON projects.CreatedDate > Dates.CreatedDate
ORDER BY CreatedDate
LIMIT 2
) as projInfo ON projInfo.ID = projects.ID
LIMIT 0 , 30

I’m not 100% on MySQL syntax so the aliasing of the subqueries might be incorrect - but play about and hopefully you get the idea

Tested it in MSSQL

Query was as follows


[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]* [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]from[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics [/COLOR][/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]TOP[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] 2 ID [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] ID [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]= [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'5'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]) [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] Dates [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]>[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] Dates[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ORDER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]UNION[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]SELECT [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]TOP[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] 3 ID [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]INNER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]JOIN[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]([/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]SELECT[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]FROM[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]WHERE[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] ID [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]= [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#ff0000][SIZE=2][COLOR=#ff0000]'5'[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] Dates [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ON[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]<=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] Dates[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ORDER [/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]BY[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]CreatedDate [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]DESC[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080])[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]as[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] PicIDs [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]on[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] PicIDs[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]ID [/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080]=[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000] pics[/COLOR][/SIZE][SIZE=2][COLOR=#808080][SIZE=2][COLOR=#808080].[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#000000]ID[/COLOR][/SIZE]
[/COLOR][/SIZE][/COLOR][/SIZE]

All I did was UNION the two result sets for the date query in the subquery and JOIN to it to get the pic data. Also notice that I’ve used JOINs instead of WHEREs to get the actual IDs for the pictures based on date order

I’m sure something similar will work in MySQL

imagined · June 3, 2008, 3:24pm

I just ran:


SELECT * 
FROM projects
INNER JOIN 
(
SELECT ID
FROM projects
INNER JOIN ( 
SELECT datecreated
FROM projects
WHERE ID = '5') as Dates ON projects.datecreated > Dates.datecreated
ORDER BY datecreated
LIMIT 2
) as projInfo ON projInfo.ID = projects.ID
LIMIT 0 , 30

and gave me error

MySQL said:  

#1052 - Column 'datecreated' in order clause is ambiguous

Charleh · June 3, 2008, 4:24pm

This just means that you (or I) didn’t specify which datecreated to order by as there’s a datecreated in the projects table and a datecreated in the subquery - simply change the ORDER BY datecreated to add the table prefix (you should be ordering by the projects table) i.e.


SELECT * 
FROM projects
INNER JOIN 
(
SELECT ID
FROM projects
INNER JOIN ( 
SELECT datecreated
FROM projects
WHERE ID = '5') as Dates ON projects.datecreated > Dates.datecreated
ORDER BY projects.datecreated -- added table prefix here
LIMIT 2
) as projInfo ON projInfo.ID = projects.ID
LIMIT 0 , 30

imagined · June 3, 2008, 4:55pm

Thanks Charleh, the query was successful. I ran this:

SELECT * 
FROM projects
INNER JOIN 
(
SELECT ID
FROM projects
INNER JOIN ( 
SELECT datecreated
FROM projects
WHERE ID = '229') as Dates ON projects.datecreated > Dates.datecreated
ORDER BY projects.datecreated -- added table prefix here
LIMIT 2
) as projInfo ON projInfo.ID = projects.ID
LIMIT 0 , 30

and gave me two rows with ids 274 and 278 but shouldn’t have given me 228 and 230?

djheru · June 3, 2008, 5:52pm

Did you try my query? I am just curious to see if it works.

imagined · June 3, 2008, 6:41pm

[QUOTE=djheru;2335752]Did you try my query? I am just curious to see if it works.[/QUOTE]

I ran this

SELECT id, name, datecreated FROM projects
WHERE
     (datecreated >=
          (SELECT datecreated FROM projects WHERE id = 229)
     LIMIT 3)
OR
     (datecreated <
          (SELECT datecreated FROM projects WHERE id = 229)
     LIMIT 2)

and got this

#1064 - 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 ‘LIMIT 3 ) OR ( datecreated < ( SELECT datecreated FROM projects WHERE id = 22’ at line 1

Charleh · June 4, 2008, 8:06am

[quote=imagined;2335690]Thanks Charleh, the query was successful. I ran this:

SELECT * 
FROM projects
INNER JOIN 
(
SELECT ID
FROM projects
INNER JOIN ( 
SELECT datecreated
FROM projects
WHERE ID = '229') as Dates ON projects.datecreated > Dates.datecreated
ORDER BY projects.datecreated -- added table prefix here
LIMIT 2
) as projInfo ON projInfo.ID = projects.ID
LIMIT 0 , 30

and gave me two rows with ids 274 and 278 but shouldn’t have given me 228 and 230?[/quote]

No - 2 reasons

because it’s ordered by date - if you do SELECT * FROM projects ORDER BY datecreated and look for ID 229 in the list you should find 274 and 278 right after it. If you want it in ID order just change the

ORDER BY projects.datecreated

to

ORDER BY projects.ID

because this query simply gets the two rows AFTER the specified row based on the given row ID and order supplied

All you need to do now is get the original row and the two before it; if you look at the original question it’s two rows after and two rows before - 4 extra rows in total. You can do it by unioning the extra data within the subquery - I’m sure that will work. I’ve done it in MSSQL above - can you work out how to do it for MySQL yourself or do you need a hand?

Topic		Replies	Views
Move thru Mysql records using PHP programming	4	73	June 26, 2008
PHP pagination : records with non-consecutive id numbers (?) programming	0	97	September 7, 2007
SQL - how to sort by id's from two databases! programming	0	85	August 21, 2007
[PHP/MySQL] Concept Help programming	0	64	January 20, 2008
Deleting a row, but keeping them in order MySQL programming	5	86	July 14, 2008

MySQL contiguous records

Follow:

Popular

Loose Ends

MySQL contiguous records

Related topics

Follow:

Popular

Loose Ends