search 2 fields in a database and sort results

flash gordon · April 4, 2007

I was trying to pay someone to do this, but things are not looking hopeful now....

Anyway, I need to search 2 fields in a database table that have text in them. So I need to search for "foo, bar, cat" in to fields. Then I need to return the greatest number of matches first, and so on down the line.

I do not have system level access to the server so Full-Text search methods are out.

Can anyone give some pointers how to do this?

desithugg · April 4, 2007

umm im not too sure about searches but you can try something like

selec * from table WHERE colum1 LIKE "%value%" and colum2 LIKE "%value%"

Barand · April 4, 2007

Do you want to know

which field has the greater number of matches or

Which value has the greater number

flash gordon · April 5, 2007

which 2 fields combined for each row in the database has the greatest matches. and then sort by greatest to least matches. No matches are completely excluded.

Barand · April 5, 2007

try

<?php
include 'db2.php'; // db connect stuff
/**
* create a temp table to effectively normalize your table  
*/
$sql = "CREATE TEMPORARY TABLE tableB SELECT id, a FROM tableA";
mysql_query($sql) or die (mysql_error()."<p>$sql</p>");

$sql = "INSERT INTO tableB SELECT id, b FROM tableA";
mysql_query($sql) or die (mysql_error()."<p>$sql</p>");

/**
* now it's a simple count query'
*/
$sql = "select a, COUNT(*) as n FROM tableB
        WHERE a IN ('foo', 'bar', 'cat')
        GROUP BY a
        ORDER BY n DESC";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");

while (list($a,$n) = mysql_fetch_row($res)) {
    echo "$a : $n <br>";
}
?>

My data

id, a, b
----------------
1, 'foo', 'x'
2, 'foo', 'bar'
3, 'y', 'cat'
4, 'bar', 'z'
5, 'cat', 'foo'
6, 'x', 'foo'
7, 'cat', 'bar'
8, 'cat', 'foo'
9, 'x', 'y'
10, 'bar', 'foo'
11, 'foo', 'bar'
12, 'foo', 'foo'

My results

foo : 9 
bar : 5 
cat : 4

flash gordon · April 8, 2007

Thanks for the reply

I don't think I'm getting it.

<?php

$username = "";
$password = "";
$db       = "";
$table    = ""; 

// Conect to the database.
$cnx = mysql_connect('localhost', $username, $password) or die ("Could not connect");
mysql_select_db($db,  $cnx) or die (mysql_error());


$sql = "select keywords, composer, COUNT(*) as n FROM $table
        WHERE keywords IN ('foo', 'bar', 'cat')
        GROUP BY keywords
        ORDER BY n DESC";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");

// and i did change the vars here to match what I need.
// i tried several different ways.
while (list($a,$n) = mysql_fetch_row($res)) {
    echo "$a : $n <br>";
}

?>

Running that code gives me no errors, but no results (even before I stuck commas in the table). I need to search composer and keywords and sort those 2 fields.

Thanks guys

flash gordon · April 8, 2007

bump*.

I could really use the help. I have already tried to hire 2 people from here and both have flaked on me already.

Barand · April 8, 2007

Just so I know exactly what you want, what output do expect to see from the data you have shown above?

flash gordon · April 8, 2007

$search = $_GET['search'];
// $search = "foo, sad, two words, monkey"; 

// search database for the words above in the 'keywords' and 'composer' field

Output:

id 1 --> because it has three matches (foo, sad, and monkey)

id 4 --> because it has two matches (sad and "two words")

id 2 --> because it has one match (sad)

id 3 --> because it has one match (sad)

I'll eventually want to echo all fields except keywords, but I think I can take care of that part once I get the finding and sorting taken care of. Something like this is how I will format it once I get the search engine happening:

<?xml version="1.0" encoding="iso-8859-1"?>
<matches>

<cue id="1" url="music/heart.mp3" name="In The Heart">
	<composer>Michael Jackson</composer>
	<time>3000</time>
	<description>Do the Beat to the heart</description>
</cue>

<cue id="4" url="music/credits.mp3" name="Perfect Ending">
	<composer>Michael Thompson</composer>
	<time>1421</time>
	<description>Music over the credits</description>
</cue>

      ....continue on......

</matches>

Thanks for your help

Barand · April 8, 2007

I can't even copy that test data as it's an image.

Can you attach a dump of some test data so I can load it.

flash gordon · April 8, 2007

-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: Apr 08, 2007 at 12:46 PM
-- Server version: 5.0.27
-- PHP Version: 5.2.0
-- 
-- Database: `digit`
-- 

-- --------------------------------------------------------

-- 
-- Table structure for table `cues`
-- 

