HTML Logo by World Wide Web Consortium (www.w3.org). Click to learn more about our commitment to accessibility and standards.

Moving forward with Composr

ocPortal has been relaunched as Composr CMS, which is now in beta. ocPortal 9 will be superseded by Composr 10.

Head over to compo.sr for our new site, and to our migration roadmap. Existing ocPortal member accounts have been mirrored.


Block main_search Error - 5.0.0

Login / Search

 [ Join | More ]
 Add topic 
Posted
Rating:
#61744 (In Topic #13441)
Avatar

Well-settled

Invalid SQL statement; returns no result

Hi Chris,

the block [main_search] returns a SQL error. I put the main_search this way:

...block limit_to="news,calendar,catalogue_categories,catalogue_entries,cedi_pages,cedi_posts,comcode_pages,download_categories,downloads,filedump,galleries,images,iotds,ocf_clubs,ocf_members,ocf_own_pt,ocf_posts,ocf_within_topic,polls,quiz,videos" sort="add_date" days="-1" direction="DESC" conjunctive_operator="AND"]main_search[/block...

I might be wrong, but I see the following issues:

1) The SQL statement appears to be invalid after the FROM clause (please find below). Query fails.
...FROM ocp4_ ( catalogue_fields...
2) After correcting 1), it returns no resultset
3) After changing
...JOIN ocp4_translate t0 on t0.id=a.cv_value...
...JOIN ocp4_translate t1 on t1.id=b.cv_value...
to LEFT JOIN
it returns a resultset, but is limited to the catalogues.


This is the stack trace:


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/sources/failure.php'
Line'592'
Function'get_html_trace'
Args

File'/opt/lampp/htdocs/mountainbike-webmagazin.de/sources/global2.php'
Line'883'
Function'_fatal_exit'
Argsobject


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/sources/database/mysql.php'
Line'232'
Function'fatal_exit'
Argsobject


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/sources/database.php'
Line'1,025'
Function'db_query'
Class'Database_Static_mysql'
Type'->'
ObjectDatabase_Static_mysql::__set_state(array( ))
Args'SELECT *,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value FROM ocp4_ ( catalogue_fields f,ocp4_catalogue_entries r) LEFT JOIN ocp4_catalogue_efv_short_trans a on (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocp4_catalogue_efv_long_trans b on (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocp4_catalogue_efv_short c on (r.id=c.ce_id AND f.id=c.cf_id) LEFT JOIN ocp4_catalogue_efv_long d on (r.id=d.ce_id AND f.id=d.cf_id) JOIN ocp4_seo_meta m on (m.meta_for_type=\'catalogue_entry\' AND m.meta_for_id=r.id) JOIN ocp4_translate tm on tm.id=m.meta_keywords JOIN ocp4_translate t0 on t0.id=a.cv_value JOIN ocp4_translate t1 on t1.id=b.cv_value WHERE tm.text_original LIKE \'%scott%\' ORDER BY ce_add_date DESC'

array ( 0 => NULL, 1 => 'mountainbike_webmagazin_de', )

NULL

NULL

false

false

false

