It is currently 25 Feb 2018, 07:17

Anything Goesphpbb Forum: How I added Image Search feature to the forum (Showcase | Slideshow)

Anything, almost anything at all
(even advertisement if it's decent, spammers will be banned)
Post Reply
User avatar
tim

Administrator
View: Showcase | Slideshow
Topic Posts: 1/2
Posts: 2195
Joined: 26 Sep 2016, 00:16
Cash on hand: 8,278.81 GLP
Bank: 53,924.25 GLP
Gender: Guy
Location: Victoria, BC
Website: https://gimplearn.net
Operating System: Windows
GIMP Version: 2.8.22
My Personal Blog
My FineArtAmerica
x 1219
x 264
Contact:
Canada

phpbb Forum: How I added Image Search feature to the forum

Post#1 by tim » 06 Oct 2017, 16:52


Phpbb forum allows searches to be done on post subject/title and/or post message text,
but I couldn't find anything (extensions) to allow Search to be done on real filenames of image attachments.

Many of my members on the forum use very descriptive words to name their attachment files when they upload.
I thought it would be a friendly feature to be able to search through these image filenames (no matter how basic).

So below is how i added this Image Search feature. (You can try out the live Image Search feature by clicking on Image Search and Image Searches links at bottom of the page (any page) on this forum).


DATABASE:
-----------------------
First I added a mysql table called named 'ikeywords' with colums: words (VARCHAR 255), count (INT), found (INT).
words column would contain search phrase (keywords) that have been performed on my forum.
count would contain how many times this search has been performed on forum.
and found would contain how many results have been found for those words.

CODE: (Note, I have the 'post love' extension installed on my phpbb forum so if you don't use 'post love' extension or plan not to use it, you'll have to change the sql query and some of the code to reflect that change.)
---------------------
Files to add.
/isearch.php

Code: Select all

<?php
/**
*
* This file is part of the phpBB Forum Software package.
*
* @copyright (c) phpBB Limited <https://www.phpbb.com>
* @license GNU General Public License, version 2 (GPL-2.0)
*
* For full copyright and license information, please see
* the docs/CREDITS.txt file.
*
*/

/**
*/

/**
* @ignore
*/
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);
include($phpbb_root_path . 'includes/functions_display.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup();

page_header('Image Search');

$template->set_filenames(array(
    'body' => 'isearch.html',
));


make_jumpbox(append_sid("{$phpbb_root_path}viewforum.$phpEx"));
page_footer();
?>
/styles/prosilver/template/isearch.html

Code: Select all

<!-- INCLUDE overall_header.html -->
<p class="{S_CONTENT_FLOW_END} responsive-center time<!-- IF S_USER_LOGGED_IN --> rightside<!-- ENDIF -->"><!-- IF S_USER_LOGGED_IN -->{LAST_VISIT_DATE}<!-- ELSE -->{CURRENT_TIME}<!-- ENDIF --></p><!-- IF S_USER_LOGGED_IN --><p class="responsive-center time">{CURRENT_TIME}</p><!-- ENDIF -->
<h2>Image Search</h2>

<div class="panel">
   <div class="inner"><span class="corners-top"><span></span></span>

   <div class="content">
      <p>
         Here we can do Image Searches on GL Forum.
      </p>
   </div>
   <!-- INCLUDEPHP isearch_body.php -->
   <span class="corners-bottom"><span></span></span></div>
</div>

<!-- INCLUDE jumpbox.html -->
<!-- INCLUDE overall_footer.html -->
/styles/prosilver/template/isearch_body.php

Code: Select all

<div style="font-size:12px">
<form action="/imagesearch.php" method="GET">
  GL Image Search:<input type="text" name="q" value="apple" size=50> <input type="submit" value="Search">
</form> 
</div>
/imagesearch.php

Code: Select all

<?php
/**
*
* This file is part of the phpBB Forum Software package.
*
* @copyright (c) phpBB Limited <https://www.phpbb.com>
* @license GNU General Public License, version 2 (GPL-2.0)
*
* For full copyright and license information, please see
* the docs/CREDITS.txt file.
*
*/

/**
*/

/**
* @ignore
*/
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);
include($phpbb_root_path . 'includes/functions_display.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup();

// condition title hack starts
$q = request_var('q',"");
//$view = request_var('view',"70");
page_header("Image Search results: " . $q);

// condition title hack ends
$template->set_filenames(array(
    'body' => 'imagesearch.html',
));


make_jumpbox(append_sid("{$phpbb_root_path}viewforum.$phpEx"));
page_footer();
?>
/styles/prosilver/template/imagesearch.html

Code: Select all

<!-- INCLUDE overall_header.html -->
<p class="{S_CONTENT_FLOW_END} responsive-center time<!-- IF S_USER_LOGGED_IN --> rightside<!-- ENDIF -->"><!-- IF S_USER_LOGGED_IN -->{LAST_VISIT_DATE}<!-- ELSE -->{CURRENT_TIME}<!-- ENDIF --></p><!-- IF S_USER_LOGGED_IN --><p class="responsive-center time">{CURRENT_TIME}</p><!-- ENDIF -->
<h2>Image Search Results</h2>

<div class="panel">
   <div class="inner"><span class="corners-top"><span></span></span>

   <div class="content">
      <p>
         Here we have image search results.
      </p>
   </div>
   <!-- INCLUDEPHP imagesearch_body.php -->
   <span class="corners-bottom"><span></span></span></div>
</div>

<!-- INCLUDE jumpbox.html -->
<!-- INCLUDE overall_footer.html -->
/styles/prosilver/template/imagesearch_body.php

Code: Select all

<div style="font-size:12px">

<?php
class Paginator {
        private $_conn;
        private $_limit;
        private $_page;
        private $_query;
        private $_total;
	 public function __construct( $conn, $query ) {
		 
		$this->_conn = $conn;
		$this->_query = $query;
	 
		$rs= $this->_conn->query( $this->_query );
		$this->_total = $rs->num_rows;
		 
	}
	public function getData( $limit = 10, $page = 1 ) {
		 
		if ($page < 1){
			$page = 1;
		}
		if ($page > $this->_total/$limit){
			$page = intval(ceil($this->_total/$limit));
		}
		$this->_limit   = $limit;
		$this->_page    = $page;
	 
		if ( $this->_limit == 'all' ) {
			$query      = $this->_query;
		} else {
			$query      = $this->_query . " LIMIT " . ( ( $this->_page - 1 ) * $this->_limit ) . ", $this->_limit";
		}
		$rs             = $this->_conn->query( $query );
	    if ($rs){
			while ( $row = $rs->fetch_assoc() ) {
				$results[]  = $row;
			}
	    }
		$result         = new stdClass();
		$result->page   = $this->_page;
		$result->limit  = $this->_limit;
		$result->total  = $this->_total;
		$result->data   = $results;
	 
		return $result;
	}
	
	public function createLinks($url, $links, $list_class ) {
		if ( $this->_limit == 'all' ) {
			return '';
		}
		$last       = ceil( $this->_total / $this->_limit );
	 
		$start      = ( ( $this->_page - $links ) > 0 ) ? $this->_page - $links : 1;
		$end        = ( ( $this->_page + $links ) < $last ) ? $this->_page + $links : $last;
	 
		$html       = '<table width=100%><tr><td><div class="pagination"><font size="1">'.$this->_total.' images</font> <ul>';
	 
		if ($this->_page == 1){
		}else{
			$html  .= '<li class="arrow previous"><a class="button button-icon-only" href="'.$url.'&limit=' . $this->_limit . '&page=' . ( $this->_page - 1 ) . '" rel="prev" role="button"><i class="icon fa-chevron-left fa-fw" aria-hidden="true"></i><span class="sr-only">Previous</span></a></li>';
		}
		if ( $start > 1 ) {
			$html .= '<li><a class="button" href="'.$url.'&limit=' . $this->_limit . '&page=1" role="button">1</a></li>';
			$html   .= '<li class="ellipsis" role="separator"><span>…</span></li>';
		}
	 
		for ( $i = $start ; $i <= $end; $i++ ) {
			if ($this->_page == $i) {
				$html .= '<li class="active"><span>'. $i . '</span></li>';
			}else{
				$html .= '<li><a class="button" href="'.$url.'&limit=' . $this->_limit . '&page=' . $i . '" role="button">'.$i .'</a></li>';
			
			}
		}
	 
		if ( $end < $last ) {
			$html   .= '<li class="ellipsis" role="separator"><span>…</span></li>';
			$html .= '<li><a class="button" href="'.$url.'&limit=' . $this->_limit . '&page=' . $last . '" role="button">'.$last .'</a></li>';
			
		}
	 
		if ($this->_page == $last){
		}else{
			$html .= '<li class="arrow next"><a class="button button-icon-only" href="'.$url.'&limit=' . $this->_limit . '&page=' . ( $this->_page + 1 ) . '" rel="next" role="button"><i class="icon fa-chevron-right fa-fw" aria-hidden="true"></i><span class="sr-only">Next</span></a></li>';
		}
		$html       .= '</ul></div></td><tr></table>';
	 
		return $html;
	}
}

$q = request_var('q',"");

print '<form action="/imagesearch.php" method="GET">';
  print 'GL Image Search:<input type="text" name="q" value="'.$q.'" size=50> <input type="submit" value="Search">';
print '</form>';
print '<br/>';
$url = "?q=" . $q;
// HACK to save keywords that were searched for. =================================
$hackkeywords   = $q;
$conn = new PDO("mysql:host=localhost;dbname=TO_BE_CHANGED",'TO_BE_CHANGED','TO_BE_CHANGED');
$query = 'INSERT INTO ikeywords(words,count) SELECT :keywords,0 FROM (SELECT 1)T WHERE NOT EXISTS (SELECT 1 FROM ikeywords WHERE words = :keywords)';
$sth = $conn->prepare($query);
$sth->execute(array(':keywords' => $hackkeywords));
$query = 'UPDATE ikeywords SET count = count+1 WHERE words = :keywords';
$sth = $conn->prepare($query);
$sth->execute(array(':keywords' => $hackkeywords));
$sth = null;
$conn = null;
// HACK ends


print 'Image Search Results for:<b>'.$q.'</b><br/>';
$q = str_replace(array(':', '\\', '/', '*', '%', ';','<','>','!','=','\'','"','?'), ' ', $q);
$q = split(" ", $q);
$sizeOfQ = sizeof($q);
$qcondition = "";
for ($i = 0;$i < $sizeOfQ; $i++){
	$qcondition .= " AND a.real_filename COLLATE UTF8_GENERAL_CI LIKE '%".$q[$i]."%'";
	$qcondition_order .= "AND CONCAT(' ',a.real_filename,' ') COLLATE UTF8_GENERAL_CI RLIKE '[^a-z]".$q[$i]."[^a-z]'";
}
$qcondition_order = "(1=1 ".$qcondition_order . ")";
$query = "SELECT a.attach_id as file_id,a.post_msg_id as post_id,a.topic_id,u.username,a.real_filename as filename,u.user_id,IFNULL(L.likes,0) as likes
			 FROM phpbbsf_attachments a INNER JOIN phpbbsf_users u ON a.poster_id = u.user_id 
			 LEFT JOIN (SELECT count(1) as likes, post_id FROM phpbbsf_posts_likes
			 GROUP BY post_id) L ON L.post_id = a.post_msg_id WHERE a.mimetype IN ('image/png','image/jpg','image/gif','image/jpeg')
			 AND a.is_orphan = 0 " . $qcondition . " ORDER BY ".$qcondition_order. " DESC, a.post_msg_id DESC";
		
$conn       = new mysqli( 'localhost', 'TO_BE_CHANGED','TO_BE_CHANGED', 'TO_BE_CHANGED' );
 
$limit      = request_var('limit',"35");
$page       = request_var('page',"1");
$links      = request_var('links',"7");

$Paginator  = new Paginator( $conn, $query );

$results    = $Paginator->getData( $limit, $page );
// HACK to save keywords that were searched for. =================================
$conn = new PDO("mysql:host=localhost;dbname=TO_BE_CHANGED",'TO_BE_CHANGED','TO_BE_CHANGED');
$query = 'UPDATE ikeywords SET found = :found WHERE words = :keywords';
$sth = $conn->prepare($query);
$sth->execute(array(':keywords' => $hackkeywords,':found' => $results->total));
$sth = null;
$conn = null;
// HACK ends

print $switchview . '<br/>';
print "<center>";

print $Paginator->createLinks( $url , $links, 'pagination pagination-sm' );
print "</center>";

if (count( $results->data )>0){
	//print count($results->data);
	print "<center><table><tr>";
	for( $i = 0; $i < count( $results->data ); $i++ ){
		$results->data[$i]['Name'];
		$postid = $results->data[$i]["post_id"];
		$fileid = $results->data[$i]["file_id"];
		$username = $results->data[$i]["username"];
		$filename = $results->data[$i]["filename"];
		$userid = $results->data[$i]["user_id"];
		$topicid = $results->data[$i]["topic_id"];
		$likes = $results->data[$i]["likes"];
		$place = $i + 1;
		$imageURL = '/download/file.php?id='.$fileid.'&t=1';
		$errorURL = '/download/file.php?id='.$fileid;
		//<img src="foo.jpg" onerror="if (this.src != 'error.jpg') this.src = 'error.jpg';">
		$img_string = '<img style="max-height:100px;max-width:150px;height:auto;width:auto;" id="img_auto_resize" src="'.$imageURL. '" onerror="this.src=\''.$errorURL.'\';" />';
			//$img_string = '<img style="height:100px;max-width:150px;width: expression(this.width > 150 ? 150: true);" id="img_DocPreview" src="/download/file.php?id='.$fileid.'" />';
			//$img_string = '<img src="/download/file.php?id='.$fileid.'&t=1" height="100" width="150">';
			print '<td><table height=100px width=152px><tr><td valign="middle"><center><a href="/viewtopic.php?t='.$topicid.'&p='.$postid.'#p'.$postid.'" title="'.$filename.' ('.$likes.' likes)">'.$img_string."</a></center></td></tr></table><table width=152px><tr><td style='width:152px;max-width:152px;white-space:nowrap;overflow:hidden;'><center>".$place.': <a href="/memberlist.php?mode=viewprofile&u='.$userid.'">'.$username."</a></center></td></tr></table></td>";
			if ($place % 7 == 0){
				print "</tr><tr>";
			}
	}
	print "</tr></table></center>";
}else{
	print 'No image found for search!';
}
?>
<center>
<?php print $Paginator->createLinks( $url,$links, 'pagination pagination-sm' ); ?> 
</center>
</div>
/isearches.php

Code: Select all

<?php
/**
*
* This file is part of the phpBB Forum Software package.
*
* @copyright (c) phpBB Limited <https://www.phpbb.com>
* @license GNU General Public License, version 2 (GPL-2.0)
*
* For full copyright and license information, please see
* the docs/CREDITS.txt file.
*
*/

/**
*/

/**
* @ignore
*/
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);
include($phpbb_root_path . 'includes/functions_display.' . $phpEx);

