PHP search engine for a mySQL D.B.?

**Justin Sampson** · Jan 22, 2001 18:19

Hi,
I was wondering if anyone new where I could find a PHP search engine for a mySQL database.

It just has to be a simple thing that I can build onto. One thing I would like is to have Page 1 2 3 4 5 buttons at the bottom.

Thanks,
Justin Sampson

**freddydoesphp** · Jan 22, 2001 18:48

It would probably be easiest ot write one yourself: here is one that would search for exact matches this should do the trick, BTW I just wrote and didn't test it, but it should get you on your way.

<?
if ($searchwords) {
//Set this to the number of records per page
$limit = 10;

//Set the initial offset
if (!isset($offset)) $offset = 0;

$fieldstosearch = array("name", "description", "content");
for($i=0;$i<count($fieldstosearch);$i++) {
$clause .= sprintf("(%s LIKE '%%%s%%') OR ", $fieldstosearch[$i], urldecode($searchwords));
}
$clause = "(".substr($clause, 0, -4).")";

print $clause;

$sql = "SELECT COUNT(*) as totalcount WHERE $clause";
$countq = mysql_query($sql);
$totalcount = mysql_result($countq, 0);

$realsql = "SELECT * WHERE $clause LIMIT $offset, $limit";
$result = mysql_query($realsql);
if (mysql_num_rows($result) > 0) {
while($row = mysql_fetch_array($result)) {
print $row["name"]."<br>";
}
}
else {
print "Sorry No records found";
}

print get_nav($offset, $limit, $totalcount, $searchwords);
}

