Textpattern CMS

#1 2006-12-24 04:32:18

jstur8jv

Member

Registered: 2006-12-23

Posts: 23

How do I determine next and previous image in a category?

I need a way via a plugin, php, or some textpattern magic, to take the current picture being viewed in a category and then determine the previous (if there is one) and next (if there is one) picture in the category, relative to the current one. Preferably alphabetically speaking, as opposed to by ID#.

Any plugins/snippets of php code that can accomplish that?

#2 2006-12-24 06:44:51

Mary

Sock Enthusiast

Registered: 2004-06-27

Posts: 6,236

Re: How do I determine next and previous image in a category?

I’m not sure of if/how you could do it with a built-in tag or existing plugin tag, but I can give what an example SQL query would look like.

Off the top of my head, say your image is named “pic.png”.

SQL query for previous image:

SELECT * FROM txp_image WHERE category = 'category-name' AND name < 'pic.png' ORDER BY name ASC;

SQL query for next image:

SELECT * FROM txp_image WHERE category = 'category-name' AND name > 'pic.png' ORDER BY name ASC;

If you end up having to make the plugin yourself, be sure and check the related resources.

#3 2006-12-24 07:08:52

jstur8jv

Member

Registered: 2006-12-23

Posts: 23

Re: How do I determine next and previous image in a category?

I tried searching for a tutorial about sql queries, but I couldn’t find one online that seemed to tell me how to put all this together.

I’ve never programmed any PHP, and I’ve not used SQL other than very basic things through the admin interface like backing something up. How do I tell the SQL queries to run, then how do I declare the results as variables that I can pop into the site via a txp tag (or PHP, if I don’t go the plugin route)?

Do you have a link handy for a very basic tutorial that deals with this specific type of situation? Or do you know of a very basic plugin that does nothing but perform an SQL query and return a result that I can modify? Anything more complicated, and I’m sure I wouldn’t be able to understand it.

Last edited by jstur8jv (2006-12-24 07:09:28)

#4 2006-12-24 10:20:26

jstur8jv

Member

Registered: 2006-12-23

Posts: 23

Re: How do I determine next and previous image in a category?

It took me a while to find a good tutorial, but after I did, and an hour of plugging away, this is what I’ve come up with:

<txp:php>
$jds_cat = $_GET["c"];
echo $jds_cat;
$jds_string = "SELECT * FROM txp_image WHERE category = '".$jds_cat."' AND name > '[imagename]' ORDER BY name ASC";
echo $jds_string;
$jds_allinfo = mysql_query($jds_string);
$jds_result = mysql_fetch_array($jds_allinfo);
echo $jds_result['id'];</txp:php>

The extra echo’s just helped me figure out where it was failing along the way.

It looks like everything is working great EXCEPT I can’t think of a way to dynamically insert the current image’s imagename in for [imagename]. If I change the string/variable/parameter/whatever-it-is manually, then it correctly picks up on the next image’s name.

It would be amazing if someone could whip up a quick way to do so, as I’m dreading working out how to run another query to find the name of the image based on the ID, store it as a variable, and then insert that into this code. This alone took me over an hour, and that added complexity is… daunting, to say the least!

Thanks so much for the SQL query, btw. It’s perfect, as far as I can tell.

—————————————————————
Update! Problem solved. I tried fitting this in during down-time at work, and I eventually managed to make my own sql query to return the name. After an our or so of fiddling with things, I finally got everything working!

The biggest change, that I almost missed, is that for the “previous” mysql string, the sort needed to be “DESC” instead of “ASC.” Otherwise, everything was fairly straightforward. I’m very thankful you gave me the strings, though, as I doubt I could have produced them myself.

<txp:php>
$jds_cat = $_GET["c"];
$jds_id = $_GET["p"];
$jds_namefinderstring = "SELECT * FROM txp_image WHERE id = '".$jds_id."'";
$jds_nameinfo = mysql_query($jds_namefinderstring);
$jds_nameresult = mysql_fetch_array($jds_nameinfo);
$jds_previousstring = "SELECT * FROM txp_image WHERE category = '".$jds_cat."' AND name < '".$jds_nameresult['name']."' ORDER BY name DESC";
$jds_previousinfo = mysql_query($jds_previousstring);
$jds_previous = mysql_fetch_array($jds_previousinfo);
echo "Previous:".$jds_previous['id']."<br/>";
$jds_nextstring = "SELECT * FROM txp_image WHERE category = '".$jds_cat."' AND name > '".$jds_nameresult['name']."' ORDER BY name ASC";
$jds_nextinfo = mysql_query($jds_nextstring);
$jds_next = mysql_fetch_array($jds_nextinfo);
echo "next:".$jds_next['id']."";
</txp:php>

There it is! Beautiful. At the moment, I just have it printing the results so I can check them, but it’ll be simplicity itself to turn them into URLs for navigation.

BUT, with all that being said, I have no experience with PHP. Will running this on a site cause significant slowdowns? Will it cause any security problems? Will this code being present allow someone to access/modify the SQL database?

The big thing, though, is this: I can’t seem to call the final variables later on down the page between two more txp:php tags; it looks like the information disappears and everything will need to be reinitialized each time I want the information, even on a single page.

Is there any viable way to retain the information that’s better than going through the mysql queries each time?

Last edited by jstur8jv (2006-12-24 12:04:50)