Ad:

PHP Discussion Thread
Marked Solved
Views: 2335

Page 1
2
Next

You are currently viewing page 1 of this multi-page discussion thread

Feb 26th, 2009

MySQL Search - Google like?

Expand Post »

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:

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?

Similar Threads

how to import excel data into mySql in PHP
Inserting into Table (MySql) in JSP
In Mysql Storing and retriving the image with asp.net in MySQL
Problems connecting jsp with MySQL database in JSP
How to install MySql++ in redhat enterprise 5 in C++

!Unreal

Reputation Points: 11

Solved Threads: 2

Junior Poster

Offline

112 posts
since Dec 2007

Permalink

Feb 26th, 2009

Re: MySQL Search - Google like?

Try looking at this page.

death_oclock

Reputation Points: 128

Solved Threads: 43

Posting Whiz

Offline

389 posts
since Apr 2006

Permalink

Feb 26th, 2009

Re: MySQL Search - Google like?

How would I do it though?

Im really lost...

!Unreal

Reputation Points: 11

Solved Threads: 2

Junior Poster

Offline

112 posts
since Dec 2007

Permalink

Feb 26th, 2009

Re: MySQL Search - Google like?

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.

death_oclock

Reputation Points: 128

Solved Threads: 43

Posting Whiz

Offline

389 posts
since Apr 2006

Permalink

Feb 26th, 2009

Re: MySQL Search - Google like?

So would I do something like

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

!Unreal

Reputation Points: 11

Solved Threads: 2

Junior Poster

Offline

112 posts
since Dec 2007

Permalink

Feb 27th, 2009

Re: MySQL Search - Google like?

Click to Expand / Collapse Quote originally posted by !Unreal ...

So would I do something like
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:

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:

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.

cwarn23

Sponsor

Featured Poster

Reputation Points: 410

Solved Threads: 258

Occupation: Genius

Offline

3,004 posts
since Sep 2007

Permalink

Feb 27th, 2009

Re: MySQL Search - Google like?

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

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.

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.

!Unreal

Reputation Points: 11

Solved Threads: 2

Junior Poster

Offline

112 posts
since Dec 2007

Permalink

Feb 27th, 2009

Re: MySQL Search - Google like?

Click to Expand / Collapse Quote 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...
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.

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;
    }

cwarn23

Sponsor

Featured Poster

Reputation Points: 410

Solved Threads: 258

Occupation: Genius

Offline

3,004 posts
since Sep 2007

Permalink

Feb 27th, 2009

Re: MySQL Search - Google like?

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

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%'

!Unreal

Reputation Points: 11

Solved Threads: 2

Junior Poster

Offline

112 posts
since Dec 2007

Permalink

Feb 27th, 2009

Re: MySQL Search - Google like?

Click to Expand / Collapse Quote originally posted by !Unreal ...

Hmm...doesnt seem to be working. Is this query valid?
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:

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

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:

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]."%'");

cwarn23

Sponsor

Featured Poster

Reputation Points: 410

Solved Threads: 258

Occupation: Genius

Offline

3,004 posts
since Sep 2007

Page 1
2
Next

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in PHP Forum Timeline: maintaing tabbed panel focus on submit

Next Thread in PHP Forum Timeline: cross browsing

DaniWeb Message

Cancel Changes

User Name
Password