Search filters in tables with pre-filters: SQL error (Solved in SVN 40)

[joaquinjgc]

Configuration:
Fabrik version: SVN Rev. 36
Joomla! Version: Joomla! 1.0.12 Stable
PHP version: 5.1.6
MySQL version: 5.0.27-standard-log
Linux
Web Server: Apache/1.3.37
Relevant PHP Settings: all correct for Joomla!
----------------------------------------------------------------

Descripcion:
If you have a table with pre-filters and search filters, when you use the search filters give a SQL error

I do...:
Use a search filter, you can see the error here if you use a search field:
http://www.zonagimnasios.com/component/option,com_fabrik/Itemid,72/

Error menssage:
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 \'where ( Gimnasio.gimnasiopubli = \'1\'
AND Gimnasio.gimnasiovalido = \'1\' \' at line 17 SQL=SELECT Gimnasio.gimnasioid AS `Gimnasio.gimnasioid`,
Gimnasio.gimnasioidusuario AS `Gimnasio.gimnasioidusuario`,
Gimnasio.gimnasionombre AS `Gimnasio.gimnasionombre`,
Gimnasio.gimnasiotipo AS `Gimnasio.gimnasiotipo`,
Gimnasio.gimnasioprovincia AS `Gimnasio.gimnasioprovincia`,
Gimnasio.gimnasiociudad AS `Gimnasio.gimnasiociudad`,
Gimnasio.gimnasiocp AS `Gimnasio.gimnasiocp`,
Gimnasio.gimnasiourlact AS `Gimnasio.gimnasiourlact`,
Gimnasio.gimnasiourlinst AS `Gimnasio.gimnasiourlinst`,
Gimnasio.gimnasiourlserv AS `Gimnasio.gimnasiourlserv`,
Gimnasio.gimnasiopubli AS `Gimnasio.gimnasiopubli`,
Gimnasio.gimnasioabierto AS `Gimnasio.gimnasioabierto`,
Gimnasio.gimnasiovalido AS `Gimnasio.gimnasiovalido`,
Gimnasio.gimnasiovisible AS `Gimnasio.gimnasiovisible`
FROM Gimnasio
WHERE Gimnasio.gimnasiotipo LIKE \'%Centro Deportivo%\'
where ( Gimnasio.gimnasiopubli = \'1\'
AND Gimnasio.gimnasiovalido = \'1\'
AND Gimnasio.gimnasiovisible = \'1\' )
ORDER BY Gimnasio.gimnasionombre ASC LIMIT 0, 30

I think that the problem could be the 2 "Where" in the code.

Thank you

[rob]

hi Joaquin

Please try checking out version 38, I've spent today reorganising the filter code to fix this issue

let me know if it works for you

Cheers
Rob

[joaquinjgc]

Thanks, I´ll try now.

[joaquinjgc]

:'( :'(, Don´t works, and now the pre-filters don´t works else.

Only if I set pre-filters to super administrator, publisher are filtered, but public, register...etc not.

When I get find that an user-level are pre-filtered the same SQL error happens.

You can see it into my page, jpre-filters are set to superadministrator, only should show 2 rows.

You can test it here:

http://www.zonagimnasios.com/component/option,com_fabrik/Itemid,72/

Sorry.

------------------------------------
Edit:
I´m testing it (you can see in previous link), the first time you access to the table prefilters works, but, when you use a search field prefilters don´t works and show all entries, if you remove the search field or go to the table link again, the pre-filters don´t work.

-----------------------------------
Edit again:
I don´t understand it, if I login as publisher prefilters work like the previous version (SVN 36), and gime the same error, but like public happend who I tell you in the previosus edition.

[rob]

Hi

I cant get that page to give me an sql errror - have you updated it since you last posted (SVN is now revision 40 )

[joaquinjgc]

:D :D :D :D :D :D :D, I updated to 38, and it don´t work, I went to jomlaforge ( I updated my svn directory 5 minutes after you uplad the files) and saw that the SVN are 40 :D, it work correctly now, thanks you very very very.... much, I had my webpage stoped.

[joaquinjgc]

There is another problem, I got to post it in a new topic.

[alanparis]

Hi,
I have download version upgrade (fabrik1.0.4_upgrade_from_1.0.x--07-06-2007.zip) (overwrite the previous files 1.3)
Then apply the prefilter in the table it's ok but then insert search criteria to element display thi error

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 \'where ( jos_alberghi.facilities LIKE \'%31%\' ) LIMIT 0, 15\' at line 10 SQL=SELECT jos_alberghi.id AS `jos_alberghi.id`,
jos_alberghi.title AS `jos_alberghi.title`,
jos_alberghi.comune AS `jos_alberghi.comune`,
jos_alberghi.provincia AS `jos_alberghi.provincia`,
jos_alberghi.regione AS `jos_alberghi.regione`,
jos_alberghi.id AS `jos_alberghi.id`,
jos_alberghi.id AS __pk_val
FROM jos_alberghi
WHERE jos_alberghi.regione LIKE \'%Campania%\'
where ( jos_alberghi.facilities LIKE \'%31%\' ) LIMIT 0, 15

In the code of template standard.html I see the imput box button "Go" in the layout page of table to site this don't see.

I have read this topic solved but don't understand. This problem is solved with ver 38? What's 38?
thanks, alan

[andrelewis]

Hi Alan,
There are several versions of Fabrik available, the last official release version is the 1.03, however the 1.04b is the last pre-packaged version of Fabrik and is the most stable of the various versions. As Rob goes through and makes fixes and tests the packages, he stores the fixes in a program that keeps track of changes between versions of Fabrik.

The software that keeps track of the different versions is called Subversion, and when you see some reference to svn or Subversion that is what they are talking about. It is a version control system.

When someone refers to 1.04b rev 40, they are referring to Subversion's internal number which tells you what the latest development version is as opposed to a pre-packaged version. When you download from Subversion you are downloading the actual development files.

You will need to download the code from the SVN at
http://joomlacode.org/gf/project/fabrik/scmsvn/

In order to use the development version, you will need to overwrite all of the files by hand or wait for an official packaged version. When you uninstall Fabrik you DELETE all of your Fabrik tables and lose your tables. ALWAYS BACK UP YOUR DATABASE before un-installing Fabrik (I do not enjoy this behavior myself) but that's what the component uninstall does.

So that is what svn thing is all about.

[alanparis]

Hi Andre, thanks, I have now understood!
Question:
I have installed copying to hand the files, but the charts are those of the 1.03 (original .zip install).
Does thing recommend me?
Do I cancel all (back to store data) and new install the version SVN 50 (this is the number of joomlacode.org) or replace only file with SVN 50?
alan

[joaquinjgc]

only overwrite and replace with the new files.

BACKUP!!!!!!

[alanparis]

Ok, backup and only overwrite and replace files, thanks Joaquin!

[andrelewis]

Another Note:

From version 1.04b rev 35 to 1.04b rev 50, the Fabrik table structure for jos_fabrik_forms has changed and a column for use_curl has been removed. This means that a restore of just data will fail for your forms. You must either edit your data, or make sure you back up table structures as well as data, including drop tables. I don't think that Joomla's / PHP's database access cares if there are extra fields in a database resultset, only if there is code looking for a missing column. That means that restoring the older 1.04b release table structures should be compatible with the new release.

[alanparis]

Ah, please Andre, do you send me the correct version file for manualy replace and the structure of table for modification with phpmyadmin?

thanks
alan

[andrelewis]

Hi Alan,
I am looking into this right now, I want to track down all of the table changes.

Please don't try any of this until I have had a chance to verify what other changes might have taken place.

I'll post when I know for sure.

Andre

[alanparis]

Ok, I waiting!
thaks
alan

[andrelewis]

The following only applies to doing a clean install on a server. DO NOT TRY THIS ON A BOX YOU HAVE CUSTOMIZED, as Fabrik will blow away all of your Form data when you uninstall it.

Ok. If you are moving it across from a 1.04b rev 35 version to a server with 1.04b rev 50 then you should do the following:

Back up you development machine's data:
Using a tool such as phpmysqladmin or mysqladministrator
Make sure you got all the Fabrik tables and your custom tables
Make sure you check include options to Drop Table / Create Table Statements

Use subversion to grab the latest version of Fabrik:
If you have the subversion client installed on your machine:
svn checkout http://joomlacode.org/svn/fabrik

Otherwise use your web browser and download everything.
Go to the following directory from where you ran subversion (Linux filesystem, in windows use the other slash)
fabrik/1.0.x/trunk

Create a temporary directory and copy in everything from the administrator/components/com_fabrik/ directories to your temporary directory.
Then copy in everything from your components/com_fabrik directories.

zip everything in this temporary directory and rename the zip file com_fabrik_1.04rev50.zip
That file is now the current component.

if you want to you can also zip up the bot_fabrik_*.php files with the corresponding .xml file (One .php and matching .xml per zip)

DO NOT uninstall anything from joomla on your local machine without doing a full backup of the database first.

Install your component on the server using the admin panel (Reminder: DON'T DO THIS ON A BOX WHERE YOU HAVE ALREADY CUSTOMIZED FABRIK. If you have customized fabrik on this box, you should probably just overwrite the previous files by hand)

Restore the Fabrik data using phpmysqladmin on the server. Voila, everything should show up.

** If we haven't drilled it into you by now, backup your databases before trying any of this **

[alanparis]

Ok Andre, I have read, now read more and more ...
at moment thanks for your attention and information.

alan
(I return)

[alanparis]

Hi Andre, I need to upgrade fabrik with last SVN because i need in frontend the filter table but my problem are the download.
Where i find pack of files group to SVN 50? Do you send me your files group?

thanks, alan

[andrelewis]

Hi Alan,
You don't want the latest SVN, there are too many bugs.

Normally you would download everything through Subversion overwrite the files.

I would wait a few days till they have a more stable version.

Andre

[alanparis]

Ok, I waiting.

thanks very much.
alan

[alanparis]

Hi Andre, there are good news for new more stable realese with filter table?

I have another problem with receipt to{email}, please do you read this post? http://fabrikar.com/index.php?option=com_smf&Itemid=9&topic=897.0
Solved!!!
I need to resolv part of problems because I go to online with the site, do you help me for this?
thanks
alan

[andrelewis]

Hi Alan, I have updated the wiki,
http://fabrikar.com/index.php?option=com_openwiki&Itemid=11 to add more information on installing, however there are still bugs in the current version (Rev 78) for editing a record. I would think they should be able to fix that quickly, and once that's done it should be fairly stable. I'm testing it now.

Post the URL up and we can try to break it :)

I didn't realize you had posted because edits don't show up as an unread post or a reply to a post.


Sooon!
Andre

[alanparis]

Hi Andre, thanks,
this upgrade/solution resolve the problem of the search-filters table?
For me is very difficult to find the correct new files for this operation.
Do you send me one .zip pack of this?

alan