function get_nav($offset, $limit, $totalnum, $searchwords = "") {
global $PHP_SELF;
if ($totalnum > $limit) {
// calculate number of pages needing links
$pages = intval($totalnum/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($totalnum%$limit) $pages++;

$return .= sprintf('<table><tr><td>Page </td>');
for ($i=1; $i <= $pages; $i++) { // loop thru
$newoffset=$limit*($i-1);
if ($newoffset != $offset) {
$return .= sprintf('<td><a href="%s?offset=%s&searchwords=%s">%s</a></td>%s', $PHP_SELF, $newoffset, urlencode($searchwords), $i, "\n");
}
else {
$return .= sprintf('<td>%s</td>%s', $i, "\n");
}
}
$return .= sprintf('</tr></table>');
}

else {
$return = "";
}
return $return;
}

?>

<html>
<head>
<title>Untitled</title>
</head>

<body>
<form action="<? print $PHP_SELF; ?>" method="post">
<input type="text" name="searchwords">
<input type="submit" name="submit">

</form>
</body>
</html>

**Justin Sampson** · Jan 23, 2001 05:21

Thanks, Freddy I'll give it a shot.

**Justin Sampson** · Jan 23, 2001 14:23

I get this error:

((body LIKE '%fdg%'))
Warning: Supplied argument is not a valid MySQL result resource in /home/webdevcl/public_html/search.php on line 23

and line 23 looks like this

$totalcount = mysql_result($countq, 0);

Anyone now what the problem is?

Thanks,
Justin Sampson

**freddydoesphp** · Jan 23, 2001 14:33

Are you only searching one field or multiple fields try making more than one field searchable by adding to the $fieldstosearch array. Also try printing $sql since it seems the query is failing.

Also change this line to be
from
$totalcount = mysql_result($countq, 0);

to

if(mysql_num_rows($countq) > 0) {
$totalcount = mysql_result($countq, 0);
}

**petesmc** · Jan 23, 2001 14:38

Once you get the final script working, can you please post it as i would love to use it aswell...

**Justin Sampson** · Jan 23, 2001 15:02

I still get this error:

((body LIKE '%what I search for%')
Warning: Supplied argument is not a valid MySQL result resource in /home/webdevcl/public_html/search.php on line 19

Warning: Supplied argument is not a valid MySQL result resource in /home/webdevcl/public_html/search.php on line 25

Heres the code so far:
<?
if ($searchwords) {
//Set this to the number of records per page
$limit = 10;

//Set the initial offset
if (!isset($offset)) $offset = 0;

$fieldstosearch = array("body");
for($i=0;$i<count($fieldstosearch);$i++) {
$clause .= sprintf("(%s LIKE '%%%s%%') OR ", $fieldstosearch[$i], urldecode($searchwords));
}
$clause = "(".substr($clause, 0, -4).")";

print $clause;

$sql = "SELECT COUNT(*) as totalcount WHERE $clause";
$countq = mysql_query($sql);
if(mysql_num_rows($countq) > 0) {
$totalcount = mysql_result($countq, 0);
}

$realsql = "SELECT * WHERE $clause LIMIT $offset, $limit";
$result = mysql_query($realsql);
if (mysql_num_rows($result) > 0) {
while($row = mysql_fetch_array($result)) {
print $row["name"]."<br>";
}
}
else {
print "Sorry No records found";
}

print get_nav($offset, $limit, $totalcount, $searchwords);
}

function get_nav($offset, $limit, $totalnum, $searchwords = "") {
global $PHP_SELF;
if ($totalnum > $limit) {
// calculate number of pages needing links
$pages = intval($totalnum/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($totalnum%$limit) $pages++;

$return .= sprintf('<table><tr><td>Page </td>');
for ($i=1; $i <= $pages; $i++) { // loop thru
$newoffset=$limit*($i-1);
if ($newoffset != $offset) {
$return .= sprintf('<td><a href="%s?offset=%s&searchwords=%s">%s</a></td>%s', $PHP_SELF, $newoffset, urlencode($searchwords), $i, "\n");
}
else {
$return .= sprintf('<td>%s</td>%s', $i, "\n");
}
}
$return .= sprintf('</tr></table>');
}

else {
$return = "";
}
return $return;
}
?>

Thanks,
Justin Sampson

**freddydoesphp** · Jan 23, 2001 15:30

Duh, sorry man my fault I guess it would help to have a from tablename strewn in there try this revised code
<?
if ($searchwords) {
//Set this to the number of records per page
$limit = 10;

//Set the initial offset
if (!isset($offset)) $offset = 0;

$fieldstosearch = array("body");
for($i=0;$i<count($fieldstosearch);$i++) {
$clause .= sprintf("(%s LIKE '%%%s%%') OR ", $fieldstosearch[$i], urldecode($searchwords));
}
if(count($fieldstosearch) > 1) {
$clause = "(".substr($clause, 0, -4).")";
}
else {
$clause = substr($clause, 0, -4);

}
print $clause;

$sql = "SELECT COUNT(*) as totalcount from tablename WHERE $clause";
$countq = mysql_query($sql);
if(mysql_num_rows($countq) > 0) {
$totalcount = mysql_result($countq, 0);
}

$realsql = "SELECT * from tablename WHERE $clause LIMIT $offset, $limit";
$result = mysql_query($realsql);
if (mysql_num_rows($result) > 0) {
while($row = mysql_fetch_array($result)) {
print $row["name"]."<br>";
}
}
else {
print "Sorry No records found";
}

print get_nav($offset, $limit, $totalcount, $searchwords);
}

function get_nav($offset, $limit, $totalnum, $searchwords = "") {
global $PHP_SELF;
if ($totalnum > $limit) {
// calculate number of pages needing links
$pages = intval($totalnum/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($totalnum%$limit) $pages++;

$return .= sprintf('<table><tr><td>Page </td>');
for ($i=1; $i <= $pages; $i++) { // loop thru
$newoffset=$limit*($i-1);
if ($newoffset != $offset) {
$return .= sprintf('<td><a href="%s?offset=%s&searchwords=%s">%s</a></td>%s', $PHP_SELF, $newoffset, urlencode($searchwords), $i, "\n");
}
else {
$return .= sprintf('<td>%s</td>%s', $i, "\n");
}
}
$return .= sprintf('</tr></table>');
}

else {
$return = "";
}
return $return;
}
?>

**petesmc** · Jan 23, 2001 15:38

I tried out the script and got this error...

Warning: Undefined variable: clause in c:\phpweb\codingclick\test.php on line 13
(body LIKE '%htaccess%')

Warning: Supplied argument is not a valid MySQL result resource in c:\phpweb\codingclick\test.php on line 26

Warning: Supplied argument is not a valid MySQL result resource in c:\phpweb\codingclick\test.php on line 32
Sorry No records found

Warning: Undefined variable: totalcount in c:\phpweb\codingclick\test.php on line 41

**freddydoesphp** · Jan 23, 2001 15:51

You'll have to post the code over again I can't tell from here.

**petesmc** · Jan 23, 2001 15:55

<?
include("articles.inc");
mysql_select_db("petesmc_articles");
if ($searchwords) {
//Set this to the number of records per page
$limit = 10;

//Set the initial offset
if (!isset($offset)) $offset = 0;

$fieldstosearch = array("body");
for($i=0;$i<count($fieldstosearch);$i++) {
$clause .= sprintf("(%s LIKE '%%%s%%') OR ", $fieldstosearch[$i], urldecode($searchwords));
}
if(count($fieldstosearch) > 1) {
$clause = "(".substr($clause, 0, -4).")";
}
else {
$clause = substr($clause, 0, -4);

}
print $clause;

$sql = "SELECT COUNT(*) as totalcount from articles WHERE $clause";
$countq = mysql_query($sql);
if(mysql_num_rows($countq) > 0) {
$totalcount = mysql_result($countq, 0);
}

$realsql = "SELECT * from articles WHERE $clause LIMIT $offset, $limit";
$result = mysql_query($realsql);
if (mysql_num_rows($result) > 0) {
while($row = mysql_fetch_array($result)) {
print $row["name"]."<br>";
}
}
else {
print "Sorry No records found";
}

print get_nav($offset, $limit, $totalcount, $searchwords);
}

function get_nav($offset, $limit, $totalnum, $searchwords = "") {
global $PHP_SELF;
if ($totalnum > $limit) {
// calculate number of pages needing links
$pages = intval($totalnum/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($totalnum%$limit) $pages++;

$return .= sprintf('<table><tr><td>Page </td>');
for ($i=1; $i <= $pages; $i++) { // loop thru
$newoffset=$limit*($i-1);
if ($newoffset != $offset) {
$return .= sprintf('<td><a href="%s?offset=%s&searchwords=%s">%s</a></td>%s', $PHP_SELF, $newoffset, urlencode($searchwords), $i, "\n");
}
else {
$return .= sprintf('<td>%s</td>%s', $i, "\n");
}
}
$return .= sprintf('</tr></table>');
}

else {
$return = "";
}
return $return;
}
?>

**freddydoesphp** · Jan 23, 2001 16:02

Okay guys I just tested this thing with my own db and it works fine. Make sure that you change all information rleated to tables, fields and dbs to match your won config. Other than that I don't see how it couldn't work it works fine on my system.

<?

//This worked for me I have a database named louie with a tabled named ip

$db = mysql_connect("somewhere.com", "username", "password");
mysql_select_db("louie");

if ($searchwords) {
//Set this to the number of records per page
$limit = 10;

//Set the initial offset
if (!isset($offset)) $offset = 0;

$fieldstosearch = array("domain");
for($i=0;$i<count($fieldstosearch);$i++) {
$clause .= sprintf("(%s LIKE '%%%s%%') OR ", $fieldstosearch[$i], urldecode($searchwords));
}
if(count($fieldstosearch) > 1) {
$clause = "(".substr($clause, 0, -4).")";
}
else {
$clause = substr($clause, 0, -4);

}
//print $clause;

$sql = "SELECT COUNT(*) as totalcount from ip WHERE $clause";
$countq = mysql_query($sql);
if(mysql_num_rows($countq) > 0) {
$totalcount = mysql_result($countq, 0);
}

$realsql = "SELECT * from ip WHERE $clause LIMIT $offset, $limit";
$result = mysql_query($realsql);
if (mysql_num_rows($result) > 0) {
while($row = mysql_fetch_array($result)) {
print $row["ipnumber"]."<br>";
}
}
else {
print "Sorry No records found";
}

print get_nav($offset, $limit, $totalcount, $searchwords);
}

function get_nav($offset, $limit, $totalnum, $searchwords = "") {
global $PHP_SELF;
if ($totalnum > $limit) {
// calculate number of pages needing links
$pages = intval($totalnum/$limit);

// $pages now contains int of pages needed unless there is a remainder from division
if ($totalnum%$limit) $pages++;

$return .= sprintf('<table><tr><td>Page </td>');
for ($i=1; $i <= $pages; $i++) { // loop thru
$newoffset=$limit*($i-1);
if ($newoffset != $offset) {
$return .= sprintf('<td><a href="%s?offset=%s&searchwords=%s">%s</a></td>%s', $PHP_SELF, $newoffset, urlencode($searchwords), $i, "\n");
}
else {
$return .= sprintf('<td>%s</td>%s', $i, "\n");
}
}
$return .= sprintf('</tr></table>');
}

else {
$return = "";
}
return $return;
}
?>

<html>
<head>
<title>Untitled</title>
</head>

<body>
<form action="<? print $PHP_SELF; ?>" method="post">
<input type="text" name="searchwords">
<input type="submit" name="submit">

</form>
</body>
</html>

<Edited by freddydoesphp on 01-23-2001 at 04:23 PM>

**petesmc** · Jan 23, 2001 16:17

Weird, still doesn't work on mine, can you please edit your last post and bold the stuff we have to change to make sure i did everything?

Thanx

**Justin Sampson** · Jan 24, 2001 06:19

Thanks, Freddy, it works great.

petesmc, make sure you have all your DB conection vars. right, I hade my database name wrong and I got the same error as you.

**petesmc** · Jan 24, 2001 11:32

the part that says domain...
what am i suppoesed to exchange that to...??

Peter

**freddydoesphp** · Jan 24, 2001 11:48

That is the field you want to search, if you want to search more than one field just add more to the array

For instance if you wanted to search a field called name it would be

$fieldstosearch = array("name");

If you wanted to search two fields name and say address

$fieldstosearch = array("name", "address");

or three fields name, address and city

$fieldstosearch = array("name", "address", "city");

Does it make sense now, maybe you should read up on arrays.

**petesmc** · Jan 24, 2001 11:50

thankyou....

User Tag List

Thread: PHP search engine for a mySQL D.B.?

Thread Tools

Display

Bookmarks

Bookmarks

Posting Permissions