DataAssist Help Required: Filtering via two recordsets

Hiya,

I have a page that contains two recordsets (Recordset1 & Recordset2) which contain the information required to place a single wordpress post into that page.

Please forgive the complete lack of technical know-how. I can place dynamic text on the page OK, but I don't know how to filter it through two recordsets. It would not be a repeating region.

I want to place on the page:

• the most recent post (Recordset1: 'post_title', 'post_date', 'post_content')
• of a certain category (Recordset2: 'slug' value = 'site-item')

[If I order Recordset1 by 'post_date' Descending, I presume this will give me the latest post by just putting in the dynamic text as mentioned in the first bullet point above. Is this the correct way? If so, I just need to filter it based on the value in Recordset2.]

Please could someone baby-step me through the process?

Thanks.

hi Neil

Are you using filter criteria for each recordset, (eg via a post or url variable ?)

i'm not familiar with wordpress, but am sure i can help you with the recordsets...

the most recent post can be shown by ordering the recordset by date and by returning a single row.
is there a need to have 2 recordsets, or could you combine data from 2 tables into a single recordset ?

Neil,

If you are importing wordpress posts into a "non wordpress page" you might consider using a specific php plugin to do the job like SimplePie which is what I use to bring posts from my blog into my site

Hi Craig and Dave,

Many thanks for your input on this. I have php code blindness - I have honestly tried, but I have let David Powers down dreadfully.

The site in question is www.buffalogals.co.uk - a band site that required an integrated wordpress blog (somewhat disguised). On the home page, there are two 'Quick List' sections, which both draw detail summaries from the databases.

The lower one (Latest Posts) is the one I am referring to regarding the problem. It works fine in itself, - if you click the title of a recent post (ordered by date descending) then the full detals are pulled in to the left of the screen on a detail page. So far so good.

However, I would like for the Home Page to always display the latest post created in Wordpress, but only if it has been listed under the category (slug) of 'site-news-item'. This is so that the owner of the site can choose to have a post appear in the lower-left section of the Home Page as a 'News Item', and still post others under different categories that won't appear on the Home page.

At the moment I can have the latest post appear there, but the latest post will appear regardless of what category it is listed under, as the categories/slugs are defined in a second wordpress table/recordset.

As the tables are heavily integrated with the way that wordpress works, I wouldn't know how to go about integrating them, and wouldn't want to try!

Dave - is this the sort of thing that 'SimplePie' does for you?

I'd appreciate any further guidance either of you can provide, I will pay for time if necessary.

Many thanks.

P.S - Dave - have been looking at your sites portfolio - fabulous design work.

P.P.S - Cheers Craig - yes the second record-set (Recordset2 - the one that contains the categories) is filtered by 'slug' (column name) value = 'site-news-item'

I never saw simplepie before, (bookmarked it now - thanks Dave)

As I said, I don't know wordpress, (something i have yet to get into, can you recommend it ?) and simplepie may be the way to go here, but in general terms, if you have two linked tables, a single recordset may be all you need.

if you want to explore this, let me know the table details and i can help you with the sql

Originally Said By: CraigR

  I never saw simplepie before, (bookmarked it now - thanks Dave)  

no worries it's a great tool

  Many thanks for your input on this. I have php code blindness - I have honestly tried, but I have let David Powers down dreadfully.  

Happens to all of us regardless of how long we have been developing so don't worry about it.

  However, I would like for the Home Page to always display the latest post created in Wordpress, but only if it has been listed under the category (slug) of 'site-news-item'. This is so that the owner of the site can choose to have a post appear in the lower-left section of the Home Page as a 'News Item', and still post others under different categories that won't appear on the Home page.

At the moment I can have the latest post appear there, but the latest post will appear regardless of what category it is listed under, as the categories/slugs are defined in a second wordpress table/recordset.

As the tables are heavily integrated with the way that wordpress works, I wouldn't know how to go about integrating them, and wouldn't want to try!

Dave - is this the sort of thing that 'SimplePie' does for you?  

Yes this is exactly the kind of thing that SimplePie can do, but there seems to be a problem with your rss feed as when I try to view it in a browser I get an error as shown in the attached screenshot.

  P.S - Dave - have been looking at your sites portfolio - fabulous design work.  