// Start session management
$user->session_begin();
$auth->acl($user->data);
$user->setup();

page_header('Image Searches');

$template->set_filenames(array(
    'body' => 'isearches.html',
));


make_jumpbox(append_sid("{$phpbb_root_path}viewforum.$phpEx"));
page_footer();
?>
/styles/prosilver/template/isearches.html

Code: Select all

<!-- INCLUDE overall_header.html -->
<p class="{S_CONTENT_FLOW_END} responsive-center time<!-- IF S_USER_LOGGED_IN --> rightside<!-- ENDIF -->"><!-- IF S_USER_LOGGED_IN -->{LAST_VISIT_DATE}<!-- ELSE -->{CURRENT_TIME}<!-- ENDIF --></p><!-- IF S_USER_LOGGED_IN --><p class="responsive-center time">{CURRENT_TIME}</p><!-- ENDIF -->
<h2>Image Searches done</h2>

<div class="panel">
   <div class="inner"><span class="corners-top"><span></span></span>

   <div class="content">
      <p>
         Here we have Image Searches done on forum. (Since Oct 4, 2017)
      </p>
   </div>
   <!-- INCLUDEPHP isearches_body.php -->
   <span class="corners-bottom"><span></span></span></div>
</div>

<!-- INCLUDE jumpbox.html -->
<!-- INCLUDE overall_footer.html -->
/styles/prosilver/template/isearches_body.html

