comparing info in 2 different tables

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

comparing info in 2 different tables

Post 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.
Mark Baker
Forum Regular
Posts: 710
Joined: Thu Oct 30, 2008 6:24 pm

Re: comparing info in 2 different tables

Post by Mark Baker »

You might be able to do it as a single SQL query if you consider using MySQL's UNION
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Re: comparing info in 2 different tables

Post 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.
bouncer
Forum Contributor
Posts: 162
Joined: Wed Feb 28, 2007 10:31 am

Re: comparing info in 2 different tables

Post 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
Post Reply