Extending Phoca plugin

[chabi01]

Hi everybody

I need some expert eyes to help me to sort my issue if possible

I'm going to explain the goal and what I did.

I use in a content page the plugin to call filelist.
The goal here is to limit the file shown in a page with the file created in the last 60 days.

I have tried several method, but the best method is to add some code in the Phocadownload plugin.

In the article, I have added a tag "day" like this :
{phocadownload view=filelist|id=51|day=60}
At the end, I have then "day=60".
The number 60 is not used right now but maybe in the future to chose the number of days (I will have to work more on this as I'm beginning on SQL and so .

Ok, in the phoca plugin file, i have add these code on line 122 in the part "Get plugin parameters from article" :

Code: Select all

else if($values[0]=='url')				{$url				= $values[1];}
else if($values[0]=='day')				{$day				= $values[1];}
	}

This way, the tag" day" is stored with the value "60" in the var "$day".

Ok, now on lines 252+, I can see the case "Filelist" with the query.

To add my filtering here, I just add a condition and my query to limit only to the 60 last days of creation (i dont even check the value $day, just if the value is not empty) :

Code: Select all

case 'filelist':
					
						$fileOrdering 		= PhocaDownloadOrdering::getOrderingText($ordering, 3);
						
						if ($day !='') {
						
                                                                                    
						$query = 'SELECT a.id, a.title, a.alias, a.filename_play, a.filename_preview, a.link_external, a.image_filename, a.filename, a.date as date, c.id as catid, a.confirm_license, c.title as cattitle, c.alias as catalias'
						. ' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(\':\', a.id, a.alias) ELSE a.id END as slug,'
						. ' CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(\':\', c.id, c.alias) ELSE c.id END as catslug'
						. ' FROM #__phocadownload AS a'
						. ' LEFT JOIN #__phocadownload_categories AS c ON a.catid = c.id'
						. ' WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) AND NOW()';
						
						
						} else {
						
						
						$query = 'SELECT a.id, a.title, a.alias, a.filename_play, a.filename_preview, a.link_external, a.image_filename, a.filename, c.id as catid, a.confirm_license, c.title as cattitle, c.alias as catalias,'
						. ' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(\':\', a.id, a.alias) ELSE a.id END as slug,'
						. ' CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(\':\', c.id, c.alias) ELSE c.id END as catslug'
						. ' FROM #__phocadownload AS a'
						. ' LEFT JOIN #__phocadownload_categories AS c ON a.catid = c.id';
						
						}

You can see in my first query, I just want to limit the list to the file in the 60 last days.

But then, Mysql throw me an error...

Code: Select all

1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(':', a.id, a.alias) ELSE a.id END ' at line 1

