searching and sorting SQL

[per1os]

<?php
/*
* DATABASE INFO
*/
$username = "";
$password = "";
$db       = "";
$table    = "cues"; 

// Conect to the database.
mysql_connect('localhost', $username, $password) or die (mysql_error());
mysql_select_db($db) or die (mysql_error());

/*
* SEARCH WORDS into SQL STATEMENT
*/
//$search  = "foo";
//$search2 = "sad";
$search = explode(",", $_GET['search']);
if (!isset($_GET['search']) || count($search) < 1 || $_GET['search'] == "") exit();

$sql2 = "";
for ($i=0; $i<count($search); $i++) {
         $searchStr .= "(keywords REGEXP '([,;. ]|^)+" . $search[$i] . "([,;. ]|$)+') OR "; // added percentage and single quote for propery syntax.
}

$searchStr = substr($searchStr, 0, -3); // remove last "OR "
$sql2 = "SELECT id, url, name, composer, time, description, keywords FROM cues WHERE " . $searchStr . " ORDER BY id";
$result = mysql_query($sql2) or die (mysql_error());

while ($row = mysql_fetch_assoc($result)) {
    $rows[$row['id']] = $row; // put data into an array.
    $keywords[$row['id']] = $row['keywords'];
}

// functions created by FrosT forrelation
function relatedTest($mainArticle, $articles) {
    if (!is_array($articles)) {
	die('No array was given');
}

$words = explode(",", $mainArticle);
foreach ($articles as $key => $article) {
	$artWords[$key] = explode(",", $article);
	$matches = compareWords($words, $artWords[$key]);

	if ($matches > 0) {
		$match[$key] = $matches;
	}else {
		unset($artWords[$key]);
	}
}
arsort($match);
return $match;
}

function compareWords($words, $compwords) {
$match = 0;
if (!is_array($words)) {
	$words[0] = $words;
}

foreach ($words as $word) {
		foreach ($compwords as $compword) {
			if (strtolower(trim($compword)) == strtolower(trim($word))) {
				$match++;					
			}
		}
}

return $match;
}

function strlenSort($array) {
// sort array by string length
foreach ($array as $key => $size) {
	$newArray[$key] = strlen($size);
}
arsort($newArray, SORT_NUMERIC);

$i=0;
foreach ($newArray as $key => $size) {
	$returnArr[$i++] = $array[$key];
}

return $returnArr;
}
// end functions
$mainSearch = $_GET['search'];

// grab the array of the related.
$related = relatedTest($mainSearch, $keywords);


/*
* 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";
foreach ($related as $key => $val) {
    $row = $rows[$key];
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>';

?>

 

More thoroughly tested. Try that out.

[md_dev]

Hi Flash,

 

give it a try!

 

SELECT id, url, name, composer, time, description,(keywords REGEXP '([,;. ]|^)+one([,;. ]|$)+') + (keywords REGEXP '([,;. ]|^)+two([,;. ]|$)+') as 'total'

FROM cues WHERE keywords REGEXP '(([,;. ]|^)+one([,;. ]|$)+|([,;. ]|^)+two([,;. ]|$)+)'

order by total desc

 

You just have to add a loop to built up this query.

 

let me know whether it helps.

 

cheers,

Maulik

[flash gordon]

Maulik, that is PERFECT! WHOO HOO! Let me know if you want a little tip for it

 

Frost, I'll still paying you, too. Thanks a lot for your time and your script does work too.

 

<?php


include("database.php");


/*
* SEARCH WORDS into SQL STATEMENT
*/
$search = explode(",", $_GET['search']);
if (!isset($_GET['search']) || count($search) < 1 || $_GET['search'] == "") exit();

$sql = "SELECT id, url, name, composer, time, description,";
$sql2 = "";
$sql3 = " as 'total' FROM cues WHERE keywords REGEXP '(";
$sql4 = "";

for ($i=0; $i<count($search); $i++)
{
$sql2 .= "(keywords REGEXP '([,;. ]|^)+$search[$i]([,;. ]|$)+') + ";
$sql4 .= "([,;. ]|^)+$search[$i]([,;. ]|$)+|";
}

$sql2 = substr($sql2, 0, -3); // remove last " + "
$sql4 = substr($sql4, 0, -1); // remove last "|"
$sql5 .= ")' order by total desc";

$sqlAll = $sql . $sql2 . $sql3 . $sql4 . $sql5; 

//echo $sqlAll;
//exit();

$result = mysql_query($sqlAll) 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>';


?> 

[per1os]

Sweet, well let us know if you have any other issues bud.

[md_dev]

hi flash,

 

great!

 

thanks for offering, if anything else comes will surely let you know 

 

cheers,

Maulik

[flash gordon]

I'm sure I'll be back in the future. Get the paypal, frost?

 

Here one issue already. I don't like being a script kiddy. Can you guys point me to some links to read about those hip SQL queries?

[MadTechie]

mysq manual is a good place to start:)

[Barand]

try

<?php

mysql_query ("CREATE TEMPORARY TABLE cue_keys (
              cueID int,
              keyword varchar(10)
            )");
            
// Normalize keywords into temp table
$sql = 'SELECT id, keywords FROM cues';
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
while (list($id, $kwds) = mysql_fetch_row($res)) {
    $ka = explode (',',$kwds);
    foreach ($ka as $kw) {
        $kw = trim($kw);
        mysql_query ("INSERT INTO cue_keys VALUES ($id, '$kw')");
    }
}

// now search

#$list = 'one,two';
$list = 'two,one';
$list = join("','", explode(',', $list));

$sql = "SELECT c.id, c.description, c.keywords, COUNT(*) as matches
        FROM cues c INNER JOIN cue_keys k
        ON c.id = k.cueID
        WHERE k.keyword IN ('$list')
        GROUP BY c.id
        ORDER BY matches DESC";
$res = mysql_query($sql) or die (mysql_error()."<p>$sql</p>");
while (list($id, $desc, $k, $m) = mysql_fetch_row($res)) {
    echo "$id $desc ($k) : $m matches<br>";
}
?>

 

-->

2 In the Mudd (one, two) : 2 matches

3 Used a film (one, two, three) : 2 matches

4 Music over the credits (one, two, three, four) : 2 matches

5 Sound of all times (one, two, three, four, five) : 2 matches

1 Do the Beat to the heart (one) : 1 matches

 

If you do this regularly, make the cue_keys table permanent