Find Next Previous Row in MySQL DB /w Different Select Options

[syn1234]

Hello,

I have been looking for a solution to this problem for a while now.

I have a table that stores information about different emails that looks like this:

id = int primary key not null auto_increment
mdate = datetime //Holds the data / time of the email
mbox = char(11) //is the mail box id EG. inbox,drafts,saved etc..
mid = char(20) //is a unique id for each email
mfrom = char(255) //is the sender of the email
msubject = char(255) //is the subject of the email
msize = char(10) //is the size of the email

When the user selects an email to view i want to have a next/previous button on the pages but in some cases the user might have 30,000 emails in a given box so i don't want to select all 30,000 to calculate the next/previous buttons. I have tryed to use the SELECT * from user.table where id>(some id) the problem is that the user can also use custom sorting when viewing the messages Eg. sort by name,date,size,from so the ID's are return not in order. Also the sorting can be accending and descending

For example if the user is viewing messages sorted by from my select statement to view the current message is:

SELECT * FROM mail_listing.$GLOBAL_USER_NAME ORDER BY mfrom DESC where mid=<some id> AND mdate=<some date>

The only thing i have that is passed to the script is the mid and mdate wich is totaly unique to each message.

What SQL statment can i use to find out the next row and the previous row without selecting the entire table?

[laserlight]

Use a LIMIT clause. This is basically the pagination problem, and so uses the same solution.

[syn1234]

The LIMIT clause won't do a thing, im a not returning more then 1 value from the DB. In my select statement:

SELECT * FROM mail_listing.$GLOBAL_USER_NAME ORDER BY mfrom DESC where mid=<some id> AND mdate=<some date>

will only return 1 row. I need to know the row before it and after it when ordering by "mfrom" and sorting by "DESC" on the whole table

[laserlight]

will only return 1 row. I need to know the row before it and after it when ordering by "mfrom" and sorting by "DESC" on the whole table

If you need to know the row before and after, it implies that you need to write a query that returns more than one row. You could use one query and discard (or possibly cache, but that could be complicated) extra data from the other two rows, or you could use two queries.

[Roger_Ramjet]

You do it with a UNION QUERY

Basically a union query allows you to run several select queries as one and get a unified resultset back. The previous-this-next problem has this standard solution - adapt it for your data.

"The only thing i have that is passed to the script is the mid and mdate wich is totaly unique to each messag" -which implies that you only need to use the mid or the mdate since either one uniquely identifies the same record.

$sql = "(SELECT * FROM table WHERE t_id < $t_id ORDER BY t_id DESC LIMIT 1)
     UNION
     (SELECT * FROM table WHERE t_id = $t_id)
     UNION
     (SELECT * FROM table WHERE t_id > $t_id ORDER BY t_id LIMIT 1)";

The first select get the one before the input id, the second get the input id and the last gets the next id = all 3 records in 1 query in ascending order.

[syn1234]

I understand what you are saying but, you can't use the ID becuase if you are sorting by "from" then the id's are in a different order then if you where sorting by "date" same with if u are sorting by "subject" or "size"

[Roger_Ramjet]

I told you this was the generic solution and you should modify it for your needs. This is a place to come and learn, not get free coding. :mad: Try learning something.

If the sort columns are the same then the result is the same- :rolleyes:
If the where conditions are the same then the result is the same- :rolleyes:

So, just use the same where conditions in all three selects; it is less than, equal to, and greater than FOR THE DETERMINANT COLUMN. Then use the same order by clause ON THAT DETERMINANT COLUMN in the first and third, with the use of DESC in the first as demonstrated.

 $sql = "(SELECT * FROM table  WHERE user_id=$id AND type=$type AND (cat=$cat1 OR cat=$cat2 OR cat=$cat3) AND date_in<$date_in ORDER BY date_in DESC LIMIT 1)
     UNION
     (SELECT * FROM table WHERE  user_id=$id AND type=$type AND (cat=$cat1 OR cat=$cat2 OR cat=$cat3) AND date_in=$date_in)
     UNION
     (SELECT * FROM table WHERE  user_id=$id AND type=$type AND (cat=$cat1 OR cat=$cat2 OR cat=$cat3) AND date_in>$date_in ORDER BY date_in LIMIT 1)"; 