So my question are :
- To achieve what I want, Is this the good way ? (I know, if I update the extension, I will lose my changes
- What is going wrong in my query ?

A big thanks in advance to all for the help
Xavier

[Jan]

Hi, if there is no other way, then customized version of plugin is OK, as you only have one file to maintain.

As the database says, there is some syntax error in the SQL query.

You should write a.date instead of date but I don't think this is the main error, as the main error is syntax error. Try to display whole SQL query and check the syntax in e.g. phpMyAdmin.

Jan

[chabi01]

Hi Jan and thank you for your help.

I find a strange thing... If I put the original sql in phpmyadmin, it raises me an error (???) but it is working in the php file ?
I really dont understand...
Do I have to put all in one line (and remove all the cariage return) ?

I know this is stupid question, but I do this for the very first time (please dont kick me hard if I'm asking stupid question for the experts...

Thanks
Xavier

[chabi01]

Ok,
If I remove the line with '.', i get this query :

Code: Select all

SELECT a.id, a.title, a.alias, a.filename_play, a.filename_preview, a.link_external, a.image_filename, a.filename, a.date, c.id as catid, a.confirm_license, c.title as cattitle, c.alias as catalias CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(\':\', a.id, a.alias) ELSE a.id END as slug,CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(\':\', c.id, c.alias) ELSE c.id END as catslug FROM #__phocadownload AS a LEFT JOIN #__phocadownload_categories AS c ON a.catid = c.id' WHERE date BETWEEN DATE_SUB(NOW(), INTERVAL 60 DAY) AND NOW()

Then, SQL raises me 2 errors :
Unexpected caracter : (near "\" at position 244)
Unexpected end of CASE : (near "" at position 0)
and at the end :
#1064 - Syntax error near 'CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(\':\', a.id, a.alias) ELSE a.id...'

Some expert is able to correct me ?
With the original query, i have just added the "a.date" and the "WHERE....." to select the interval of date.
What am I doing wrong ?

Again, thanks to all people who will help me to learn and make this working

Xavier

[chabi01]

I continue to search also on my own.
If I understand well the issue is for \':\'
The first \ is used to escape the whole :\ ?
How to write it in SQL and how to write it the Query in the Php file then ?

To check, I have used the original Query (without any change from me) in SQL and an error is also raised ???

Code: Select all

SELECT a.id, a.title, a.alias, a.filename_play, a.filename_preview, a.link_external, a.image_filename, a.filename, c.id as catid, a.confirm_license, c.title as cattitle, c.alias as catalias, CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(\':\', a.id, a.alias) ELSE a.id END as slug, CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(\':\', c.id, c.alias) ELSE c.id END as catslug FROM #__phocadownload AS a LEFT JOIN #__phocadownload_categories AS c ON a.catid = c.id

Again, thanks for your patience...
Xavier

[Jan]

Hi, unfortunately I am not on my PC now, so I cannot add any exact advice.

But it depends on used " or ' in PHP if the query will be OK or not. In case, you don't get any PHP error but SQL error, this means, the syntax is OK for PHP (so " or ' are used correct) but not OK for SQL

The first \ is used to escape the whole :\ ?

No, escape works only for first character, so with \':\' you escape both ' (at start and at end) and you escape it for PHP, so then PHP sends to SQL : only.

Jan

[chabi01]

Hi Jan,
Still trying... still losing my hairs... still stuck...

How do you understand this : why the original query is working but not mine on this escaping caracters, but if I put your query directly in phpmyadmin i get an error ? How this can work in the php file but is refused on phpmyadmin ???

Is this because of the a.something ? Phpmyadmin needs the full name in the query ?

So hard to learn alone...

Thanks for your help anyway (and if you are not in front of your computer, a bigger thanks for your time to answer me ! I hope you are not helping lost guys like me in your vacation !).

Best to you,
Xavier

[Jan]

Hi, yes, it is hard for me to give you some clue advice in case I don't know which all steps you are doing.

The error can come from many sides, from different steps: e.g. when copying. When you copy SQL you need to render it by PHP, so the SQL query is raw and final and does not include any source code parts (like quotes, etc.). I would say, there is some problem when transforming raw SQL query to PHP where you need to escape the variables, etc. Or maybe there is only some simple typo error in the SQL query itself, really hard to say

Jan

[chabi01]

Hi Jan,
I did it !!! YES, I did it !!!! ))

As other people could be interrested, I give you the code I have added, but all this could be improved

So, I dont come back on the first part (day, the day recognition in the phocadownload plugin) and jump directly to the solution I have found :
For the query, I have this one (only one is enough in fact, if the var $day is not setted, the date will be useless that's all.

So, the query is :

Code: Select all

$query = 'SELECT a.id, a.title, a.alias, a.filename_play, a.filename_preview, a.link_external, a.image_filename, a.filename, c.id as catid, a.confirm_license, a.date, c.title as cattitle, c.alias as catalias,'
						. ' CASE WHEN CHAR_LENGTH(a.alias) THEN CONCAT_WS(\':\', a.id, a.alias) ELSE a.id END as slug,'
						. ' CASE WHEN CHAR_LENGTH(c.alias) THEN CONCAT_WS(\':\', c.id, c.alias) ELSE c.id END as catslug'
						. ' FROM #__phocadownload AS a'
						. ' LEFT JOIN #__phocadownload_categories AS c ON a.catid = c.id';

I did not include here the filtering in the query, just added the field date (a.date ! Thanks Jan !).

Now, I have added this in the for each (around line 295) :

Code: Select all

$output .= '<div class="phocadownloadfilelist">';
							foreach ($items as $item) {
						//Begin CODE
						if (isset($item->date) && ($item->date != '') && $day !='') {
						
						$dateString = strtotime($item->date) ;
						$dateStringtoday = strtotime(date("Y-m-d H:i:s")) ;
                                                
                                                	if (floor(($dateStringtoday - $dateString)/24/60/60) > $day) {
                                                    	continue;  
                                                    	}
						}
						//End CODE

That's it !

This is working fine !!!
I was so stupid to try to create a too complex query ! Much more easier in PHP

So, now, the thing to think is :

- With this code, all the page is filtered : if i put "day=a_value" in the string, the filter is working well but is working also for all the string in the page : I can not with this solution filter a first category on the 60 last days and keep a second category with the full list unfiltered.

- I will have to work on myself to try to understand why the query was not working on my first approach and why.

Anyway, this is working, with possible enhancement, but working !

Thanks anyway for the help you gave me
Xavier (very happy to succeed ))

[Jan]

Hi, great to hear it.

Jan