Best way to handle the previous next (one record)

PHP
1

Hi Guys,

Never mind, I know this should be posted in the MySQL or Database forums but I am posting it here because to have more peoples’ views.

Okay, I have a listing page where the records are listed and I can filter the records from search form (about 5/6 fields) and also choose ordering field and order by. With three conditions, I have following query:


SELECT 
	p.id,p.title,p.views
FROM 
	jos_prpt_properties AS p 
	INNER JOIN jos_prpt_agents AS a ON p.agent_id=a.id 
	INNER JOIN jos_prpt_type AS pt ON p.property_type=pt.id 
	INNER JOIN jos_prpt_listingtype AS lt ON p.listing_type=lt.id 
	INNER JOIN jos_prpt_features AS f ON p.id=f.property_id 
	INNER JOIN jos_prpt_locations AS l ON p.id=l.property_id 
	INNER JOIN jos_prpt_availablilties AS av ON p.id=av.property_id 
	INNER JOIN jos_prpt_prices AS pr ON p.id=pr.property_id
WHERE p.published=1 AND lt.reference_id IN(1,3) AND f.bedrooms>=4
ORDER BY views DESC;

So first of all, suggest me if I am doing something wrong in my joining ;).

There will be pagination if more records are found. Search criterias are stored in cookies because the client himself wants if a visitor selects once some filtration options then the same filtration should remain next time when he visits the page.

Now when a record is clicked and went to the details page, there is again a navigation as Previous | Next which should show the records in the same way/ordering how they were shown in listing page. So what I am thinking is; instead of passing the id I would just pass the index number of the record. Lets say if there are 35 records listed in the listing page then I will pass 10 for the 11th record in the listing/result and I will hit the following query in the detail page:


SELECT 
	p.id,p.title,p.views
FROM 
	jos_prpt_properties AS p 
	INNER JOIN jos_prpt_agents AS a ON p.agent_id=a.id 
	INNER JOIN jos_prpt_type AS pt ON p.property_type=pt.id 
	INNER JOIN jos_prpt_listingtype AS lt ON p.listing_type=lt.id 
	INNER JOIN jos_prpt_features AS f ON p.id=f.property_id 
	INNER JOIN jos_prpt_locations AS l ON p.id=l.property_id 
	INNER JOIN jos_prpt_availablilties AS av ON p.id=av.property_id 
	INNER JOIN jos_prpt_prices AS pr ON p.id=pr.property_id
WHERE p.published=1 AND lt.reference_id IN(1,3) AND f.bedrooms>=4
ORDER BY views DESC
LIMIT 10,1;

So that I can just increment the index for Next record and decrement the index for Previous record.

Hope I described it clearly. If it is not clear to you, feel free to ask for :wink:

So can you point out some pitfalls with this method or suggest some better ways to handle the situation or how you guys are handling this type of situation?

Thank you so much guys in advance for reading the long description.

Raju Gautam

2

Just echo two links with (id = current + 1) and (id = current - 1).

Also, why does your schema need so many joins? Can’t you merge some tables?

3

What do you mean? ID cannot be just +/- 1 with the current one. I don’t what you are tying to say.

I know those could be merged but just for separation since the properties table has more fields already so I just splitted and some tables can have one-to-many relations too.

4

good luck getting a decent mysql answer from php developers, is all i can say

:smiley: :smiley: :smiley:

just kidding!!!

5

to create a “previous id” and a “next id” link, when the id’s come in random order, you could use a for-loop.

The problem with a while mysql_fetch_assoc, is that you dont know what the next record is… one solution, is to take all the result-data from the query, and put it into a new array.

Then you can use the for-loop on the new array, and know what the “previous-id” and “next id” is.

$query = "SELECT
    p.id,p.title,p.views
FROM
    jos_prpt_properties AS p
    INNER JOIN jos_prpt_agents AS a ON p.agent_id=a.id
    INNER JOIN jos_prpt_type AS pt ON p.property_type=pt.id
    INNER JOIN jos_prpt_listingtype AS lt ON p.listing_type=lt.id
    INNER JOIN jos_prpt_features AS f ON p.id=f.property_id
    INNER JOIN jos_prpt_locations AS l ON p.id=l.property_id
    INNER JOIN jos_prpt_availablilties AS av ON p.id=av.property_id
    INNER JOIN jos_prpt_prices AS pr ON p.id=pr.property_id