Thanks, I have been lucky enough to work with some really good designers over the years.

Cheers Craig and Dave,

The error that shows up in the RSS feed is caused by a script I have that is called by all the pages;

At the bottom of every page, I have a 'Join our contact list' mini-form, - and I didn't want to have captcha (design reasons) or have the possibility that the form got sent without the tick-box being checked. The script disables the 'send' button unless the checkbox is ticked. Maybe I should re-think that utility.

In the meantime (next hour or two) I will disable the script and let normal form validation take care of it. Perhaps (Dave) you could check again later and see if the RSS feeds (blog/) have been cleaned up and work with SimplePie.

I have looked at the blurb for simplePie - and think it may be a bit of a brainfull for such as myself to implement straight away, but would be willing to give it a go if I knew you could help me get the results I'm after.

If it were quicker though, I'd probably find it more easily achievable to use the resources I have available on the existing page, i.e. the two recordsets.

Wordpress creates several tables - two of which I use and have recordsets for; with (I am guessing) the potential to enable placing the exact information I require on the Home page.

I don't know how to link the tables (I am a PHP idiot). As I you can see on the home page, I can get the relevant elements of the latest wordpress post placed on the homepage, (from one table/recordset) but I just need to make it conditional on being in a certain category (as defined in a second recordset/table). It's making the link, and creating that filter that has me beaten, jeered at and hiding in the broom closet.

If it helps, I can email phpMyAdmin url, login & pass if anyone wants to see the wordpress tables, and provide any other info (ftp, admin) privately.

I use Wordpress in a few sites recently - the clients have asked for it. It can initially be a pain to bring seemlessly into lime with your site style, but once you get the hang of adding the includes (header, footer, nav etc) to the existing Wordpress theme code, it becomes fairly easy. This is something I want to look at with the new php framework as a possible cool plugin. That will be for later, though.

Having wordpress on the site has many advantages - clients get extra CMS, can create extra pages, forms etc on their own, post galleries of images or video. Wordpress automatically announces new posts to the main search engines, and with ping-backs and track-backs can really boost a site's ranking. Lots more to it, but that is the gist.

I'll post back once I remove the errant script.

Thanks both,

PS - If you click on the 'Blog' link and then click on the entry Rss link (or go to ?feed=rss2) the feed seems to go through ok. But will disable the naughty script anyway.


EDIT: I have removed the script.

Looking at the existing tables/recordset option:

These are the rs from the home page:

mysql_select_db($database_buffalogals_wp, $buffalogals_wp);
$query_Recordset1 = "SELECT * FROM wp_posts ORDER BY post_date DESC";
$Recordset1 = mysql_query($query_Recordset1, $buffalogals_wp) or die(mysql_error());
$row_Recordset1 = mysql_fetch_assoc($Recordset1);
$totalRows_Recordset1 = mysql_num_rows($Recordset1);

mysql_select_db($database_buffalogals_wp, $buffalogals_wp);
$query_Recordset2 = "SELECT * FROM wp_terms WHERE slug = 'site-news-item'";
$Recordset2 = mysql_query($query_Recordset2, $buffalogals_wp) or die(mysql_error());
$row_Recordset2 = mysql_fetch_assoc($Recordset2);
$totalRows_Recordset2 = mysql_num_rows($Recordset2);

The first block gets me the latest post. I don't know how to filter it based on the second one (filtered by category (slug) equalling 'site-news-item').

i looked at the table relationships of the wordpress tables on this link Database_Description

i can't confirm the relationship between wp_posts and wp_term_relationships, so for now i have assumed object_id on the relationships table is ID on the posts table.

based on this massive guess of an assumption, i have collated a sql statement with a few inner joins, which at present should return all the rows in the posts table where the slug is 'site-news-item'

if my assumption isn't total rubbish, you may be able to use this to get what you want.

SELECT * FROM wp_posts INNER JOIN wp_term_relationships ON wp_term_relationships.object_id = wp_posts.ID INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id INNER JOIN wp_terms ON wp_terms.term_id = wp_term_taxonomy.term_id WHERE wp_terms.slug = 'site-news-item'

failing that, i either need to play with wordpress a bit, (long overdue) or you could send me a pm with your sql dump and i will have another try.

as for now, it's Sunday night and i am going to grab a glass! :-)