It would still work if this involved 15 table joins and 32 where conditions. You just write the query that would return all the matching items EXCEPT for the specific condition that is going to return 1 record. Then you modify it to return 1 before that recodr, that record and one after that record. Ultimately, there is always one condition that applies or you would not be able to talk about previous/this/next in the first place.

[syn1234]

K finally got it i was just thinking in terms of using the table id to do the next/prev calcuations.

anyways, i got date working but there is alot more involved, in terms of < > and desc and acs.. here is my code so far, remeber i have only debuged date so the others are a work in progress (for anyone else look for an anwser to this problems)

function GetNextPrev($mbox,$mid)
{

global $GLOBAL_USER_NAME;
global $GLOBAL_USER_SORT_ORDER;
global $GLOBAL_USER_SORT_BY;

#mid consists of the <DATE_MID> in it
$mdate = $this->filetomysqldate($mid,0,strpos($mid,""));
$xmid = substr($mid,strpos($mid,"")+1,strlen($mid));

--- get the info about the message

$q = "SELECT mfrom,msubject,msize from mail_listing.$GLOBAL_USER_NAME WHERE mid='$xmid' AND mdate='$mdate'";
$q = mysql_query($q,$this->DBconnectid);
if (mysql_num_rows($q) == 0) { $this->ErrorMessage = "Message not found in NextPrev"; return FALSE; }
$mfrom = mysql_result($q,0,'mfrom');
$msubject = mysql_result($q,0,'msubject');
$msize = mysql_result($q,0,'msize');

-- make the next/prev SQL statements depending on the sort type

if ($GLOBAL_USER_SORT_ORDER == 'dsc') {
$qo = ' DESC'; $qx = ' ASC'; $qn = "<"; $qp = ">"; } else {
$qo = ' ASC'; $qx = ' DESC'; $qn = ">"; $qp = "<"; }

if ($GLOBAL_USER_SORT_BY == 'from') {
$qnext = "mfrom>'$mfrom' ORDER BY mfrom";
$qprev = "mfrom<'$mfrom' ORDER BY mfrom"; }
elseif ($GLOBAL_USER_SORT_BY == 'subj') {
$qnext = "msubject>'$msubject' ORDER BY msubject";
$qprev = "msubject<'$msubject' ORDER BY msubject"; }
elseif ($GLOBAL_USER_SORT_BY == 'size') {
$qnext = "msize>'$msize' ORDER BY msize";
$qprev = "msize<'$msize' ORDER BY msize"; }
else {
$qnext = "mdate $qn '$mdate' ORDER BY mdate";
$qprev = "mdate $qp '$mdate' ORDER BY mdate";
$qend = " ORDER BY mdate"; }

$qbase = "SELECT mdate,mid,msubject FROM mail_listing.$GLOBAL_USER_NAME ";

$qgo = $qbase . "WHERE mbox='$mbox' AND " . $qnext . $qo . " LIMIT 1";
echo "NEXT - $qgo<BR>";

$q = mysql_query($qgo,$this->DBconnectid);
if (mysql_num_rows($q) == 0) {
#At the end of the records so get the 1st
$qgo = $qbase . $qend . $qo . " LIMIT 1";
echo "NEXT - $qgo<BR>";
$q = mysql_query($qgo,$this->DBconnectid);
if (mysql_num_rows($q) == 0) { $this->ErrorMessage = "Message not found in (next) on get 1st"; return FALSE; } }
#set the end user MID for the next/prev button in view.php
$this->MidNext = $this->mysqltofiledate(mysql_result($q,0,'mdate')) . "_" . mysql_result($q,0,'mid');

$qgo = $qbase . "WHERE mbox='$mbox' AND " . $qprev . $qx . " LIMIT 1";
echo "PREV - $qgo<BR>";

$q = mysql_query($qgo,$this->DBconnectid);
if (mysql_num_rows($q) == 0) {
#At the end of the records so get the last
$qgo = $qbase . $qend . $qx . " LIMIT 1";
echo "PREV - $qgo<BR>";
$q = mysql_query($qgo,$this->DBconnectid);
if (mysql_num_rows($q) == 0) { $this->ErrorMessage = "Message not found in (prev) on get 1st"; return FALSE; } }
$this->MidPrev = $this->mysqltofiledate(mysql_result($q,0,'mdate')) . "_" . mysql_result($q,0,'mid');

return TRUE;
}

