Page 1 of 1

comparing info in 2 different tables

Posted: Tue Dec 23, 2008 12:14 pm
by bouncer
hi there,

i'm trying to validate some information, so i need to compare that information by getting the info from one table and then see if that info exists in the other table and if's the same, so far this is all i've got,

Code: Select all

 
$query = "SELECT * FROM `rel_exp`"; //get all the info in this table
$result = mysql_query($query);
 
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
    $cod_obj1 = $row['cod_obj'];
    $ref_exp = substr($row['ref_exp'], 2, 11);
    $dat_env = $row['dat_env'];
    
    $query1 = "SELECT * FROM `content`, `flags` WHERE `content`.`ref`=`flags`.`ref` ..."; // see if i can find $cod_obj1, $ref_exp in those tables
    $row1 = mysql_fetch_array(mysql_query($query1), MYSQL_ASSOC);
 
    $ref = $row1['ref'];
    $vd = $row1['vd'];
    $cod_obj = $row1['cod_obj'];
    
    if(!preg_match("/^[a-zA-Z0-9]{2}[0-9]{9}PT$/", $cod_obj1) || !preg_match("/^[a-zA-Z0-9]{2}[0-9]{9}PT$/", $cod_obj)) {
        @mysql_query("INSERT INTO `div` VALUES ()"); // insert some info in div table in case something wrong
        
        if($cod_obj1 != $cod_obj) { // see if cod_obj1 is like cod_obj
        @mysql_query(""); // same here
        }
    }
    if(ereg("^[0-9]+$", $ref_exp, $regs) || ereg("^[0-9]+$", $vd, $regs)) {
        @mysql_query(""); // same here but to a different field
        
        if($ref_exp != $vd) {
        @mysql_query("");
        }
    }
}
 
 
is there a better and efficient way to do a validation like this ?

Thanks in advance.

Re: comparing info in 2 different tables

Posted: Tue Dec 23, 2008 1:49 pm
by Mark Baker
You might be able to do it as a single SQL query if you consider using MySQL's UNION

Re: comparing info in 2 different tables

Posted: Wed Dec 24, 2008 4:58 am
by bouncer
Mark Baker wrote:You might be able to do it as a single SQL query if you consider using MySQL's UNION
hi, i'm following your tip but now i'm stuck because i need to add a 3rd table and it's not working, i only want to check if that info exists and if it's the same.

Code: Select all

 SELECT `content`.`ref`, LEFT(`content`.`date_es`,0,10), `flags`.`vd`, `flags`.`cod_obj` FROM `content`, `flags` WHERE `content`.`ref`=`flags`.`ref` AND `content`.`est`='3' AND (LEFT(`content`.`date_es`,0,10), `flags`.`vd`, `flags`.`cod_obj`) NOT IN (SELECT `ref_expr`, `cod_obj`, `dat_en` FROM `rel_exp`) UNION all SELECT `nr`, `ref_exp`, `cod_obj`, `dat_en` FROM `rel_exp` WHERE (`ref_exp`, `cod_obj`, `dat_en`) NOT IN (SELECT LEFT(`content`.`date_es`,0,10), `flags`.`vd`, `flags`.`cod_obj` FROM `content`, `flags`) 
thanks in advance.

Re: comparing info in 2 different tables

Posted: Wed Dec 24, 2008 8:56 am
by bouncer
trying to do this with minus, but how can i run it in mysql since mysql dont have MINUS

Code: Select all

 SELECT `content`.`ref`, LEFT(`content`.`date_es`,0,10), `flags`.`vd`, `flags`.`cod_obj` FROM `content`, `flags` WHERE `content`.`ref`=`flags`.`ref` AND`content`.`est`='3'minusSELECT `content`.`ref`, LEFT(`content`.`date_es`,0,10), `flags`.`vd`, `flags`.`cod_obj` FROM `content`, `flags`,`rel_exp` WHERE `content`.`ref`=`flags`.`ref` AND`content`.`est`='3'AND LEFT(`content`.`date_es`,0,10)=`rel_exp`.`ref_expr` AND `flags`.`vd`=`rel_exp`.`vd` AND  `flags`.`cod_obj` = `rel_exp`.`cod_obj` 
thanks in advance