Jump to content

MYSQL


leeh14

Recommended Posts

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

 

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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??

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.