[Roger_Ramjet]

You must learn to use the php tags. Go read the posting FAQ.

function GetNextPrev($mbox,$mid)
{

global $GLOBAL_USER_NAME;
global $GLOBAL_USER_SORT_ORDER;
global $GLOBAL_USER_SORT_BY;

#mid consists of the <DATE_MID> in it
$mdate = $this->filetomysqldate($mid,0,strpos($mid,"_"));
$xmid = substr($mid,strpos($mid,"_")+1,strlen($mid));

# --- get the info about the message
$q = "SELECT mfrom,msubject,msize from mail_listing.$GLOBAL_USER_NAME WHERE mid='$xmid' AND mdate='$mdate'";
$q = mysql_query($q,$this->DBconnectid);
if (mysql_num_rows($q) == 0) { $this->ErrorMessage = "Message not found in NextPrev"; return FALSE; }
$mfrom = mysql_result($q,0,'mfrom');
$msubject = mysql_result($q,0,'msubject');
$msize = mysql_result($q,0,'msize');

# -- make the next/prev SQL statements depending on the sort type
if ($GLOBAL_USER_SORT_ORDER == 'dsc') {
$qo = ' DESC'; $qx = ' ASC'; $qn = "<"; $qp = ">"; } else {
$qo = ' ASC'; $qx = ' DESC'; $qn = ">"; $qp = "<"; }

if ($GLOBAL_USER_SORT_BY == 'from') {
$qnext = "mfrom>'$mfrom' ORDER BY mfrom";
$qprev = "mfrom<'$mfrom' ORDER BY mfrom"; }
elseif ($GLOBAL_USER_SORT_BY == 'subj') {
$qnext = "msubject>'$msubject' ORDER BY msubject";
$qprev = "msubject<'$msubject' ORDER BY msubject"; }
elseif ($GLOBAL_USER_SORT_BY == 'size') {
$qnext = "msize>'$msize' ORDER BY msize";
$qprev = "msize<'$msize' ORDER BY msize"; }
else {
$qnext = "mdate $qn '$mdate' ORDER BY mdate";
$qprev = "mdate $qp '$mdate' ORDER BY mdate";
$qend = " ORDER BY mdate"; }

$qbase = "SELECT mdate,mid,msubject FROM mail_listing.$GLOBAL_USER_NAME ";

$qgo = $qbase . "WHERE mbox='$mbox' AND " . $qnext . $qo . " LIMIT 1";
echo "NEXT - $qgo<BR>";

$q = mysql_query($qgo,$this->DBconnectid);
if (mysql_num_rows($q) == 0) {
#At the end of the records so get the 1st
$qgo = $qbase . $qend . $qo . " LIMIT 1";
echo "NEXT - $qgo<BR>";
$q = mysql_query($qgo,$this->DBconnectid);
if (mysql_num_rows($q) == 0) { $this->ErrorMessage = "Message not found in (next) on get 1st"; return FALSE; } }
#set the end user MID for the next/prev button in view.php
$this->MidNext = $this->mysqltofiledate(mysql_result($q,0,'mdate')) . "_" . mysql_result($q,0,'mid');

$qgo = $qbase . "WHERE mbox='$mbox' AND " . $qprev . $qx . " LIMIT 1";
echo "PREV - $qgo<BR>";

$q = mysql_query($qgo,$this->DBconnectid);
if (mysql_num_rows($q) == 0) {
#At the end of the records so get the last
$qgo = $qbase . $qend . $qx . " LIMIT 1";
echo "PREV - $qgo<BR>";
$q = mysql_query($qgo,$this->DBconnectid);
if (mysql_num_rows($q) == 0) { $this->ErrorMessage = "Message not found in (prev) on get 1st"; return FALSE; } }
$this->MidPrev = $this->mysqltofiledate(mysql_result($q,0,'mdate')) . "_" . mysql_result($q,0,'mid');

return TRUE;
}