Outputting table with mysql join

[ntroycondo]

I'm outputting my DB to a table. I want to include a JOIN so the table shows name, email, and company (which is in a different table). Not sure where to put the join.

2 other things I'd like to do:

Not all users have email and I want any NULL to output as N/A instead of NULL.

Would like the rows to alternate background color between white and blue for each row.

Any hints appreciated.

 

 

$query= "select * from Managers";
$result=mysql_query($query);
echo mysql_error();

echo '<table align="center" cellspacing="1" cellpadding="2">
<tr>
<td align="left"><b>Name</b></td>
<td align="left"><b>Email</b></td>
<td align="left"><b>Company</b></td>
</tr>
';
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<tr bgcolor="' . $bg . '">
	<td align="left">' . $row['name'] . '</td>
	<td align="left">' . $row['email'] . '</td>
	<td align="left">' . $row['company.name'] . '</td> //Need JOIN here
</tr>
';
}

[Rifts]

http://www.tizag.com/mysqlTutorial/mysqljoins.php

 

should help

[wildteen88]

What is the table structure for your Mangers and Company tables? In order to have a join there needs to be a column which associates a manager with the company.

 

[ntroycondo]

The join is I think:

 

JOIN Manager ON Manager.companyid = Company.companyid

 

But I'm not sure if I just create a variable for this in the PHP?

 

Table structure is:

Manager with 4 columns managerid, name, email, companyid

Company with 2 columns companyid, name

[ntroycondo]

Ok, so i think I have to replace:

$query= "select * from Managers";

with the JOIN query.

 

I'll play around with it.

[wildteen88]

Your query will be like this

 

SELECT m.name as manager_name, 
       m.email as manager_email,
       c.name as company_name
FROM Managers m
LEFT JOIN Company c ON 
    c.companyid = m.companyid

 

So your complete code would be

$query= "SELECT m.name as manager_name, 
       m.email as manager_email,
       c.name as company_name
FROM Managers m
LEFT JOIN Company c ON 
    c.companyid = m.companyid";
    
$result = mysql_query($query);
echo mysql_error();

echo '<table align="center" cellspacing="1" cellpadding="2">
<tr>
<td align="left"><b>Name</b></td>
<td align="left"><b>Email</b></td>
<td align="left"><b>Company</b></td>
</tr>
';
while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
echo '<tr bgcolor="' . $bg . '">
	<td align="left">' . $row['manager_name'] . '</td>
	<td align="left">' . $row['manager_email'] . '</td>
	<td align="left">' . $row['company_name'] . '</td>
</tr>
';
}

[ntroycondo]

Thanks, that's working and table is outputting nicely.

 

Now I just need my NULL values to echo out as N/A, and add alternating background color to the rows.

[wildteen88]

For the displaying N/A if email is NULL. Change this

		<td align="left">' . $row['manager_email'] . '</td>

To

		<td align="left">' . (!empty($row['manager_email']) ? $row['manager_email'] : 'N/A') . '</td>

 

For adding alternative row colours add

$i = 0;
$color1 = '#CCCCFF';
$color2 = '#CCFFFF';

NOTE: Set your two alternative colours above

 

Before

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {

Now change

	echo '<tr bgcolor="' . $bg . '">

To

	$bg = ($i++ % 2) ? $color1 : $color2;
echo '<tr bgcolor="' . $bg . '">

[ntroycondo]

Code for alternating colors works nicely.

 

But since the NULL rows aren't empty, N/A isn't being echo'd out instead.

[wildteen88]

But since the NULL rows aren't empty, N/A isn't being echo'd out instead.

By NULL do you mean the data type null. Or do you mean the string 'null'?

 

empty will work if the data type is null. Null stands for no value.

[ntroycondo]

Good point, it's a string. The person that created the table did make that column varchar with default value NULL.

[wildteen88]

You'd compare the $row['manager_email'] with the string 'null'

		<td align="left">' . ((strtolower($row['manager_email']) == 'null') ? $row['manager_email'] : 'N/A') . '</td>

[ntroycondo]

It worked with making the operator != instead of ==.

Thanks!