Sponsored Links

Dan Spada · 10-07-2008, 02:01 PM

I have a MySQL table which stores these fields:
Filename, Description, Rating

I am using the Smarty template engine and need to navigate between individual pages of the data (each page will show one Filename) to show the pages based on a criteria (for example highest rating to lowest rating).

Essentially what I need is something similar to this: http://phplens.com/adodb/tutorial.im....previous.html

However the problem with this is that the URL contains which position you are through the MySQL result set.

I am using mod_rewrite so that each filename appears as a standard HTML page, and I want the script to know automatically which is "next" so the link refers to the next filename/page specifically, not the position in the data.

So essentially I need to know the current file's position in the total filter (rating) and the file that is before and after it? Does anyone know how I can achieve this?

Thanks!

Dan.

AndrewK · 10-07-2008, 05:36 PM

I think I understand what you need.

Code:

<?php

$filename = $_GET['filename'];

$filename = mysql_real_escape_string($filename);

// get the current row
$currentresult = mysql_query("SELECT * FROM sometable WHERE filename = '$filename'");

$currentinfo = mysql_fetch_strip($currentresult);

// get the rating for the current file
$currentrating = $currentinfo['rating'];

// query the database and get the next rating lower than the current one
$prevresult = mysql_query("SELECT filename FROM sometable WHERE rating < '$currentrating' ORDER BY rating DESC LIMIT 1");

$previnfo = mysql_fetch_strip($prevresult);

$prevfilename = $previnfo['filename'];


// query the database and get the next rating higher than the current one
$nextresult = mysql_query("SELECT filename FROM sometable WHERE rating > '$currentrating' ORDER BY rating ASC LIMIT 1");

$nextinfo = mysql_fetch_strip($nextresult);

$nextfilename = $nextinfo['filename'];

?>

So that code would take $filename from the URL, and it would find the filenames for the record in the database that has the next lower $prevfilename) and next higher ratings ($nextfilename).

Dan Spada · 10-07-2008, 08:34 PM

Hey there!

That's the sort of thing I need, but that doesn't take into consideration multiple ratings of the same value?

The only way I can think to do it is to load up an array (sorted from the mysql query), determining the current position and then moving to the next item in the array?

Obviously I don't want to do that because the array has the potential to get quite large (even if I am only storing the filenames in the array)? :-/

AndrewK · 11-07-2008, 11:35 AM

Quote:

Originally Posted by Dan Spada

The only way I can think to do it is to load up an array (sorted from the mysql query), determining the current position and then moving to the next item in the array?

Sure you could do that - but how does MySQL itself know what record is supposed to be next? I believe if you actually created a table with those 3 fields and entered in some rows with the same rating, MySQL would return them in a random order anyway (ie different every time), as it doesn't know how to sort it itself.

If you are going to have records with the same rating value, then you will probably need to add another field such as "order", that keeps track of the order of the records.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
LFW: PHP / MySQL web developer	shorten.ws	Programming and Web Development	0	22-06-2008 10:52 AM
Dark-Host.net 5GB Space, 50GB Bandwidth, Unlimited MySQL, POP3, FTP, PHP - $5/Month!!	D3C13	International	0	20-05-2008 10:46 AM
PHP & MYSQL problem..	samson	Programming and Web Development	9	28-02-2008 04:48 PM
[US] Quality Hosting-ASP.NET 2.0 & 1.1, CFMX7, PHP 4.0 & 5.0, MSSQL with 24/7 Support	xHosts	Australia	1	28-01-2006 02:27 PM
[split thread] data connections	Spirit Connect	Lounge Bar	47	13-10-2005 07:10 PM

Sponsored Links

Advertise Here