single result from multiple

[eluser]Unknown[/eluser]
I am writing a product display page that displays the selected product in detail as well as displaying the rest of the available products as a list of links.

Initially I wrote two SQL queries to accomplish this and that worked fine to a point. But here is where I ran into trouble. For each product displayed I am also providing a previous and a next button as another navigation option. Because the products are separated into categories the database index does not create an accurate numerical incrementing of products.

I am thinking of getting all of the items form a specific category and then loop through the results to find the matching product_id I would like as the main display piece. This would give me access to:

Code:

$row = $query->next_row()
$row = $query->previous_row()

So my question is: Is this the correct/best way to go about this? Is their a more efficient way? Is "I can't believe it's not butter" really not butter?

Any thoughts or impressions would be greatly appreciated.

Thanks

[eluser]TheFuzzy0ne[/eluser]
Welcome to the CodeIgniter forums.

[quote author="industryslave" date="1245012391"]
So my question is: Is this the correct/best way to go about this?
[/quote]

I'm quite sure it's not.

[quote author="industryslave" date="1245012391"]
Is their a more efficient way?
[/quote]

Perhaps. I'm not saying my way is the best way, but I really believe it's an improvement.

Why not have a next and prev method in your controller, that accepts the ID of the product, then you can simply grab a single next result, or previous result. It's less queries, and less complication in my humble opinion.

Code:

function next($product_id="")
{
    $product_id = (int)$product_id;

    # This should be in a model method, but for simplicity, I've put it here.

    # Get the category ID
    $this->db->select('cat_id');
    $this->db->where('id', $product_id);
    $cat_id = $this->db->get('products');
    $cat_id = $cat_id->row_array();
    $cat_id = $cat_id['id'];

    # Get the ID of the next product.
    $this->db->select('id');
    $this->db->where('cat_id', $cat_id);
    $this->db->where('id >', $product_id);
    $this->limit(1);
    $pid = $this->db->get('products');
    $pid = $pid->row_array();
    $pid = $pid['id'];

    redirect('view_product/'.$pid);
}

It might even be possible to do it in a single query, but I'm not sure how. You'll need to add some error checking/handling in there too, but hopefully you get the gist.

[quote author="industryslave" date="1245012391"]
Is "I can't believe it's not butter" really not butter?
[/quote]

"I Can't Believe It's Not Butter" is to butter, as Amy Winehouse is to fluffy puppies.

[quote author="industryslave" date="1245012391"]
Any thoughts or impressions would be greatly appreciated.
[/quote]

Perhaps, but what's on my mind is a bit too explicit to share in a public place.

Thanks[/quote]

[eluser]sophistry[/eluser]
There is a confirmed bug on the next_row and previous_row functions so I would stay away from them.

buttermilk is the main ingredient in "I can't believe it's not butter"

there is nothing wrong with multiple SQL queries - use them to get the job done easiest if that's the easiest.

If the Higgs Boson exists, it is an integral and pervasive component of the material world.

[eluser]Unknown[/eluser]
Awesome! Thanks for the replies and the information. I got it all set up and it works fantastically now. I set up get_previous and get_next in my model. I also added a get_first to handle bad product requests.

id > product_id is the WIN.

Thanks again!