bantandor bantandor - 1 month ago 4
MySQL Question

Two SELECT SQL statements

I try to get data from db. I can get max min values but I can't get the x and y values listed.

the codes are as follows. Thank you for your help

<?php

$sql = "SELECT * FROM veri UNION ALL SELECT MAX(x) as 'maxx', MIN(x) as 'minx', MAX(y) as 'maxy', MIN(y) as 'miny' FROM veri ";
$result = mysql_query($sql);
$result_array = array();
while($row = mysql_fetch_array($result)){
$result_array[] = $row;

echo $row['x'];
echo $row['y'];
echo $row['miny'];
echo "<br>";
echo $row['maxy'];
echo "<br>";
echo $row['minx'];
echo "<br>";
echo $row['maxx'];
echo "<br>";
}

?>

Answer

I'm not sure I understand your intention with the query, but if veri have two columns your two selects are not union compatible since your first query have two columns and your second query have four columns. A query is union compatible with another query i.f.f. they have the same number of columns and the corresponding columns have the same type. Furthermore, the result of a query is a table so you cannot have different names on the columns for different rows (x vs maxx).

You could either use four sub-selects for min and max values:

SELECT x, y, (SELECT MAX(x) FROM veri) as maxx
           , (SELECT MIN(x) FROM veri) as minx
           , (SELECT MAX(y) FROM veri) as maxy
           , (SELECT MIN(y) FROM veri) as miny
FROM veri

or you need a classifier to determine what the row represents:

SELECT 'row' as origin, x, y FROM veri
UNION ALL
SELECT 'max' as origin, max(x) as x, max(y) as y FROM veri
UNION ALL
SELECT 'min' as origin, min(x) as x, min(y) as y FROM veri

However, in this case it probably makes most sense to split it into two queries:

q1: SELECT x, y FROM veri

q2: SELECT MAX(x) as maxx, MIN(x) as minx, MAX(y) as maxy, MIN(y) as miny FROM veri

and handle them individually.