Search Feature with PHP and MySQL
Views 1320
Sweets 4
Images 1

3 years ago Topic

Zotti
Offline
Member
Total Posts: 3
Joined: April 23, 2016
Posted: April 23, 2016 @ 11:03 AM
How to Create a Search Feature with PHP and MySQL or PDO in Nova Framework

Image Attachments

Reply By: DaVaR
3 years ago #1

DaVaR
Offline
Administrator
Total Posts: 141
Joined: July 14, 2015
Posted: April 23, 2016 @ 11:32 AM
Here is how I was able to get this working with the UAP Forums Plugin. Routes
Code
Router::any('SearchForum', 'AppModulesForumControllersForum@forumSearch');
Router::any('SearchForum/(:any)', 'AppModulesForumControllersForum@forumSearch');
Router::any('SearchForum/(:any)/(:num)', 'AppModulesForumControllersForum@forumSearch');
ControllerForum.php
Code
namespace AppModulesForumControllers;

use HelpersPaginator;

class Forum extends Controller{

	private $model;
	
	public function __construct(){
  		parent::__construct();
  		$this->model = new AppModulesForumModelsForum();
		$this->forum_topic_limit = "10";
		$this->forum_topic_reply_limit = "20";
		$this->pagesTopic = new HelpersPaginator($this->forum_topic_limit);
		$this->pagesReply = new HelpersPaginator($this->forum_topic_reply_limit);
	}
	
	/* Forum Search Function */
	public function forumSearch($search = null, $current_page = null){
		/** Check to see if user is logged in **/
		if($data['isLoggedIn'] = $this->auth->isLogged()){
			/** User is logged in - Get their data **/
			$u_id = $this->auth->user_info();
			$data['currentUserData'] = $this->user->getCurrentUserData($u_id);
			$data['isAdmin'] = $this->user->checkIsAdmin($u_id);
		}
		// Collect Data for view
		$data['title'] = "Search ".$this->forum_title;
		$data['welcome_message'] = $this->forum_description;
		// Display What user is searching for
		$data['search_text'] = urldecode($search);
		// Make sure search entry is not too short
		if(strlen($data['search_text']) > 2){
			// Ready the search words for database
			$search_db = str_replace(' ', '%', $data['search_text']);
			// Get data related to search
			$data['forum_topics'] = $this->model->forum_search($search_db, $this->pagesTopic->getLimit($current_page, $this->forum_topic_limit));
			// Set total number of messages for paginator
			$total_num_topics = count($this->model->forum_search($search_db));
			$this->pagesTopic->setTotal($total_num_topics);
			// Send page links to view
			$pageFormat = DIR."SearchForum/$search/$id/"; // URL page where pages are
			$data['pageLinks'] = $this->pagesTopic->pageLinks($pageFormat, null, $current_page);
			// Display How Many Results
			$data['results_count'] = $total_num_topics;
		}else{
			$data['error'] = "Search context is too small.  Please try again!";
			$data['results_count'] = 0;
		}
		// Get Recent Posts List for Sidebar
		$data['forum_recent_posts'] = $this->model->forum_recent_posts();
		// Setup Breadcrumbs
		$data['breadcrumbs'] = "
			Home
			".$this->forum_title."
		";
		$data['csrf_token'] = Csrf::makeToken('forum');
		// Send data to view
		View::renderTemplate('header', $data);
		View::renderModule('Forum/views/searchForum', $data);
		View::renderModule('Forum/views/forum_sidebar', $data);
		View::renderTemplate('footer', $data);
	}
}
ModelsForum.php
Code
namespace AppModulesForumModels;

use CoreModel;

