I'm new to MySQL and PHP. I have two tables, one to hold all the company names and the other table has only the company name below the user:

Table 1
| # | Company name |
--------------------
| 1 | Microsoft    |
| 2 | HP           |
| 3 | Asus         |
| 4 | Apple        |
| 5 | Amazon       |
| 6 | CCN          |

table 2
| # | Company name | User name |
--------------------------------
| 1 | Asus         |   x1      |
| 2 | Apple        |   x1      |
| 3 | HP           |   x2      |
| 4 | Asus         |   x2      |
| 5 | Apple        |   x2      |

I need to create a query that achieves the following. First of all the companies are shown which are associated with a specific user (say Asus and Apple for user x1). After that, the remaining companies from table 1 are shown.

For example, the result of the query I'm looking for, for user X1 will display the rows in this way:

| # | Company name |
--------------------
| 1 | Asus         |
| 2 | Apple        |
| 3 | Microsoft    |
| 4 | HP           |
| 5 | Amazon       |
| 6 | CCN          |

How can I achieve this?

• php
• mysql

It looks like you want to include all companies, but for a given user, list the companies associated with that user first. If that's the case, you do not want to use an INNER JOIN.

Here's some SQL that should work. I've provided reasonable table and field names since you didn't give those. I'm also assuming that you have a reasonably sane table design with no duplicate rows.

SELECT c.company_name,

  CASE
    WHEN u.company_name IS NULL THEN 'N'
    ELSE 'Y'
  END AS user_has_company

FROM companies c

LEFT JOIN (
  SELECT *
  FROM users
  WHERE user_name = 'x1'
) u
ON u.company_name = c.company_name

ORDER BY user_has_company DESC, c.company_name

This query will return an extra column - user_has_company. I'd use that to indicate whether the current user is associated with a given company, but you can ignore it if you want.

You will need a JOIN Statement to join another in the SELECT-Statement of table1

Quick example:

SELECT * FROM table2 INNER JOIN table1.id = table2.id WHERE table2.username = 'x1'

You'll find everything you need in the Documentation of JOINs. http://dev.mysql.com/doc/refman/5.1/en/left-join-optimization.html

Create your query like this:

$sql = "SELECT b.companyName FROM table1 a INNER JOIN table2 b ON a.companyName = b.companyName WHERE b.userName = 'x1'";

Then, using PHP, use:

$con = mysql_connect("localhost","peter","abc123");
if (!$con)
{
    die('Could not connect: ' . mysql_error());
}

mysql_select_db("my_db", $con);

$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
{
    echo $row['companyName'];
    echo "<br />";
}

mysql_close($con);

Try this query:

SELECT company_name FROM table2 ORDER BY user_name ASC

In the HTML table, using PHP code:

$result = mysql_query(" SELECT company_name, user_name FROM table2 ORDER BY user_name ASC");
echo "<table>
      <tr><th>Company Name</th><th>username</th></tr>";
while($row = mysql_fetch_array($result) {
    echo "<tr><td>{$row['company_name']}</td><td>{$row['user_name']}</td></tr>";
}
echo "</table>"

If you're just after the MySQL query for this then something like this would work

SELECT company_name,SUM(IF(user_name='x1',1,0)) as ordering
FROM `table2`
GROUP BY company_name
ORDER BY ordering DESC

But you should look at your schema before you go much further. If you have a column (company_name) in one table that refers to another table you should make that column refer to the PRIMARY KEY of the other table, i.e.

Table1

| # | company_name |
--------------------
| 1 | microsoft    |
| 2 | hp           |
| 3 | asus         |
| 4 | apple        |
| 5 | amazon       |
| 6 | CCN          |

table2
| # | company_id   | user_name |
--------------------------------
| 1 | 3            |   x1      |
| 2 | 4            |   x1      |
| 3 | 2            |   x2      |
| 4 | 3            |   x2      |
| 5 | 4            |   x2      |

This is one of the first things you learn in database design/normalisation. You will need to change your query in this case. Something like this:

SELECT company_name,SUM(IF(user_name='x1',1,0)) as ordering
FROM `table1`
    LEFT JOIN `table2` ON table2.company_id=table1.id
GROUP BY company_name
ORDER BY ordering DESC