[SOLVED] Search engine [NEED HELP]

Kadir Özüak · #1 (**permalink**) 10-19-08, 12:58 PM

Okay, my search engine is really bad.
Dont know why, but my host is sending me mails about that my search engine, has slow mysql query, and it's slowing the server, so other users get mysql errors.
And I don't know how to do a table layout to it, so it looks good.
Because ive tryed, but it always seems bad, because the table, isnt 1 table for all of the items the search found, it's one seperate table for each result it found.

So I need your help, to optimize my mysql query, so it becomes fast, and the layout.

PHP Code:


			
<html>

<head>

<style type="text/css">

body {background-color: #808080}

</style>

</head>

 

<?php

 

require 'config.php';

 

mysql_connect ($dbhost, $dbusername, $dbuserpass);

mysql_select_db($dbname) or die('Cannot select database');

 

if(isset($_GET['search']))

{

$search = $_GET['search'];

}

 

$search = trim($search);

$search = preg_replace('/\s+/', ' ', $search);

 

$keywords = explode(" ", $search);

 

$image = " - ";

$lol = "<b>Item Name</b>";

$loll = "<b>Item Description</b>";

 

$keywords = array_diff($keywords, array(""));

 

if ($search == NULL or $search == '%'){

} else {

for ($i=0; $i<count($keywords); $i++) {

$query = "SELECT * FROM itemlist " .

"WHERE ItemName LIKE '%".$keywords[$i]."%'".

" OR ItemDesc LIKE '%".$keywords[$i]."%'";

}

 

$result = mysql_query($query) or die(mysql_error());

}

if ($search == NULL or $search == '%'){

} else {

$count = mysql_num_rows($result);

}

 

require 'header.php';

echo "<html>";

echo "<head>";

echo "<title>Search</title>";

echo "<link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\" />";

echo "</head>";

echo "<body onLoad=\"self.focus();document.searchform.search.focus()\">";

echo "<center>";

echo "<br /><form name=\"searchform\" method=\"GET\" action=\"test.php\">";

echo "<input type=\"text\" name=\"search\" size=\"20\" TABINDEX=\"1\" />";

echo " <input type=\"submit\" value=\"Search\" />";

echo "</form>";

if ($search == NULL) {

} else {

echo "You searched for <b><FONT COLOR=\"blue\">";

foreach($keywords as $value) {

   print "$value ";

}

echo "</font></b>";

}

echo "<p> </p><br />";

echo "</center>";

 

if ($search == NULL){

echo "<center><b><FONT COLOR=\"orange\">Please enter a search parameter to continue.</font></b><br /></center>";

} elseif ($search == '%'){

echo "<center><b><FONT COLOR=\"orange\">Please enter a search parameter to continue.</font></b><br /></center>";

} elseif ($count <= 0){

echo "<center><b><FONT COLOR=\"orange\">Your search returned no results.</font></b><br /></center>";

} else {

while($row = mysql_fetch_array($result))

{

//table background color = row_color variable

echo "<center>";

echo "<table border='1'>";

echo "<tr>";

echo "<td>".$text1."</td>";

echo "<td>".$text2."</td>";

echo "</tr>";

echo "<tr>";

echo "<td>".$row['ItemName']."</td>";

echo "<td>".$row['ItemDesc']."</td>";

echo "</tr>";

echo "</center>";

echo "</table>";

//end while

}

//end if

}

 

echo "</body>";

echo "</html>";

if ($search == NULL or $search == '%') {

} else {

mysql_free_result($result);

}

require 'footer.php';

?>

Thanks

EDIT:
Bad painting, about how the layout looks like.
example.jpg

ianbrind · #2 (**permalink**) 10-19-08, 01:36 PM

Not to sure about the query but with the table you should try this.

PHP Code:


			
echo "<table><tr><td>$text1</td><td>$text2</td></tr>';

while ($row == mysql_fetch_array($query){

echo "<tr><td>$row[ItemName]</td><td>$row[ItemDesc]</td></tr>";

}

echo '</table>';

To zebra colour the rows use an if statment where if num_rows %2 == 0 is equal.

Kadir Özüak · #3 (**permalink**) 10-19-08, 02:40 PM

Thanks, will try.

Kadir Özüak · #4 (**permalink**) 10-19-08, 02:54 PM

I really preciate your help in here.
It helped me, thank you.
Just had to change a few things, to get it work probebly(spell?)

By the way, how can I set the widht of the Item ID table border?

Nico · #5 (**permalink**) 10-19-08, 03:02 PM

Quote:

Originally Posted by Kadir Özüak

probebly(spell?)

Properly.

Kadir Özüak · #6 (**permalink**) 10-19-08, 04:05 PM

By the way, any suggestions to how I would do the mysql query fast?
Because my host will delete my account soon, for the slow mysql query, and that it's giving others of the same server, some mysql errors, and lagness.

By the way, I figured out the width thingy

Also, how would I make that it wouldnt search for keywords, and just search for the whole post.

Now if I type: Cooking gloves
It would get some cooking stuff, and some gloves stuff, keywords.

I want it to be like, if I type Cooking gloves, it only displays the Cooking gloves.
Any idea?

UPDATED CODE:

PHP Code:


			
<head> 
<style type="text/css"> 
body {background-color: #808080} 
</style> 
</head> 
 
<?php 
 
require 'config.php'; 
 
mysql_connect ($dbhost, $dbusername, $dbuserpass); 
mysql_select_db($dbname) or die('Cannot select database'); 
 
if(isset($_GET['search'])) 
{ 
$search = $_GET['search']; 
} 
 
$search = trim($search); 
$search = preg_replace('/\s+/', ' ', $search); 
 
$keywords = explode(" ", $search); 
 
$image = " - "; 
$text1 = "<b>Item ID</b>"; 
$text2 = "<b>Item Name</b>"; 
 
$keywords = array_diff($keywords, array("")); 
 
if ($search == NULL or $search == '%'){ 
} else { 
for ($i=0; $i<count($keywords); $i++) { 
$query = "SELECT * FROM itemlist " . 
"WHERE ItemId LIKE '%".$keywords[$i]."%'". 
" OR ItemName LIKE '%".$keywords[$i]."%'"; 
} 
 
$result = mysql_query($query) or die(mysql_error()); 
} 
if ($search == NULL or $search == '%'){ 
} else { 
$count = mysql_num_rows($result); 
} 
 
require 'header.php'; 
echo "<html>"; 
echo "<head>"; 
echo "<title>508 Item Database</title>"; 
echo "<link rel=\"stylesheet\" type=\"text/css\" href=\"style.css\" />"; 
echo "</head>"; 
echo "<body onLoad=\"self.focus();document.searchform.search.focus()\">"; 
echo "<center>"; 
echo "<br /><form name=\"searchform\" method=\"GET\" action=\"index.php\">"; 
echo "<input type=\"text\" name=\"search\" size=\"20\" TABINDEX=\"1\" />"; 
echo "</form>"; 
if ($search == NULL) { 
} else { 
echo "You searched for <b><FONT COLOR=\"blue\">"; 
foreach($keywords as $value) { 
   print "$value "; 
} 
echo "</font></b>"; 
} 
echo "<p> </p><br />"; 
echo "</center>"; 
 
if ($search == NULL){ 
echo "<center><b><FONT COLOR=\"orange\">Please enter a search parameter to continue.</font></b><br /></center>"; 
} elseif ($search == '%'){ 
echo "<center><b><FONT COLOR=\"orange\">Please enter a search parameter to continue.</font></b><br /></center>"; 
} elseif ($count <= 0){ 
echo "<center><b><FONT COLOR=\"orange\">Your search returned no results.</font></b><br /></center>"; 
} else { 
while($row = mysql_fetch_array($result)) 
{ 
echo "<center>"; 
echo "<table border='1'><tr><td width='80'>".$text1."</td><td width='250'>".$text2."</td></tr>"; 
while($row = mysql_fetch_array($result)){ 
echo "<tr><td>".$row['ItemId']."</td><td>".$row['ItemName']."</td></tr>";
echo "</center>";
   $row_count++; 
} 
echo '</table>';  
}
}
 
echo "</body>"; 
echo "</html>"; 
if ($search == NULL or $search == '%') { 
} else { 
mysql_free_result($result); 
} 
require 'footer.php'; 
?>

Kadir Özüak · #7 (**permalink**) 10-20-08, 07:47 AM

PROBLEM!
The code ianbrind gave me, isnt displaying the first thing.
Like if I searched for fish, and there came up Pike and Salmon with the old table engine, then with the one ianbrind gave, it would only give salmon.

How to fix?

curbview.com · #8 (**permalink**) 10-20-08, 09:49 AM

This is what is causing the slow mysql query problem:

PHP Code:


			
if ($search == NULL or $search == '%'){ 

} else { 

for ($i=0; $i<count($keywords); $i++) { 

$query = "SELECT * FROM itemlist " . 

"WHERE ItemId LIKE '%".$keywords[$i]."%'". 

" OR ItemName LIKE '%".$keywords[$i]."%'"; 

}

1. Create an index for ItemID
2. Create an index for ItemName
3. Change your query. Using '*" in your query is greedy! Something like this is a lot better:

PHP Code:


			
if ($search == NULL or $search == '%'){ 

} else { 

for ($i=0; $i<count($keywords); $i++) { 

$query = "SELECT ItemID, ItemName FROM itemlist " . 

"WHERE ItemId ='$keywords[$i]' OR ItemName = '$keywords[$i]' "; 

}

Kadir Özüak · #9 (**permalink**) 10-20-08, 10:48 AM

How do I create index for ItemId and ItemName?
Or did you index it, in the code you posted.

Kadir Özüak · #10 (**permalink**) 10-20-08, 11:38 AM

Sorry for double posting, but it seems I cant edit my post.
Anyway, the post you posted, with the mysql slow query fix, isn't giving any results.

EDIT: It is working, but is searching for full text. So it doesnt search for keywords anymore.
I want it to search for keywords, only. Because if I type Water it can't find the word Watermelon in the database.