Search one field in mysql databases for a phrase

[jasonc]

i have a table with username and list as fields
the list field contains a string that contains a lists of items, each one on a seperate line.

is there any easier way to search the whole databases for a phrase conatined within the list field and have returned just the complete lines where the phrase is and the usernames of each one found?

without having to load each one and do a search on each entry

hope so!!

please advise how this might be done like a search engine.

i would like to have all results returned.

just thinking maybe in an array?



thanks

[fenway]

You're encountering this problem because you're storing non-atomic data in this "list" field. If you were to store on record per "line", linked with a FK back to user_uid, then this would be a trivial query. I think you should reconsider your table design.

[jasonc]

i have this at the moment, any ideas what i might be doing wrong, this is what my database might look like. (i have put in random data)

if they search 'dog' then it will show all enties in the DB

but if let say one list name is 'dogs and dog' is in the field it should only show once. (this part is not really needed but might help later.


is there a better way to do this. i have only just started the project so re-write is still an option.


CREATE TABLE `items` (
`id` varchar(40) NOT NULL default '',
`list1` longtext NOT NULL,
`list2` longtext NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `items` VALUES ('1', 'cats and dog\r\ntom and jerry\r\ncat and mouse\r\ncar and tyre\r\nhouse and door', 'tom with jerry\r\ncats not dogs\r\ncat with a mouse\r\ncar has a tyre\r\nhouse has a door');
INSERT INTO `items` VALUES ('2', 'tom loves jerry\r\ncats love mice\r\ncar without a tyre\r\nhouse without a door');




<? // index.php
?>
<table width="527" height="417" border="0">
<tr>


<td width="511" height="358" valign="top">
<?
if ($_POST[searchquery]) {
$search = $_POST[searchquery];
echo("search query:".$search.".<br>");
echo('SELECT * FROM items WHERE list1 LIKE "%'.$search.'%" OR list2 LIKE "%'.$search.'%"<br>');
// get data
$result = mysql_query('SELECT * FROM items WHERE list1 LIKE "%$search%" OR list2 LIKE "%$search%"') or die(mysql_error());
$num = mysql_num_rows($result);
echo($num.'<br>');
unset($allitemsfound);
$allitemsfound = array($result);
// show data
for ($i = 1; $i <= $num; $i++) {
echo("user: ".$allitemsfound[$i][0]."<br>from list1: ".$allitemsfound[$i][1]."<br>from list2: ".$allitemsfound[$i][2]."<br>");
}

} else {
echo("Sorry the 'search' field either contains in valid characters or is empty! Please try again.");
}
?>
</td>


</tr>
</table>

[fenway]

If a rewrite is an option, then do so -- this design is very inflexible, and not very robust. Multiple lines in a sinle field, especially a line break (!) is a bad idea. I mentioned this in my original response.

[jasonc]

well somehow i got there!

i have the arrays now, what i can not figure out is how i get the arrays to show just the lines that contain the search query.


strip string of lines that do not contain the search query.

if $string="cats and dogs\r\nmice and cats\r\nmice and chesse"

and

$search="cats"

how do i best remove the lines that do not contine the search query so i get the result of

$string="cats and dogs\r\nmice and cats"

thanks in advance for your help

[fenway]

This is now far removed from a MySQL question; but regardless, split by your delimeter, iterate through this new array, and remove any strings that don't match.