• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

4 tables query mysql and php


Hello Experts,

I am trying to build a query in php and mysql. please have a look at the image below.

 query mysql
here is my query code... i do not seem to get it right. i would appreciate your help/comments.

$query = @mysql_query("SELECT
o.office_id,
o.company_id,
o.office_plan,
e.company_id,
e.company_emails_email,
p.partner_id,
p.company_id,
pe.partner_id,
pe.partner_emails_email
FROM
`vo-offices` o,
`vo-company_emails` e,
`vo-partners` p,
`vo-partner_emails` pe
WHERE
o.office_plan = '1'
AND
o.company_id = e.company_id
AND
o.company_id = p.company_id
AND
p.partner_id = pe.partner_id
");
if (!$query) {
	exit ('<p>Error performing query: ' .mysql_error(). '</p>');
}
printf("Records found: %d\n", mysql_affected_rows());
?>
<?php
while ($row = mysql_fetch_array($query)) {
	$office_plan = $row['office_plan'];
	$company_id = $row['company_id'];
	$company_email = $row['company_emails_email'];
	$partner_email = $row['partner_emails_email'];	
?>
<table>
<tr>
<td><?php echo $office_plan; ?></td>
<td><?php echo $company_id; ?></td>
<td><?php echo $company_email; ?></td>
<td><?php echo $partner_email; ?></td>
</tr>
</table>
<p>&nbsp;</p>
<?php ?>
<?php }?>

Open in new window

0
Refael
Asked:
Refael
  • 10
  • 7
  • 2
  • +1
2 Solutions
 
PranjalShahCommented:
All the company_id are same so why do you need to get it from all the tables. Just get it from one of them. And if you want unique email addresses then do

GROUP BY e.company_emails_email, pe.partner_emails_email

0
 
RefaelAuthor Commented:

Hi PranjalShah,
 
when i do : "GROUP BY e.company_emails_email, pe.partner_emails_email"
for some reason the company email is displayed twice.
is it because of my while loop?

0
 
PranjalShahCommented:
Hmmm...there is one more way of doing this. Since you only want unique emails for the partners as well do one more query in the while loop. In you first query dont include the partner email table.



$query = @mysql_query("SELECT
o.office_id,
o.company_id,
o.office_plan,
e.company_emails_email,
p.partner_id,
FROM
`vo-offices` o,
`vo-company_emails` e,
`vo-partners` p,
WHERE
o.office_plan = '1'
AND
o.company_id = e.company_id
AND
o.company_id = p.company_id
GROUP BY e.company_emails_email
");
if (!$query) {
	exit ('<p>Error performing query: ' .mysql_error(). '</p>');
}
printf("Records found: %d\n", mysql_affected_rows());
?>
<?php
while ($row = mysql_fetch_array($query)) {
        //find the partner email
        $partner_qry = mysql_query("SELECT partner_email_email FROM vo-partner_emails WHERE partner_id = '".$row['partner_id']") or die("error in partner email qry".mysql_error());
        $partner_row = mysql_fetch_array($partner_qry);
        $office_plan = $row['office_plan'];
	$company_id = $row['company_id'];
	$company_email = $row['company_emails_email'];
	$partner_email = $partner_row['partner_emails_email'];	
?>
<table>
<tr>
<td><?php echo $office_plan; ?></td>
<td><?php echo $company_id; ?></td>
<td><?php echo $company_email; ?></td>
<td><?php echo $partner_email; ?></td>
</tr>
</table>
<p>&nbsp;</p>
<?php ?>
<?php }?>

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Ray PaseurCommented:
Just a question... Is this by any chance an academic exercise?  If it is school work, the terms of use of EE do not allow us to answer directly.  We can provide learning resources, but EE requires its community members to do school assignments on their own.
0
 
RefaelAuthor Commented:

Ray_Paseur, not its not school assignment and you have been helping me a lot here, if you remember :-)
 PranjalShah there are few errors in the above code... i am trying to fix them.....
0
 
Ray PaseurCommented:
I'm glad to help - it's just that the question diagram seemed so generic that it looked "academic" -- just don't want anyone to get in trouble!
0
 
RefaelAuthor Commented:

PranjalShah and Ray_Paseur maybe you can help too :-)

in some cases the 'company_emails_email' and the 'partner_emails_email' are the same.... also in some cases an owner email is the same in different company.... How can i make sure that i do not get duplicates meaning only unique emails ?
0
 
DerokorianCommented:
If you got the emails you need, with duplicates you could build an $emails array, and every email you print you put in the array. You could then check if an email is already in the array (in_array), if so skip to the next record (continue)
0
 
RefaelAuthor Commented:

Hi Derokorian, is there an example online to see how its done? i am a newbie and i will appropriate any help.
0
 
DerokorianCommented:
Not any examples I know of online so here is an example:

<?php
// Query, and other previous code

$foundEmails = array();
while( $row = mysql_fetch_assoc($result) ) {
   if( in_array($row['email'],$foundEmails) ) continue;
   $foundEmails[] = $row['email'];

   // Whatever output you need
   echo $row['email'].'<br>';
}

Open in new window


Hope that helps!
0
 
RefaelAuthor Commented:

Hi Derokorian.

Question:

i added another line so it will check against the partner emails and the company emails.
in some cases the partner email is the same as the company email... is there a chance not only to check for duplicates in each but against? e.g. if there is duplicates in the results between the partner emails against the company emails?

I thought about simply combine then both in one result and then check using your solution,,, will it work?

if( in_array($row['partner_emails_email'], $foundEmails) ) continue;
if( in_array($row['company_emails_email'],$foundEmails) ) continue;

Open in new window

0
 
DerokorianCommented:
See if this helps:

$foundEmails = array();
while( $row = mysql_fetch_assoc($result) ) {
   if( in_array($row['partner_emails_email'],$foundEmails) ) continue;
   if( in_array($row['company_emails_email'],$foundEmails) ) continue;
   $foundEmails[] = $row['company_emails_email'];
   if( $row['company_emails_email'] != $row['partner_emails_email'] ) {
      $foundEmails[] = $row['partner_emails_email'];
   }

   // Whatever output you need
   echo  $row['company_emails_email'].'<br>';
   if( $row['company_emails_email'] != $row['partner_emails_email'] ) {
      $foundEmails[] = $row['partner_emails_email'].'<br>';
   }
}

Open in new window


Hope that helps
0
 
DerokorianCommented:
I failed...


$foundEmails = array();
while( $row = mysql_fetch_assoc($result) ) {
   if( in_array($row['partner_emails_email'],$foundEmails) ) continue;
   if( in_array($row['company_emails_email'],$foundEmails) ) continue;
   $foundEmails[] = $row['company_emails_email'];
   if( $row['company_emails_email'] != $row['partner_emails_email'] ) {
      $foundEmails[] = $row['partner_emails_email'];
   }

   // Whatever output you need
   echo  $row['company_emails_email'].'<br>';
   if( $row['company_emails_email'] != $row['partner_emails_email'] ) {
      echo $row['partner_emails_email'].'<br>';
   }
}

Open in new window

0
 
DerokorianCommented:
Actually upon second thought, that still wont work because if one of them is unique and the other isn't the unique will be skipped... try this instead..

<?php

$foundEmails = array();
while( $row = mysql_fetch_assoc($result) ) {
   if( in_array($row['partner_emails_email'],$foundEmails)
      && in_array($row['company_emails_email'],$foundEmails) ) continue;
   if( !in_array($row['company_emails_email'],$foundEmails) ) {
      $foundEmails[] = $row['company_emails_email'];
      echo $row['company_emails_email'] . '<br>';
   }
   if( !in_array($row['partner_emails_email'],$foundEmails) ) {
      $foundEmails[] = $row['partner_emails_email'];
      echo $row['partner_emails_email'] . '<br>';
   }
}

Open in new window

0
 
RefaelAuthor Commented:


Derokorian, not for nothing you an expert!
i worked around it a bit more and now it works perfect..... i guess that its case-sensitive but i can live with that! Thank you so much! all i need now is to export it to TXT file with ";" between and i am done thanks to you.
0
 
DerokorianCommented:
Well if case sensitivity becomes a problem you could use strtolower:

<?php

$foundEmails = array();
while( $row = mysql_fetch_assoc($result) ) {
   $row['partner_emails_email'] = strtolower($row['partner_emails_email']);
   $row['company_emails_email'] = strtolower($row['company_emails_email']);
   if( in_array($row['partner_emails_email'],$foundEmails)
      && in_array($row['company_emails_email'],$foundEmails) ) continue;
   if( !in_array($row['company_emails_email'],$foundEmails) ) {
      $foundEmails[] = $row['company_emails_email'];
      echo $row['company_emails_email'] . '<br>';
   }
   if( !in_array($row['partner_emails_email'],$foundEmails) ) {
      $foundEmails[] = $row['partner_emails_email'];
      echo $row['partner_emails_email'] . '<br>';
   }
}

Open in new window

0
 
RefaelAuthor Commented:
this guy is an expert :-)
0
 