false


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/sources/database.php'
Line'920'
Function'_query'
Class'database_driver'
Type'->'
Objectdatabase_driver::__set_state(array( 'table_prefix' => 'ocp4_', 'connection_read' => array ( 0 => NULL, 1 => 'mountainbike_webmagazin_de', ), 'connection_write' => array ( 0 => NULL, 1 => 'mountainbike_webmagazin_de', ), 'text_lookup_original_cache' => array ( 78 => '', 79 => '', 36 => 'Administratoren', 38 => 'Chef-Moderatoren', 37 => '', 39 => 'Site staff', 9 => '', 4 => 'Mountainbike Webmagazin und Community', 16 => 'About me', 18 => 'AIM ID', 20 => 'MSN Messenger ID', 22 => 'Yahoo messenger ID', 24 => 'Skype ID', 26 => 'Interests', 28 => 'Location', 30 => 'Occupation', 4543 => 'Bikes', 4545 => 'Bikerevier', 4547 => 'Bikekategorien', 17 => 'Some personally written information.', 19 => 'AIM username.', 21 => 'E-mail address of MSN Messenger account.', 23 => 'Log in name of a Yahoo messenger account.', 25 => 'Skype username.', 27 => 'A summary of your interests.', 29 => 'Your geographical location.', 31 => 'This member\'s occupation.', 4544 => 'Welche Bikes fahren Sie?', 4546 => 'Wo ist oder sind Ihr(e) Bikerevier(e)? Wo biken Sie am liebsten?', 4548 => 'Was fahren Sie am liebsten? XC, Marathon, Tour, Trail, All Mountain, Freeride, Downhill, Dirt, oder ganz etwas anderes?', ), 'text_lookup_cache' => array ( 78 => 'return unserialize("a:6:{i:0;a:0:{}i:1;a:0:{}i:2;s:10:\":container\";i:3;N;i:4;N;i:5;s:0:\"\";}"); ', 79 => '', 36 => '', 38 => '', 37 => '', 39 => '', 9 => '', 4 => '', 16 => '', 18 => '', 20 => '', 22 => '', 24 => '', 26 => '', 28 => '', 30 => '', 4543 => '', 4545 => '', 4547 => '', 17 => '', 19 => '', 21 => '', 23 => '', 25 => '', 27 => '', 29 => '', 31 => '', 4544 => '', 4546 => '', 4548 => '', ), 'table_exists_cache' => array ( ), 'static_ob' => Database_Static_mysql::__set_state(array( )), ))
Args'SELECT *,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value FROM ocp4_(catalogue_fields f,ocp4_catalogue_entries r) LEFT JOIN ocp4_catalogue_efv_short_trans a on (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocp4_catalogue_efv_long_trans b on (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocp4_catalogue_efv_short c on (r.id=c.ce_id AND f.id=c.cf_id) LEFT JOIN ocp4_catalogue_efv_long d on (r.id=d.ce_id AND f.id=d.cf_id) JOIN ocp4_seo_meta m on (m.meta_for_type=\'catalogue_entry\' AND m.meta_for_id=r.id) JOIN ocp4_translate tm on tm.id=m.meta_keywords JOIN ocp4_translate t0 on t0.id=a.cv_value JOIN ocp4_translate t1 on t1.id=b.cv_value WHERE tm.text_original LIKE \'%scott%\' ORDER BY ce_add_date DESC'

NULL

NULL

false

false

NULL

''


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/sources/database_search.php'
Line'407'
Function'query'
Class'database_driver'
Type'->'
Objectdatabase_driver::__set_state(array( 'table_prefix' => 'ocp4_', 'connection_read' => array ( 0 => NULL, 1 => 'mountainbike_webmagazin_de', ), 'connection_write' => array ( 0 => NULL, 1 => 'mountainbike_webmagazin_de', ), 'text_lookup_original_cache' => array ( 78 => '', 79 => '', 36 => 'Administratoren', 38 => 'Chef-Moderatoren', 37 => '', 39 => 'Site staff', 9 => '', 4 => 'Mountainbike Webmagazin und Community', 16 => 'About me', 18 => 'AIM ID', 20 => 'MSN Messenger ID', 22 => 'Yahoo messenger ID', 24 => 'Skype ID', 26 => 'Interests', 28 => 'Location', 30 => 'Occupation', 4543 => 'Bikes', 4545 => 'Bikerevier', 4547 => 'Bikekategorien', 17 => 'Some personally written information.', 19 => 'AIM username.', 21 => 'E-mail address of MSN Messenger account.', 23 => 'Log in name of a Yahoo messenger account.', 25 => 'Skype username.', 27 => 'A summary of your interests.', 29 => 'Your geographical location.', 31 => 'This member\'s occupation.', 4544 => 'Welche Bikes fahren Sie?', 4546 => 'Wo ist oder sind Ihr(e) Bikerevier(e)? Wo biken Sie am liebsten?', 4548 => 'Was fahren Sie am liebsten? XC, Marathon, Tour, Trail, All Mountain, Freeride, Downhill, Dirt, oder ganz etwas anderes?', ), 'text_lookup_cache' => array ( 78 => 'return unserialize("a:6:{i:0;a:0:{}i:1;a:0:{}i:2;s:10:\":container\";i:3;N;i:4;N;i:5;s:0:\"\";}"); ', 79 => '', 36 => '', 38 => '', 37 => '', 39 => '', 9 => '', 4 => '', 16 => '', 18 => '', 20 => '', 22 => '', 24 => '', 26 => '', 28 => '', 30 => '', 4543 => '', 4545 => '', 4547 => '', 17 => '', 19 => '', 21 => '', 23 => '', 25 => '', 27 => '', 29 => '', 31 => '', 4544 => '', 4546 => '', 4548 => '', ), 'table_exists_cache' => array ( ), 'static_ob' => Database_Static_mysql::__set_state(array( )), ))
Args'SELECT *,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value FROM ocp4_(catalogue_fields f,ocp4_catalogue_entries r) LEFT JOIN ocp4_catalogue_efv_short_trans a on (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocp4_catalogue_efv_long_trans b on (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocp4_catalogue_efv_short c on (r.id=c.ce_id AND f.id=c.cf_id) LEFT JOIN ocp4_catalogue_efv_long d on (r.id=d.ce_id AND f.id=d.cf_id) JOIN ocp4_seo_meta m on (m.meta_for_type=\'catalogue_entry\' AND m.meta_for_id=r.id) JOIN ocp4_translate tm on tm.id=m.meta_keywords JOIN ocp4_translate t0 on t0.id=a.cv_value JOIN ocp4_translate t1 on t1.id=b.cv_value WHERE tm.text_original LIKE \'%scott%\' ORDER BY ce_add_date DESC'


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/sources/hooks/modules/search/catalogue_entries.php'
Line'318'
Function'get_search_rows'
Args'catalogue_entry'

