Page 1 of 1
comparing result sets from mysql statements
Posted: Wed Jul 25, 2007 4:33 am
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:]]';
Posted: Wed Jul 25, 2007 7:45 am
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.
help!!!
Posted: Sun Jul 29, 2007 2:27 am
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.
Posted: Sun Jul 29, 2007 5:00 am
by Ollie Saunders
nice job on the triplicate post btw
