There was an error while processing a database query

[jonahnaylor]

I'm suddenly getting errors on the search of my coppermine website. When you click search and type something  you get this error:

"There was an error while processing a database query."

I've enabled debug mode which has given me the following:

While executing query 'SELECT COUNT(*) FROM cpg_pictures AS p
LEFT JOIN `user_photos`.cpg_users AS u ON p.owner_id = u.user_id
WHERE ((title LIKE '%walls%' OR caption LIKE '%walls%' OR keywords LIKE '%walls%')) AND approved = 'YES' ' in include/search.inc.php on line 267

mySQL error: Column 'title' in where clause is ambiguous


AND ALSO:

Code Select Expand

USER:
------------------
Array
(
[ID] => b81666bc21fa23d0f3d3b25071604258
[am] => 1
[lang] => english_gb
[search] => Array
(
[params] => Array
(
[title] => on
[caption] => on
[keywords] => on
[type] => AND
[newer_than] =>
[older_than] =>
)

[search] => walls
)

)

==========================
USER DATA:
------------------
Array
(
[user_id] => 1
[user_name] => jonah
[groups] => Array
(
[0] => 1
)

[disk_max] => 0
[disk_min] => 0
[can_rate_pictures] => 1
[can_send_ecards] => 1
[can_post_comments] => 1
[can_upload_pictures] => 1
[can_create_albums] => 1
[has_admin_access] => 1
[access_level] => 3
[pub_upl_need_approval] => 0
[priv_upl_need_approval] => 0
[group_name] => Administrators
[can_create_public_albums] => 0
[group_quota] => 0
[can_see_all_albums] => 1
[group_id] => 1
[allowed_albums] => Array
(
)

)

==========================
Queries:
------------------
Array
(
[0] => SELECT name, value FROM cpg_config [include/init.inc.php:179] (0 ms)
[1] => SELECT * FROM cpg_plugins ORDER BY priority [include/plugin_api.inc.php:52] (0 ms)
[2] => SELECT user_id, time FROM `user_photos`.cpg_sessions WHERE session_id = '30d38af07be143b4dfcbc28226db13b2' [bridge/coppermine.inc.php:267] (0 ms)
[3] => SELECT user_id, user_password FROM `user_photos`.cpg_users WHERE user_id = 1 [bridge/coppermine.inc.php:279] (0 ms)
[4] => SELECT u.user_id AS id, u.user_name AS username, user_password AS password, u.user_group AS group_id FROM `user_photos`.cpg_users AS u LEFT JOIN `user_photos`.cpg_usergroups AS g ON u.user_group=g.group_id WHERE u.user_id='1' [bridge/udb_base.inc.php:70] (0 ms)
[5] => SELECT user_group_list FROM `user_photos`.cpg_users AS u WHERE user_id='1' AND user_group_list <> '' [bridge/coppermine.inc.php:205] (0 ms)
[6] => SELECT MAX(group_quota) AS disk_max, MIN(group_quota) AS disk_min, MAX(can_rate_pictures) AS can_rate_pictures, MAX(can_send_ecards) AS can_send_ecards, MAX(can_post_comments) AS can_post_comments, MAX(can_upload_pictures) AS can_upload_pictures, MAX(can_create_albums) AS can_create_albums, MAX(has_admin_access) AS has_admin_access, MAX(access_level) AS access_level, MIN(pub_upl_need_approval) AS pub_upl_need_approval, MIN( priv_upl_need_approval) AS priv_upl_need_approval FROM cpg_usergroups WHERE group_id in (1) [bridge/udb_base.inc.php:323] (0 ms)
[7] => SELECT group_name FROM cpg_usergroups WHERE group_id= 1 [bridge/udb_base.inc.php:327] (0 ms)
[8] => SELECT COUNT(*) FROM cpg_categorymap WHERE group_id in (1) [bridge/udb_base.inc.php:340] (0 ms)
[9] => SELECT description from cpg_albums WHERE aid = '0' [themes/user/theme.php:770] (0 ms)
[10] => SELECT lang_id FROM cpg_languages WHERE enabled='YES' [include/init.inc.php:330] (0 ms)
[11] => SELECT user_favpics FROM cpg_favpics WHERE user_id = 1 [include/init.inc.php:388] (0 ms)
[12] => SELECT name, value FROM cpg_cpgshop_config [plugins/cpgshop/libs/cpgDB.class.php:24] (0 ms)
[13] => DELETE FROM cpg_banned WHERE expiry < '2013-02-02 10:09:53' [include/init.inc.php:444] (0 ms)
[14] => SELECT null FROM cpg_banned WHERE (user_id=1 OR '188.222.203.115' LIKE ip_addr OR '' LIKE ip_addr ) AND brute_force=0 LIMIT 1 [include/init.inc.php:460] (0 ms)
[15] => SELECT COUNT(*) FROM cpg_pictures WHERE approved = 'NO' [include/functions.inc.php:2428] (0 ms)
[16] => SELECT lang_id, abbr FROM cpg_languages WHERE available='YES' AND enabled='YES' [include/themes.inc.php:2316] (0 ms)
[17] => SELECT message FROM cpg_temp_messages WHERE message_id = 'b74f6f3e06f2a2f00fbba9fee94e9f04' LIMIT 1 [include/functions.inc.php:4893] (0 ms)
[18] => DELETE FROM cpg_temp_messages WHERE message_id = 'b74f6f3e06f2a2f00fbba9fee94e9f04' [include/functions.inc.php:4904] (0 ms)
[19] => DELETE FROM cpg_temp_messages WHERE time < 1359814193 [include/functions.inc.php:4925] (1 ms)
[20] => SELECT COUNT(*) FROM cpg_pictures AS p
LEFT JOIN `user_photos`.cpg_users AS u ON p.owner_id = u.user_id
WHERE ((title LIKE '%walls%' OR caption LIKE '%walls%' OR keywords LIKE '%walls%')) AND approved = 'YES' [include/search.inc.php:267] (0 ms)
[21] => SELECT message FROM cpg_temp_messages WHERE message_id = 'b74f6f3e06f2a2f00fbba9fee94e9f04' LIMIT 1 [include/functions.inc.php:4893] (0 ms)
[22] => DELETE FROM cpg_temp_messages WHERE message_id = 'b74f6f3e06f2a2f00fbba9fee94e9f04' [include/functions.inc.php:4904] (0 ms)
[23] => DELETE FROM cpg_temp_messages WHERE time < 1359814193 [include/functions.inc.php:4925] (1 ms)
)

