DaniWeb Forum Index > Web Development > PHP

MySQL Search - Google like?

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

Thread Solved

•

Join Date: Dec 2007

Posts: 112

Reputation: !Unreal is an unknown quantity at this point

Solved Threads: 2

!Unreal

Offline

Junior Poster

MySQL Search - Google like?

Feb 26th, 2009

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:

Help with Code Tags

PHP Syntax (Toggle Plain Text)

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?

•

Join Date: Apr 2006

Posts: 357

Reputation: death_oclock will become famous soon enough

Solved Threads: 37

death_oclock death_oclock is offline

Offline

Posting Whiz

Re: MySQL Search - Google like?

Feb 26th, 2009

Try looking at this page.

•

Join Date: Dec 2007

Posts: 112

Reputation: !Unreal is an unknown quantity at this point

Solved Threads: 2

!Unreal

Offline

Junior Poster

Re: MySQL Search - Google like?

Feb 26th, 2009

How would I do it though?

Im really lost...

•

Join Date: Apr 2006

Posts: 357

Reputation: death_oclock will become famous soon enough

Solved Threads: 37

death_oclock death_oclock is offline

Offline

Posting Whiz

Re: MySQL Search - Google like?

Feb 26th, 2009

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.

•

Join Date: Dec 2007

Posts: 112

Reputation: !Unreal is an unknown quantity at this point

Solved Threads: 2

!Unreal

Offline

Junior Poster

Re: MySQL Search - Google like?

Feb 26th, 2009

So would I do something like

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
SELECT FROM table1 WHERE column1='$foo' IN table1 WHERE column1='$foo2'
SELECT FROM table1 WHERE column1='$foo' IN table1 WHERE column1='$foo2'

Is that right? Just guess work :p

•

Join Date: Sep 2007

Posts: 1,463

Reputation: cwarn23 has a spectacular aura about

Solved Threads: 136

cwarn23

Offline

Nearly a Posting Virtuoso

Re: MySQL Search - Google like?

Feb 27th, 2009

•

Originally Posted by !Unreal

So would I do something like
Help with Code Tags

mysql Syntax (Toggle Plain Text)
SELECT FROM table1 WHERE column1='$foo' IN table1 WHERE column1='$foo2'
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:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$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."<br>";
        }
    }
$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."<br>";
        }
    }

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:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
mysql_query("SELECT * FROM `table` WHERE `domain`='".mysql_real_escape_string($sites[0])."'");
mysql_query("SELECT * FROM `table` WHERE `domain`='".mysql_real_escape_string($sites[0])."'");

Last edited by cwarn23; Feb 27th, 2009 at 2:10 am.

Try not to bump 10 year old threads as it can be really annoying.
Like php then read my website at http://syntax.cwarn23.net/
Star-Trek-Atlantis - now that's what I call a movie ^_^
My favourite PC. - MacGyver Fan
Bad english note: dis-iz-2b4u

•

Join Date: Dec 2007

Posts: 112

Reputation: !Unreal is an unknown quantity at this point

Solved Threads: 2

!Unreal

Offline

Junior Poster

Re: MySQL Search - Google like?

Feb 27th, 2009

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...

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$search = explode(' ', $string);
echo $search[1];
$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.

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$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%'";
    }
$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%'";
    }

Last edited by !Unreal; Feb 27th, 2009 at 5:53 am.

•

Join Date: Sep 2007

Posts: 1,463

Reputation: cwarn23 has a spectacular aura about

Solved Threads: 136

cwarn23

Offline

Nearly a Posting Virtuoso

Re: MySQL Search - Google like?

Feb 27th, 2009

•

Originally Posted by !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...
Help with Code Tags

php Syntax (Toggle Plain Text)
$search = explode(' ', $string);
echo $search[1];
$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.

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
$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><br>";
foreach ($sites AS $siteval)
    {
    echo $siteval."<br>";
    }
echo "<br><b>Keywords</b>";
foreach ($search AS $val)
    {
    echo "<br>".$val;
    }
$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><br>";
foreach ($sites AS $siteval)
    {
    echo $siteval."<br>";
    }
echo "<br><b>Keywords</b>";
foreach ($search AS $val)
    {
    echo "<br>".$val;
    }

•

Join Date: Dec 2007

Posts: 112

Reputation: !Unreal is an unknown quantity at this point

Solved Threads: 2

!Unreal

Offline

Junior Poster

Re: MySQL Search - Google like?

Feb 27th, 2009

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

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
SELECT * FROM list WHERE title LIKE '%$search%' AND WHERE url LIKE '%$sites%'
SELECT * FROM list WHERE title LIKE '%$search%' AND where url LIKE '%$sites%'

•

Join Date: Sep 2007

Posts: 1,463

Reputation: cwarn23 has a spectacular aura about

Solved Threads: 136

cwarn23

Offline

Nearly a Posting Virtuoso

Re: MySQL Search - Google like?

#10

Feb 27th, 2009

•

Originally Posted by !Unreal

Hmm...doesnt seem to be working. Is this query valid?
Help with Code Tags

mysql Syntax (Toggle Plain Text)
SELECT * FROM list WHERE title LIKE '%$search%' AND WHERE url LIKE '%$sites%'
SELECT * FROM list WHERE title LIKE '%$search%' AND where url LIKE '%$sites%'

The mysql query should look more like the following:

 Help with Code Tags 
 mysql Syntax (Toggle Plain Text) 
SELECT * FROM list WHERE title LIKE '%$search%' AND url LIKE '%$sites%'
SELECT * FROM list WHERE title LIKE '%$search%' AND url LIKE '%$sites%'

or for my preference

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
mysql_query("SELECT * FROM `list` WHERE `title` LIKE '%".$search."%' AND `url` LIKE '%".$sites."%'");
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:

 Help with Code Tags 
 php Syntax (Toggle Plain Text) 
mysql_query("SELECT * FROM `list` WHERE `title` LIKE '%".$search[0]."%' AND `url` LIKE '%".$sites[0]."%'");
mysql_query("SELECT * FROM `list` WHERE `title` LIKE '%".$search[0]."%' AND `url` LIKE '%".$sites[0]."%'");