[FIXED] SQL error on search sorted by author

Submitted by Anonymous on Fri, 09/18/2009 - 17:49
Written by
IngoB

Hello,

try to search something and select 'Sort by: Author'

This will raise an error:

    In file sources/db_mysql.php on line 117:

SQL_ERROR - Unknown column 'u.displayed_name' in 'order clause'

Enable debug mode level 2 to see the error and erroneous SQL query.

You can test it with this forum also.

Same error message on this site and on mine.

This should help:

in search.php:

find

$result = $db->query("SELECT ".$query_select." FROM ".TABLE_PREFIX."posts p LEFT JOIN ".TABLE_PREFIX."members m ON p.poster_id = m.id, ".TABLE_PREFIX."posts p2, ".TABLE_PREFIX."topics t, ".TABLE_PREFIX."forums f WHERE p2.id = t.last_post_id AND t.id = p.topic_id AND f.id = t.forum_id AND ".join(' AND ', $query_where_parts)." ORDER BY ".$sort_items[$_REQUEST['sort_by']]." ".$_REQUEST['order']." LIMIT ".$functions->get_config('search_limit_results'));

and replace the m with u:

$result = $db->query("SELECT ".$query_select." FROM ".TABLE_PREFIX."posts p LEFT JOIN ".TABLE_PREFIX."members u ON p.poster_id = u.id, ".TABLE_PREFIX."posts p2, ".TABLE_PREFIX."topics t, ".TABLE_PREFIX."forums f WHERE p2.id = t.last_post_id AND t.id = p.topic_id AND f.id = t.forum_id AND ".join(' AND ', $query_where_parts)." ORDER BY ".$sort_items[$_REQUEST['sort_by']]." ".$_REQUEST['order']." LIMIT ".$functions->get_config('search_limit_results'));

Don't forget to backup your search.php...

Regards,
Ingo

Thanks IngoB.

Not changing just yet.

Was still getting error with your fix IngoB.

Had to make another change, lines 216 and 222, from...

$author = preg_replace('#\s+#', ' ', $_REQUEST['author']);
$guest_search = ( !empty($_REQUEST['include_guests']) ) ? " OR p.poster_guest = '".$author."'" : '';
$query_where_parts[] = "( m.displayed_name = '".$author."'".$guest_search." )";

} else {

$author = preg_replace(array('#%#', '#_#', '#\s+#'), array('\%', '\_', ' '), $_REQUEST['author']);
$guest_search = ( !empty($_REQUEST['include_guests']) ) ? " OR p.poster_guest LIKE '%".$author."%'" : '';
$query_where_parts[] = "( m.displayed_name LIKE '%".$author."%'".$guest_search." )";

To...

$author = preg_replace('#\s+#', ' ', $_REQUEST['author']);
$guest_search = ( !empty($_REQUEST['include_guests']) ) ? " OR p.poster_guest = '".$author."'" : '';
$query_where_parts[] = "( u.displayed_name = '".$author."'".$guest_search." )";

} else {

$author = preg_replace(array('#%#', '#_#', '#\s+#'), array('\%', '\_', ' '), $_REQUEST['author']);
$guest_search = ( !empty($_REQUEST['include_guests']) ) ? " OR p.poster_guest LIKE '%".$author."%'" : '';
$query_where_parts[] = "( u.displayed_name LIKE '%".$author."%'".$guest_search." )";

Fixed the error here, hope its correct way of doing this.

Fixed in CVS. Thanks for reporting.