comparing result sets from mysql statements

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

comparing result sets from mysql statements

Post by afbase »

Could somebody please forward me to an old post or a webpage that shows how i can compare result sets in mysql.
I don't have an exact clue as to start a comparison but I'd like to try!

I'd like to find the difference between these two statements:

Code: Select all

SELECT ticker FROM `curldata` WHERE `dividend` OR `curass` OR `multiplier` OR `bookval` OR `long_term_debt` OR `total_cur_liabilities` OR `past_5_earnings` OR `annsales` REGEXP '[[:digit:]]';
and

Code: Select all

SELECT ticker FROM `curldata` WHERE `dividend` AND `curass` AND `multiplier` AND `bookval` AND `long_term_debt` AND `total_cur_liabilities` AND `past_5_earnings` AND `annsales` REGEXP '[[:digit:]]';
User avatar
superdezign
DevNet Master
Posts: 4135
Joined: Sat Jan 20, 2007 11:06 pm

Post by superdezign »

You mean the result sets? You could just process the results one by one and check them.

Code: Select all

$result1 = mysql_query($query1);
$rows1 = mysql_num_rows();
$result2 = mysql_query($query2);
$rows2 = mysql_num_rows();

echo '<style type="text/css">.match{font-weight:normal;}.nomatch{font-weight:bold;color:#F00;}</style>';
echo '<table><tr><th>Query #1</th><th>Query #2</th></tr>';
for($i = 0; $i < $rows1 || $i < $rows2; $i++)
{
    $q1 = mysql_fetch_object($result1);
    $q2 = mysql_fetch_object($result2);
    echo '<tr';
    echo ($q1 == $q2) ? ' class="match"' : ' class="nomatch"';
    echo '>';
    echo $q1 ? '<td>' . print_r($q1, true) . '</td>' : '<td></td>';
    echo $q2 ? '<td>' . print_r($q2, true) . '</td>' : '<td></td>';
    echo '</tr>';
}
echo '</table>';
The above script is untested, but it's simplistic and you should be able to get the idea.
afbase
Forum Contributor
Posts: 113
Joined: Tue Aug 15, 2006 1:29 pm
Location: SoCAL!!!!

help!!!

Post by afbase »

Well I can't really make sense as to how to compare two sql statements in mysql. The sql statement above with a bunch of "OR" in it will always be greater than the "AND". They both return the column, ticker, that match the query, and I wanted to find the difference between the two statements. I figured I'd give it a shot in php but no luck in the "logic" part of piecing this together

Code: Select all

$this->connect("cli");
		
		
		$sql[0]		= "SELECT ticker FROM `curldata` WHERE `dividend` OR `curass` OR";
		$sql[0]    .= " `multiplier` OR `bookval` OR `long_term_debt` OR `total_cur_liabilities`";
		$sql[0]    .= " OR `past_5_earnings` OR `annsales` REGEXP '[[]]'";
		$result1 	= mysql_query($sql[0]);
		//$rows1 		= mysql_num_rows($result1);
		//$array1 	= mysql_fetch_array($result1);
		
		$sql[1]		= "SELECT ticker FROM `curldata` WHERE `dividend` AND `curass` ";
		$sql[1]	   .= "AND `multiplier` AND `bookval` AND `long_term_debt` AND `total_cur_liabilities` ";
		$sql[1]	   .= "AND `past_5_earnings` AND `annsales` REGEXP '[[]]'";
		$result2 	= mysql_query($sql[1]);
		//$rows2 		= mysql_num_rows($result2);
		//$array2 	= mysql_fetch_array($result2);
		
		
		//$sql[2]="SELECT ticker FROM `curldata` WHERE (";
		$difference;
		$int = 0;
		$ANDcount=0;
		$ORcount=0;
		try{
			while($array1 	= mysql_fetch_array($result1)){
				while ($array2 	= mysql_fetch_array($result2)) {
					if ($array1[0]==$array2[0]){
						$difference[$int]=$array1[0];// meaning that there the ticker will have all needed values	
						$int++;
					print $difference[$int]."\n";
					break;
					}
					$thecountand++;
				}
		
				$thecount++;
			}
		}
		catch (Exception $e) {
    		echo 'Caught exception: ',  $e->getMessage(), "\n";
    		die();
		}
		print "\nthe OR array size is: ".$thecount;
		print "\nthe AND array size is: ".$thecountand;
		print "\nthis is the difference array size: ".count($difference)."\n";
This is what I get in return
Connection to coldowl.com username: root complete

the OR array size is: 5257
the AND array size is: 253
this is the difference array size: 0
my $difference array isn't being built properly the size should be 253 less than the 5257. Can someone help me figure out why this is, or help me make a mysql statement that does this.
User avatar
Ollie Saunders
DevNet Master
Posts: 3179
Joined: Tue May 24, 2005 6:01 pm
Location: UK

Post by Ollie Saunders »

Code: Select all

array_diff()?
nice job on the triplicate post btw :roll:
Post Reply