class Forum extends Model {
	/**
	* forum_search
	*
	* get list of topics for given category
	*
	* @param int $search = Search String
	* @param string $limit data from Paginator class
	*
	* @return array returns forum topics list data
	*/
	public function forum_search($search, $limit = null){
	$data = $this->db->select("
		(SELECT
		  forum_id,
		  forum_post_id,
		  forum_timestamp as tstamp,
		  forum_content as content,
		  forum_title as title,
		  forum_user_id,
		  forum_status,
		  allow,
		  'main_post' AS post_type
		FROM
		  `".PREFIX."forum_posts`
		WHERE
		  (forum_content LIKE :search
		OR
		  forum_title LIKE :search)
		AND
		  allow = 'TRUE')
		UNION ALL
		(SELECT
		  fpr.fpr_id,
		  fpr.fpr_post_id,
		  fpr.fpr_timestamp as tstamp,
		  fpr.fpr_content as content,
		  fp.forum_title as title,
		  fpr.fpr_user_id as forum_user_id,
		  fp.forum_status as forum_status,
		  fpr.allow,
		  'reply_post' AS post_type
		FROM
		  `".PREFIX."forum_posts_replys` fpr
		LEFT JOIN
		  `".PREFIX."forum_posts` fp
		ON
		  fp.forum_post_id = fpr.fpr_post_id
		WHERE
		  fpr_content LIKE :search
		AND
		  fpr.allow = 'TRUE')
		ORDER BY tstamp DESC
		$limit
	",
	array(':search' => '%'.$search.'%'));
	return $data;
	}
}
ViewssearchForum.php
Code
/**
  * Forum Topics List View
  *
  * @author David "DaVaR" Sargent - davar@thedavar.net
  * @version 2.0
  * @date Jan 13, 2016
  * @date updated Jan 13, 2016
  */
  use Core\Language,
  Helpers\ErrorHelper,
  Helpers\SuccessHelper,
  Helpers\Form,
  Helpers\TimeDiff,
  Helpers\CurrentUserData,
  Helpers\PageViews,
  Helpers\Sweets,
  Helpers\BBCode,
  Helpers\Images;
  /* Hightlight Search Text Function */
  /**
  * Highlighting matching string
  * @param   string  $text           subject
  * @param   string  $words          search string
  * @return  string  highlighted text
  */
  function highlight_search_text($text, $words) {
  $keywords = implode('|',explode(' ',preg_quote($words)));
  //var_dump($keyword);
  $highlighted = preg_replace("/($keywords)/i","<mark>$0</mark>",$text);
  return $highlighted;
  }
  ?>
  <div class='col-lg-8 col-md-8'>
 <div class='panel panel-default'>
  <div class='panel-heading'>
  <h3 class='jumbotron-heading'><?php echo $data['title'] ?></h3>
  </div>
  <div class='panel-body'>
  <p><?php echo $data['welcome_message'] ?></p>
  <div class="text-center">
  Search found <?php echo $data['results_count']; ?> matches: <?php echo $data['search_text']; ?>
  </div><br>
 <?php
  if(empty($data['error'])){
  // Display Paginator Links
  // Check to see if there is more than one page
  if($data['pageLinks'] > "1"){
  echo "<div class='panel panel-info'>";
  echo "<div class='panel-heading text-center'>";
  echo $data['pageLinks'];
  echo "</div>";
  echo "</div>";
  }
  ?>
 <?php
  // Setup form list table stuff
  echo "<div class='row'>";
  echo "<div class='col-lg-12 col-md-12 col-sm-12'>";
  foreach($data['forum_topics'] as $row2)
  {
  echo "<hr>";
  echo "<div class='panel panel-default'>";
  echo "<div class='panel-heading'>";
  echo "<h4>";
  $title = stripslashes($row2->title);
  $title_output = highlight_search_text($title, $data['search_text']);
  if($row2->post_type == "reply_post"){ echo "Reply to: "; }
  echo "<a href='".DIR."Topic/$row2->forum_post_id/' title='$title' ALT='$title'>$title_output</a>";
  echo "</h4>";
  echo "</div>";
  echo "<div class='panel-body'>";
  echo "<div class='row'>";
  echo "<div class='col-lg-12 col-md-12 col-sm-12'>";
  if(!empty($row2->content)){
  $bb_content = BBCode::getHtml($row2->content);
  $countent_output = highlight_search_text($bb_content, $data['search_text']);
  echo $countent_output;
  }
  echo "</div>";
  echo "</div>";
  echo "</div>";
  echo "<div class='panel-footer'>";
  echo "<div class='text small'>";
  $poster_username = CurrentUserData::getUserName($row2->forum_user_id);
  echo " Posted by <a href='".DIR."Profile/$row2->forum_user_id' style='font-weight: bold'>$poster_username</a> - ";
  //Display how long ago this was posted
  $timestart = $row2->tstamp;  //Time of post
  echo " " . TimeDiff::dateDiff("now", "$timestart", 1) . " ago ";
  // Display Locked Message if Topic has been locked by admin
  if($row2->forum_status == 2){
  echo " <strong><font color='red'>Topic Locked</font></strong> ";
  }
  echo "</div>";
  echo "</div>";
  echo "</div>";
  } // End query
  echo "</div>";
  echo "</div>";
  // Display Paginator Links
  // Check to see if there is more than one page
  if($data['pageLinks'] > "1"){
  echo "<div class='panel panel-info'>";
  echo "<div class='panel-heading text-center'>";
  echo $data['pageLinks'];
  echo "</div>";
  echo "</div>";
  }
  }
  ?>
  </div>
  </div>
  </div>


