Page 1 of 1

Retreive a Row Pointer

Posted: Thu Sep 13, 2007 10:44 am
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?

Posted: Thu Sep 13, 2007 5:14 pm
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.

Posted: Fri Sep 14, 2007 9:34 am
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>

Posted: Fri Sep 14, 2007 9:41 am
by VladSun
I think that your initial idea was to use mysql_data_seek. That is pretty much like "row pointer".

Posted: Fri Sep 14, 2007 10:47 am
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.

Posted: Fri Sep 14, 2007 11:05 am
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