我该如何优化这个mysql / php查询？

My page displays an image, and I want to display the previous and next image that is relevant to the current one. At the moment I run the same query 3x and modify the "where" statement with =, >, <.

我的页面显示一个图像,我想显示与当前图像相关的上一个和下一个图像。目前我运行相同的查询3x并使用=,>, <修改“where”语句。< p>

It works but I feel there must be a better way to do this.

它有效,但我觉得必须有一个更好的方法来做到这一点。

The image id's are not 1,2,3,4,5. and could be 1,2,10,20,21 etc. But if it is much more efficient I am willing to change this.

图像ID不是1,2,3,4,5。并且可能是1,2,10,20,21等。但如果效率更高,我愿意改变这一点。

mysql_select_db("database", $conPro);
$currentid = mysql_real_escape_string($_GET['currentid']);
$query ="SELECT * FROM database WHERE id ='".$currentid."' LIMIT 1 ";
$result = mysql_query($query,$conPro) or die(mysql_error());
$affected_rows = mysql_num_rows($result);                               
if ($affected_rows==1)
    { 
        $row = mysql_fetch_array($result)or die ('error:' . mysql_error());     
        $current_id = $row['id'];
        $current_header = $row['title'];
        $current_description =$row['desc'];
        $current_image = "http://".$row['img'];
        $current_url = "http://".$row['id']."/".$db_title."/";
        $current_thumb = "http://".$row['cloud'];
}

mysql_select_db("database", $conPro);   
$query ="SELECT * FROM database WHERE id <'".$currentid."' ORDER BY id DESC LIMIT 1 ";
$result = mysql_query($query,$conPro) or die(mysql_error());
$affected_rows = mysql_num_rows($result);                               
if ($affected_rows==1)
    { 
    $row = mysql_fetch_array($result)or die ('error:' . mysql_error()); 
        $previous_id = $row['id'];
        $previous_header = $row['title'];
        $previous_description =$row['desc'];
        $previous_image = "http://".$row['img'];
        $previous_url = "http://".$row['id']."/".$db_title."/";
        $previous_thumb = "http://".$row['cloud'];
    }else{
        $previous_none = "true"; //no rows found
    }

mysql_select_db("database", $conPro);   
$query ="SELECT * FROM database WHERE id >'".$currentid."' ORDER BY id ASC LIMIT 1 ";
$result = mysql_query($query,$conPro) or die(mysql_error());
$affected_rows = mysql_num_rows($result);                               
if ($affected_rows==1)
    { 
    $row = mysql_fetch_array($result)or die ('error:' . mysql_error()); 
        $next_id = $row['id'];
        $next_header = $row['title'];
        $next_description =$row['desc'];
        $next_image = "http://".$row['img'];
        $next_url = "http://".$row['id']."/".$db_title."/";
        $next_thumb = "http://".$row['cloud'];
    }else{
        $next_none = "true"; //no rows found
        }
mysql_close($conPro);

Thank you for your time

感谢您的时间

2 个解决方案

#1

You don't have to do select_db each time. Once you 'select' a db, it stays selected until you select something else.

您不必每次都执行select_db。一旦你选择了一个数据库,它就会一直保持选中状态,直到你选择其他东西。

You can't really get away from doing two separate queries to get the next/previous images, but you can fake it by using a union query:

您无法真正摆脱两个单独的查询来获取下一个/上一个图像,但您可以使用联合查询伪造它:

(SELECT 'next' AS position, ...
FROM yourtable
WHERE (id > $currentid)
ORDER BY id ASC
LIMIT 1)

UNION

(SELECT 'prev' AS position, ...
FROM yourtable
WHERE (id < $currentid)
ORDER BY id DESC
LIMIT 1)

This would return two rows, containing a pseudofield named 'position' which will allow you to easily identify which row is the 'next' record, and which is the 'previous' one. Note that the brackets are required so that the 'order by' clauses apply to the individual queries. Without, mysql will take the order by clause from the last query in the union sequence and apply it to the full union results.

这将返回两行,包含一个名为“position”的伪字段,它允许您轻松识别哪一行是“下一个”记录,哪一行是“前一个”记录。请注意,括号是必需的,以便“order by”子句适用于各个查询。如果没有,mysql将从union序列中的最后一个查询中获取order by子句,并将其应用于完整的union结果。

#2

You can get the "previous" one first WHERE id <'".$currentid."' ORDER BY id DESC, and then query for two "above" it: SELECT * FROM database WHERE id >= '".$currentid."' ORDER BY id ASC then it takes only two queries instead of three.

你可以先得到“前一个”WHERE id <'“。$ currentid。”'ORDER BY id DESC,然后查询两个“上面”它:SELECT * FROM database WHERE id> ='“。$ currentid。” 'ORDER BY id ASC然后它只需要两个查询而不是三个。

#1