Mysql query works in phpmyadmin but not in php script

[johniem]

I have a really weird problem.

I'm trying to run a mysql query that works fine in phpmyadmin but in php script is giving me an error.

The query is:

 

(SELECT DISTINCT art.`TEMPLATE`,gal.`ARTICLE_ID`,art.`TITLE`,art.`DESCRIPTION`,MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('WORD*' IN BOOLEAN MODE) AS score 
FROM articles art,galeries gal 
WHERE gal.`ARTICLE_ID`=art.`ARTICLE_ID` AND MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) 
AGAINST ('WORD* ' IN BOOLEAN MODE)) 
UNION 
(SELECT DISTINCT `TEMPLATE`,`ARTICLE_ID`,`TITLE`,`DESCRIPTION`,MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) 
AGAINST ('WORD* ' IN BOOLEAN MODE) AS score 
FROM articles 
WHERE (MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('WORD* ' IN BOOLEAN MODE))) 
ORDER BY score DESC LIMIT 0,30

 

Snipset from php script code:

 

function search($start_row,$ammount,$search_key,$pages){
        $start_row = intval($start_row) * $ammount;

        $return_val = "";
        $symbols = array('/','\\','\'','"',',','.','<','>','?',';',':','[',']','{','}','|','=','+','-','_',')','(','*','&','^','%','$','#','@','!','~','`'	);//this will remove punctuation
        $pattern = "#[^(\w|α|β|γ|δ|ε|ζ|η|θ|ι|κ|λ|μ|ν|ξ|ο|π|ρ|σ|τ|υ|φ|χ|ψ|ω|Α|Β|Γ|Δ|Ε|Ζ|Η|Θ|Ι|Κ|Λ|Μ|Ν|Ξ|Ο|Π|Ρ|Σ|Τ|Υ|Φ|Χ|Ψ|Ω|ς|ά|έ|ό|ί|ύ|ώ|ή|ϊ|ϋ|ΐ|ΰ|Ά|Έ|Ό|Ί|Ύ|Ώ|Ή|Ϊ|Ϋ|\d|\'|\"|\.|\!|\?|;|,|\\|\/|\-|:|\&|@)]+#";

        $search_key = greek_text::to_upper($search_key);
        $wc = strip_tags($search_key);
        $wc = preg_replace($pattern, " ", $wc);

        for ($i = 0; $i < sizeof($symbols); $i++) {

                $wc = str_replace($symbols[$i],' ',$wc);
        }

        $wc = str_replace("΄", " ", $wc);
        $wc = str_replace(chr(162), " ", $wc);

        if( !$keep_numbers ) {
            $wc = preg_replace('#(^|\s+)[\d\s]+(\s+|$)#',' ',$wc);
            $pattern = '#(^|\s+)([0-9]+[a-zA-ZαβγδεζηθικλμνξοπρστυφχψωΑΒΓΔΕΖΗΘΙΚΛΜΝΞΟΠΡΣΤΥΦΧΨΩςάέόίύώήϊϋΐΰΆΈΌΊΎΏΉΪΫ]+\s*)+(\s+|$)#';
            preg_match($pattern,$wc,$aa);
            $wc = preg_replace($pattern, " ", $wc);
        }
        $wc = trim(preg_replace("/\s\s+/", " ", $wc));
        $wc = explode(" ", $wc);
        $cleaned_keyword = array_filter($wc);

        $cleaned_keyword = greek_text::removeStopWordsFromArray($cleaned_keyword);
        $stemmed_keywords = greek_text::stemWordsArray($cleaned_keyword);

        $query = "(SELECT DISTINCT art.`TEMPLATE`,gal.`ARTICLE_ID`,art.`TITLE`,art.`DESCRIPTION`,MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('";

        while(list($key,$val)=each($stemmed_keywords)){
                if($val<>" " and strlen($val) > 1){
                        $query .= $val."*";
                        $search_keys .= $val."* ";

                }
        }

        $query .= "' IN BOOLEAN MODE) AS score FROM articles art,galeries gal WHERE gal.`ARTICLE_ID`=art.`ARTICLE_ID` AND MATCH(art.`TITLE`,art.`DESCRIPTION`,gal.`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE))";
        $query .= " UNION (SELECT DISTINCT `TEMPLATE`,`ARTICLE_ID`,`TITLE`,`DESCRIPTION`,MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE) AS score FROM articles WHERE (MATCH(`TITLE`,`DESCRIPTION`,`CONTENT`) AGAINST ('".$search_keys."' IN BOOLEAN MODE))) ORDER BY score DESC LIMIT ".intval($start_row).",".$ammount;
                
        $rs = $this->dbActions->execQuery($query);
        $this->dbActions->execQuery("INSERT INTO searches (`KEY`,`DATE`,`RESULTS`) VALUES ('".$search_key."',NOW(),".$pages.")");
        $search_results = "<div id='results'>";
        while($row = mysql_fetch_array($rs)){           
           $search_results.= "<div id='result'>";
           $search_results.= "<div class='result_title'><h4><a href='article.php?articleId=".$row["ARTICLE_ID"]."'>".$row['TITLE']."</a></h4></div>";
           $search_results.= "<div class='result_description'>".$row['DESCRIPTION']."</div>";
           $search_results.= "</div>";
           $search_results.= "<div class='result_seperator'></div>";
        }
        $search_results .= "</div>";
        $return_val = $search_results;

        return $return_val;
    }

 

