Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Multiple keyword search in SQL

Posted on 2008-10-22
12
Medium Priority
?
1,351 Views
Last Modified: 2012-05-05
Dear Experts,

This isn't mega important but I figured I'd get your invaluable input just for my peace of mind. I have two ways of searching my database. The field searching aspect of my system works great but trying to figure out the SQL query for searching multiple keywords as a global "Google" search sort of thing.

A couple of things I'd like to please ask you. Firstly, is the speed decrease huge using LIKE %searchterm%? It is, however, running from a View and not the physical table. Also, for MATCH and AGAINST to work, would I need to upgrade my MySQL server from 5.0.30 to a later version?

Please see below for the code I've been using.

Thanks in advance.
//////////////////snippet of search_db_fields.php////////////////////
////This works great but would any of you recommend any changes//////
/////////////////////////////////////////////////////////////////////
 
<div id="content">
<?php
 
foreach ($_POST as $key => $value) {
 $_POST[$key] = mysql_real_escape_string($value);
}
 
$search = "SELECT * from vsearch WHERE Sheet_No LIKE '%$_POST[fSheetNo_S]%' AND Sales_Ledger_No LIKE '%$_POST[fSalesRef_S]%' AND LoggedBy LIKE '%$_POST[fLoggedBy_S]%' AND ProblemsClass LIKE '%$_POST[fProblems_S]%' AND CreationDateTime LIKE '%$_POST[fCreation_S]%' AND InvoiceDate LIKE '%$_POST[fInvoice_S]%' AND CompanyName LIKE '%$_POST[fCompany_S]%' AND ContactName LIKE '%$_POST[fContact_S]%' AND Postcode LIKE '%$_POST[fPostcode_S]%' AND Email LIKE '%$_POST[fEmail_S]%' AND Phone LIKE '%$_POST[fPhone_S]%' AND ProblemDescription LIKE '%$_POST[fDescription_S]%' AND GoodsToBeReturned LIKE '%$_POST[fGoodsRet_S]%' AND GoodsToBeShipped LIKE '%$_POST[fGoodsShip_S]%' AND GoodsToBeSentOnReceipt LIKE '%$_POST[fGoodsReceipt_S]%' ORDER BY Sheet_No"; 
$sql = mysql_query($search) or die(mysql_error());
$row = mysql_fetch_assoc($sql);
 
?>
 
<?php echo "<div id=\"search_table_fields\">
			<table width=\"1000\" align=\"left\" class=\"Arial_12\" rules=\"groups\">
				<tr>
					<td class=\"Arial_14_Bold\" colspan=\"10\">Search results</td>
				<tr>
					<td colspan=\"10\">&nbsp;</td>
				<tr>
					<td colspan=\"10\">for: ". $_POST["fSheetNo_S"] ." ". $_POST["fSalesRef_S"] ." ". $_POST["fLoggedBy_S"] ." ". $_POST["fProblems_S"] ." ". $_POST["fInvoice_S"] ." ". $_POST["fCompany_S"] ." ". $_POST["fContact_S"] ." ". $_POST["fPostcode_S"] ." ". $_POST["fEmail_S"] ." ". $_POST["fPhone_S"] ." ". $_POST["fDescription_S"] ." ". $_POST["fGoodsRet_S"] ." ". $_POST["fGoodsShip_S"] ." ". $_POST["fGoodsReceipt_S"] ." </td>
				<tr>
					<td colspan=\"10\">&nbsp;</td>
				<tr>
					<td width=\"8%\" align=\"left\" class=\"Arial_12_Bold\">Sheet_No</td>
					<td width=\"12%\" align=\"left\" class=\"Arial_12_Bold\">Sales_Ledger_No</td>
					<td width=\"8%\" align=\"left\" class=\"Arial_12_Bold\">Status</td>
					<td width=\"12%\" align=\"left\" class=\"Arial_12_Bold\">Actionee</td>
					<td width=\"10%\" align=\"left\" class=\"Arial_12_Bold\">Type</td>
					<td width=\"12%\" align=\"left\" class=\"Arial_12_Bold\">CreationDateTime</td>
					<td width=\"10%\" align=\"left\" class=\"Arial_12_Bold\">ProblemsClass</td>
					<td width=\"15%\" align=\"left\" class=\"Arial_12_Bold\">CompanyName</td>
					<td width=\"15%\" align=\"left\" class=\"Arial_12_Bold\">GoodsToBeReturned</td>
					<td width=\"10%\" align=\"left\" class=\"Arial_12_Bold\">ActionReqd</td>
				 " ?>
<?php do { ?>
		   		<tr>
					<td class="Arial_12_Blue"><a href="edit_sheet.php?sheet=<?php echo $row['Sheet_No']; ?>"><?php echo $row['Sheet_No']; ?></a></td>
					<td><?php echo $row['Sales_Ledger_No']; ?></td>
					<td><?php echo $row['Status']; ?></td>
					<td><?php echo $row['Actionee']; ?></td>
					<td><?php echo $row['Type']; ?></td>
					<td><?php echo $row['CreationDateTime']; ?></td>
					<td><?php echo $row['ProblemsClass']; ?></td>
					<td><?php echo $row['CompanyName']; ?></td>
					<td><?php echo $row['GoodsToBeReturned']; ?></td>
					<td><?php echo $row['ActionReqd']; ?></td> 
			<?php   } 	while ($row = mysql_fetch_assoc($sql));
						$rows = mysql_num_rows($sql);
						if($rows > 0) {
						mysql_data_seek($sql, 0);
						$row = mysql_fetch_assoc($sql);
						}
						else if($rows > 50) {
						echo "<table><tr><td class=\"Arial_12\">Too many results (over 50) please refine your search.</td></tr></table>";
						}
						else if($rows == 0) {
						echo "<table><tr><td class=\"Arial_14_Red_Bold\">No results found. Please refine your search and try again.</td></tr></table>";
						}  ?>
					
					</table></div> 
						
					
 
</div>
 
 
///////////////snippet of search_db_keywords.php///////////////////
This doesn't work properly at the moment. Again, if there are any
recommendations it would be most appreciated.//////////////////////
///////////////////////////////////////////////////////////////////
 
<div id="content">
<?php
 
foreach ($_POST as $key => $value) {
 $_POST[$key] = mysql_real_escape_string($value);
}
 
$search = "SELECT * from vsearch WHERE (Sheet_No LIKE '%$_POST[fKeyword1]%' OR Sheet_No LIKE '%$_POST[fKeyword2]%' OR Sheet_No LIKE '%$_POST[fKeyword3]%') AND (CompanyName LIKE '%$_POST[fKeyword1]%' OR CompanyName LIKE '%$_POST[fKeyword2]%' OR CompanyName LIKE '%$_POST[fKeyword3]%') ORDER BY Sheet_No";
$search = "SELECT * from vsearch WHERE Sheet_No LIKE '%$_POST[fKeyword1]%' AND CompanyName LIKE '%$_POST[fKeyword1]%' OR Sheet_No LIKE '%$_POST[fKeyword2]%' AND CompanyName LIKE '%$_POST[fKeyword2]%' OR Sheet_No LIKE '%$_POST[fKeyword3]%' AND CompanyName LIKE '%$_POST[fKeyword3]%'";
$sql = mysql_query($search) or die(mysql_error());
$row = mysql_fetch_assoc($sql); 
?>
 
