MySQL Search - Google like?

[!Unreal]

Ive got a MySQL search, which searches an index of urls. The search parameter searches titles. What I want to do is have something like Google.

So, if someone searches:

site:example.com something to search

It would look for example.com in one column then from those results it would look for 'something to search' in another given column.

How would I do this?

[death_oclock]

Try looking at this page .

[!Unreal]

How would I do it though? :| Im really lost...

[death_oclock]

Do you already have the part of the query that matches the site? Add to that (with an AND clause) a MATCH(field) AGAINST('value') as demonstrated in the link I gave you.

[!Unreal]

So would I do something like

SELECT FROM table1 WHERE column1='$foo' IN table1 WHERE column1='$foo2'

Is that right? Just guess work :p

[cwarn23]

So would I do something like

SELECT FROM table1 WHERE column1='$foo' IN table1 WHERE column1='$foo2'

Is that right? Just guess work :p

For starters that mysql query is wrong and I shall share a way help answer this question. First to retrieve all site: within a string use the following:

$string='test site:example.com site:test.com test2';
if (preg_match('/site:/i',$string))
    {
    preg_match_all('/site:[^ ]+/i',$string,$site);
    for ($ii=0;isset($site[0][$ii]);$ii++)
        {
        $site[0][$ii]=strtolower(substr($site[0][$ii],5,strlen($site[0][$ii])));
        }
    $sites=$site[0];  unset($site);
    
    //now to display it
    foreach ($sites AS $siteval)
        {
        echo $siteval."";
        }
    }

That will turn it into the $sites array. The reason why an array is just in case there is more than one site specified. Then to do the mysql query it would be something like the following:

mysql_query("SELECT * FROM `table` WHERE `domain`='".mysql_real_escape_string($sites[0])."'");

[!Unreal]

Thats amazing, thank you so much. What I will have to do is to tell my users to set site:example.com first then put the query. To make it easier.

So, to get the query. Ive done this...

$search = explode(' ', $string);
echo $search[1];

It works but is pretty rudimentary, would you recomend something better?

EDIT: This is my final code. Im really tired now and there are probably loads of errors lol.

$search= $_GET['q'];
if (preg_match('/site:/i',$string))
    {
    preg_match_all('/site:[^ ]+/i',$string,$site);
    
    for ($ii=0;isset($site[0][$ii]);$ii++)
        {
        $site[0][$ii]=strtolower(substr($site[0][$ii],5,strlen($site[0][$ii])));
        }
    $sites=$site[0];
    $query = explode(' ', $string);
    
    $sql = "SELECT * FROM list WHERE title LIKE '%$query%' AND WHERE url LIKE '%$sites%'";
    }
    else {
    	$sql = "SELECT * FROM list WHERE title LIKE '%$search%'";
    }

[cwarn23]

Thats amazing, thank you so much. What I will have to do is to tell my users to set site:example.com first then put the query. To make it easier.

So, to get the query. Ive done this...

$search = explode(' ', $string);
echo $search[1];

It works but is pretty rudimentary, would you recomend something better?

Yes, using the explode function would be the best way to get all of the keywords but in your example, you have got the site:example.com as a keyword below is how I would do it.

$string='test site:example.com site:test.com test2';
if (preg_match('/site:/i',$string))
    {
    preg_match_all('/site:[^ ]+/i',$string,$site);
    for ($ii=0;isset($site[0][$ii]);$ii++)
        {
        $site[0][$ii]=strtolower(substr($site[0][$ii],5,strlen($site[0][$ii])));
        }
    $sites=$site[0];  unset($site);
    }
$search=explode(' ',preg_replace('/[\h]+/',' ',preg_replace('/site:[^\h]+/i','',$string)));


//now to display
echo "<b>Sites</b>";
foreach ($sites AS $siteval)
    {
    echo $siteval."";
    }
echo "<b>Keywords</b>";
foreach ($search AS $val)
    {
    echo "".$val;
    }

[!Unreal]

Hmm...doesnt seem to be working. Is this query valid?

SELECT * FROM list WHERE title LIKE '%$search%' AND where url LIKE '%$sites%'

[cwarn23]

Hmm...doesnt seem to be working. Is this query valid?

SELECT * FROM list WHERE title LIKE '%$search%' AND where url LIKE '%$sites%'

The mysql query should look more like the following:

SELECT * FROM list WHERE title LIKE '%$search%' AND url LIKE '%$sites%'

or for my preference

mysql_query("SELECT * FROM `list` WHERE `title` LIKE '%".$search."%' AND `url` LIKE '%".$sites."%'");

Also note that$search and $sites must not be an array when used in the query. If you do use an array in a mysql query and you don't specify the exact value (example: $search[0]) Then it will just scan the database for the word 'Array'. So if $search and $sites are arrays then you may need to put the query inside a loop and for testing purposes you could use the following:

mysql_query("SELECT * FROM `list` WHERE `title` LIKE '%".$search[0]."%' AND `url` LIKE '%".$sites[0]."%'");

[!Unreal]

Aha! Got it working. Took me a while to work out that I needed to change $search[0] to $search[1] :p

Thank you very much for all your help, youre a legend :)