Store Community Support Documentation
Hi,

Has long I wish develop a way to show all blogs (pages, feed and blog) in the blog section. Well I decide this morning to take a look at phpfox files and understand how the blog module works.

To my surprise I found out quickly what is the SQL sentences that which filters only the results of the blogs module using the Debug Tool, but could not find which performs this query.

I would like to ask to phpfox community if there is some trick to find a specific query in phpfox files or if the code is so spread between controller and services that in the end this is real work of analysis?

I tried to find some logic but couldn't find one. Seems that the final SQL sentence is generated in "pieces".
Be the first person to like this.
#1
What actually you are trying to achieve? Are you wanting to display records in a block? in a controller? How do yo want to display it? What fields or information you want to show?
Be the first person to like this.
Hi,

I want to fetch all blog entries at main blog section (for while). I noticed that there is a SQL sentence in Debug that filter only "module_id = blog" and I want to include "module = pages" the question is how to identify which file handle this to find the SQL setence that fetch only blogs from blog module?
Be the first person to like this.
As you noticed the SQL sentences are made by parts. So, for example, this SQL sentence is for listing the blogs in the controller blog.index:

Phpfox::getService('core')->getLegacyItem(array(
'field' => array('blog_id', 'title'),
'table' => 'blog',
'redirect' => 'blog',
'title' => $sLegacyTitle,
'search' => 'title'
)
);

So, you need to build a similar code for the pages. Maybe for loading pages.index there is a similar code. I assume you want to order all them together pages and blogs alphabetically. So, you need to deal with it too.

Please remember customizations are not supported by the PhpFox's Support Service.
Be the first person to like this.
Hi Aileen,

Thanks by the reply, I know that this kind of thing isnt inside the phpfox support, this is the reason why I ask a open question to the community. My question was more about how to find a SQL query amoung phpfox files than how to do a customizatio. As I said I know how to add the pages together with blogs result, however couldnt figure out where to find the SQL, because the SQL query displayed with debug tool is different from what I find insode the blog service files.
Be the first person to like this.
You mean just the SQL query?

SELECT *
FROM phpfox_blog;

SELECT *
FROM phpfox_pages
Be the first person to like this.
Hi Aileen,

To be more specif the query that I and looking for is this:

SELECT COUNT(*)
FROM phpfox_blog AS blog
WHERE blog.is_approved = 1 AND blog.privacy IN(0) AND blog.post_status = 1 AND blog.module_id = 'blog'
LIMIT 1

This is one of filters utilized to fetch only blogs from phpfox_blog that was originating from blog modules, this way the blog section does not display blogs posted from pages because they have a "pages" instead "blog" in module_id column.

I found this using using the debug tool. I thought I would find easily this query in some service or controller from blog module, but I searched almost everything and couldn't find where the query is generated.

Because of this I asked if there is some logic about how these queries are build in phpfox. From what I noticed seems that the query is generated in parts so there isn't a simple query as above, but a set of codes that in the end generate this query.
Be the first person to like this.
You can add the pages's blogs just adding a condition to this query:

SELECT COUNT(*)
FROM phpfox_blog AS blog
WHERE blog.is_approved = 1 AND blog.privacy IN(0) AND blog.post_status = 1 AND (blog.module_id = 'blog' OR blog.module_id = 'pages')
LIMIT 1

As you noticed, there are not explicit queries in the PhpFox's code because it needs a different strategy, because of the big number of queries required by PhpFox.

Even so, if you develop your own module and use this query, you don't need to follow the regular PhpFox's strategy, so you can directly store the query in a variable and send it to the data base.
Be the first person to like this.
#8
Quote:
Originally posted by: Aileen Marcell

Even so, if you develop your own module and use this query, you don't need to follow the regular PhpFox's strategy, so you can directly store the query in a variable and send it to the data base.


Not exactly true. There are a number of cases where fox will trigger errors or reject statements. There really is no way to send any query directly to the database unless you circumvent the database interface.

A feature request is needed.


This example is contrived but exhibits one issue:

Phpfox::getLib('database')->query("UPDATE phpfox2_user SET full_name='fred' WHERE user_name='steward';UPDATE phpfox2_user SET user_name='fred' WHERE user_id=2");

SQL does this no problem. But Phpfox triggers an error.


There's another, long forgotten now... if I played with the IN() clause I could find it again. Basically fox has reduced all of us to the lowest common denominator of sql.

Which is fine.

But seriously don't kill yourself trying to use the phpfox database library unless you are doing exactly what fox has anticipated.

Compound queries or offbeat constructs require PDO.

Many hours lost here.

It is not the case that you can send arbitrary queries to the database.
Be the first person to like this.