CREATE TABLE `cues` (
  `id` int(11) NOT NULL auto_increment,
  `url` varchar(255) collate latin1_general_ci NOT NULL,
  `name` tinytext collate latin1_general_ci NOT NULL,
  `composer` tinytext collate latin1_general_ci NOT NULL,
  `time` int(11) NOT NULL,
  `description` text collate latin1_general_ci NOT NULL,
  `keywords` text collate latin1_general_ci NOT NULL,
  `demo` tinyint(1) NOT NULL,
  `saved` tinyint(1) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=5 ;

-- 
-- Dumping data for table `cues`
-- 

INSERT INTO `cues` VALUES (1, 'music/heart.mp3', 'In The Heart', 'Michael Jackson', 3000, 'Do the Beat to the heart', 'monkey, sad, foo, mustard, Ron', 0, 1);
INSERT INTO `cues` VALUES (2, 'music/mudd.mp3', 'Mudd ', 'Michael Jackson', 4352, 'In the Mudd', 'foodinkle, Ron, mud, mudd, sad ', 0, 1);
INSERT INTO `cues` VALUES (3, 'music/tense.mp3', 'Movie Music', 'Michael Jackson', 7452, 'Used a film ', 'movie, music, ron, sad, happy', 0, 1);
INSERT INTO `cues` VALUES (4, 'music/credits.mp3', 'Perfect Ending', 'Michael Thompson', 1421, 'Music over the credits', 'sad, short, minor, mud, complex, two words,', 0, 1);

Thanks mate

Barand · April 8, 2007

As data more complex than envisaged in earlier solution, resorted to PHP rather than just SQL

<?php
include '../test/db2.php';

$search = array('foo', 'sad', 'two words', 'monkey');

$sql = "SELECT id, composer, keywords FROM cues";
$res = mysql_query($sql);
$data = array();
/**
* create an array of keywords from both fields
*/
while (list($i, $c, $k)= mysql_fetch_row($res)) {
    echo "$i, $c, $k<br>";
    $a = explode(' ', $c);
    $b = explode(',', $k);
    $tmp = array_merge($a, $b);
    /**
    * trim unwanted spaces from keywords
    */
    foreach($tmp as $k=>$v) {
        $tmp[$k] = trim($v);
    }
    $data[$i] =  $tmp;
}


/**
* find matching words
*/
foreach ($data as $k=>$a) {
    $data[$k] = array_intersect($a, $search);
}

/**
* sort the array based on jeyword content
*/
uasort ($data, 'mysort');

function mysort ($a, $b)
{
    $ka = count($a);
    $kb = count($b);
    
    if ($ka==$kb) return 0;
    return ($ka > $kb) ? -1 : 1;
}

/**
* view sorted array
*/
echo '<pre>Results ', print_r($data, true), '</pre>';
?>

flash gordon · April 8, 2007

Thanks for the reply Barand.

Is there any way to do this, where I'm not selecting everything in the database and then looping through it? The database is going to have 10,000+ records in it. Is it possible to do it why mySQL like what you had above?

$sql = "select a, COUNT(*) as n FROM tableB
        WHERE a IN ('foo', 'bar', 'cat')
        GROUP BY a
        ORDER BY n DESC";

If not, at least I got something that words

Thanks again, mate.

Barand · April 8, 2007

[code]
CREATE TEMPORARY TABLE tmp_cues
SELECT id  FROM cues WHERE keywords LIKE '%two words%'
UNION
SELECT id FROM cues WHERE keywords LIKE '%sad%'
UNION
SELECT id FROM cues WHERE keywords LIKE '%foo%'
UNION
SELECT id FROM cues WHERE keywords LIKE '%monkey%' ;

followed by


SELECT id, COUNT(*) as t FROM tmp_cues
GROUP BY id
ORDER BY t DESC

[/code]

flash gordon · April 8, 2007

in your opinion, which do you think would execute faster? (but I won't be able to use %foo% because that would also give me foobar or foodinkle).

Cheers

Barand · April 8, 2007

I'd redesign the table so you had a keyword table, each row with a single keyword + id field. That would make searching far easier.

You may need REGEX() function for the foo% problem (but I'm useless at those) or search for "foo,%" but then youd have to guarantee the last keyword in each record had a comma following it.

As for time, try both ways, getting microtime() at beginning and end and checking difference.

flash gordon · April 9, 2007

I'd redesign the table so you had a keyword table, each row with a single keyword + id field. That would make searching far easier.

You mean just a TABLE devoted entirely to "keywords". And then another table with all my other data?

So table KEYWORD would look like this

id | keywords

and table CUES would look like this

is that what you mean?

Barand · April 9, 2007

That's it.

[pre]

1 | monkey

1 | sad

1 | foo

1 | mustard

1 | Ron

2 | foodinkle

2 | Ron

2 | mud

2 | mudd

2 | sad

[/pre]

Now you can use "WHERE keyword = 'foo' " and not pull foodinkle and the SQL becomes nore straightforward as in my first post

flash gordon · April 9, 2007

I'm not following you....

Are you saying just 1 keyword per mp3 record? That doesn't seem to optimal to me....but is that what you are really saying. I need several keywords per mp3 and not just one.

This is getting frustrating...

Barand · April 9, 2007

Look at the data

There are 5 keywords for id 1 and 5 for id 2

flash gordon · April 9, 2007

i have no idea what you are trying to say.....

but thanks again for all the help thus far

Barand · April 9, 2007

[pre]

ID| KEYWORD

--+-----------

1 | monkey -+

1 | sad |

1 | foo +-- 5 keywords for id = 1

1 | mustard |

1 | Ron -+

2 | foodinkle -+

2 | Ron |

2 | mud +-- 5 keywords for id = 2

2 | mudd |

2 | sad -+

[/pre]

flash gordon · April 10, 2007

got ya.....let me have day or so to see what I can come up with.

Thanks again!

flash gordon · April 27, 2007

Hey again....long time but I'm still working on it. I've been looking on the web, and I can't see how to run the SQL command of CREATE TEMPORARY TABLE tmp_cues

Here is why my codes looks like now (oh by the way, thanks for the RegEx hit!)

$search = "two words";
$sql = "CREATE TEMPORARY TABLE tmp_cues";
mysql_query($sql);
$sql = "SELECT id, url, name, composer, time, description FROM cues WHERE keywords REGEXP '([,;. ]|^)+$search([,;. ]|$)+'";
$result = mysql_query($sql) or die (mysql_error());

$sql = "SELECT id, COUNT(*) as t FROM tmp_cues GROUP BY id ORDER BY t DESC";
$result = mysql_query($sql) or die (mysql_error());

header("Content-Type: application/xml; charset=ISO-8859-1");
echo '<?xml version="1.0" encoding="iso-8859-1"?>' . "\n";
echo '<matches>' . "\n";
while($row = mysql_fetch_array($result))
{
echo "\t" .   '<cue id="' . $row['id'] . '">' .  "\n";
echo "\t\t" . '<url>' . $row['url'] . '</url>' . "\n";
echo "\t\t" . '<name>' . $row['name'] . '</name>' . "\n";
echo "\t\t" . '<composer>' . $row['composer'] . '</composer>' . "\n";
echo "\t\t" . '<time>' . $row['time'] . '</time>' . "\n";
echo "\t\t" . '<description>' . $row['description'] . '</description>' . "\n";
echo "\t" .   '</cue>' . "\n\n"; 
}

echo '</matches>';

Error message: Table 'digit.tmp_cues' doesn't exist

but if I concatinate the sql's I get this:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\xampp\htdocs\test.php on line 26

flash gordon · April 27, 2007

Never mind....I don't know how I fixed it but I got 1 final problem. WHOO HOO!!!!

The data isn't sorted:

// search word
$search = "sad";
$search2 = "two words";




/*
* SQL QUERIES
*/

// put data into temporary table
$sql = "CREATE TEMPORARY TABLE tmp_cues";
$sql .= " SELECT id, url, name, composer, time, description FROM cues WHERE keywords REGEXP '([,;. ]|^)+$search([,;. ]|$)+'";
$sql .= " UNION";
$sql .= " SELECT id, url, name, composer, time, description FROM cues WHERE keywords REGEXP '([,;. ]|^)+$search2([,;. ]|$)+'";
$result = mysql_query($sql) or die (mysql_error());

// get date from temporary table and sort
$sql = " SELECT id, url, name, composer, time, description, COUNT(*) as t FROM tmp_cues GROUP BY id ORDER BY t DESC";
$result = mysql_query($sql) or die (mysql_error());




/*
* ECHO DATA OUT TO SCREEN
*/
header("Content-Type: application/xml; charset=ISO-8859-1");
echo '<?xml version="1.0" encoding="iso-8859-1"?>' . "\n";
echo '<matches>' . "\n";
while($row = mysql_fetch_array($result))
{
echo "\t" .   '<cue id="' . $row['id'] . '">' .  "\n";
echo "\t\t" . '<url>' . $row['url'] . '</url>' . "\n";
echo "\t\t" . '<name>' . $row['name'] . '</name>' . "\n";
echo "\t\t" . '<composer>' . $row['composer'] . '</composer>' . "\n";
echo "\t\t" . '<time>' . $row['time'] . '</time>' . "\n";
echo "\t\t" . '<description>' . $row['description'] . '</description>' . "\n";
echo "\t" .   '</cue>' . "\n\n"; 
}

echo '</matches>';

Sign In

search 2 fields in a database and sort results

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information