<?php echo "<div id=\"search_table_fields\">
			<table width=\"1000\" align=\"left\" class=\"Arial_12\" rules=\"groups\">
				<tr>
					<td class=\"Arial_14_Bold\" colspan=\"10\">Search results</td>
				<tr>
					<td colspan=\"10\">&nbsp;</td>
				<tr>
					<td colspan=\"10\">for: ". $_POST["fKeyword1"] ." ". $_POST["fKeyword2"] ." ". $_POST["fKeyword3"] ." </td>
				<tr>
					<td colspan=\"10\">&nbsp;</td>
				<tr>
					<td width=\"8%\" align=\"left\" class=\"Arial_12_Bold\">Sheet_No</td>
					<td width=\"12%\" align=\"left\" class=\"Arial_12_Bold\">Sales_Ledger_No</td>
					<td width=\"8%\" align=\"left\" class=\"Arial_12_Bold\">Status</td>
					<td width=\"12%\" align=\"left\" class=\"Arial_12_Bold\">Actionee</td>
					<td width=\"10%\" align=\"left\" class=\"Arial_12_Bold\">Type</td>
					<td width=\"12%\" align=\"left\" class=\"Arial_12_Bold\">CreationDateTime</td>
					<td width=\"10%\" align=\"left\" class=\"Arial_12_Bold\">ProblemsClass</td>
					<td width=\"15%\" align=\"left\" class=\"Arial_12_Bold\">CompanyName</td>
					<td width=\"15%\" align=\"left\" class=\"Arial_12_Bold\">GoodsToBeReturned</td>
					<td width=\"10%\" align=\"left\" class=\"Arial_12_Bold\">ActionReqd</td>
				 " ?>
<?php do { ?>
		   		<tr>
					<td class="Arial_12_Blue"><a href="edit_sheet.php?sheet=<?php echo $row['Sheet_No']; ?>"><?php echo $row['Sheet_No']; ?></a></td>
					<td><?php echo $row['Sales_Ledger_No']; ?></td>
					<td><?php echo $row['Status']; ?></td>
					<td><?php echo $row['Actionee']; ?></td>
					<td><?php echo $row['Type']; ?></td>
					<td><?php echo $row['CreationDateTime']; ?></td>
					<td><?php echo $row['ProblemsClass']; ?></td>
					<td><?php echo $row['CompanyName']; ?></td>
					<td><?php echo $row['GoodsToBeReturned']; ?></td>
					<td><?php echo $row['ActionReqd']; ?></td> 
			<?php   } 	while ($row = mysql_fetch_assoc($sql));
						$rows = mysql_num_rows($sql);
						if($rows > 0) {
						mysql_data_seek($sql, 0);
						$row = mysql_fetch_assoc($sql);
						}
						else if($rows > 50) {
						echo "<table><tr><td class=\"Arial_12\">Too many results (over 50) please refine your search.</td></tr></table>";
						}
						else if($rows == 0) {
						echo "<table><tr><td class=\"Arial_14_Red_Bold\">No results found. Please refine your search and try again.</td></tr></table>";
						}  ?>
					
					</table></div> 
						
					
 
</div>

Open in new window

0
Comment
Question by:BenthamLtd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
12 Comments
 
LVL 17

Expert Comment

by:psimation
ID: 22776127
MATCH AGAINST = fulltext search and is MySQL 5 or later AFAIK.

Not sure about speed - I guess a normal "like" would probably be faster than a fulltext search in certain conditions.

Didn't go through your code completely, but it looks like you are overwriting your $sql on lines 84 and 85 - not sure if that is intended or not - just saw that when scanning thru your code...

0
 
LVL 17

Expert Comment

by:psimation
ID: 22776146
sorry, meant overwriting $search on 84 and 85...
0
 

Author Comment

by:BenthamLtd
ID: 22776185
Thanks for the quick reply Psimation. My apologies, I forgot to remove that. It was tagged as // when I was developing it. Also please note I haven't listed all fields in the "search_db_keywords.php" SQL query $search for the moment. Just Sheet_No (the primary key) and CompanyName to test the keyword search :)

The correct code I've been using should be:


///////////////snippet of search_db_keywords.php///////////////////
//This doesn't work properly at the moment. Again, if there are any
//recommendations it would be most appreciated.//////////////////////
///////////////////////////////////////////////////////////////////
 
<div id="content">
<?php
 
foreach ($_POST as $key => $value) {
 $_POST[$key] = mysql_real_escape_string($value);
}
 
