syntax error on MYSQL Date/Time Subquery

Question

trying to add a sub query that only shows records from the last calendar month. The column it needs to check is called DateOfCheck and is an auto TimeStamp column.

I have this:

$SelectedMonth = "
  select *
  from   Data_Table
  where  DateOfCheck >= date_sub(curdate(), interval 1 month)
     and DateOfCheck <= date_sub(curdate(), interval 1 day)
";

and am getting a syntax error when I place it in the main query.

Also I think (I'm a noob) this is just looking at the last month from today, when I need it to look at the last calendar month.

I'll also need a seperate subquery to call for all records in the current Calendar month.

Okay the full query (I'm aware this is probably terribly inefficient code - as I said I'm a total noob who is trying to cobble something together!) is the below, it works fine, until I try to to enter the date subquery above.

$Area = $_POST['Area'];
$product = $_POST['Product'];
$AverageScore = ("ROUND(AVG(Score),1)AS 'Avg 
 Score'");
$AverageAutofails = ("ROUND(AVG(Autofails),1)AS 'Autofails 
 per Check'");
$ProductTotal = "SELECT (COUNT (CA001Result) from Data_Table WHERE Product ='$product')";




$SelectedMonth = "select * from Data_Table where DateOfCheck >= date_sub(curdate(), interval 1 month) and DateOfCheck <= date_sub(curdate(), interval 1 day)"; 
ECHO $SelectedMonth;

if ($product == "All"){


$CA001 ="ROUND ((SELECT 100 * SUM(IF(CA001Result='Fail', 1, 0)) / COUNT(CA001Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
001'";

$CA002 ="ROUND ((SELECT 100 * SUM(IF(CA002Result='Fail', 1, 0)) / COUNT(CA002Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
002 '";

$CA003 ="ROUND ((SELECT 100 * SUM(IF(CA002Result='Fail', 1, 0)) / COUNT(CA002Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
003 '";

$CA004 ="ROUND ((SELECT 100 * SUM(IF(CA004Result='Fail', 1, 0)) / COUNT(CA004Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
004 '";

$CA005 ="ROUND ((SELECT 100 * SUM(IF(CA005Result='Fail', 1, 0)) / COUNT(CA005Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
005 '";

$CA006 ="ROUND ((SELECT 100 * SUM(IF(CA006Result='Fail', 1, 0)) / COUNT(CA006Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
006 '";

$CA007 ="ROUND ((SELECT 100 * SUM(IF(CA007Result='Fail', 1, 0)) / COUNT(CA007Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
007 '";

$CA008 ="ROUND ((SELECT 100 * SUM(IF(CA008Result='Fail', 1, 0)) / COUNT(CA008Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
008 '";

$CA009 ="ROUND ((SELECT 100 * SUM(IF(CA009Result='Fail', 1, 0)) / COUNT(CA009Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
009 '";

$CA010 ="ROUND ((SELECT 100 * SUM(IF(CA010Result='Fail', 1, 0)) / COUNT(CA010Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
010 '";


$CA011 ="ROUND ((SELECT 100 * SUM(IF(CA011Result='Fail', 1, 0)) / COUNT(CA011Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
011 '";

$CA012 ="ROUND ((SELECT 100 * SUM(IF(CA012Result='Fail', 1, 0)) / COUNT(CA012Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA0
12 '";

$CA013 ="ROUND ((SELECT 100 * SUM(IF(CA013Result='Fail', 1, 0)) / COUNT(CA013Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
013 '";

$CA014 ="ROUND ((SELECT 100 * SUM(IF(CA014Result='Fail', 1, 0)) / COUNT(CA014Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
014 '";

$CA015 ="ROUND ((SELECT 100 * SUM(IF(CA015Result='Fail', 1, 0)) / COUNT(CA015Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
015 '";

$CA016 ="ROUND ((SELECT 100 * SUM(IF(CA016Result='Fail', 1, 0)) / COUNT(CA016Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
016 '";

$CA017 ="ROUND ((SELECT 100 * SUM(IF(CA017Result='Fail', 1, 0)) / COUNT(CA017Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA0
17 '";

$CA018 ="ROUND ((SELECT 100 * SUM(IF(CA018Result='Fail', 1, 0)) / COUNT(CA018Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
018 '";

$CA019 ="ROUND ((SELECT 100 * SUM(IF(CA019Result='Fail', 1, 0)) / COUNT(CA019Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
019 '";

$CA020 ="ROUND ((SELECT 100 * SUM(IF(CA020Result='Fail', 1, 0)) / COUNT(CA020Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
020 '";

$CA021 ="ROUND ((SELECT 100 * SUM(IF(CA021Result='Fail', 1, 0)) / COUNT(CA021Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
021 '";

$CA022 ="ROUND ((SELECT 100 * SUM(IF(CA022Result='Fail', 1, 0)) / COUNT(CA022Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
022 '";

$CA023 ="ROUND ((SELECT 100 * SUM(IF(CA023Result='Fail', 1, 0)) / COUNT(CA023Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
023 '";

$CA024 ="ROUND ((SELECT 100 * SUM(IF(CA024Result='Fail', 1, 0)) / COUNT(CA024Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
024 '";


$CA025 ="ROUND ((SELECT 100 * SUM(IF(CA025Result='Fail', 1, 0)) / COUNT(CA025Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
025 '";

$CA026 ="ROUND ((SELECT 100 * SUM(IF(CA026Result='Fail', 1, 0)) / COUNT(CA026Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
026 '";

$CA027 ="ROUND ((SELECT 100 * SUM(IF(CA027Result='Fail', 1, 0)) / COUNT(CA027Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
027 '";

$CA028 ="ROUND ((SELECT 100 * SUM(IF(CA028Result='Fail', 1, 0)) / COUNT(CA028Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
028 '";

}
else{

$CA001 ="ROUND ((SELECT 100 * SUM(IF(CA001Result='Fail', 1, 0)) / COUNT(CA001Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
001 '";

$CA002 ="ROUND ((SELECT 100 * SUM(IF(CA002Result='Fail', 1, 0)) / COUNT(CA002Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
002 '";

$CA003 ="ROUND ((SELECT 100 * SUM(IF(CA003Result='Fail', 1, 0)) / COUNT(CA003Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
003 '";

$CA004 ="ROUND ((SELECT 100 * SUM(IF(CA004Result='Fail', 1, 0)) / COUNT(CA004Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
004 '";


$CA005 ="ROUND ((SELECT 100 * SUM(IF(CA005Result='Fail', 1, 0)) / COUNT(CA005Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
005 '";

$CA006 ="ROUND ((SELECT 100 * SUM(IF(CA006Result='Fail', 1, 0)) / COUNT(CA006Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
006 '";


$CA007 ="ROUND ((SELECT 100 * SUM(IF(CA007Result='Fail', 1, 0)) / COUNT(CA007Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
007 '";

$CA008 ="ROUND ((SELECT 100 * SUM(IF(CA008Result='Fail', 1, 0)) / COUNT(CA008Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
008 '";

$CA009 ="ROUND ((SELECT 100 * SUM(IF(CA009Result='Fail', 1, 0)) / COUNT(CA009Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
009 '";

$CA010 ="ROUND ((SELECT 100 * SUM(IF(CA010Result='Fail', 1, 0)) / COUNT(CA010Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
010 '";

$CA011 ="ROUND ((SELECT 100 * SUM(IF(CA011Result='Fail', 1, 0)) / COUNT(CA011Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
011 '";

$CA012 ="ROUND ((SELECT 100 * SUM(IF(CA012Result='Fail', 1, 0)) / COUNT(CA012Result) 
FROM Data_Table
WHERE  Area='$Area'),0) as 'CA
012 '";

$CA013 ="ROUND ((SELECT 100 * SUM(IF(CA013Result='Fail', 1, 0)) / COUNT(CA013Result) 
FROM Data_Table
WHERE  Area='$Area' AND Product = '$product'),0) as 'CA
013 '";

