Print Neighboring Rows Of Mysql

[mobone]

This doesnt seem that hard but I cant seem to find any tutorials on it. I just want to find where name = "rob" and print out the rows 2 before, rob's row, and the 2 rows after.I didn't know if this should go here or in php.

[jlhaslip]

find where name = "rob" and print out the rows 2 before, rob's row, and the 2 rows after.

Alphabetical? or by index? Either way, you would need to perform a select to discover the rows before and after.

[mobone]

Alphabetical? or by index? Either way, you would need to perform a select to discover the rows before and after.

Um by a rank, float.

[jlhaslip]

You need one select to find Bob's rank, then adjust the Select to find the others.What happens if Bob is first or last?

Edited June 20, 2009 by jlhaslip

[mobone]

Well heres what I've come up with. Extremely dirty and hard to read. Any clean up help would be appreciated.$_SESSION[uid] is the currently logged in user. We want to find the people before and after his rank.My qry function simply connects to the database and runs the query.

		//find the user and print 2 in front and 2 in back			$sql = "SELECT name FROM players ORDER BY rank DESC";			$result = qry($sql);			$data = array();			$numrows = mysql_num_rows($result);						//load up the array with all of the table			for ( $i=0; $i<$numrows; $i++ )			{				$row = mysql_fetch_array($result);				$data[$i] = $row['name'];			}			//find what rank the player is in, then subtract two.			$rank_number = array_search( $_SESSION[uid], $data );			switch($rank_number)			{				case 0:  //if first, start at this entry					break;				case 1: //if second, start at one above					$rank_number-=1;					break;				case $numrows-1:  //if last, start four above					$rank_number-=4;					break;				default:  //anything in between, start two above, making selection be middle					$rank_number-=2;			}										$sql = 'SELECT name, exp FROM players ORDER BY rank DESC LIMIT '.$rank_number.', 5';			$result = qry($sql);			//print out the 5 players			for ($i=0; $i<=5; $i++)			{			$row = mysql_fetch_array($result);			echo $row['name']."<br>";			}

[jlhaslip]

Is it working for you?

[justsomeguy]

It's not going to be all that efficient to get everything from the database and then search through it yourself, it would probably be better to get the information for the user in question, determine if they're first or last or in the middle, and calculate the range of ranks to select and show. The database is built to do sorting and filtering, it's going to be more efficient to have the database do that instead of selecting everything and doing it yourself.