Code: Select all

<div style="font-size:12px">
<?php
$all = request_var('view', "");
//connect to your database
mysql_connect('localhost','TO_BE_CHANGED','TO_BE_CHANGED'); //(host, username, password)

//specify database
mysql_select_db('TO_BE_CHANGED') or die("Unable to select database"); //select which database we're using
$query = "
SELECT words, count, found FROM ikeywords ORDER BY count DESC, found DESC ";
if ($all == "all"){
}else{
	$query .= "limit 100";
}
 $result = mysql_query($query) or die("Couldn't execute query");
 $numrows=mysql_num_rows($result);
  

if ($numrows > 0) { 
// now you can display the results returned
    $place = 0;
	print "<table>";
	print "<tr><td>#</td><td>&nbsp;<b>Search keywords</b>&nbsp;</td><td>&nbsp;<b>Searched Times</b>&nbsp;</td><td>&nbsp;<b>Search Results</b>&nbsp;</td></tr>";
	while ($row=mysql_fetch_array($result)) {
	  //$filename = $row["physical_filename"];
		$words = $row["words"];
		$count = $row["count"];
		$found = $row["found"];
		if (($place % 2 == 1)){
			$bgcolor = 'bgcolor="#E0E0E0" ';
		}else{
			$bgcolor = "";
		}
		if ($found == 0){
			$bgcolor = 'bgcolor="#f3bfcc" ';
		}
		//$topicviews = $row["topic_views"];
		//$percent = $row["percent"];
		//$percent = number_format((float)$percent * 100, 2, '.', '');
		$place += 1;
		$shortened = (strlen($words) > 23) ? substr($words,0,20).'...' : $words;
		print "<tr><td ".$bgcolor."> ".$place." - </td><td ".$bgcolor."><a href='https://gimplearn.net/imagesearch.php?q=".urlencode($words)."' title='Image Search for \"".$words."\" on Gimp Learn Forum'>".$shortened."</a></td><td ".$bgcolor.">".$count."</td><td ".$bgcolor.">".$found."</td>
		</tr>";
	  } 
	print "</table>";
  }
  