$search = "SELECT * from vsearch WHERE Sheet_No LIKE '%$_POST[fKeyword1]%' AND CompanyName LIKE '%$_POST[fKeyword1]%' OR Sheet_No LIKE '%$_POST[fKeyword2]%' AND CompanyName LIKE '%$_POST[fKeyword2]%' OR Sheet_No LIKE '%$_POST[fKeyword3]%' AND CompanyName LIKE '%$_POST[fKeyword3]%'";
$sql = mysql_query($search) or die(mysql_error());
$row = mysql_fetch_assoc($sql); 
?>
 
<?php echo "<div id=\"search_table_fields\">
                        <table width=\"1000\" align=\"left\" class=\"Arial_12\" rules=\"groups\">
                                <tr>
                                        <td class=\"Arial_14_Bold\" colspan=\"10\">Search results</td>
                                <tr>
                                        <td colspan=\"10\"> </td>
                                <tr>
                                        <td colspan=\"10\">for: ". $_POST["fKeyword1"] ." ". $_POST["fKeyword2"] ." ". $_POST["fKeyword3"] ." </td>
                                <tr>
                                        <td colspan=\"10\"> </td>
                                <tr>
                                        <td width=\"8%\" align=\"left\" class=\"Arial_12_Bold\">Sheet_No</td>
                                        <td width=\"12%\" align=\"left\" class=\"Arial_12_Bold\">Sales_Ledger_No</td>
                                        <td width=\"8%\" align=\"left\" class=\"Arial_12_Bold\">Status</td>
                                        <td width=\"12%\" align=\"left\" class=\"Arial_12_Bold\">Actionee</td>
                                        <td width=\"10%\" align=\"left\" class=\"Arial_12_Bold\">Type</td>
                                        <td width=\"12%\" align=\"left\" class=\"Arial_12_Bold\">CreationDateTime</td>
                                        <td width=\"10%\" align=\"left\" class=\"Arial_12_Bold\">ProblemsClass</td>
                                        <td width=\"15%\" align=\"left\" class=\"Arial_12_Bold\">CompanyName</td>
                                        <td width=\"15%\" align=\"left\" class=\"Arial_12_Bold\">GoodsToBeReturned</td>
                                        <td width=\"10%\" align=\"left\" class=\"Arial_12_Bold\">ActionReqd</td>
                                 " ?>
<?php do { ?>
                                <tr>
                                        <td class="Arial_12_Blue"><a href="edit_sheet.php?sheet=<?php echo $row['Sheet_No']; ?>"><?php echo $row['Sheet_No']; ?></a></td>
                                        <td><?php echo $row['Sales_Ledger_No']; ?></td>
                                        <td><?php echo $row['Status']; ?></td>
                                        <td><?php echo $row['Actionee']; ?></td>
                                        <td><?php echo $row['Type']; ?></td>
                                        <td><?php echo $row['CreationDateTime']; ?></td>
                                        <td><?php echo $row['ProblemsClass']; ?></td>
                                        <td><?php echo $row['CompanyName']; ?></td>
                                        <td><?php echo $row['GoodsToBeReturned']; ?></td>
                                        <td><?php echo $row['ActionReqd']; ?></td> 
                        <?php   }       while ($row = mysql_fetch_assoc($sql));
                                                $rows = mysql_num_rows($sql);
                                                if($rows > 0) {
                                                mysql_data_seek($sql, 0);
                                                $row = mysql_fetch_assoc($sql);
                                                }
                                                else if($rows > 50) {
                                                echo "<table><tr><td class=\"Arial_12\">Too many results (over 50) please refine your search.</td></tr></table>";
                                                }
                                                else if($rows == 0) {
                                                echo "<table><tr><td class=\"Arial_14_Red_Bold\">No results found. Please refine your search and try again.</td></tr></table>";
                                                }  ?>
                                        
                                        </table></div> 
                                                
                                        
 
</div>

Open in new window

0
Survive A High-Traffic Event with Percona

Your application or website rely on your database to deliver information about products and services to your customers. You can’t afford to have your database lose performance, lose availability or become unresponsive – even for just a few minutes.

 
LVL 17

Expert Comment

by:psimation
ID: 22776218
OK, so what is not working in your script atm (putting aside the fulltext search and speed issues)?  

0
 

Author Comment

