Author Topic: Rigging up a search function  (Read 1522 times)

Offline tfs

  • Quality Assurance
  • *
  • Posts: 973
    • View Profile
Rigging up a search function
« on: August 10, 2011, 10:12:54 am »
I'm finally going to go live with SimpleDesk after much preparation.  The next thing I need to do is to rig up some sort of rudimentary search capability.  I don't know what the future of SimpleDesk development will be, and I don't have the skill to develop it myself, so I'm looking for ideas that will get it done in another way.

I thought I'd execute a SQL query that returns the ticket ID number, date, Username, Subject and body, and I'd dump that to a TXT file.  Then I'd find some 3rd party TXT file search utility... something like this: http://www.sadmansoftware.com/search/

I could use that for searching the tickets on my local machine.  It would show me the ticket ID as the first column, which I could then use to jump to the ticket.

Hmmm... for that matter, I could maybe just dump it into Excel for the searching, which would preserve the columnar format.

A drawback is that the search won't find data newer than my last data dump, though I don't see that as critical if I create a reasonably simple way to refresh the local data.  Note that this isn't just for me but for the boss and the accountant in particular.  They're not particularly computer savvy, and so this needs to be simple to do.

Another drawback is that our customers won't be able to search their own tickets.  Only staff.

Anyway... if you've got ideas to do this in some other way, or if you know of a superior search utility for this, I'm anxious to hear what they are.

A good tree cannot bring forth evil fruit, neither can an evil tree bring forth good fruit.

Gruffen

  • Guest
Re: Rigging up a search function
« Reply #1 on: August 10, 2011, 12:20:39 pm »
Putting aside certain limitations, namely that bbcode will be searched in this manner, that   will also be a valid search target along with any other entities, a crude search method can be achieved as follows from phpMyAdmin on the relevant database (assuming smf_ is the prefix)

SELECT hdt.id_ticket, hdt.last_updated, hdtr.poster_time, hdtr.poster_name, hdt.subject, hdtr.body
FROM smf_helpdesk_tickets AS hdt
INNER JOIN smf_helpdesk_ticket_replies AS hdtr ON (hdtr.id_ticket = hdt.id_ticket)
WHERE hdtr.body LIKE '%search term%'

This does NOT perform any checks on ticket access. To achieve that you'd have to insert the query into SD's code and include the {query_see_ticket} clause into the WHERE (e.g. WHERE {query_see_ticket} AND ...)

It's also unholy inefficient but it's the quickest and dirtiest method of achieving it. A proper search facility would be far more intricate and complicated to build.

If you wanted to make it a little friendlier, you could achieve it with a fairly crude SSI based page to validate their access, and execute the above query.

Offline tfs

  • Quality Assurance
  • *
  • Posts: 973
    • View Profile
Re: Rigging up a search function
« Reply #2 on: August 10, 2011, 02:15:27 pm »
The clients will just have to not have search ability for now.  It's really the accountant who will need it in some capacity.  So I figured I'd setup her desktop with a copy of the DB that she can use to find the ticket ID's.

Maybe one of our other devs will decide to fiddle with a plugin to implement a simple search.  I wonder if there's any PHP classes around me at the community college level.  This would be useful for me to be able to do.
A good tree cannot bring forth evil fruit, neither can an evil tree bring forth good fruit.

Offline tfs

  • Quality Assurance
  • *
  • Posts: 973
    • View Profile
Re: Rigging up a search function
« Reply #3 on: August 10, 2011, 02:57:45 pm »
So I Googled for php/sql/search and found a ton of links with examples.  I tried to shoehorn Gruffen's query into one of them, substituting my database name and password, but when I run it and submit a query I get "Forbidden - You don't have permission to access /forum1/ on this server."

I'm running this on a wampserver.  Obviously the three fields don't match up, etc, but one step at a time.  :)  If I can get just a simple search form working I can place it in a password protected directory and work from there.

Code: [Select]
<h2>Search Form</h2>

<form name="search" method="post" action="<?=$PHP_SELF?>">
Seach for: <input type="text" name="find" /> in
<Select NAME="field">
<Option VALUE="fname">First Name</option>
<Option VALUE="lname">Last Name</option>
<Option VALUE="designation">Designation</option>
</Select>

<input type="hidden" name="searching" value="yes" />
<input type="submit" name="search" value="Search" />
</form>

<?php
if (
$searching =="yes"){
echo "<h3>Search Results</h3><p>";
if ($find == ""){
echo "<p>Please Enter a search term";
exit;
}

mysql_connect("localhost""root""") or die(mysql_error());
mysql_select_db("smf1") or die(mysql_error());
$find strtoupper($find);
$find strip_tags($find);
$find trim ($find);

$query mysql_query("SELECT hdt.id_ticket, hdt.last_updated, hdtr.poster_time, hdtr.poster_name, hdt.subject, hdtr.body
FROM smf_helpdesk_tickets AS hdt
INNER JOIN smf_helpdesk_ticket_replies AS hdtr ON (hdtr.id_ticket = hdt.id_ticket)
WHERE hdtr.body LIKE '%find%'"
);

// $query = mysql_query("SELECT * FROM user WHERE upper($field) LIKE'%$find%'");

while($result mysql_fetch_array($query)){
echo $result['fname'];
echo " ";
echo $result['lname'];
echo "<br>";
echo $result['designation'];
echo "<br>";
echo "<br>";
}

$matches=mysql_num_rows($query);
if ($matches == 0){
echo "Sorry, we can not find an entry to match your query<br><br>";
}

echo "<b>Searched For:</b> " .$find;
}
?>
A good tree cannot bring forth evil fruit, neither can an evil tree bring forth good fruit.

Gruffen

  • Guest
Re: Rigging up a search function
« Reply #4 on: August 10, 2011, 03:38:17 pm »
Where's the file in relation to /forum1/ and what are its permissions?

Offline tfs

  • Quality Assurance
  • *
  • Posts: 973
    • View Profile
Re: Rigging up a search function
« Reply #5 on: August 10, 2011, 05:56:23 pm »
It's in /forum1/, but I haven't figured out how to check its permissions yet.  I've always done this on the web before, where I can easily FTP and set permissions.  This LOCAL server has thwarted my attempts to FTP into it.  :)  Just a matter of me getting the time to figure that part out.  Just finishing up a 2.5 hour support call, and now need to call the next one.  :(
A good tree cannot bring forth evil fruit, neither can an evil tree bring forth good fruit.

Offline tfs

  • Quality Assurance
  • *
  • Posts: 973
    • View Profile
Re: Rigging up a search function
« Reply #6 on: August 10, 2011, 07:18:48 pm »
How do you FTP into a Wampserver?  The LOCALHOST home page shows FTP as a loaded extension, but I can't figure out where to configure users and paths.  I've got an FTP client installed locally, but can't get it to connect to localhost.

I suppose I could just install FileZilla server also, but I'm keen to figure out how it's done with Wampserver and the default FTP module. 
A good tree cannot bring forth evil fruit, neither can an evil tree bring forth good fruit.

Offline tfs

  • Quality Assurance
  • *
  • Posts: 973
    • View Profile
Re: Rigging up a search function
« Reply #7 on: August 10, 2011, 09:38:06 pm »
Well, I struck out with Wampserver and that script.  I ended up putting it on my WWW server and it doesn't return any errors, but problem is that it returns nothing.  Have carefully defined sql login info, but it appears to be dead.  :(
A good tree cannot bring forth evil fruit, neither can an evil tree bring forth good fruit.