Logic problem?

[waynew]

I have a system that allows the administrator to add clothing items. Each clothing item is added to a category. Simple stuff. When a visitor to the site clicks on a specific category, they are automatically shown the first item in that category.

 

Note that I'm adding in the <?php tags to make it easier to read. Also note that I don't think this piece of code is the problem.

 

<?php
function get_first_item(){
        $cond = "";
        if(isset($_GET['category_id'])){
            $cat = (int) $_GET['category_id'];
            if($cat != 0){
                $cond = " WHERE category_id = '$cat' ";
            }
        }
        $get = $this->db->get_row("SELECT * FROM item $cond ORDER BY item_id LIMIT 1");
        return $get;
    }
?>

 

The above code selects the first item from the chosen category. On each page, a PREVIOUS and NEXT link is shown. These links allow the user to navigate further through the chosen category. I get the previous and next link by getting the id of the current item and giving it to function below:

 

    function get_prev_and_last_items($id){
        
        $arr['prev'] = 0;
        $arr['next'] = 0;
        $id = (int) $id;
        
        if($id == 0) return $arr;
        
        $cond = "";
        if(isset($_GET['category_id'])){
            $cat = (int) $_GET['category_id'];
            if($cat != 0){
                $cond = " WHERE category_id = '$cat' ";
            }
        }
                
        $select = $this->db->query("SELECT * FROM item $cond ORDER BY item_id");
        $items_in_cat = array();
        
        while($row = mysql_fetch_assoc($select)){
            array_push($items_in_cat,$row['item_id']);
        }
        
        foreach($items_in_cat as $i){
            if($i < $id){
                $arr['prev'] = $i;
            }
            if($i > $id){
                $arr['next'] = $i;
                break;
            } 
        }
        
        return $arr;
    }

 

Now I know that the second loop is redundant and that I could have just carried out the same actions in the first loop. The problem is that certain items aren't showing after been added to a category. I added 17 items to one category, yet I was only able to navigate, from start to finish, through 7.

[waynew]

I'm guessing that my logic is somewhat screwed in the second loop of the second piece of code?

[mikesta707]

Why don't you just use a limit clause? that would make the pagination a lot simpler.

 

And yeah, that logic is slightly off. For example, say I have, in category shirts, 10 items. They have ids as follows

1

3

4

5

6

7

9

10

14

17

 

Say im at the shirt with id of 4. Well based on your logic, the prev button will produce the shirt with an id of 1 (since 1 < 4, and 1 comes up first because you order by the id).

[waynew]

Why don't you just use a limit clause? that would make the pagination a lot simpler.

 

And yeah, that logic is slightly off. For example, say I have, in category shirts, 10 items. They have ids as follows

1

3

4

5

6

7

9

10

14

17

 

Say im at the shirt with id of 4. Well based on your logic, the prev button will produce the shirt with an id of 1 (since 1 < 4, and 1 comes up first because you order by the id).

 

Yes, but the loop doesn't break until the NEXT id has been found, meaning that 1 will be overwritten by 3. Or am I wrong here?

[sasa]

try

<?php
function get_prev_and_last_items($id){
        
        $arr['prev'] = 0;
        $arr['next'] = 0;
        $id = (int) $id;
        
        if($id == 0) return $arr;
        
        $cond = "";
        if(isset($_GET['category_id'])){
            $cat = (int) $_GET['category_id'];
            if($cat != 0){
                $cond = " WHERE category_id = '$cat' ";
            }
        }
                
        $select = $this->db->query("SELECT * FROM item $cond ORDER BY item_id");
        $items_in_cat = array();
        
        while($row = mysql_fetch_assoc($select)){
            array_push($items_in_cat,$row['item_id']);
        }
/*        
        foreach($items_in_cat as $i){
            if($i < $id){
                $arr['prev'] = $i;
            }
            if($i > $id){
                $arr['next'] = $i;
                break;
            } 
        }
*/
	$key = array_search($id, $items_in_cat);
	if ($key > 0) $arr['prev'] = $items_in_cat[$key - 1];
	if ($key < count($items_in_cat) - 1) $arr['next'] = $items_in_cat[$key + 1];
        return $arr;
    }
?>

[laffin]

With a bit more sql u can do it very simply

<?php
   function get_prev_and_last_items($id){
        
        $arr['prev'] = 0;
        $arr['next'] = 0;
        $id = (int) $id;
        
        if($id == 0) return $arr;
        
        $cond = "";
        if(isset($_GET['category_id'])){
            $cat = (int) $_GET['category_id'];
            if($cat != 0){
                $cond = " AND category_id = '$cat' ";
            }
        }
                
        $select = $this->db->query("SELECT item_id AS prev,(SELECT id from item WHERE id>{$id}{$cond} ORDER BY item_id ASC LIMIT 1) AS next FROM items WHERE item_id<{$id}{$cond} ORDER BY id DESC LIMIT 1");
        return mysql_fetch_assoc($select);
)