CODEWITHSUNDEEP
phpmysqldatabase
Shaune
Shaune

Reputation: 23

How can I optimise this mysql/php query?

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 =, >, <.

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.

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

Upvotes: 2

Views: 146

Answers (2)

Ranty
Ranty

Reputation: 3352

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.

Upvotes: 0

Marc B
Marc B

Reputation: 360562

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

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.

Upvotes: 2

Related Questions