$CA014 ="ROUND ((SELECT 100 * SUM(IF(CA014Result='Fail', 1, 0)) / COUNT(CA014Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
014 '";

$CA015 ="ROUND ((SELECT 100 * SUM(IF(CA015Result='Fail', 1, 0)) / COUNT(CA015Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
015 '";

$CA016 ="ROUND ((SELECT 100 * SUM(IF(CA016Result='Fail', 1, 0)) / COUNT(CA016Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
016 '";

$CA017 ="ROUND ((SELECT 100 * SUM(IF(CA017Result='Fail', 1, 0)) / COUNT(CA017Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
017 '";

$CA018 ="ROUND ((SELECT 100 * SUM(IF(CA018Result='Fail', 1, 0)) / COUNT(CA018Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
018 '";

$CA019 ="ROUND ((SELECT 100 * SUM(IF(CA019Result='Fail', 1, 0)) / COUNT(CA019Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
019 '";

$CA020 ="ROUND ((SELECT 100 * SUM(IF(CA020Result='Fail', 1, 0)) / COUNT(CA020Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
020 '";

$CA021 ="ROUND ((SELECT 100 * SUM(IF(CA021Result='Fail', 1, 0)) / COUNT(CA021Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
021 '";

$CA022 ="ROUND ((SELECT 100 * SUM(IF(CA022Result='Fail', 1, 0)) / COUNT(CA022Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
022 '";

$CA023 ="ROUND ((SELECT 100 * SUM(IF(CA023Result='Fail', 1, 0)) / COUNT(CA023Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
023 '";

$CA024 ="ROUND ((SELECT 100 * SUM(IF(CA024Result='Fail', 1, 0)) / COUNT(CA024Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
024 '";


$CA025 ="ROUND ((SELECT 100 * SUM(IF(CA025Result='Fail', 1, 0)) / COUNT(CA025Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
025 '";

$CA026 ="ROUND ((SELECT 100 * SUM(IF(CA026Result='Fail', 1, 0)) / COUNT(CA026Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
026 '";

$CA027 ="ROUND ((SELECT 100 * SUM(IF(CA027Result='Fail', 1, 0)) / COUNT(CA027Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
027 '";

$CA028 ="ROUND ((SELECT 100 * SUM(IF(CA028Result='Fail', 1, 0)) / COUNT(CA028Result) 
FROM Data_Table
WHERE  Area='$Area'AND Product = '$product'),0) as 'CA
028 '";



}

if ($product == "All"){
echo SQLResultTable2(" SELECT $SelectedMonth $CA001, $CA002, $CA003, $CA004, $CA005, $CA006, $CA007, $CA008, $CA009, $CA010, $CA011, $CA012, $CA013, $CA014, $CA015, $CA016, $CA017, $CA018, $CA019, $CA020,$CA021, $CA022, $CA023, $CA024, $CA025, $CA026, $CA027, $CA028 FROM Data_Table WHERE Area='$Area' GROUP BY Area");}
else{
    echo SQLResultTable2("SELECT $SelectedMonth, $CA001, $CA002, $CA003, $CA004, $CA005, $CA006, $CA007, $CA008, $CA009, $CA010, $CA011, $CA012, $CA013, $CA014, $CA015, $CA016, $CA017, $CA018, $CA019, $CA020,$CA021, $CA022, $CA023, $CA024, $CA025, $CA026, $CA027, $CA028 FROM Data_Table WHERE Area='$Area' GROUP BY Area");}

?>

Any Help appreciated, Cheers!

Chris Trahey · Accepted Answer

The problem is that the final query will start with "SELECT SELECT". You assemble it like this:

"SELECT $SelectedMonth, $CA001 ..."

and $selectedMonth starts with:

"select * from Data_Table where ..."

So you are getting:

"SELECT select * from Data_Table where ..."

Another issue is that this is the only of your subqueries which selects more than one column, which makes the syntax tougher to integrate into such a massive query. The whole thing should be reworked into a large single SELECT since you are only selecting from one table. Here's a very quick idea (one select, no subqueries):

$cols[] = "ROUND(AVG(Score),1)AS 'Avg 
 Score'";
$cols[] = "ROUND(AVG(Autofails),1)AS 'Autofails 
 per Check'";
$cols[] = "(COUNT (CA001Result)";

// Note: %03i will format 3 as 003
$columnFormat = "100 * SUM(IF(CA%03iResult='Fail', 1, 0)) / COUNT(CA%03iResult) AS CA%03i";
for($i = 1; $i <=28; $i++) {
  $cols[] = sprintf($columnFormat, $i, $i, $i);
}

$fieldList = implode(', ', $cols);
$sql = "SELECT " . $fieldList;
$sql .= " FROM Data_table WHERE Area='$Area' AND DateOfCheck BETWEEN CURDATE() - INTERVAL 1 MONTH AND CURDATE() - INTERVAL 1 DAY";
if($product != 'ALL') {
  $sql .= " AND Product = '$product'";
}
$sql .= " GROUP BY Area";

rationale: The above code, in the simplest terms, does this:

Build a PHP array of "columns" to select ($cols)
assemble those columns (implode)
tack on your WHERE ad GROUP BY clauses as appropriate via concatenation.

The trickiest part is the sprintf, which is well-explained on it's php manual page. Essentially, it parses a string and places a formatted version of a variable inside of it. The %i format specifier says "this argument will be an integer". However, with no additional treatment, your column aliases would be 'CA1' instead of 'CA001'. Luckily, sprintf has what we need, %03i says "make the int at least 3 digits wide, and pad it with 0 (instead of space)". So, for instance this: COUNT(CA%03iResult) when passed 23, will result in COUNT(CA023Result).

It is highly recommended that you consider this course for your script. It is orders-of-magnitude more performant, and far less prone to errors (for example, since your execution forks in two places, you may not immediately notice when you introduce SQL errors in one of the paths (for example, your $product=ALL query in the OP is missing a comma that the other one has, simple oversight but easy to miss if you aren't running automated tests frequently).

syntax error on MYSQL Date/Time Subquery

Answers (2)

Related Questions