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

RefaelAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.