[RESOLVED] JOIN and display data

amg55nj

I am new to PHP & MySQL and was just playing around to see if I could get this to work. Apparently I can't. I have 2 tables.

Table 1 - Users
userid -- primary key
fname
lname
companyid -- foreign key

Table 2 - Companies
companyid -- primary key
name

I am displaying the data in a table and only get the number (companyid). How can I get the name of the company to show up in the table?

Here is my code:

$result = mysql_query("SELECT * FROM users WHERE id = '$q'") or die(mysql_error());

echo "<table border='1'>

<tr>

<th>Firstname</th>

<th>Lastname</th>

<th>Company</th>

</tr>";

while($row = mysql_fetch_array($result))

  {

  echo "<tr>";

  echo "<td>" . $row['firstname'] . "</td>";

  echo "<td>" . $row['lastname'] . "</td>";

  echo "<td>" . $row['companyid'] . "</td>";

  echo "</tr>";

  }

echo "</table>";

mysql_close();

?>

I tried inserting this code:

$result = mysql_query("SELECT name FROM companies JOIN users ON users.companyid=companies.companyid WHERE id = '$q'") or die(mysql_error());

but still got nothing. What am I doing wrong? How can I get all the data to show?
Your help is much appreciated.

Ashley_Sheridan

Try something like this:

SELECT u.*, c.name AS company_name
FROM users u
LEFT JOIN companies c ON u.companyid = c.companyid

amg55nj

Thanks for the reply. I tried that code and am still only getting numbers under the Company column. Not sure why the names aren't showing up because every tutorial I've looked at says that code should work.

Just wondering how code knows what u and c are? Does it automatically assume they are for the tables users and companies or does this have to be defined somewhere?

bradgrafelman

Can you show us some sample data from your tables?

amg55nj wrote:
Just wondering how code knows what u and c are?

They are aliases created when the actual table identifiers were used; the notation "users u" is equivalent to "users AS u" which creates an alias for 'users' known as 'u'. The 'AS' keyword, however, is optional and commonly omitted for brevity (at least that's why I always omit it anyway).

amg55nj

I have a dropdown select form submit to the PHP code and it outputs as:

Firstname Lastname Company

John Smith 2

I made up 3 companies in the companies table and then put numbers 1, 2, or 3 of companyid in the users table. Does that help any?

Thanks for the explanation about the aliases.

amg55nj

bradgrafelman;10995885 wrote:
Can you show us some sample data from your tables?

Oops I thought you were asking for the output but now I realize you wanted data from the actual tables.

Table users:

userid firstname lastname companyid
1 john smith 2
2 mike williams 1

3 bill johnson 3

Table companies:

companyid name
1 apple
2 microsoft

3 google

And as I showed in my last post, the output is showing the number rather than the name of the company.

amg55nj

Ok I got it to work finally!

I changed companyid to name in the table: echo "<td>" . $row['name'] . "</td>;
This corresponds to the field name in the table companies. Is this the proper way to do it? Thanks for all your help....much appreciated.

$result = mysql_query("SELECT users.*, companies.name FROM users INNER JOIN companies ON users.companyid = companies.companyid WHERE id = '$q'") or die(mysql_error());

echo "<table border='1'> 

<tr> 

<th>Firstname</th> 

<th>Lastname</th> 

<th>Company</th> 

</tr>"; 

while($row = mysql_fetch_array($result)) 

  { 

  echo "<tr>"; 

  echo "<td>" . $row['firstname'] . "</td>"; 

  echo "<td>" . $row['lastname'] . "</td>"; 

  echo "<td>" . $row['name'] . "</td>"; 

  echo "</tr>"; 

  } 

echo "</table>";