[Resolved] WHERE DIFFERENCE query help needed

[rayzun]

Can someone show me a working query which uses the
WHERE DIFFERENCE feature

Example:
$psquery = "select * FROM list WHERE DIFFERENCE(text, ‘$models’) >= 1
ORDER BY DIFFERENCE(text, ‘$models’) DESC";

$psresult = mysql_query($psquery);
while ($row = mysql_fetch_array($psresult))
{
echo $row[text];
}

I get this message:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

I dont know what Im doing wrong???

[bradgrafelman]

It looks like you don't have any results being returned. Is there any chance you could attach a dump of your list table, including structure and data (or just dummy data if the data is confidential/private) in a plain text (.txt file extension) form?

Before you do that, however, you might want to try debugging:

$psquery = "select * FROM `list` WHERE DIFFERENCE(text, ‘$models’) >= 1 
ORDER BY DIFFERENCE(text, ‘$models’) DESC"; 

echo 'Query is: <hr>' . $psquery . '<hr><br><br>';

$psresult = mysql_query($psquery) or die('Error, MySQL said: ' . mysql_error()); 
while ($row = mysql_fetch_array($psresult)) 
{ 
echo $row['text']; 
} 

Also note that I changed "$row[text]" to "$row['text']" simply to clean up the coding. If you don't have the quotes surrounding the word "text", PHP first tries to find the constant 'text' to use as an index. Upon failure, it will generate an E_WARNING, notifying you of this error and that it assumed it to be a string.

At any rate, please try using the above sample debug code to debug your SQL query. If that doesn't work, go ahead and ATTACH (not copy and paste into a post) a .txt file of the structure & data of that table.

For more information and awesome tips on debugging SQL queries, check out the Debugging 101 thread.

[rayzun]

I gave it a shot, however the table is such that only to fields exist..

1. ID auto-increment
2. word - a list of words basic 4-5 letter words

I did find what I think does similar query:

SELECT word
FROM dictionary
WHERE word LIKE 'mon%'
ORDER BY ABS( ORD( UPPER( SUBSTRING( word, 1, 1 ) ) ) -
ORD( UPPER( SUBSTRING( 'mon%', 1, 1 ) ) ) ) ,
ABS( ORD( UPPER( SUBSTRING( word, 2, 1 ) ) ) -
ORD( UPPER( SUBSTRING( 'mon%', 2, 1 ) ) ) ) ,
ABS( ORD( UPPER( SUBSTRING( word, 3, 1 ) ) ) -
ORD( UPPER( SUBSTRING( 'mon%', 3, 1 ) ) ) ) ,
ABS( ORD( UPPER( SUBSTRING( word, 4, 1 ) ) ) -
ORD( UPPER( SUBSTRING( 'mon%', 4, 1 ) ) ) )
LIMIT 0 , 3

Im starting to find this type of query will be un-useful
My original thoughts were to compare words with the SOUNDEX & DIFFERENCE somehow..

Anyways thanks for the efforts.