dbactions class:

 

require 'includes/errors.php';
error_reporting(0);

class DBActions{

var $dbCon;
var $errorHandler;

function DBActions(){			
	$this->dbCon = $this->dbCon();
	$errorHandler = new errors(1);
}

private function dbCon(){	
	require 'conf/configuration.php';
	$dbcon = mysql_connect($dbUrl,$dbUser,$dbPass);
	if(!$dbcon)trigger_error("Unable to connect to database $dbUrl for user $dbUser",E_USER_ERROR);
	mysql_select_db($dbName);
	mysql_query("SET NAMES 'UTF8'");
	mysql_query('set character set utf8');

	return $dbcon;
}

function dbClose(){		
	if($this->dbCon)
		mysql_close($this->dbCon);		
}

function execQuery($query){		              
                $result = mysql_query($query,$this->dbCon);
                                               
	$msg = "Unable to execute query ".$query;
                if(mysql_num_rows($result) > 0)
		return $result;
	else if($result == false)		
		trigger_error($msg,E_USER_ERROR);
	else
		return $result;
}

function send_error_mail(){
	$this->errorHandler->sendErrorEmail();
}
}

 

I've try everything but can't get this working..

Any healp whould be really appreciated.

 

Thanks in advance.

[gizmola]

Echo out $query before you execute and cut and paste that into phpmyadmin.  I bet you get an error.

[Pikachu2000]

And what would the error be?

[johniem]

The query I posted is the echoed one.

Php doesn't through any error that is why I can't find any solution.

I really can't figure it out. My code worked fine so far for the queries I tried .

[gizmola]

Ok, are you using php4 because you're using php4 oop syntax?

 

If you're using php4, then your initialization of a database handle in the constructor is not going to work unless you force it to pass by reference.  You didn't post the code that defines the class... there's just this call

 

$rs = $this->dbActions->execQuery($query);

 

You have "function search" but no class definition.  Why would you be using $this-> unless function search is a class method?  Either way we need to see where you are instantiating the dbActions object.  It looks like what is happening is that your database class wrapper isn't working.

 

 

[johniem]

My vps has php 5.2.16 installed.

search() is a class method indeed. I'm defining dbActions in that class.

The dbActions class is working perfectly with other methods in the class.

Only the current query causes me problems.

 

[Jessica]

in php script is giving me an error.

 

You still haven't posted the error, have you?

[johniem]

in php script is giving me an error.

 

You still haven't posted the error, have you?

 

Php doesn't through any errors.

I have my own error handling but if I remove it and just use mysql_error() nothing happens.

[Jessica]

in php script is giving me an error.

 

You still haven't posted the error, have you?

 

Php doesn't through any errors.

I have my own error handling but if I remove it and just use mysql_error() nothing happens.

 

1. Throw

2. You said it gives you an error. Then you said it doesn't, but it does.

How do you know it's not working.

|

V

[johniem]

Cause i tried to remove my error handling and just use the default error handling and i just don't get anything.. mysql_error() doesn't through anything. The error that I said is the just my custom error without mysql_error() that doesn't through anything. Here is my error class:

 