Signature UAP v4.3.0 is Here!!!

Please keep questions and comments in the forum.

Reply By: Zotti
3 years ago #2

Zotti
Offline
Member
Total Posts: 3
Joined: April 23, 2016
Posted: April 23, 2016 @ 11:41 AM
I'll try and reply to you :D !!


Reply By: DaVaR
3 years ago #3

DaVaR
Offline
Administrator
Total Posts: 141
Joined: July 14, 2015
Posted: April 23, 2016 @ 11:46 AM
I am sure you will have to change some things around to fit your needs. At least this will get you going in the right direction.

Signature UAP v4.3.0 is Here!!!

Please keep questions and comments in the forum.

Reply By: DaVaR
3 years ago #4

DaVaR
Offline
Administrator
Total Posts: 141
Joined: July 14, 2015
Posted: April 23, 2016 @ 11:50 AM
Also I used Union All to allow search of both Topics and Topic Replies. Here is the database structure for both tables...
Code
-- --------------------------------------------------------

--
-- Table structure for table `uap4main_forum_posts`
--

CREATE TABLE IF NOT EXISTS `uap4main_forum_posts` (
  `forum_post_id` int(20) NOT NULL AUTO_INCREMENT,
  `forum_id` int(20) NOT NULL,
  `forum_user_id` int(20) NOT NULL,
  `forum_title` varchar(255) NOT NULL,
  `forum_content` text NOT NULL,
  `forum_edit_date` varchar(20) DEFAULT NULL,
  `forum_status` int(11) NOT NULL DEFAULT '1',
  `subscribe_email` varchar(10) NOT NULL DEFAULT 'true',
  `allow` varchar(11) NOT NULL DEFAULT 'TRUE',
  `hide_reason` varchar(255) NOT NULL,
  `hide_userID` int(11) NOT NULL,
  `hide_timestamp` datetime NOT NULL,
  `forum_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`forum_post_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- --------------------------------------------------------

--
-- Table structure for table `uap4main_forum_posts_replys`
--

CREATE TABLE IF NOT EXISTS `uap4main_forum_posts_replys` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `fpr_post_id` int(20) NOT NULL,
  `fpr_id` int(20) NOT NULL,
  `fpr_user_id` int(20) NOT NULL,
  `fpr_title` varchar(255) NOT NULL,
  `fpr_content` text NOT NULL,
  `subscribe_email` varchar(10) NOT NULL DEFAULT 'true',
  `fpr_edit_date` varchar(20) DEFAULT NULL,
  `allow` varchar(11) NOT NULL DEFAULT 'TRUE',
  `hide_reason` varchar(255) NOT NULL,
  `hide_userID` int(11) NOT NULL,
  `hide_timestamp` datetime NOT NULL,
  `fpr_timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;


Signature UAP v4.3.0 is Here!!!

Please keep questions and comments in the forum.

Reply By: DaVaR
3 years ago #5

DaVaR
Offline
Administrator
Total Posts: 141
Joined: July 14, 2015
Posted: April 23, 2016 @ 11:53 AM
You can download the Forum Plugin Here and poke around a little as well.

Signature UAP v4.3.0 is Here!!!

Please keep questions and comments in the forum.

Reply By: Zotti
3 years ago #6

Zotti
Offline
Member
Total Posts: 3
Joined: April 23, 2016
Posted: April 23, 2016 @ 11:53 AM
Yes, but no problem Thank you again ^__^ !!


Forum Permissions
You cannot post in this forum.
You cannot moderate this forum.
You cannot administrate this forum.
Search Forums
Forum Recent Posts