如何更改 MySQL 查询的结果排序?

2022-01-23 00:00:00 php mysql drupal drupal-7

我正在尝试获取页面的上一个和下一个节点链接/缩略图，并根据其标题或文件 URI 或文件名对结果进行排序...

I'm trying to get the previous and next node link/thumbnail for my pages and order the results according to their titles or file URIs or filenames...

代码是查询数据库并根据节点ID(nid，n.nid)输出上一个和下一个节点链接.我想根据节点标题(title，n.title)，文件名(filename，f.filename)甚至文件URI(uri，f.uri)对结果进行排序.

The code is querying the database and outputting previous and next node links according to node IDs (nid, n.nid). I want to order the results according to either node title (title, n.title), filename (filename, f.filename), or even file URI (uri, f.uri).

但是，当我更改此行时:

However, when I change this line:

->orderBy('n.nid', $order)

到:

->orderBy('n.title', $order)

这行不通.唯一的区别是，如果您从一个图片库的最后一页移动到另一个，它会稍微改变顺序，但在画廊内部，一切都是一样的.问题是如果您有一个画廊并决定在一段时间后插入新图像.节点 ID 现在与其他节点 ID 完全不同，此代码无法识别它.

it won't work. The only difference is that it slightly changes the order if you move from the last page in one image gallery to another, but otherwise inside of the galleries everything is the same. The problem is if you have one gallery and decide to insert a new image after a while. Node ID is now totally different from the others and this code doesn't pick it up.

我也尝试过更改 nid 出现的其他部分，但它不起作用.我想这对于更了解 MySQL 和 PHP 的人来说是微不足道的，但我坚持了几个小时，希望能得到任何帮助.

I have also tried with changing other parts where nid shows up, but it's not working. I suppose that it's something trivial for someone who understands MySQL and PHP better, but I'm stuck with it for hours and would appreciate any help.

这是整个代码(来自 Vlad Stratulat，最初在这里找到):

Here is the whole code (from Vlad Stratulat, originally found here):

模板.php

function dad_prev_next($nid = NULL, $op = 'p', $start = 0) {
if ($op == 'p') {
    $sql_op = '>';
    $order = 'ASC';
}
elseif ($op == 'n') {
    $sql_op = '<';
    $order = 'DESC';
}
else {
    return NULL;
}

$output = '';

// your node must have an image type field
// let's say it's name is IMAGEFIELD
// select from node table
$query = db_select('node', 'n');
// join node table with image field table
$query->leftJoin('field_data_field_IMAGEFIELD', 'i', 'i.entity_id = n.nid');
// join file managed table where all data about managed files stored
$query->leftJoin('file_managed', 'f', 'f.fid = i.field_IMAGEFIELD_fid');
$query
    // select nid and title from node
    ->fields('n', array('nid', 'title'))
    // select uri from file_managed (image path)
    ->fields('f', array('uri'))
    // select image alt and title
    ->fields('i', array('field_IMAGEFIELD_alt', 'field_IMAGEFIELD_title'))
    // where nid "greater than"/"lower than" our current node nid
    ->condition('n.nid', $nid, $sql_op)
    // where node type in array('your content types')
    ->condition('n.type', array('PHOTOS'), 'IN')
    // where node is published
    ->condition('n.status', 1)
    // where requested node has image to display (if you want thumbnail)
    ->condition('f.uri', '', '!=')
    // order by nid
    ->orderBy('n.nid', $order)
    // limit result to 1
    ->range($start, 1);     

// make query
$result = $query->execute()->fetchAll();

foreach ($result as $node) {
    // theme your thumbnail image
    $variables = array(
        // default image style name `thumbnail`
        // you can use your own by following
        // admin/config/media/image-styles on your site
        'style_name' => 'thumbnail',
        'path' => $node->uri,
        'alt' => $node->field_IMAGEFIELD_alt,
        'title' => $node->field_IMAGEFIELD_title
    );
    $image = theme('image_style', $variables);

    $options = array(
        'html' => TRUE,
        'attributes' => array(
            'title' => $node->title
        )
    );
    $output = l($image, "node/{$node->nid}", $options);
}

return $output;
}

Node.tpl.php

Node.tpl.php

<?php print dad_prev_next($node->nid, 'p', 0); ?>
<?php print dad_prev_next($node->nid, 'n', 0); ?>

编辑 2:

尝试使用strcmp函数:

Trying to use strcmp function:

function dad_prev_next($title = NULL, $op = 'p', $start = 0) {
if ($op == 'p') {
    $strcmp = '1';
    $order = 'ASC';
}
elseif ($op == 'n') {
    $strcmp = '2';
    $order = 'DESC';
}
else {
    return NULL;
}

$output = '';

// your node must have an image type field
// let's say it's name is IMAGEFIELD
// select from node table
$query = db_select('node', 'n');
// join node table with image field table
$query->leftJoin('field_data_field_IMAGEFIELD', 'i', 'i.entity_id = n.nid');
// join file managed table where all data about managed files stored
$query->leftJoin('file_managed', 'f', 'f.fid = i.field_IMAGEFIELD_fid');
$query
    // select nid and title from node
    ->fields('n', array('nid', 'title'))
    // select uri from file_managed (image path)
    ->fields('f', array('uri'))
    // select image alt and title
    ->fields('i', array('field_IMAGEFIELD_alt', 'field_IMAGEFIELD_title'))
    // where node type in array('your content types')
    ->condition('n.type', array('PHOTOS'), 'IN')
    // where node is published
    ->condition('n.status', 1)
    // where requested node has image to display (if you want thumbnail)
    ->condition('f.uri', '', '!=')
    // order by nid
    ->orderBy('n.title', $order)
    // limit result to 1
    ->range($start, 1);     

// make query
$result = $query->execute()->fetchAll();

foreach ($result as $node) {
    // theme your thumbnail image
    $variables = array(
        // default image style name `thumbnail`
        // you can use your own by following
        // admin/config/media/image-styles on your site
        'style_name' => 'thumbnail',
        'path' => $node->uri,
        'alt' => $node->field_IMAGEFIELD_alt,
        'title' => $node->field_IMAGEFIELD_title
    );
    $image = theme('image_style', $variables);

    $options = array(
        'html' => TRUE,
        'attributes' => array(
            'title' => $node->title
        )
    );
    $output = l($image, "node/{$node->nid}", $options);
}

return $output;
}

现在没有记录错误，但始终显示相同的照片 - 按字母顺序排列的第一张照片中的 2 张和最后一张中的 2 张.

There are no errors logged now, but there are always same photos displayed - 2 of the first ones and 2 of the last ones in the alphabetical list.

推荐答案

您正在按 n.title 排序，但是您的第一个条件是查找大于/小于 $nid 的 n.nid.这没有任何意义，并导致半随机选择.我建议在按标题排序时将条件更改为 n.title $node->title，它会起作用.

You're sorting by n.title, but then your first condition is looking for an n.nid greater than/lower than $nid. This makes no sense, and results in a semi-random selection. I suggest when sorting by title you change the condition to something n.title $node->title, and it would work.

相关文章