Jump to content

Print Neighboring Rows Of Mysql


mobone
 Share

Recommended Posts

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.
Link to comment
Share on other sites

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 by jlhaslip
Link to comment
Share on other sites

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>";			}

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...