<?php
class errors{

var $debugLevel = 0;
var $error_str = "";
var $error_num = "";

function errors($debugLevel=0){

	$this->debugLevel = $debugLevel;		
	set_error_handler(array($this,"errorHandle"));
}

function errorHandle($errno,$errstr,$errfile,$errline,$errorcontext){
	require "conf/configuration.php";

	$this->error_str = $errstr;
	$this->error_num = $errno;

	switch ($errno) {

		case E_USER_WARNING:
			echo "<h3>$domainName WARNING [$errno] $errstr</h3>";
			echo "<p>Warning on line $errline in file $errfile</p><br/>";
			break;

		case E_USER_NOTICE:
			echo "<h3>$domainName NOTICE [$errno] $errstr</h3>";
			echo "<p>Notice on line $errline in file $errfile</p><br/>";
			break;

		case E_USER_ERROR:
			echo "<h3>$domainName ERROR</h3>";
			echo "<h4>Error number [$errno] $errstr</h4>";
			echo "<p>Fatal error on line $errline in file $errfile</p><br/>";
			echo "<p>$errorcontext</p>";
			echo "<h5>System informations</h5>";
			echo "<p>PHP version : " . PHP_VERSION ." - OS : " . PHP_OS . "</p>";
			echo "Stopping script execution...";
			exit(1);
			break;

		case E_STRICT:
			echo "<h3>$domainName E_STRICT [$errno] $errstr</h3>";
			echo "<p>E_STRICT on line $errline in file $errfile</p><br/>";
			echo "<h5>System informations</h5>";
			echo "<p>PHP version : " . PHP_VERSION ." - OS : " . PHP_OS . "</p>";
			break;
	}
}

function exceptionHandler($exception){
	echo "Uncaught exception: " . $exception->getMessage() . "\n";
}

function sendErrorEmail(){
	$to = "maris@vnet-marketing.com";
	$subject = "Error at $site_name";
	$random_hash = md5(date('r',time()));
	$headers = "From: $site_email\r\nReply-To: $site_email";
	$headers .= "\r\nContent-Type: multipart/alternative; boundary=\"PHP-alt-".$random_hash."\"";
	ob_start();
	echo "--PHP-alt-$random_hash";
	echo "Content-Type: text/html; charset=\"utf-8\"";
	echo "Content-Transfer-Encoding: 7bit";
	echo "<h2>Σφάλμα $this->error_num</h2>";
	echo "<p>$this->error_str</p>";
	echo "--PHP-alt-$random_hash--";
	$msg = ob_get_clean();			
	mail($to,$subject,$msg,$headers);
}
}

 

If you see anything wrong just point it out.

[Jessica]

THROW.

 

What error does YOUR class print out?

[johniem]

I said that is not gives me an error. I have it just for info printing and then I'm changing this:

 

function execQuery($query){
      
                $result = mysql_query($query,$this->dbCon);
                $msg = "Unable to execute query ".$query;
                if(mysql_num_rows($result) > 0)
                    return $result;
               else if($result == false)
                   trigger_error($msg,E_USER_ERROR);
               else
                   return $result;
}

 

to this

 

function execQuery($query){
      
                $result = mysql_query($query,$this->dbCon);
                $msg = "Unable to execute query ".mysql_error($this->dbCon);
                if(mysql_num_rows($result) > 0)
                    return $result;
               else if($result == false)
                   trigger_error($msg,E_USER_ERROR);
               else
                   return $result;
}

 

To get the THROWN error.

But for this case i get nothing.

[Jessica]

I give up.

[kenrbnsn]

Try changing the execQuery function to

<?php
function execQuery($query){
                $result = mysql_query($query,$this->dbCon);
                $msg = "Unable to execute query: $query<br>".mysql_error($this->dbCon);
                if (!$result) {
                   trigger_error($msg,E_USER_ERROR);
               else
                   return $result;
}
?>

 

The way you had it, you were trying to execute another mysql function before checking if the query succeeded.

 

Ken

[johniem]

The printed message for the above code from my error class is

 

http://www.tellawoman.gr ERROR
Error number [256] Unable to execute query: ( SELECT DISTINCT art.TEMPLATE, gal.ARTICLE_ID, art.TITLE, art.DESCRIPTION, MATCH (art.TITLE, art.DESCRIPTION, gal.CONTENT) AGAINST ('ΓΟΒ*' IN BOOLEAN MODE) AS score FROM articles art, galeries gal WHERE gal.ARTICLE_ID = art.ARTICLE_ID AND MATCH (art.TITLE, art.DESCRIPTION, gal.CONTENT)AGAINST ('ΓΟΒ* ' IN BOOLEAN MODE)) UNION (SELECT DISTINCT TEMPLATE, ARTICLE_ID, TITLE, DESCRIPTION, MATCH (TITLE, DESCRIPTION, CONTENT) AGAINST ('ΓΟΒ* ' IN BOOLEAN MODE) AS score FROM articles WHERE ( MATCH (TITLE, DESCRIPTION, CONTENT) AGAINST ('ΓΟΒ* ' IN BOOLEAN MODE))) ORDER BY score DESC LIMIT 0,30

Fatal error on line 35 in file /var/www/vhosts/tellawoman.gr/httpdocs/database/DBActions.php

Array
System informations

PHP version : 5.2.16 - OS : Linux
Stopping script execution...

[gizmola]

**EDITED** nevermind Ken helped pinpoint your error.

 

I've noted that you didn't tell us the truth when i asked you early on if the test query was the same one you were using in phpmyadmin.

[Jessica]

Where do you set dbCon in your errors class?

[johniem]

Just found the problem.

All the code I've post here was just fine.

The root cause was in another class, in which I was calling the dbClose() ( i don't know why i did that ) function so there was no connection to execute my query afterwards.

Thanks for all your time guys and girls.