'id'

array ( 0 => 'scott', )

false

'DESC'

10

0

false

'(catalogue_fields f,ocp4_catalogue_entries r) LEFT JOIN ocp4_catalogue_efv_short_trans a on (r.id=a.ce_id AND f.id=a.cf_id) LEFT JOIN ocp4_catalogue_efv_long_trans b on (r.id=b.ce_id AND f.id=b.cf_id) LEFT JOIN ocp4_catalogue_efv_short c on (r.id=c.ce_id AND f.id=c.cf_id) LEFT JOIN ocp4_catalogue_efv_long d on (r.id=d.ce_id AND f.id=d.cf_id)'

array ( 0 => 'a.cv_value', 1 => 'b.cv_value', )

''

'MATCH (?) AGAINST (\'scott\')'

'ce_add_date'

'*,r.id AS id,r.cc_id AS r_cc_id,b.cv_value AS b_cv_value'

array ( 0 => 'c.cv_value', 1 => 'd.cv_value', )


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/site/pages/modules/search.php'
Line'553'
Function'run'
Class'Hook_search_catalogue_entries'
Type'->'
ObjectHook_search_catalogue_entries::__set_state(array( ))
Argsarray ( 0 => 'scott', )

false

'DESC'

10

0

false

'MATCH (?) AGAINST (\'scott\')'

''

NULL

NULL

'add_date'

''

'OR'

''

'!'

0


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/site/pages/modules/search.php'
Line'452'
Function'results'
Class'Module_search'
Type'->'
ObjectModule_search::__set_state(array( ))
Args''

''

NULL

-1

'add_date'

'DESC'

false

'!'


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/site/pages/modules/search.php'
Line'217'
Function'form'
Class'Module_search'
Type'->'
ObjectModule_search::__set_state(array( ))
Args

File'/opt/lampp/htdocs/mountainbike-webmagazin.de/sources/zones.php'
Line'336'
Function'run'
Class'Module_search'
Type'->'
ObjectModule_search::__set_state(array( ))
Args

File'/opt/lampp/htdocs/mountainbike-webmagazin.de/sources/site.php'
Line'962'
Function'load_module_page'
Args'site/pages/modules/search.php'

'search'


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/sources/misc_scripts.php'
Line'188'
Function'request_page'
Args'search'

true


File'/opt/lampp/htdocs/mountainbike-webmagazin.de/data/iframe.php'
Line'49'
Function'iframe_script'
Args

Back to the top
 
Posted
Rating:
#61763
Avatar

Attachment
sources/database_search.php
» Download: database_search.php (30 Kb, 117 downloads so far)


Thanks for the report. I think this will fix it. I accidentally removed some code during previous debugging.


Become a fan of ocPortal on Facebook or add me as a friend. Add me on on Twitter.
Was I helpful?
  • If not, please let us know how we can do better (please try and propose any bigger ideas in such a way that they are fundable and scalable).
  • If so, please let others know about ocPortal whenever you see the opportunity.
  • If my reply is too Vulcan or expressed too much in business-strategy terms, and not particularly personal, I apologise. As a company & project maintainer, time is very limited to me, so usually when I write a reply I try and make it generic advice to all readers. I'm also naturally a joined-up thinker, so I always express my thoughts in combined business and technical terms. I recognise not everyone likes that, don't let my Vulcan-thinking stop you enjoying ocPortal on fun personal projects.
  • If my response can inspire a community tutorial, that's a great way of giving back to the project as a user.
Back to the top
 
Posted
Rating:
#61780
Avatar

Well-settled

Hi Chris,

Thanks very much for the fix. It resolved the issue.

Regards,

Dirk
Back to the top
 
1 guests and 0 members have just viewed this: None
Control functions:

Quick reply   Contract

Your name:
Your message: