List Enum String in HTML/PHP Dropdown with values from 2 MSSQL tables

Question

I want to list a string in my dropdown selection like:

Name | IntervallNumber | Intervall

This 3 values in the string need to populated from 2 MSSQL tables. Now in deep I explain the tables and what results I want in the dropdown as a string.

This is table 1:

+-------------+------+
| PK_Table1_ID| Name |
+-------------+------+
|     1       | Name1|
|     2       | Name2|
|     3       | Name3|
+-------------+------+

table 2:

+--------------+--------------+-----------------+-----------+
| PK_Table2_ID | FK_Table1_ID | IntervallNumber | Intervall |
+--------------+--------------+-----------------+-----------+
|      1       |      2       |        1        |    168    |
|      2       |      2       |        2        |    336    |
|      3       |      2       |        3        |    500    |
|      4       |      2       |        4        |    672    |
|      5       |      2       |        5        |    840    |
|      6       |      2       |        6        |    1000   |
|      7       |      3       |        1        |    168    |
|      8       |      3       |        2        |    500    |
|      9       |      3       |        3        |    1000   |
+--------------+--------------+-----------------+-----------+

I think I need multiple SQL SELECT statements and merge them together to a string, to populate it for my dropdown.

Maybe something like this:

$sql = "SELECT Name FROM table1";
$sql2 = "SELECT IntervallNumber, Intervall 
    FROM table2 
    WHERE table1.PK_Table1_ID = table2.FK_Table1_ID";
$seperator = " | ";
$string = $sql . $seperator . $sql2;

The output string should look like in detail like:

echo "<option>" . $string . "</option>";

first select option: "Name2 | 6 | 168,336,500,672,840,100"

second select option: "Name3 | 3 | 168,500,100"

The 3rd value needs to be a string too.

The big problem is how to declare the MSSQL statements right and combine it to a enum only in the selction, not in SQL column, because the colums is integer and cant be take nvarchar with the ,.

Any suggestions how I can solve this?

Its ususally a rule of thumb that there is always an SQL statement to replace merging two `SELECT`s. SQL querys are very powerful. — SapuSeven, Aug 27 '18 at 07:42

Barry · Answer 1 · 2018-08-22T11:05:37.127

1

The SQL you need will look something like this:

SELECT CONCAT(Name, ' | ', MAX(IntervallNumber), ' | ', GROUP_CONCAT(Intervall))
FROM table1 t1
JOIN table2 t2 ON t1.PK_Table1_ID = t2.FK_Table1_ID
GROUP BY t1.PK_Table1_ID

Edit: the above is MySQL, but should instead use something like how-to-make-a-query-with-group-concat-in-sql-server

edited Aug 22 '18 at 11:05

answered Aug 22 '18 at 10:47

Barry

3,303
7
23
42

i tryed the code from the link but i get errors:

SELECT rop.PK_Table1_ID, rop.Name, ropp.IntervallNumber, Intervall = STUFF((
SELECT ',' + ropp.Intervall FROM Table2 ropp
WHERE rop.PK_Table1_ID = ropp.FK_Table1_ID
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM Table1 rop 
JOIN table2 ropp ON rop.PK_Table1_ID = ropp.FK_Table1_ID
ORDER BY rop.name

– Daniel Aug 22 '18 at 13:21

That is why I said "will look something like this" and then linked to where to replace the GROUP_CONCAT, I'm sorry I'm not in a position to debug sql server at present – Barry Aug 22 '18 at 13:22
ok i see the problem the soultion is not for me usefull, becasue its only considered for sql based output results if the column would be a nvarchar but the last columns Intervall is a int, so i cant enum it inside her. so i need a soloution string combined in php – Daniel Aug 22 '18 at 14:08

lutakyn · Answer 2 · 2018-08-22T11:22:53.400

1

Change this query

    $sql = "SELECT Name FROM table1";
  $sql2 = "SELECT IntervallNumber, Intervall 
  FROM table2 
 WHERE table1.PK_Table1_ID = table2.FK_Table1_ID";

To

 $sql = "SELECT a.Name,b.IntervallNumber from table b LEFT JOIN table a on 
 a.PK_Table1_ID = b.FK_Table1_ID WHERE a.PK_Table1_ID = b.FK_Table1_ID";

With this you can get the name and the interval number in the dropdown

edited Aug 22 '18 at 11:22

answered Aug 22 '18 at 10:50

lutakyn

444
1
7
22

ok and how i get the column 'intervall' all the values with same FK_Table1_ID enum as a result next to NAME and IntervallNumber in the dropdown ? – Daniel Aug 22 '18 at 13:20
$sql = "SELECT a.Name + "|" + b.IntervallNumber+"|" b.Intervall from table b LEFT JOIN table a on a.PK_Table1_ID = b.FK_Table1_ID WHERE a.PK_Table1_ID = b.FK_Table1_ID"; – lutakyn Aug 22 '18 at 14:42

Zhorov · Accepted Answer · 2018-08-27T07:48:05.440

0

You may try with this:

T-SQL statement:

SELECT 
    A.name, 
    IntervallNumber = MAX(IntervallNumber),
    Intervall = STUFF((
        SELECT ', ' + CONVERT(VARCHAR(MAX), X.Intervall)
        FROM Table2 X
        WHERE B.FK_Table1_ID = X.FK_Table1_ID
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'), 1, 1, '')
FROM Table1 A
JOIN Table2 B ON A.PK_Table1_ID = B.FK_Table1_ID
GROUP BY B.FK_Table1_ID, A.name

Output:

Name2 6 168, 336, 500, 672, 840, 1000
Name3 3 168, 500, 1000

Notes:

Tested with SQL Server 2012.

edited Aug 27 '18 at 07:48

answered Aug 27 '18 at 07:34

Zhorov

28,486
6
27
52

Perfect, thats what i was looking for. thank you sir. – Daniel Aug 27 '18 at 11:08

List Enum String in HTML/PHP Dropdown with values from 2 MSSQL tables

3 Answers3