Search Feature with PHP and MySQL

Views 637
Sweets 4
Images 1

2 years ago Topic
Zotti
Member
Total Posts: 3
Joined: April 23, 2016
Profile
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
2 years ago #1
DaVaR
Administrator
Total Posts: 109
Joined: July 14, 2015
Profile
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
MmMmmm... Apple Pie!
Reply By: Zotti
2 years ago #2
Zotti
Member
Total Posts: 3
Joined: April 23, 2016
Profile
Posted: April 23, 2016 @ 11:41 AM
I'll try and reply to you :D !!
Reply By: DaVaR
2 years ago #3
DaVaR
Administrator
Total Posts: 109
Joined: July 14, 2015
Profile
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
MmMmmm... Apple Pie!
Reply By: DaVaR
2 years ago #4
DaVaR
Administrator
Total Posts: 109
Joined: July 14, 2015
Profile
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
MmMmmm... Apple Pie!
Reply By: DaVaR
2 years ago #5
DaVaR
Administrator
Total Posts: 109
Joined: July 14, 2015
Profile
Posted: April 23, 2016 @ 11:53 AM
You can download the Forum Plugin Here and poke around a little as well.
Signature
MmMmmm... Apple Pie!
Reply By: Zotti
2 years ago #6
Zotti
Member
Total Posts: 3
Joined: April 23, 2016
Profile
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