I need to create a query using MySQL and PHP to get a result of two tables

Go To StackoverFlow.com

-2

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?

2012-04-03 20:43
by tony
table 2 should use company id from table 1, not duplicate name - NoName 2012-04-03 20:46
I wasn't able to make any sense out of this question - jordanm 2012-04-03 20:46
Use an inner join - Ben Carey 2012-04-03 20:46
@jordanm I am still trying to figure this out - Pierre-Olivier 2012-04-03 20:47
You should read about JOI - Jarosław Gomułka 2012-04-03 20:47
@BenCarey - That was my first thought, but if you look at his expected output, he is not just joining relationships - jordanm 2012-04-03 20:48
so you want to get the content of table 1 in the same order as it is, but the entries used in table 2 with user x you want to be displayed at the top of the result... am I right - Legy 2012-04-03 20:48
I interpreted this as asking how to return all companies with a certain ordering. So, a select from companies, left join on a subset of users, using the value from users to determine ordering. I don't think this is a bad question, just really badly worded - jnylen 2012-04-03 20:53
Use left join to select unique rows from one table then right join to select unique rows from second table and then use union between both join query to get your desired result - Ankit Sharma 2012-04-04 05:54
thanks a lot for hel - tony 2012-04-04 14:02


2

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.

2012-04-03 20:50
by jnylen
thanks a lot for help that was quick i will try i - tony 2012-04-04 13:58
hello sir i still have some miss understanding for the code can you help me again thanks my first table call table1 has 1 field call companyname the second table call table2 has 2 field call companyname and user_name so i did get where the users and companies come from do i need to create them is users is a new table thanks agai - tony 2012-04-04 14:29
it work perfect thank - tony 2012-04-04 18:02
You really should rename your tables to indicate what data they actually contain. It sounds like if you rename table1 -> companies and table2 -> users then my query will work as-is - jnylen 2012-04-04 19:33


0

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

2012-04-03 20:47
by peipst9lker
While this looks like the sane solution, it wouldn't produce his expected output based on the shown input - jordanm 2012-04-03 20:48
this was just a quick example and not his perfect solution, you can only learn programming by doing programming buddy - peipst9lker 2012-04-03 20:57
thanks a lot for hel - tony 2012-04-04 13:59


0

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);
2012-04-03 20:49
by saluce
thanks a lot for hel - tony 2012-04-04 14:01


0

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>"
2012-04-04 15:35
by Chibuzo


0

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
2012-04-04 15:39
by liquorvicar
thanks a lot i'm going to try i - tony 2012-04-04 16:11
please can you check that this is the way i write the code SELECT table1.companyname,SUM(IF(table2.username='x1',1,0)) as ordering FROM table2 LEFT JOIN table1 ON table2.companyid=table1.id GROUP BY table1.companyname ORDER BY ordering DESC but it showing me only the commun row not all row in the table please help agai - tony 2012-04-04 16:43
@tony My bad, tables were in the wrong order. Try table1 LEFT JOIN table - liquorvicar 2012-04-04 16:46