search 2 fields in a database and sort results

[flash gordon]

nevermind....

 

[flash gordon]

hxxp://localhost/test.php?search=foo,sad

hxxp://localhost/test.php?search=sad,foo

 

the results are different, but they should be the same?????

 

/*
* SEARCH WORDS into SQL STATEMENT
*/
//$search  = "foo";
//$search2 = "sad";
$search = explode(",", $_GET['search']);

$sql2 = "";
for ($i=0; $i<count($search)-1; $i++)
{
$sql2 .= " SELECT id, url, name, composer, time, description FROM cues WHERE keywords REGEXP '([,;. ]|^)+$search[$i]([,;. ]|$)+'";
$sql2 .= " UNION";
}
$last = count($search)-1;
$sql2 .= " SELECT id, url, name, composer, time, description FROM cues WHERE keywords REGEXP '([,;. ]|^)+$search[$last]([,;. ]|$)+'";



/*
* SQL QUERIES
*/

// put data into temporary table
$sql = "CREATE TEMPORARY TABLE tmp_cues";
$sql .= $sql2;
//$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 ASC";
$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>';

[flash gordon]

I wish I could just attach this to the other threads, but I can't (mod feel free to clean it up)...Attached is the php file and database info.

 

"sad" appears in all rows, and the script does not work when sad appears first e.g. sad,foo However it works fine as foo, sad.

 

Cheers

 

[attachment deleted by admin]