by using an html-form, I want to read out data from an sql-database and show them in an html-table. The number of the selected criteria is variable and there are two types of selections:
and simultaneously
Both requests are linked by UNION.
My code is:
if (!empty($daten)) {
$request1 = "SELECT DISTINCT $selection FROM $tabelle WHERE
$masterarray";
} else {
$request1 = "";
}
if ((!empty($daten)) && !empty($contraconcat)) {
$union1 = "UNION";
} else {
$union1 = "";
}
if (!empty($contraconcat)) {
$request2 = "SELECT DISTINCT p.Name, absent.Comment
FROM absent_criteria absent
JOIN product p
ON p.master_id = absent.product_id
GROUP BY product_id
HAVING SUM(absent_criteria IN ($absentselection))=0";
} else {
$request2 = "";
}
$sql = "$request1
$union1
$request2";
$selection contains the entered selectioncriteria,
$tabelle the respective tables and
$masterarray the respective WHERE-conditions
Every selection that can be made has a comment-field in the database. Now, I want to present the results in the html-table with the first column containing the product names. This works well!
In addition, I would like to have the comments of the respective selections in additional columns, each comment of a selection in a separate column. And this is the problem. In request2, there is a non-varying number of two fields. However, in request1 the content of $selection is highly variable since the selection here is a dynamic process . But for combining the two SELECT-requests with UNION, I need to have the same number of selections. Is there any possibility to show the comment-columns separately for each selection?
if (!empty($contraconcat)) {
$request2 = "SELECT DISTINCT p.Name as Name, absent.Comment as Comment
FROM absent_criteria absent
JOIN product p
ON p.master_id = absent.product_id
GROUP BY product_id
HAVING SUM(absent_criteria IN ($absentselection))=0";
} else {
$request2 = "";
}
if (!empty($daten)) {
if($request2!=""){
$request1 = "SELECT DISTINCT $selection, table2.Name,table2.Comment
FROM $tabelle,($request2) as table2
WHERE $masterarray";
//Here maybe you will need to add validation for both tables because it maybe will have many results
}
$request1 = "SELECT DISTINCT $selection FROM $tabelle WHERE
$masterarray";
} else {
$request1 = "";
}
You don't need UNION, because there aren't a same number columns.You need create objects to tables. First p.Name and absent.Comment have an alias, later validate $request2, if is not null, then build $request2 columns in request1 but see $request2 has an alias table. If $request2 is null then query will not build
Second, you need add an object table to $selection,$tabelle and $masterarray. If query don't build $request1 and $request2, no problem, it works, as this example:
$selection= t.name,t.product
$tabelle= table1 t
$masterarray= t.product LIKE "%R"...
In your case, you don't need UNION, UNION is for 2 select with same number of columns, in your case, check this, for example:
Your request1, for example, your query is:
SELECT c.Color as Color,W.Weight as weight,m.Measure as Measure
FROM color c,weight w,measure m
Here you must add a temporally ID to the result with ROW_NUMBER()(I don't know what database use, but with row_number works), and $request1 will be:
SELECT ROW_NUMBER() OVER(ORDER BY c.Color DESC) as IDReq1,c.Color as Color,W.Weight as weight,m.Measure as Measure
FROM color c,weight w,measure m
For Example,The result for $request1 will be:
IDReq1 | Color | Weight | Measure
1 | White| 10 | 10ft
2 | Black| 15 | 13ft.....
Later, with request 2, use the same method with ROW_NUMBER, and the result of $request2 will be, for example:
IDReq2 | Name | Comment
1 | name1|comment1
2 | name2|comment2...
Then, when 2 queries are built, JOIN ID respective queries, check this:
SELECT table1.Color,table1.Weight,table1.Measure,table2.Name,table2.Comment
FROM ($request1) as table1, ($request2) as table2
WHERE table1.IDReq1=table2.IDReq2
The final result will be:
Color | Weight | Measure | Name | Comment
White| 10 | 10ft | name1|comment1
Black| 15 | 13ft | name2|comment2
It maybe will help you, but it will depend from your validations Good Luck!