if ($all == "all") {} else {
	print '<br/><a href="/isearches.php?view=all">View all(if more than 100 results)</a>';
}
?>
</div>
EDIT:
-------------------------
Go back to /styles/prosilver/template/isearches_body.php and /styles/prosilver/template/imagesearch_body.php, find occurences of "TO_BE_CHANGED" and replace your database details.
Change ocrrences of gimplearn.net to your own site links.
------------------------
That's it.
Now you have /isearch.php which will show you a form that user can submit an image search to.
Which then submits to /imagesearch_body.php (this page does all the work).
and /isearches.php just shows you searches that had been performed on your forum.
------------------------
Cheers.

NEW VERSION
----------------------
In this version we'll try to provide better search using in addition the mySQL FULLTEXT search.
---------
DB: to add ability to use FULLTEXT search on real_filename

Code: Select all

ALTER TABLE phpbbsf_attachments ADD FULLTEXT(real_filename);
CODE CHANGE: in imagesearch_body.php
find where the query is defined change it to below code

Code: Select all

$match = "MATCH (a.real_filename) AGAINST ('".join(" ",$q)."')";
$qcondition_order = "(1=1 ".$qcondition_order . ")";
$qcondition = "(1=1 ".$qcondition. ")";
// old query uses basic LIKE and RLIKE to match.
/*$query = "SELECT a.attach_id as file_id,a.post_msg_id as post_id,a.topic_id,u.username,a.real_filename as filename,u.user_id,IFNULL(L.likes,0) as likes
			 FROM phpbbsf_attachments a INNER JOIN phpbbsf_users u ON a.poster_id = u.user_id 
			 LEFT JOIN (SELECT count(1) as likes, post_id FROM phpbbsf_posts_likes
			 GROUP BY post_id) L ON L.post_id = a.post_msg_id WHERE a.mimetype IN ('image/png','image/jpg','image/gif','image/jpeg')
			 AND a.is_orphan = 0 " . $qcondition . " ORDER BY ".$qcondition_order. " DESC, a.post_msg_id DESC";
*/
// new query uses match against full text search
$query = "SELECT a.attach_id as file_id,a.post_msg_id as post_id,a.topic_id,u.username,a.real_filename as filename,u.user_id,IFNULL(L.likes,0) as likes,"
			.$match." as match_score
			 FROM phpbbsf_attachments a INNER JOIN phpbbsf_users u ON a.poster_id = u.user_id 
			 LEFT JOIN (SELECT count(1) as likes, post_id FROM phpbbsf_posts_likes
			 GROUP BY post_id) L ON L.post_id = a.post_msg_id WHERE a.mimetype IN ('image/png','image/jpg','image/gif','image/jpeg')
			 AND a.is_orphan = 0 AND (".$match. " OR  ".$qcondition.") ORDER BY match_score DESC,".$qcondition_order." DESC, a.post_msg_id DESC";
0 x



aka Tin
Image gimplearn.net                                              

Registered members enjoy ads-free content
User avatar
Pat625
Script Tester
View: Showcase | Slideshow
Topic Posts: 1/2
Posts: 2245
Joined: 28 Sep 2016, 03:35
Cash on hand: 13,378.90 GLP
Gender: Gal
Location: USA
Operating System: Windows
GIMP Version: 2.8.22
x 8
x 471
United States of America

Post#2 by Pat625 » 06 Oct 2017, 17:12


Sounds very detailed, Tin. Thank you for your efforts.
1 x




Post Reply

Last 20 Members Who Visited This Topic

User avatar tim (20), User avatar RJKD (1), User avatar Pat625 (2)

  • Similar Topics
    Replies
    Views
    Last post
• Featuring Members' Works (Image Attachments Only) •
1: Conbagui
2: dinasset
3: Conbagui
4: oldmangrumpy
5: animicule
6: tim
7: gnerd

Who is online

Users browsing this forum: No registered users and 1 guest