Latest photo from a specific category

[pixelposeur]

I have a bunch of categories defined on my blog, one of which is called "daily".

I would like to change the SQL logic that chooses the photo to display on the main page so that it only selects from the photos that are in the "daily" category.

This way, I can upload a bunch of shots at one time and tag them for various categories but only select ones would be visible on the main page as the "photo of the day".

My SQL is rusty, so I'm looking for help in constructing the correct
SQL statement that will choose only those photos from a specific
category to display on the main page.

Any help would be appreciated!

-Will

Will,

maybe you try to use PP for something what it is not made for ?

[pixelposeur]

Hmm, I don't think so. Pixelpost is a great system, I like that it lets me categorize my photos and I like the flexibility it offers. I am just interested
in adding a slight twist in how the front-page images are selected. I don't think
this is a radical change or goes against the original intent of the system.

[Joe[y]+]

it's certainyl possible. unfortunately i too haven't the skill with sql. good luck finding someone who does.

[pixelposeur]

I figured it out. Once I test it some more to make sure I didn't break something else, I'll post the details.

[spazzwig]

Any word? I would like to see what your solution was

Originally Posted by pixelposeur

I figured it out. Once I test it some more to make sure I didn't break something else, I'll post the details.

[tinyblob]

*puts on database administrator hat*

if you want to change it so that your index page ONLY displays photos from that category, then it should be as simple as finding the relevant SQL queries and expanding on the WHERE clause.

you can do this a quick way if you already know the relevant ID for the daily category.

i haven't actually looked at the source for index.php before, i'm just having a quick look before i leave work, but at first glance the following seems apparent:

the query for selecting the latest photograph is defined on line 137.

PHP Code:

$row = sql_array("select * from ".$pixelpost_db_prefix."pixelpost where datetime<='$cdate' order by datetime DESC limit 0,1"); 


change this to:

PHP Code:

$row = sql_array("select * from ".$pixelpost_db_prefix."pixelpost where datetime<='$cdate' and category='1' order by datetime DESC limit 0,1"); 


the added code there is and category='1', and i'm assuming your "daily" category has an ID of 1.

this is what we'd call a "dirty hack", and i wouldn't swear that it works without reading the rest of the source, and testing it.

if you want some help i'll happily code a full front and back end hack, or even an addon if it's possible under the current structure.

EDIT:
The above solution isn't valid for the latest version of pixelpost! This is because of added functionality in the database structure. Feel free to PM me if you'd like a solution that will work

[spazzwig]

Thanks for the help. Both to pixelposeur for sharing his result and tinyblob for the offer.

Tinyblob: You have email... what I have in mind is off topic for this thread. I think it will be of interest to others tho so if anything comes of it I'll make a new thread

[pixelposeur]

Tthe way I did it was to modify a few of the SQL querys so that they only included a specific category. My query is slightly more complicated (and perhaps unnecessarily so) since I did a join between the category association table and the main pixelpost table since many pictures can appear in multiple categories. Just keying off of the "category" column in the main pixelpost table is not sufficient to capture all of the photos. There are also several links that have to be adjusted in order to be consistent.

Also, I think that the pixelpost table itself should not even have a 'category' column, since each photo can appear in multiple categories. Categories for each photo should be determined by a query to the 'catassoc' table, ignoring the category tag in the pixelpost table.

The fixes below seem to work with 1.4.3, but I may be doing something wrong, this is just a personal hack. If someone from team pixelpost has suggestions for improvement, please let me know.

First, find the category id that you want to be the default.
This could easily be done with another SQL query, but I was lazy,
so I just hardcoded it.

$daily_cat = 2;

Fix the "showimage" link (around line 135):

if($_GET['showimage'] == "") {
 	$q = "select t1.* from ".$pixelpost_db_prefix . "pixelpost as t1
		inner join " . $pixelpost_db_prefix . "catassoc as t2 on t1.id = 
                t2.image_id where ((t2.cat_id='$daily_cat') AND (t1.datetime
                <='$cdate')) order by datetime DESC limit 0,1";

	$row = sql_array($q);
}

Next, change the "previous row" and "next row" queries:

(around line 180)

$previous_row = sql_array("select t1.image,t1.id,t1.headline from " . 
     $pixelpost_db_prefix."pixelpost as t1 inner join "  .  
     $pixelpost_db_prefix . "catassoc t2 on t1.id = t2.image_id where 
    (t2.cat_id = 'daily_cat') AND (t1.datetime < '$image_datetime') AND 
    (t1.datetime<='$cdate') order by datetime DESC limit 0,1");

(around line 200)

$next_row = sql_array( "select t1.id,t1.headline,t1.image from " . 
     $pixelpost_db_prefix."pixelpost as t1 inner join" . 
     $pixelpost_db_prefix."catassoc t2 on t1.id = t2.image_id
     where (t2.cat_id = '$daily_cat') AND (t1.datetime > '$image_datetime') and 
     (t1.datetime<='$cdate') order by datetime asc limit 0,1");

Also fix the "aheadnum" and "behindnum" computations:

 $aheadnumb = sql_array("select count(*) as count from " . 
      $pixelpost_db_prefix . "pixelpost as t1 inner join  " . 
      $pixelpost_db_prefix . "catassoc t2 on t1.id = t2.image_id 			   
      where (t2.cat_id = '$daily_cat') AND (t1.datetime > '$image_datetime') 
      and (t1.datetime<='$cdate')");

$behindnumb = sql_array("select count(*) as count from " . 
     $pixelpost_db_prefix . "pixelpost as t1 inner join"  . 
     $pixelpost_db_prefix . "catassoc t2 on t1.id = t2.image_id where 
     (t2.cat_id = '$daily_cat') AND (t1.datetime < '$image_datetime') and 
     (t1.datetime<='$cdate')");

Also, fix the "thumbsahead" and "thumbsbehind" code:

$thumbs_ahead = mysql_query("select t1.id,t1.headline,t1.image from " . 
     $pixelpost_db_prefix . "pixelpost as t1 inner join  " .
     $pixelpost_db_prefix . "catassoc t2 on t2.id = t1.image_id	where 
     (t2.cat_id = '$daily_cat') AND (t1.datetime > '$image_datetime') and 
     (t1.datetime<='$cdate') order by t1.datetime asc limit 0,$aheadlimit");


$thumbs_behind = mysql_query("select t1.id,t1.headline,t1.image from " . 
     $pixelpost_db_prefix . "pixelpost as t1 inner join  " .
     $pixelpost_db_prefix . "catassoc t2 on t2.id = t1.image_id	where 
     (t2.cat_id = '$daily_cat') AND (t1.datetime < '$image_datetime') and 
     (t1.datetime <= '$cdate') order by t1.datetime asc limit 0,$behindlimit");

-Pixelposeur

[tinyblob]

Originally Posted by pixelposeur

Just keying off of the "category" column in the main pixelpost table is not sufficient to capture all of the photos.

Yep. The category column is deprecated.

Also, I think that the pixelpost table itself should not even have a 'category' column, since each photo can appear in multiple categories. Categories for each photo should be determined by a query to the 'catassoc' table, ignoring the category tag in the pixelpost table.

yep

The fixes below seem to work with 1.4.3, but I may be doing something wrong, this is just a personal hack. If someone from team pixelpost has suggestions for improvement, please let me know.

My extensive categories admin addon will be out with version 1.5 of Pixelpost. Which does all of this without the need for index.php hacking. With a few other goodies which i haven't mentioned yet


Good job on the code.