Retreive a Row Pointer

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Retreive a Row Pointer

Post by TipPro »

I am trying to retrieve a set of 5 rows from a table in a MySQL database. I want to search for a specific row and retrieve the two rows above and below that record for a total of five records.

What I was thinking was if I could get the row number pointer of the row I am searching for I could use LIMIT to select the five rows I need.

something like this...

Code: Select all

$rowNumber;  //how do I get the row number?

$startAtThisRow = $rowNumber - 2;

$query = "SELECT * FROM tableName LIMIT $startAtThisRow, 5";
Does someone know how to get a specific row's number... or does someone have a better way of accomplishing this?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

There are no "row number pointers" in MySQL, per se. There are primary key indexes, but you add those yourself. Besides, they may not relate to your needs.

Generally, what you would do is use a UNION. Select the two rows that would appear before the specific row, the specific row, and the two after all bunched together with a UNION.
TipPro
Forum Commoner
Posts: 35
Joined: Wed Mar 15, 2006 6:39 pm

Post by TipPro »

Thanks for the reply feyd...

I got stuck trying to solve this problem in a particular way...

here is a rough version of my code for anyone interested... (not sure how efficient this code is, my experience w/ php and mysql is limited).

Code: Select all

function outputQuickIndexParts($startingPartId, $numBelow, $numAbove, $numTotal){
  if($numTotal > ($numAbove + $numBelow)){
    $operator = '<=';
    $includeStartingPart = 1;
  }else{
    $operator = '<';
    $includeStartingPart = 0;
  }
  $allParts = array();
  $allParts = array_merge($allParts, array_reverse($this->database->retreiveQuickIndexParts($startingPartId, $operator, $numBelow + $includeStartingPart, 'DESC')));
  $allParts = array_merge($allParts, $this->database->retreiveQuickIndexParts($startingPartId, '>', $numAbove, ""));
  $output = "";
  foreach($allParts as $key=>$value) $output .= '<div class="quickPartIndex">'.$this->outputLinkedThumbnail($value, 'quickIndexThumbnail').'</div>'."\n";
  return $output;
}


function outputLinkedThumbnail($partInfo, $imgClass=""){
  $directory = explode("-", $partInfo['code']);
  return '<a href="'.$this->getRootPath().'parts.php?currentPart='.$partInfo['code'].'" title="'.$partInfo['code'].'">'.
         '<img class="'.$imgClass.'" src="images/parts/'.$partInfo['code'].'-thumb.gif" alt="'.$partInfo['code'].'" />'.
         '</a>';
}
   
   
function retreiveQuickIndexParts($partId, $operator, $numOfRecords, $desc=""){
  $listingsArray = array();

  $q = "SELECT * FROM parts WHERE partId $operator $partId ORDER BY partId $desc LIMIT $numOfRecords";
  if($result = $this->connection->query($q)){
    while($row = $result->fetch_assoc()){
      array_push($listingsArray, $row);
    }
  }
  return $listingsArray;
}

Code: Select all

<!-- 
example:
outputQuickIndexParts(5067, 2, 2, 5) 
where 5067 is the part id for xx312
-->
<div class="quickPartIndex"><a href="parts.php?currentPart=xx310" title="xx310"><img class="quickIndexThumbnail" src="images/parts/xx310-thumb.gif" alt="xx310" /></a></div>
<div class="quickPartIndex"><a href="parts.php?currentPart=xx311" title="xx311"><img class="quickIndexThumbnail" src="images/parts/xx311-thumb.gif" alt="xx311" /></a></div>
<div class="quickPartIndex"><a href="parts.php?currentPart=xx312" title="xx312"><img class="quickIndexThumbnail" src="images/parts/xx312-thumb.gif" alt="xx312" /></a></div>
<div class="quickPartIndex"><a href="parts.php?currentPart=xx313" title="xx313"><img class="quickIndexThumbnail" src="images/parts/xx313-thumb.gif" alt="xx313" /></a></div>
<div class="quickPartIndex"><a href="parts.php?currentPart=xx314" title="xx314"><img class="quickIndexThumbnail" src="images/parts/xx314-thumb.gif" alt="xx314" /></a></div>
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

I think that your initial idea was to use mysql_data_seek. That is pretty much like "row pointer".
There are 10 types of people in this world, those who understand binary and those who don't
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post by Begby »

You can do this.


First get the rank of the part we are looking for by finding a count of all the parts that come before it.

Code: Select all

SELECT COUNT(*) as rank FROM parts WHERE partID < 'xx312'

Next we are going to select all the records and add a rank field, then filter out only those records that have a rank between plus or minus two of the value we got from the previous query.

Code: Select all

SELECT *, (SELECT COUNT(*) FROM parts p2 WHERE p2.partID < p1.partID) as partRank
FROM parts p1
WHERE partRank BETWEEN $rank-2 AND $rank+2
ORDER BY p1.partID
It is possible to do this in one query by adding subqueries into the between clause I think.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Post by VladSun »

feyd's idea of using UNION makes more sense in such cases:

Code: Select all

select * from table where rowid< $rowid order by rowid desc limit 2
union
select * from table where rowid= $rowid
union
select * from table where rowid> $rowid order by rowid limit 2
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply