Page 1 of 1

AD0_ACCESS and complex query

Posted: Thu Jan 01, 2004 3:51 pm
by largeinnit
Hi,
What I'm trying to is write a function in php that can use a previously created SQL string to pick out possible words from a dictionary database (part of a scrabble solution I'm working on).

The function accurately connects to the database, but doesn't return any data when I use the sql string $myquery. The annoying thing is that if I change $myquery into a simpler query it returns results - so I'm fairly certain that database code is correct.

If I print $myquery into a webpage and then copy and paste it into a query in access, the query works fine and returns results - so I assume the query syntax is ok. I'm not given any error messages.

I'd be grateful if someone could help me understand whats going on or give some advice on debugging this problem.

Code: Select all

function GetWords()
{
include ("c:\php\adodb331\adodb.inc.php");  
$myquery="SELECT word FROM words WHERE ( length= 8) AND ((word Like 's*') or (word Like '*s') or (word Like '*s*')) AND( (word Like 't*') or (word Like '*t') or (word Like '*t*'))AND( (word Like 'e*') or (word Like '*e') or (word Like '*e*')) AND((word Like 'n*') or (word Like '*n') or (word Like '*n*'))AND( (word Like 'i*') or (word Like '*i') or (word Like '*i*') AND ((word Like 'r*') or (word Like '*r') or (word Like '*r*')) AND ( (word Like 'e*') or (word Like '*e') or (word Like '*e*')) AND ( (word Like 's*') or (word Like '*s') or (word Like '*s*')));";
    
	$cnx=NewADOConnection('ado_access');
    $access="c:\php\wordsortedall.mdb";
    $myDsn= 'PROVIDER=Microsoft.Jet.OLEDB.4.0;'
			. 'DATA SOURCE=' .$access . ';' ;
    $cnx->PConnect($myDsn,"","","");
    $cnx->debug=true;
    $cur=$cnx->Execute("$myquery") or die("Error in query: $myquery  " . $cnx->ErrorMsg());  
    $nbrow=0;   //Local variable to count number of rows  

	if ($cur===false) die("failed");
	while (!$cur->EOF)			//returns results
	{
         $word=$cur->fields[0];
			print($word);
		 $nbrow++;	
		       $cur->MoveNext();
    } //while
	
	 print("<p><b>$nbrow words returned</b></p>");  //output number of results
     $cnx->Close();

}//GetWords
THANKS IN ADVANCE
Dan

[Edit: Added php tags for eyecandy. --JAM]

Posted: Thu Jan 01, 2004 4:51 pm
by JAM
You are using * as wildcards, but in SQL it should be %.
Try that instead...

Code: Select all

select foo from bar where moo = '%end' or moo = '%mid%' or moo = 'start%'
Edit:
Massrenamed, not sure it was correct tho ;):

Code: Select all

$myquery="SELECT word FROM words WHERE ( length= 8) AND ((word Like 's%') or (word Like '%s') or (word Like '%s%')) AND( (word Like 't%') or (word Like '%t') or (word Like '%t%'))AND( (word Like 'e%') or (word Like '%e') or (word Like '%e%')) AND((word Like 'n%') or (word Like '%n') or (word Like '%n%'))AND( (word Like 'i%') or (word Like '%i') or (word Like '%i%') AND ((word Like 'r%') or (word Like '%r') or (word Like '%r%')) AND ( (word Like 'e%') or (word Like '%e') or (word Like '%e%')) AND ( (word Like 's%') or (word Like '%s') or (word Like '%s%')));";

Jam you're a genius!

Posted: Fri Jan 02, 2004 2:50 pm
by largeinnit
Jam that worked a treat, cheers.

Replace the '*' with '%' and it works fine. Funnily tho, if you type the % instead of * directly into an access query and run it from access 2000 it won't return any results. I've also shortened the query, as '%s','s%' is a subset of '%s%'. E.g:

SELECT words.word FROM words WHERE ((words.length)=8) AND (((words.word) Like '%h%')) AND( ((words.word) Like '%c%'))AND(((words.word) Like '%t%')) AND( ((words.word) Like '%a%')) AND(((words.word) Like '%t%') AND (((words.word) Like '%i%')) AND ( ((words.word) Like '%r%')) AND (((words.word) Like '%e%')));