The Web Design Group

... Making the Web accessible to all.

Welcome Guest ( Log In | Register )

 
Reply to this topicStart new topic
> I have a mySQL database with 600+ rows, and I seek a script to let visitors to my site type search terms into a box and search through the databse
JRBobDobbs
post Jun 20 2010, 07:21 PM
Post #1


Member
***

Group: Members
Posts: 41
Joined: 2-November 09
Member No.: 10,212



Hello.

As the title says I have a mySQL database. I would like to put a search box on my website pages so that users can type one or more words in and find corresponding entries in my database.

I have spent hours on Google searching things like "mySQL search", "mySQL fulltext search", "mySQL search engine", etc, but I have had no luck. Three times I tried to implement a script but it did not function correctly. One time there was a tutorial article that looked promising but at the end after inspecting the various user comments the last person said there were major security breaches and the database would get hacked (I think "injection" was the term). All the other things I found on Google either it was not what I was looking for or I could not understand it well enough to implement the script (or I could not understand it well enough to determine if it was or wasn't what I was looking for tongue.gif).

So I'm just looking for a reeeeally simple script here, which I think should be extremely easily availabe! Except it is not so easily available, apparently... one thing I like about posting on a forum like this, is that if one poster gives me a not-so-good response, others will correct him, so overall I know I won't be wasting my time, I know y'all can give me the right solution (and that does not leave me too vulnerable to security threats)!

From what I have learnt in my search so far, all I would need to do to the script is obviously edit in my database name, username, password etc, and also specify which table and columns to search (I would only search a single column in my table). So really if this is correct, what I seek to do is extremely easy, I only need to add a small handful of things!... I just need to find the correct script. I would also like to be able to display results on different pages (ex: max ten results per page) and all the other basic stuff like allow the user to specify with quotation marks their search terms or use a + sign to specify more than one search term, etc. I do not need the search terms to be highlighted in the results, but if that's already in your script then it's no problem I will take it.

I hope some of you would know good websites to refer me to, that would contain the script I'm looking for! I hope it's as simple as I have in my mind, and that I can accomplish this pretty easily with all your help.

Thanks in advance for any assistance, it is so much appreciated! smile.gif

PS - I think there are programs I could download to my server that would allow me to search, but this is too complicated for me, and I'm going to change servers sometimes so I would rather just have a script I can easily copy from place to place, and that it doesn't have all the bells and whistles is better for me because they would just complicate my life.

PPS - Please if you can indicate where I put your script on my page, for example does it go before <html> or does it go between <body> and </body> or does some go one place and other parts go another place, etc.. sometimes this has caused me confusion, so thank you for clarifying.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Jun 20 2010, 11:46 PM
Post #2


Jocular coder
********

Group: Members
Posts: 2,460
Joined: 31-August 06
Member No.: 43



The short answer is: No, there is not going to be a "script" you paste into your page which magically produces exactly what you wanted.

Your server needs to support some programming language (most likely PHP), and the mysql server. Then you need to write or have written a bit of program to read the fields you want under the conditions you want, and write them to the output page in the format you want.

... OK, second answer. The above isn't really true. You _ought_ to be able to find a script which includes the standard convenient functionality like dividing the results into blocks of ten, and so on. But the problem is: you have to find the script, and you have to tell whether the script is properly written to protect against obvious sql injection attacks. And the only way you can tell is by being expert enough to write your own. By which time it's quicker to write your own than trawl through various online offerings spotting the flaws in them.

For a start, can you tell us the table structure of the mysql database you say you have?

User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
JRBobDobbs
post Jun 21 2010, 04:50 PM
Post #3


Member
***

Group: Members
Posts: 41
Joined: 2-November 09
Member No.: 10,212



QUOTE(Brian Chandler @ Jun 21 2010, 12:46 AM) *

The short answer is: No, there is not going to be a "script" you paste into your page which magically produces exactly what you wanted.

Your server needs to support some programming language (most likely PHP), and the mysql server. Then you need to write or have written a bit of program to read the fields you want under the conditions you want, and write them to the output page in the format you want.

... OK, second answer. The above isn't really true. You _ought_ to be able to find a script which includes the standard convenient functionality like dividing the results into blocks of ten, and so on. But the problem is: you have to find the script, and you have to tell whether the script is properly written to protect against obvious sql injection attacks. And the only way you can tell is by being expert enough to write your own. By which time it's quicker to write your own than trawl through various online offerings spotting the flaws in them.

For a start, can you tell us the table structure of the mysql database you say you have?


Thanks for the reply. Wow, I'm really surprised there's no quick solution! I thought all mySQL databases were at their core the same: you have a certain number of columns/fields, and then you can add entries into your archive which become rows. I think you can also do other stuff, make different elements correspond with each other in certain ways, but that's extra. So I was just looking for a script dealing with the base stuff, columns and rows.

I have no idea what the structure of my mySQL database is...

Is an SQL injection attack very serious? I only update my files now and then and I always back it up afterwards. Maybe I should just keep trying to find a script through Google, I came pretty close sometimes but there were things that would not work, like when I clicked on "next page" it would not display the next results. The fact that I had at least some success, by the way, indicates to me that my server does support the necessary programming language, but don't quote me on that.

I suppose I could use a Google custom search instead with AdSense, although I would be afraid it would get knocked off one day because they find one of the links features objectionable content.. maybe I can find something like Google custom search but without the restrictions...
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Darin McGrew
post Jun 21 2010, 08:10 PM
Post #4


WDG Member
********

Group: Root Admin
Posts: 8,365
Joined: 4-August 06
From: Mountain View, CA
Member No.: 3



QUOTE
Is an SQL injection attack very serious?
Decide for yourself:
http://en.wikipedia.org/wiki/SQL_injection...-world_examples
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
JRBobDobbs
post Jun 22 2010, 01:58 AM
Post #5


Member
***

Group: Members
Posts: 41
Joined: 2-November 09
Member No.: 10,212



QUOTE(Darin McGrew @ Jun 21 2010, 09:10 PM) *

QUOTE
Is an SQL injection attack very serious?
Decide for yourself:
http://en.wikipedia.org/wiki/SQL_injection...-world_examples


Aha, I think this is good news... my mySQL database consists merely of information all of which I openly display on my website, it contains nothing secret, there is no data of value to be stolen.. so I think I'm in the clear as far as injection attacks go? Hmmmm, unless it could let someone store a virus on my website to infect my visitors, that would definitely be no good....

This post has been edited by JRBobDobbs: Jun 22 2010, 02:04 AM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Jun 22 2010, 02:50 AM
Post #6


Jocular coder
********

Group: Members
Posts: 2,460
Joined: 31-August 06
Member No.: 43



Yes, in cases like this there is not very much to worry about...

But you still have to understand something about how your "SQL database" is structured. Can you use phpmyadmin or similar to look at it?
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
JRBobDobbs
post Jun 22 2010, 10:36 AM
Post #7


Member
***

Group: Members
Posts: 41
Joined: 2-November 09
Member No.: 10,212



QUOTE(Brian Chandler @ Jun 22 2010, 03:50 AM) *

But you still have to understand something about how your "SQL database" is structured. Can you use phpmyadmin or similar to look at it?


OK let's see. It says "Type: MyISAM", "Collation: latin1_swedish_ci".

This post has been edited by JRBobDobbs: Jun 22 2010, 10:38 AM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Jun 22 2010, 11:37 AM
Post #8


Jocular coder
********

Group: Members
Posts: 2,460
Joined: 31-August 06
Member No.: 43



QUOTE(JRBobDobbs @ Jun 23 2010, 12:36 AM) *

QUOTE(Brian Chandler @ Jun 22 2010, 03:50 AM) *

But you still have to understand something about how your "SQL database" is structured. Can you use phpmyadmin or similar to look at it?


OK let's see. It says "Type: MyISAM", "Collation: latin1_swedish_ci".


That's a start, though not the most interesting bit. What is the table name, and what is its structure? I mean, you say you "have a database", but it can hardly be something you inherited from a relative's attic -- presumably you made it? What are the content? Links to other pages? Or little segments of text? (Etc etc)
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
JRBobDobbs
post Jun 22 2010, 03:18 PM
Post #9


Member
***

Group: Members
Posts: 41
Joined: 2-November 09
Member No.: 10,212



QUOTE(Brian Chandler @ Jun 22 2010, 12:37 PM) *

QUOTE(JRBobDobbs @ Jun 23 2010, 12:36 AM) *

QUOTE(Brian Chandler @ Jun 22 2010, 03:50 AM) *

But you still have to understand something about how your "SQL database" is structured. Can you use phpmyadmin or similar to look at it?


OK let's see. It says "Type: MyISAM", "Collation: latin1_swedish_ci".


That's a start, though not the most interesting bit. What is the table name, and what is its structure? I mean, you say you "have a database", but it can hardly be something you inherited from a relative's attic -- presumably you made it? What are the content? Links to other pages? Or little segments of text? (Etc etc)

Ho, now I see what you're asking. The table's name is TMA, it has three columns: (1) the main text like this "<a href="http://www.almasryalyoum.com/en/news/ongoing-demonization-egyptian-metalheads">The ongoing demonization of Egyptian Metalheads</a> (news article, by Jano Charbel, 2010, Al-Masry Al-Youm website)", (2) just the title repeated again (to alphabetize the rows by title) like this "The ongoing demonization of Egyptian Metalheads", (3) and then its categories like this "law & order: repression; metal/Islam conflict; national/regional history & identity: Middle East;" which I use so that I can put all rows of a certain category on a certain page (ex: all rows dealing with "law & order: repression" category will be on the "Law & Order: Repression" page of my website). I currently have 622 rows. My site is a link archive, so all rows include a link.

This post has been edited by JRBobDobbs: Jun 22 2010, 03:25 PM
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Jun 23 2010, 12:48 PM
Post #10


Jocular coder
********

Group: Members
Posts: 2,460
Joined: 31-August 06
Member No.: 43



OK, so for a simple search function, assuming the database server is there and connected, it's not a big job to write an SQL request to get records, and some php to format the output as you want it. Perhaps you should use the MySQL full-text search function -- here's an article about it:

http://devzone.zend.com/article/1304

(I googled for [mysql full text search])

How are your keyword things organised? Exactly one keyword per entry? Are you using the database to generate your current website?

It also strikes me that if you only have <1000 entries in all, you hardly need to worry about paged output (which is a bit of a pain, unless you can find some prebuilt code to do it, which I fear is unlikely). Surely a page with 200 entries even, would be manageable?

HTH
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Frederiek
post Jun 24 2010, 01:49 AM
Post #11


Programming Fanatic
********

Group: Members
Posts: 5,146
Joined: 23-August 06
From: Europe
Member No.: 9



Nice article, Brian. Thank you.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
JRBobDobbs
post Jun 24 2010, 07:06 PM
Post #12


Member
***

Group: Members
Posts: 41
Joined: 2-November 09
Member No.: 10,212



QUOTE(Brian Chandler @ Jun 23 2010, 01:48 PM) *

OK, so for a simple search function, assuming the database server is there and connected, it's not a big job to write an SQL request to get records, and some php to format the output as you want it. Perhaps you should use the MySQL full-text search function -- here's an article about it:

http://devzone.zend.com/article/1304

(I googled for [mysql full text search])

How are your keyword things organised? Exactly one keyword per entry? Are you using the database to generate your current website?

It also strikes me that if you only have <1000 entries in all, you hardly need to worry about paged output (which is a bit of a pain, unless you can find some prebuilt code to do it, which I fear is unlikely). Surely a page with 200 entries even, would be manageable?

HTH


I must say that I am confused.

QUOTE

According to the MySQL manual, Full-text is a “natural language search”; it indexes words that appear to represent the row, using the columns you specified. As an example, if all your rows contain “MySQL” then “MySQL” won’t match much. It’s not terribly unique, and it would return too many results. However, if “MySQL” were present in only 5% of the rows, it would return those rows because it doesn’t appear too often to be known as a keyword that’s very common. (If you have “MySQL” in none of your rows, it’ll return nothing; duh.)

Ideally I do not want this. Rather I would want every single instance of a given search term to be retrieved and displayed.

Perhaps a boolean search would be what I need. (Using SELECT version(); code I determined I have above mySQL version 4.1, so a boolean search can work with my database.) However I have NO idea how to put a boolean search box on my pages. Forgive the lengthy quote, but the article says:

QUOTE

A Basic Boolean Searching Application
Again, we’ll start with the code straight off:

<?php
/* call this script "advs.php" */
if(!$c) {
?>
<form action="advs.php?c=1" method=POST>
<b>Find Results with: </b><br>
Any of these words: <input type="text" length=40 name="any"> <br>
All of these words: <input type="text" length=40 name="all"> <br>
None of these words: <input type="text" length=40 name="none"> <br>
<input type="submit" value="Search">
</form>
<?
} else if($c) {
MySQL_connect("hostname", "username", "password");
MySQL_select_db("database");
if((!$all) || ($all == "")) { $all = ""; } else { $all = "+(".$all.")"; }
if((!$any) || ($any == "")) { $any = ""; }
if((!$none) || ($none == "")) { $none = ""; } else { $none = "-(".$none.")"; }
$query = "
SELECT *,
MATCH(title, story) AGAINST ('$all $none $any' IN BOOLEAN MODE) AS score
FROM compsite
WHERE MATCH(title, story) AGAINST ('$all $none $any' IN BOOLEAN MODE)";
$artm1 = MySQL_query($query);
if(!$artm1) {
echo MySQL_error()."<br>$query<br>";
}
echo "<b>Article Matches</b><br>";
if(MySQL_num_rows($artm1) > 0) {
echo "<table>";
echo "<tr><td>Score </td><td>Title </td><td>Body</td></tr>";
while($artm2 = MySQL_fetch_array($artm1)) {
$val = round($artm2['score'], 3);
$val = $val*100;
echo "<tr><td>$val</td>";
echo "<td>{$artm2['title']}</td>";
echo "<td>{$artm2['body']}</td></tr>";
}
echo "</table>";
}
else {
echo "No Results were found in this category.<br>";
}
echo "<br>";
}


After we get the input from the form, $c Code Inlineis set to 1 and we start the real work.

First we check our input. If it's empty, we leave it empty, if it's not, we append the proper + or - to it. The parentheses are to allow for the user typing more than 1 word in a given field.

$query = "
SELECT *
MATCH(title, story) AGAINST ('$all $none $any' IN BOOLEAN MODE) AS score
FROM compsite
WHERE
MATCH(title, story) AGAINST ('$all $none $any' IN BOOLEAN MODE)";


That's the final query that we use. $all, $none Code Inline, and $any have already been prepared for the query, and they are inserted. Score is returned as a column to order them by (if we wanted to do that), and from there on, we just have to output the results.

if(MySQL_num_rows($artm1) > 0) {
echo "<table>";
echo "<tr><td>Score </td><td>Title </td><td>Body</td></tr>";
while($artm2 = MySQL_fetch_array($artm1)) {
$val = round($artm2['score'], 3);
$val = $val*100;
echo "<tr><td>$val</td>";
echo "<td>{$artm2['title']}</td>";
echo "<td>{$artm2['body']}</td></tr>";
}
echo "</table>";


That's the output code. If there's less than 1 row to output, we send a "no records found" message out.


I have come across this phenomenon many times in my quest for a search script. The author says "Again, we’ll start with the code straight off:" and then proceeds to display lots of code. But then the author continues saying something weird, in this case "After we get the input from the form, $c Code Inlineis set to 1 and we start the real work."... I thought he just gave me all the code? Why is there more code now? After everything is said and done, three separate sections of code are given, and I don't know how to combine these three sections in order to obtain a boolean search box on my page.

In my quest for a search script I have come across some instances of code put before <html></html> and then the rest of the code goes between <body> and </body>, so I'm thinking I have to separate this code in a similar way, but it's all just speculation on my part at this point. Ay karumba. Please have mercy on my poor soul, Mr. Chandler! Your continued assistance is VERY much appreciated...

PS - In the future I will be having over 1000 rows so I would need to divide the results up into separate pages at some point, but it's not an immediate need.
User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post
Brian Chandler
post Jun 25 2010, 08:25 AM
Post #13


Jocular coder
********

Group: Members
Posts: 2,460
Joined: 31-August 06
Member No.: 43



QUOTE(JRBobDobbs @ Jun 25 2010, 09:06 AM) *

QUOTE(Brian Chandler @ Jun 23 2010, 01:48 PM) *

OK, so for a simple search function, assuming the database server is there and connected, it's not a big job to write an SQL request to get records, and some php to format the output as you want it. Perhaps you should use the MySQL full-text search function -- here's an article about it:

http://devzone.zend.com/article/1304

(I googled for [mysql full text search])

How are your keyword things organised? Exactly one keyword per entry? Are you using the database to generate your current website?

It also strikes me that if you only have <1000 entries in all, you hardly need to worry about paged output (which is a bit of a pain, unless you can find some prebuilt code to do it, which I fear is unlikely). Surely a page with 200 entries even, would be manageable?

HTH


I must say that I am confused.



I don't think you should be confused (at this point). It would help if you answer my three questions, especially no. 3:

How are your keyword things organised? Exactly one keyword per entry? Are you using the database to generate your current website?


QUOTE

Ideally I do not want this [MySQL Full-text]. Rather I would want every single instance of a given search term to be retrieved and displayed.


Right; I don't know any details of the mysql full-text searching options. With a relatively small number of records the "boolean" method may be best.

QUOTE


Perhaps a boolean search would be what I need. (Using SELECT version(); code I determined I have above mySQL version 4.1, so a boolean search can work with my database.) However I have NO idea how to put a boolean search box on my pages. Forgive the lengthy quote, but the article says:

QUOTE

A Basic Boolean Searching Application
Again, we’ll start with the code straight off:

<snip>



I have come across this phenomenon many times in my quest for a search script. The author says "Again, we’ll start with the code straight off:" and then proceeds to display lots of code. But then the author continues saying something weird, in this case "After we get the input from the form, $c Code Inlineis set to 1 and we start the real work."... I thought he just gave me all the code? Why is there more code now? After everything is said and done, three separate sections of code are given, and I don't know how to combine these three sections in order to obtain a boolean search box on my page.



I'm afraid this is what I said originally. You won't in practice find a "plug-in" script that you can just use. IMO, the code in this example is not very good (lots of bits of total nonsense); and it is not documented properly at all. So it would be easier to write the bits using the mysql manual than try to get this working as it is. Here's the manual page:

http://dev.mysql.com/doc/refman/5.0/en/fulltext-boolean.html

You have to decide what format you want the search form in. You don't need to make it too complicated: perhaps just an ANDed search -- "find all these terms".

Do you understand how forms work? There is a basic trick to writing pages like this -- apparently backwards. (We do the output first, in response to the previous page displayed; the input after, ready for the *next* page.)

[Step 1]
Look for search inputs (POST variables); if present, run DB query and output results. Remember whether there was a search or not ($yes_we_produced_output = TRUE/FALSE)

[Step 2]
Output the search form. In the title use $yes_we_produced_output to decide whether to say "Do a search" or "Do another search".

QUOTE


In my quest for a search script I have come across some instances of code put before <html></html> and then the rest of the code goes between <body> and </body>, so I'm thinking I have to separate this code in a similar way, but it's all just speculation on my part at this point. Ay karumba. Please have mercy on my poor soul, Mr. Chandler! Your continued assistance is VERY much appreciated...


I sympathise with a bit of schizophrenia about php pages. Is it really a web page with bits of program peppered around the place? Or is it really a program that just happens to contain lots of bits of web page text? The answer to this is 42, obviously.

Generally bits of backend program can be anywhere in the file; but you need to organise it for readability, and it's generally better to be somewhere inside the HTML body, then if something just prints an error message it will appear (more or less) normally.

Anyway, it would help to understand if you already have pages for producing the other stuff on your website, including doing the database connection. Then you can just copy one of these to start with.

HTH


User is offlinePM
Go to the top of the page
Toggle Multi-post QuotingQuote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 



- Lo-Fi Version Time is now: 29th March 2024 - 02:20 AM