by:BenthamLtd
ID: 22776290
Well it works but it either returns the entire database result or nothing at all.
For example if I were to search

[fKeyword1]  = 1004
[fKeyword2] = fred

It returns everything, despite there being only one row in the database with Sheet_No = 1004 and CompanyName with "Fred" in it.

It's more than likely (more definitely) my SQL syntax which is poo. Any ideas?
0
 
LVL 17

Accepted Solution

by:
psimation earned 2000 total points
ID: 22776315
OK, it is most likely the logic -ie, all the ANDs and ORs in your SQL - you usually need to group them properly with parenthesis (this AND that) OR ((this AND something_else) AND (this and that)) or whatever.
0
 

Author Comment

by:BenthamLtd
ID: 22776370
Ok changed it to

$search = "SELECT * from vsearch WHERE (Sheet_No LIKE '%$_POST[fKeyword1]%' AND CompanyName LIKE '%$_POST[fKeyword1]%') OR (Sheet_No LIKE '%$_POST[fKeyword2]%' AND CompanyName LIKE '%$_POST[fKeyword2]%') OR (Sheet_No LIKE '%$_POST[fKeyword3]%' AND CompanyName LIKE '%$_POST[fKeyword3]%')";

but it still returns everything where

$_POST[fKeyword1] is "1004"
$_POST[fKeyword2] is "fred"
$_POST[fKeyword3] is ""

maybe it's the blank fKeyword3 that is causing the system to return everything. I'm hoping we can get around this without using if statements lol
0
 
LVL 17

Expert Comment

by:psimation
ID: 22776401
Yes, the blank value will basically cause a "true" for the last OR, which will then make the entire query "valid" regardless of any other conditions.

If you make sure the fKeyword3 has a value, does the query work?
0
 

Author Comment

by:BenthamLtd
ID: 22776485
Ahhhh ok. The blank values seemed to work on the field searching aspect of the system but that's probably because of the numerous AND statements and the fact that there were individual forms for each column name.

To answer your question, I've added in Phone in the WHERE just so we have three values to test with.

$search = "SELECT * from vsearch WHERE (Sheet_No LIKE '%$_POST[fKeyword1]%' AND CompanyName LIKE '%$_POST[fKeyword1]%' AND Phone LIKE '%$_POST[fKeyword1]%') OR (Sheet_No LIKE '%$_POST[fKeyword2]%' AND CompanyName LIKE '%$_POST[fKeyword2]%' AND Phone LIKE '%$_POST[fKeyword2]%') OR (Sheet_No LIKE '%$_POST[fKeyword3]%' AND CompanyName LIKE '%$_POST[fKeyword3]%' AND Phone LIKE '%$_POST[fKeyword3]%')";

And no, it doesn't return anything. This was using search parameters [fKeyword1] = "1004", [fKeyword2] = "fred", [fKeyword3] = "1".  (yes, there is a number 1 in the phone number on that row!)
0
 
LVL 17

Expert Comment

by:psimation
ID: 22776533
OK, but your fKeyword1, is that always corresponding with Sheet_No and fkeyword2 and fkeyword3 are your "search terms"?

ie, your search form, what are you trying to match: textfield1 against company name, textfield2 and textfield3 against either company name or phone number?
0
 

Author Comment

by:BenthamLtd
ID: 22776628
It's a global search. Basically it's not meant to matter what values user's enter into either of the three text form boxes.

So, if I were to search for "ted" and "2008"..........errrrr actually wtf am I doing. I've just realised how illogical that would be for a user as they would need to know three things about their search criteria anyway.

I tell ye what Psimation, thank you ever so much for all your help this afternoon and sorry for wasting your time. I'll happily chuck you the points for all your assistance.

I'll just stick with having one box on the keyword search for now. Not only does it make it more logical in terms of system usability, it saves me the hassle of coding something that will probably not be used!

Thank you again.
0
 
LVL 17

Expert Comment

by:psimation
ID: 22776669
not a probblem

Have a beer and a "think-over" - if you still wanna go this route - just add comments to this thread and I'll continue helping if I can...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, we’ll look at how to deploy ProxySQL.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question