searching and sorting SQL

flash gordon · May 9, 2007

Hi,

I have gotten a lot of help from some people here which I am very thankful for. However, I still a problem with the script. The best way to explain it is by viewing it yourself.

hxxp://localhost/search.php?search=one,two

hxxp://localhost/search.php?search=two,one

should display the same results. However, it does not. The latter one works correctly. However, the first one doesn't seem to sort. Attached and posted are the scripts. Any help would be very much appreciated.

Cheers

<?php


/*
* DATABASE INFO
*/
$username = "";
$password = "";
$db       = "digit";
$table    = "cues"; 

// Conect to the database.
mysql_connect('localhost', $username, $password) or die ("Could not connect");
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)-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>';

?>

mySQL data

-- phpMyAdmin SQL Dump
-- version 2.9.1.1
-- http://www.phpmyadmin.net
-- 
-- Host: localhost
-- Generation Time: May 09, 2007 at 04:37 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=6 ;

-- 
-- 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', 'one', 0, 1);
INSERT INTO `cues` VALUES (2, 'music/mudd.mp3', 'Mudd ', 'Michael Jackson', 4352, 'In the Mudd', 'one, two', 0, 1);
INSERT INTO `cues` VALUES (3, 'music/tense.mp3', 'Movie Music', 'Michael Jackson', 7452, 'Used a film ', 'one, two, three', 0, 1);
INSERT INTO `cues` VALUES (4, 'music/credits.mp3', 'Perfect Ending', 'Michael Thompson', 1421, 'Music over the credits', 'one, two, three, four', 0, 1);
INSERT INTO `cues` VALUES (5, 'music/bogus.mp3', 'Five alive', 'Poo nannie', 45321, 'Sound of all times', 'one, two, three, four, five', 0, 0);

[attachment deleted by admin]

btherl · May 10, 2007

Have you tried printing out all your mysql queries (for both the call that works and the one that doesn't) and inspecting them? Often you can see what's wrong when you take a look.

Edit: The other approach I use to fix 90% of my bugs is just to print out the value of variables throughout the script, using var_dump() or print_r(). Most bugs can be fixed this way. You can print out "< pre >" just before dumping the data to make it readable on an HTML page.

flash gordon · May 10, 2007

Honestly bro, the scripts are over my head right now. I tired paying 2 people (from here) to do it for me but both bolted. PHP isn't really my thing, but I'd like to learn more about it. In other words I don't know how to: "printing out all your mysql queries"

btherl · May 10, 2007

ok, understood

You have two options here.. one is to pay someone else to do it (and risk having them bolt again). The other is to learn enough yourself that you can fix it with our assistance.

What we probably won't do (unless we're bored) is fix it completely for you, but we can guide you on what to do and what to learn to fix it yourself.

Can you add this code into your script:

$sql .= $sql2;
echo "<pre>";
echo "$sql\n";
echo "</pre>";

The first line is already in your script.. I included that to show you were to add it.

Then, copy and paste the EXACT output and post it here. It should look like "CREATE TEMPORARY TABLE ...." and a whole lot more, and may be a few lines long.

flash gordon · May 10, 2007

added it.

When I ran the url as this, I get

hxxp://localhost/search.php?search=one,two

<pre>CREATE TEMPORARY TABLE tmp_cues SELECT id, url, name, composer, time, description FROM cues WHERE keywords REGEXP '([,;. ]|^)+one([,;. ]|$)+' UNION SELECT id, url, name, composer, time, description FROM cues WHERE keywords REGEXP '([,;. ]|^)+two([,;. ]|$)+'
</pre><?xml version="1.0" encoding="iso-8859-1"?>
<matches>
<cue id="1">
	<url>music/heart.mp3</url>
	<name>In The Heart</name>
	<composer>Michael Jackson</composer>
	<time>3000</time>

	<description>Do the Beat to the heart</description>
</cue>

<cue id="2">
	<url>music/mudd.mp3</url>
	<name>Mudd </name>
	<composer>Michael Jackson</composer>

	<time>4352</time>
	<description>In the Mudd</description>
</cue>

<cue id="3">
	<url>music/tense.mp3</url>
	<name>Movie Music</name>

	<composer>Michael Jackson</composer>
	<time>7452</time>
	<description>Used a film </description>
</cue>

<cue id="4">
	<url>music/credits.mp3</url>

	<name>Perfect Ending</name>
	<composer>Michael Thompson</composer>
	<time>1421</time>
	<description>Music over the credits</description>
</cue>

<cue id="5">

	<url>music/bogus.mp3</url>
	<name>Five alive</name>
	<composer>Poo nannie</composer>
	<time>45321</time>
	<description>Sound of all times</description>
</cue>

</matches>

When I run it as this, I get

hxxp://localhost/search.php?search=two,one

<pre>CREATE TEMPORARY TABLE tmp_cues SELECT id, url, name, composer, time, description FROM cues WHERE keywords REGEXP '([,;. ]|^)+two([,;. ]|$)+' UNION SELECT id, url, name, composer, time, description FROM cues WHERE keywords REGEXP '([,;. ]|^)+one([,;. ]|$)+'
</pre><?xml version="1.0" encoding="iso-8859-1"?>
<matches>
<cue id="2">
	<url>music/mudd.mp3</url>
	<name>Mudd </name>
	<composer>Michael Jackson</composer>
	<time>4352</time>

	<description>In the Mudd</description>
</cue>

<cue id="3">
	<url>music/tense.mp3</url>
	<name>Movie Music</name>
	<composer>Michael Jackson</composer>

	<time>7452</time>
	<description>Used a film </description>
</cue>

<cue id="4">
	<url>music/credits.mp3</url>
	<name>Perfect Ending</name>

	<composer>Michael Thompson</composer>
	<time>1421</time>
	<description>Music over the credits</description>
</cue>

<cue id="5">
	<url>music/bogus.mp3</url>

	<name>Five alive</name>
	<composer>Poo nannie</composer>
	<time>45321</time>
	<description>Sound of all times</description>
</cue>

<cue id="1">

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

</matches>

I don't understand how the order makes a difference....

Cheers mate

btherl · May 10, 2007

Hmm.. aren't those the same results, just in a different order? Is it important for you to have your results in order of id?

flash gordon · May 10, 2007

Yea, same results but different order. The order is very important.

id 1 has "one" as a keyword

id 2 has "one, two" as keywords

....

id 5 has "one, two, three, four, five" as keywords.

Therefore, search.php?search=one,two should list all ids with ids 2-5 at the top because they have the most matches and id 1 only has 1 match.

Any ideas what is going on? Could it be my RegEX is screwing it up?

flash gordon · May 14, 2007

**bump**

marf · May 15, 2007

on a side note I was lookin through it

what does

REGEXP '([,;. ]|^)+one([,;. ]|$)+'

I'm sure thats not the problem, just curious.

marf · May 15, 2007

Ok, I think I have a way, not sure if it works as I can't test it. Basically first we put all the values we get from the MySQL query into an array. Then we want to sort the array by the id field. That's the part I'm not sure I have correct.

Replace the stuff after this

/*

* ECHO DATA OUT TO SCREEN

*/

with

/*
* ECHO DATA OUT TO SCREEN
*/

//This populates our array
$i=0;
while($row = mysql_fetch_array($result)){
    $myarray[$i] = $row;
    $i++;
}

$sortedarray = sort($myarray[]['id'])

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

The other thing I was thinking, seems too easy to be true, is in your long, long query, can you just throw on the end ORDER BY id ?

flash gordon · May 15, 2007

Ok, I think I have a way, not sure if it works as I can't test it. Basically first we put all the values we get from the MySQL query into an array. Then we want to sort the array by the id field. That's the part I'm not sure I have correct.

Why am I ordering my id? That's not how I'm trying to sort it.

You code gave me this error message

Warning: sort() expects parameter 1 to be array, null given in C:\Program Files\xampp\xampp\htdocs\search.php on line 71

Thanks

flash gordon · May 16, 2007

After trying to pay 2 different people from here, having them bail on me, and a couple of post, I'm not going to get this answered here, am I?

Thanks for the help getting me this far, anyway.

Cheers

flash gordon · May 18, 2007

I'll pay for a fix.

SocomNegotiator · May 18, 2007

Hey flash my partner and I are interested in fixing the problem for you. Please email me or contact me through MSN-kb_hoopstar01@msn.com

Thanks for your time.

per1os · May 18, 2007

Looking at this script, I would only rely on MySQL to do some of the work. PHP is very fast and efficient, you want your MySQL server freed up more or less. I never used the regex in MySQL, but if I were doing thing I would go about it like this:

<?php


/*
* DATABASE INFO
*/
$username = "";
$password = "";
$db       = "digit";
$table    = "cues"; 

// Conect to the database.
mysql_connect('localhost', $username, $password) or die ("Could not connect");
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 LIKE '" . $search[$i] . "') OR ";
}

$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($sql2)) {
    $rows[$row['id']] = $row; // put data into an array.
    $keywords[$row['id']] = $row['keywords'];
}

// functions created by FrosT forrelation
function relatedTest($mainArticle, $articles) {
$mainArticle = $mainArticle;
$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)) {
	foreach ($words as $word) {
		foreach ($compwords as $compword) {
			if (strtolower($compword) == strtolower($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>';

?>

See if that helps any, untested and unsure.

per1os · May 18, 2007

EDIT: Sorry I missed the 5 minute time frame, here is correct workng code:

<?php


/*
* DATABASE INFO
*/
$username = "";
$password = "";
$db       = "digit";
$table    = "cues"; 

// Conect to the database.
mysql_connect('localhost', $username, $password) or die ("Could not connect");
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 LIKE '%" . $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($sql2)) {
    $rows[$row['id']] = $row; // put data into an array.
    $keywords[$row['id']] = $row['keywords'];
}

// functions created by FrosT forrelation
function relatedTest($mainArticle, $articles) {
$mainArticle = $mainArticle;
$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)) {
	foreach ($words as $word) {
		foreach ($compwords as $compword) {
			if (strtolower($compword) == strtolower($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>';

?>

flash gordon · May 18, 2007

[quote author=frost110 link=topic=139990.msg602245#msg602245 date=1179518610]
EDIT: Sorry I missed the 5 minute time frame, here is correct workng code:

[code]
         $searchStr .= "(keywords LIKE '%" . $search[$i] . "%') OR "; // added percentage and single quote for propery syntax.
}

I can't use LIKE because search "foo" can NOT hit foodinkle. With like it will.

And don't worry about the "5 minute time frame". I'd rather pay a help member with 1K+ post than someone here just to look for jobs. But I'll pay whomever I have to.

Also, you code gives me many errors.[/code]

per1os · May 18, 2007

Well maybe replace that part your regex. See if that works for your sorting issue.

flash gordon · May 18, 2007

code as is outputs:

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

Warning: Invalid argument supplied for foreach() in C:\Program Files\xampp\xampp\htdocssearch_fix.php on line 45

Warning: arsort() expects parameter 1 to be array, null given in C:\Program Files\xampp\xampp\htdocs\search_fix.php on line 56

Warning: Cannot modify header information - headers already sent by (output started at C:\Program Files\xampp\xampp\htdocs\search_fix.php:35) in C:\Program Files\xampp\xampp\htdocs\search_fix.php on line 99

Warning: Invalid argument supplied for foreach() in C:\Program Files\xampp\xampp\htdocs\search_fix.php on line 102

per1os · May 18, 2007

<?php


/*
* DATABASE INFO
*/
$username = "";
$password = "";
$db       = "digit";
$table    = "cues"; 

// Conect to the database.
mysql_connect('localhost', $username, $password) or die ("Could not connect");
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($sql2)) {
    $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');
             }
$mainArticle = $mainArticle;
$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)) {
	foreach ($words as $word) {
		foreach ($compwords as $compword) {
			if (strtolower($compword) == strtolower($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>';

?>

Give that a try, just so I know that the code is correct if this does not work, give me a few hours to get home and do some testing of it for you.

flash gordon · May 18, 2007

ran "as is":

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Program Files\xampp\xampp\htdocs\search_fix.php on line 35
No array was given

When I stick the "correct" resource into mysql_fetch_assoc($result) I get this

Warning: arsort() expects parameter 1 to be array, null given in C:\Program Files\xampp\xampp\search_fix.php on line 59

Warning: Cannot modify header information - headers already sent by (output started at C:\Program Files\xampp\xampp\htdocs\search_fix.php:59) in C:\Program Files\xampp\xampp\htdocs\search_fix.php on line 102

Warning: Invalid argument supplied for foreach() in C:\Program Files\xampp\xampp\htdocs\search_fix.php on line 105

Thanks for the hand, bro. Make sure you PM me a price or price range before you start working on this too much at home. I'd hate for there to be a disagreement on price. It's only worth "X" to me, and after that I'm taking a loss on building the Flash application.

per1os · May 18, 2007

How about this, you just give me $25 for beer/pizza money tonight and we call it good? Once I get home I will dig deeper into it and once done I'll let you know my paypal.

flash gordon · May 18, 2007

cheers!

and I'll donate a little to the forums too.

per1os · May 18, 2007

<?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');
             }
$mainArticle = $mainArticle;
$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)) {
	foreach ($words as $word) {
		foreach ($compwords as $compword) {
			if (strtolower($compword) == strtolower($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>';

?>

Just tested it on my machine, it seemed to do what you asked, see if it works. PM'ing my information now just incase ^.-

<matches>

one, two, three, four, five

−
<cue id="5">
<url>music/bogus.mp3</url>
<name>Five alive</name>
<composer>Poo nannie</composer>
<time>45321</time>
<description>Sound of all times</description>
</cue>


one, two, three, four

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


one, two, three

−
<cue id="3">
<url>music/tense.mp3</url>
<name>Movie Music</name>
<composer>Michael Jackson</composer>
<time>7452</time>
<description>Used a film </description>
</cue>


one, two

−
<cue id="2">
<url>music/mudd.mp3</url>
<name>Mudd </name>
<composer>Michael Jackson</composer>
<time>4352</time>
<description>In the Mudd</description>
</cue>


one

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

flash gordon · May 19, 2007

Nothing works except for

search_fix.php?search=one,two

or

search_fix.php?search=two,one

but I need to be able to search for anything.

search_fix.php?search=two,three

or

search_fix.php?search=four,

etc.

Warning: arsort() expects parameter 1 to be array, null given in C:\Program Files\xampp\xampp\htdocs\search_fix.php on line 55

Warning: Cannot modify header information - headers already sent by (output started at C:\Program Files\xampp\xampp\htdocs\search_fix.php:55) in C:\Program Files\xampp\xampp\htdocs\search_fix.php on line 98

Warning: Invalid argument supplied for foreach() in C:\Program Files\xampp\xampp\htdocs\search_fix.php on line 101

Sign In

searching and sorting SQL

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