PHP - Moved: Mysql Union Help

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=308424.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=306274.0

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=332593.0

Here's what i've been attempting to do .

a. I have a table "travelexpense" in 2 DBs. All columns are identical.
b. Based on a set of parameters, a user can have a record in this table in one or both of the databases.
c. When i want to output all the entries pertaining to that user in both these tables, I use Select Union. So far so good.
d. However, the query references customers pertaining to those expenses . The customer table is present in both databases but is populated with different records.
e. Now when the SELECT UNION runs, it picks up only the customer in the first database and not the second, even if there is an entry pertaining only to the second database.
f. Odlly though the other data - that is the expenses themselves are selected correctly. Its only the customers entries that are wrong

Code snippet below

Code: [Select]

$sqlZ= " SELECT * FROM $dbname2.`Travelexpense`  WHERE Userid='$user' && MONTH(`Enddate`)=$month && YEAR(`Enddate`)=$year 
UNION

select * from $dbname3.`Travelexpense`  WHERE Userid='$user' && MONTH(`Enddate`)=$month && YEAR(`Enddate`)=$year  ";

$resultZ = mysql_query($sqlZ) or die (mysql_error());

$numofrows = mysql_num_rows($resultZ);

for($i = 0; $i < $numofrows; $i++) {

$myrowZ = mysql_fetch_array($resultZ);
$coid = $myrowZ["CID"];

$s1 = "SELECT * FROM $dbname2.`Customers` WHERE `CID` = $coid  UNION select * from $dbname3.`Customers` WHERE `CID` = $coid";
$result1 = mysql_query($s1);
$myrow2 = mysql_fetch_array($result1);
$Name=$myrow2["Company"];

//here's the problem . If say $coid = 150 , then it will look only at record 150 in Table Customers in dbname2 and NOT in Table Customers in dbname3 even if only dbname3 has a relevant entry.

blah blah
}

Am i using UNION incorrectly  esp in the query $s1?

Appreciate any help . Thanks !
Swati

The following union query is supposed to return the total number of alerts and the total number of messages, however a dump of the returned array shows only the alerts(num_alerts).


$sql = "SELECT COUNT(a.a_aid) as num_alerts 
                    FROM ".Asf_Db::$prefix."alerts a
                    UNION
                    SELECT COUNT(m.m_mid) as num_messages
                    FROM ".Asf_Db::$prefix."messages m";
                    
            $sth = Asf_Core::$db->prepare($sql);
            $sth->execute() or die(Asf_Core::$db->error($sth, $sql));
            
            $results = $sth->fetch(PDO::FETCH_ASSOC);
            
            dump($results);


anyone see where im going wrong?