RefaelAuthor Commented:

Hi Derokorian

I noticed that companies without an email... the solution above will not print the emails of the company partners.

Can you still have a look at the solution above or should i post a new Q?

Thank you again!

 
0
 
DerokorianCommented:
Can you give an example of the data that's not returning the expected results? IE:
Company_emails_email    Partner_emails_email
example@domain.org      another@domain.org
NULL                    email@example.org
example@email.ocom      email@domain.net

Open in new window

0
 
RefaelAuthor Commented:

Derokorian, first thing first... thank you for your reply!

Your example above is exact!

When the company email is left blank (NULL) it does not print its partners emails.

A company can have multiple emails or none. Each partner (of a company) can have multiple emails or none.

The issue was that sometime the same email of a company is similar to the partner and that's why you made it check for duplicates.

To make it easier we can just check for duplicates in general and NOT per company or partner, so simply echo all the emails of a company (if found) and the company partners (if found) when it checks for duplicates and remove them while echo....

jeee, that sound like WW2 :-)

 
0
 
RefaelAuthor Commented:

Hi Derokorian
i was hopping to find your reply by now.
anyway i re-tested it and i found out that the problem is:
if partner email does not exist (left blank) then it will not echo the company email.
i am copying your solution and i will open a new ticket now.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 10
  • 7
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now