Re: Next, prev links

A bit more google searching uncovered something I had never seen before....

MySQL variables.

This seems to do the trick. It is more queries than i would hope. However, since
the data is ordered only once and then store on the mysql server side as a temp
table, it should be faster than doing multiple selects with orders.

Pretty nifty. Not sure that I like it, but i will keep thinking and post what i
find.


<?
$query = 'SELECT @row := 0;';
$Result = $GLOBALS['account_db']->query($query);

$query = 'DROP TABLE IF EXISTS temptable;';
$Result = $GLOBALS['account_db']->query($query);

$query = 'CREATE TEMPORARY TABLE temptable SELECT @row := @row + 1 as rownum,
gallery.* FROM gallery
       ORDER BY gallery.`lastmodified_date` DESC;';
$Result = $GLOBALS['account_db']->query($query);
$query = 'SELECT @current := rownum
       FROM temptable
       WHERE item_id = ' . 50 . ';';
$Result = $GLOBALS['account_db']->query($query);


$query = 'SELECT IF(rownum < @current, \'-1\', \'1\') as position, item_id,
filename
       FROM temptable
       WHERE (rownum = @current - 1) OR (rownum = @current + 1) OR (rownum =
@current)
       ORDER BY position ASC;';
 $Result = $GLOBALS['account_db']->query($query);

echo '<pre>';
while($Result->next()) {
    print_r($Result->get_row());
}
echo '</pre>';
?>











Quoting Blaine Garrett <blaine-4hELYU93S8xBDgjK7y7TUQ@xxxxxxxxxxxxxxxx>:

> Hi,
> I am trying to figure out a more optimal way to do next, prev links. I have
> this
> working as described below, but it doesn't seem efficiant and there may be a
> cool mysql feature to do this I do not know of.
>
> Does anyone have a quick sneaky method for generating the next, previous urls
> for <link> tags given that ids are indirectly part of the url and sorting may
> apply.
>
> For example the urls are in the form of ...
> http://example.com/gallery/image_4543654_sort_newest.html
>
> >From this I would be able to generate a query like
> SELECT * FROM gallery WHERE id = 4543654
>
> Now, I would like to have next and prev <link> tags to the header and in the
> page. But, sorting may apply. In theory we know the sorting. So, the method I
> have been doing all along is getting all items sorted accordingly, finding
> the
> index $i of current one, then then also selecting $i-1 and $i+1.
>
> Anyone know of a better way to do this. I really don't want to select all of
> the
> items just to get the next and previous. It seems this info would be
> available
> on the mysql server side and be easy to retrieve. Maybe even some crazy use
> of
> LIMIT 3 centering around the current one, or something like that.
>
> Any thoughts?
>
>
>
>
>
>
>
> --
> Blaine Garrett
> Chief Web Applications Developer
> JR Computer Associates INC
> http://jrcorps.com
> blaine-4hELYU93S8xBDgjK7y7TUQ@xxxxxxxxxxxxxxxx
>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: 
> talk-unsubscribe-4zcLI8jJc/rYtjvyW6yDsg@xxxxxxxxxxxxxxxx
>
> Please read and follow the list guidelines:
>   http://www.tcphp.org/mailing_list/guidelines
>
> The tcphp.org mailing list is sponsored by pajunas interactive, inc.
>
>


--
Blaine Garrett
Chief Web Applications Developer
JR Computer Associates INC
http://jrcorps.com
blaine-4hELYU93S8xBDgjK7y7TUQ@xxxxxxxxxxxxxxxx




---------------------------------------------------------------------
To unsubscribe, e-mail: talk-unsubscribe-4zcLI8jJc/rYtjvyW6yDsg@xxxxxxxxxxxxxxxx

Please read and follow the list guidelines:
  http://www.tcphp.org/mailing_list/guidelines

The tcphp.org mailing list is sponsored by pajunas interactive, inc.