==========================
GET :
------------------
Array
(
[search] => walls
[submit] => search
[album] => search
[title] => on
[newer_than] =>
[caption] => on
[older_than] =>
[keywords] => on
[type] => AND
[message_id] => b74f6f3e06f2a2f00fbba9fee94e9f04
[message_icon] => success
)

==========================
POST :
------------------
Array
(
)

==========================
COOKIE :
------------------
Array
(
[cpg15x_data] => YTo0OntzOjI6IklEIjtzOjMyOiJiODE2NjZiYzIxZmEyM2QwZjNkM2IyNTA3MTYwNDI1OCI7czoyOiJhbSI7aToxO3M6NDoibGFuZyI7czoxMDoiZW5nbGlzaF9nYiI7czo2OiJzZWFyY2giO2E6Mjp7czo2OiJwYXJhbXMiO2E6Njp7czo1OiJ0aXRsZSI7czoyOiJvbiI7czo3OiJjYXB0aW9uIjtzOjI6Im9uIjtzOjg6ImtleXdvcmRzIjtzOjI6Im9uIjtzOjQ6InR5cGUiO3M6MzoiQU5EIjtzOjEwOiJuZXdlcl90aGFuIjtzOjA6IiI7czoxMDoib2xkZXJfdGhhbiI7czowOiIiO31zOjY6InNlYXJjaCI7czo1OiJ3YWxscyI7fX0=
[__cfduid] => d70cbacdf50056b3e900b4a479252a1a01359817781
[__utma] => 28431924.774999450.1359817782.1359817782.1359817782.1
[__utmb] => 28431924.4.10.1359817782
[__utmc] => 28431924
[__utmz] => 28431924.1359817782.1.1.utmcsr=(direct)|utmccn=(direct)|utmcmd=(none)
[826f9f0b7449e99cf6dad6f4ab516cb4] => 14046c5b66f87dedc6adaaa4a9d355ad
)

==========================
VERSION INFO :
------------------
PHP version 5.3.20 OK
MySQL version 5.1.66-cll OK
Coppermine version 1.5.22 stable

==========================
Module: GD
------------------
Exact version 2.0.34
GD Version bundled (2.0.34 compatible)
FreeType Support 1
FreeType Linkage with freetype
T1Lib Support
GIF Read Support 1
GIF Create Support 1
JPEG Support 1
PNG Support 1
WBMP Support 1
XPM Support 1
XBM Support 1
JIS-mapped Japanese Font Support

==========================
Key config settings
------------------
site_url http://www.yorkstockphotos.co.uk/
charset utf-8
allow_private_albums 1
cookie_name cpg15x
cookie_path /
impath
lang english
main_page_layout anycontent/breadcrumb/catlist/alblist/random,2/lastup,2
silly_safe_mode 0
smtp_host
theme user
thumb_method gd2

==========================
Plugins
------------------
Name Google Analytics
Actions plugin_install, plugin_configure, plugin_uninstall, plugin_cleanup
Filters gallery_footer
--------------
Name cpgShop
Actions plugin_install, plugin_configure, plugin_uninstall, plugin_cleanup, page_start, profile_display_form, profile_submit_form
Filters register_form_create, register_form_submit, admin_menu, gallery_header, gallery_footer, file_data
--------------

==========================
Server restrictions
------------------
safe_mode 0
safe_mode_exec_dir /usr/local/php/bin
safe_mode_gid 0
safe_mode_include_dir
sql.safe_mode
disable_functions
file_uploads 1
include_path .:/usr/lib/php:/usr/local/lib/php
open_basedir
allow_url_fopen 1
max_execution_time 600
max_input_time 600
upload_max_filesize 64M
post_max_size 100M
memory_limit 512M
suhosin.post.max_vars
suhosin.request.max_vars


==========================
Page (performance)
------------------
Parameter Current Peak
Memory usage 7.42 MiB 7.72 MiB
Page generation 123 ms 123 ms
Page query time 2 ms 2 ms
Page query count 24 24


==========================



Thanks for any help with this.

[Αndré]

It seems that your users table contains a column "title". This was probably added by a plugin. However, to fix it open include/search.inc.php, find

Code Select Expand

if (in_array($param, $allowed)) $fields[] = "$param LIKE '%$word%'";
and replace with

Code Select Expand

if (in_array($param, $allowed)) $fields[] = ($param == 'title' ? 'p.title' : $param)." LIKE '%$word%'";