WHERE p.published=1 AND lt.reference_id IN(1,3) AND f.bedrooms>=4
ORDER BY views DESC";

$result = mysql_query($query);
$item = array();
while($row = mysql_fetch_assoc($result))
{
	$item[] = $row;
}

$size_of_result = sizeof($item);
for($i = 0; $i < $size_of_result; $i++)
{
	// no previous at the first position.
	if($i > 0)
	{
		echo "Previous: ".$item[$i-1]['id']." ";
	}
	
	echo "Current: ".$item[$i]['id']." ";
	
	// there are no next item, at the last position
	if($i == $size_of_result-1)
	{
		echo "Next: ".$item[$i+1]['id']." <br />"; 
	}
}

I hope you get the idea…

Note, that if your result is very large, this solution is not a good choice.

6

Its okay. But I was expecting something from you too :frowning:

I posted here just because if there any other PHP related performance issues can happen then guys can suggest.

7

Well, I’d make 2 additional queries to get prev/next ids (can be shortened to one)
Tere is also an option to put whole result set of the main query (ids only) into session

8

oh, all right, if you insist

you might have found this on your own if you had searched for “previous and next links”

9

Thank you all for the responses.

Rudy, I had already gone through them but I was particularly expecting some reviews/suggestions with my current approach.

10

Limit method is just unacceptable.
The URL of the page must remain the same
But with position, not id, it will lead to different pages

11

Why? How do you mean LIMIT method is unacceptable? Without limit how do you limit the rows and construct paginations?

Remain the same means sending the index value what I have mentioned in first post?

I really could not understand what you are trying to say here. Can you please elaborate your last two lines?

Actually I have already implemented the approach which seems to be working just fine so far.

12

We have a general rule here in the web development:
An URL of the page must reflect actual page content.
E.g. this topic url, http://www.sitepoint.com/forums/showthread.php?t=662555 will lead you exactly to this page, not anything else.
If limit were used instead, you will always hit another page. That’s just not handy.

13

And what do you think the following URL will lead you in wrong place?


http://www.mysite.com/?show=details&showindex=10
14

of course
as soon as another record added or deleted or search condition changed.
Don’t think of this page as of position. It is product details page. Certain product. And it must have certain url

15

I agree with Shrapnel on this one - if a row is removed, then all pages with an ‘index’ larger than that would be shifted down by one.

By doing that, any links made before said row is removed would then point to an incorrect page. If that isn’t bad enough for business as it is, it would be terrible from an SEO perspective.

16

Umm! Really good point now. Fully agreed with this logic. I really did not think about that. It seems indeed sometimes to be a terrible specially when a row is deleted or search criteria is modified as mentioned above by yourself.

So guys, how would you recommend to do exactly then? I would like to hear some precise suggestions.

17

According to my knowledge, there is only one precise method.
By running 2 additional queries, to get previous an next records.
As of meaning of these terms based on the list order, so we must use the same fields we use in the ORDER BY statements, to filter records using WHERE statements.
Say, if we order our query by id field, the next record would be found using WHERE id > $id
Same for previous.

In your case views must be used instead. if this field is not unique, another criteria must be added to both ORDER BY and WHERE. so, your query must be ordered by “views DESC, id” and WHERE must be “views < $currentviews AND id > $id”

Just came to my mind another solution - immediate redirect from the ?show=details&showindex=10 to ?show=details&id=12&showindex=10
I don’t think it’s good idea though

Another option is to store all found id’s in the session. It has it’s own pros and contras.

18

Okay, among the three suggestions I think I would go for the first one by running two queries which could be the more precise one and reliable too.

Thank you very much.

I am still curious to see some more methods if there are any which are already implemented somewhere :slight_smile:

19

raju, do me a favour, show me your actual query, i’ll give it a try

20

Your try would definitely be a solution I hope :slight_smile:

Apart from some more fields and one more order by field (that is unique id), the same queries are being used what i have posted in my original post. Do you really need something more rudy?