MYSQL

[leeh14]

Hi,

 

Basically i am using some PHP to extract some data from a mysql database and was wouding if there is a way to search the database for the two rows of data either side of a select one?

 

for example:

 

ID - 1

ID - 2 ----Selected

ID - 3

 

is there then a way to select ID 1 and 2?

 

the code used to select the first row is:

 

$query = "select * from videos ORDER BY ID DESC";

 

[Lamez]

sure.

 

$q = mysql_query("SELECT * FROM videos ORDER BY id DESC");
while($f = mysql_fetch_assoc($q)){
  echo $f['id']."<BR>";
}

 

I'm not sure if that is what you are looking for. That while loop returns an array names $f.

[leeh14]

Basically the end result should display all three results! either side of the row of data. but not the whole database! so a user can click a link on the next or prev record! so will the above code allow me to do that??

[PFMaBiSmAd]

To get the next higher ID -

 

$query = "select * from videos WHERE ID > $id ORDER BY ID ASC LIMIT 1";

 

To get the next lower ID -

 

$query = "select * from videos WHERE ID < $id ORDER BY ID DESC LIMIT 1";

[leeh14]

will that break though if say the user is on the highest record and there is nothing above?

[DavidAM]

How about this:

$idSELECTED = 8;
$query = "SELECT * FROM videos WHERE ID IN ($idSELECTED, 
    (SELECT MAX(ID) FROM videos WHERE ID < $idSELECTED),
    (SELECT MIN(ID) FROM videos WHERE ID > $idSELECTED) )";

The second two SELECT statements are inside of the IN () phrase.  Hopefully, your ID field is indexed (Primary Key?) so these will run quickly.

 

Of course, if your ID field will ALWAYS be sequential, with NO gaps (from DELETES or so forth) then you can simply do:

$idSELECTED = 8;
$idBEFORE = $idSELECTED - 1;
$idAFTER = $idSELECTED + 1;
$query = "SELECT * FROM videos WHERE ID IN ($idSELECTED, $idBEFORE, $idAFTER)";

[PFMaBiSmAd]

Use LIMIT 2 and then check using mysql_num_rows() how many actual rows were returned. When mysql_num_rows is only 1, you know you have fetched the last row at either end of the available data. You would not output the next/previous link that corresponded to the end you hit or if you are unconditionally outputting next/previous links, the next time you choose the one corresponding to the end you were already at, mysql_num_rows() would return a zero and you would use that to output an appropriate user informational message.

[leeh14]

If any one is interested I used 'DavidAm' method and it worked fine!

 

Cheers