Sim1980
asked on
Unknown column in MySQL query in php file
Hi all.
I have the following php file that is giving me the error: "Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CreateDate' in 'field list'' "
CreateDate is a date time field in the MySQL table: FieldSuperDataEntry
What is wrong with my query? Thank you in advance!
I have the following php file that is giving me the error: "Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'CreateDate' in 'field list'' "
CreateDate is a date time field in the MySQL table: FieldSuperDataEntry
What is wrong with my query? Thank you in advance!
<?php
require("common.php");
$query ='SELECT DATE_FORMAT(CreateDate,"%M%Y") As MonthYear, sum(x.Slab) as Slab, SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total from (SELECT DATE_FORMAT(CreateDate,"%M%Y") As MonthYear, count(1) as Slab, 0 as DriedIn FROM FieldSuperDataEntry Where Type = 1 AND (Date(CreateDate) >= :reportdate1 AND Date(CreateDate) <= :reportdate2) group by DATE_FORMAT(CreateDate,"%M%Y") union all SELECT DATE_FORMAT(CreateDate,"%M%Y") As MonthYear, 0 as Slab, count(1) as DriedIn FROM FieldSuperDataEntry Where Type = 2 AND (Date(CreateDate) >= :reportdate3 AND Date(CreateDate) <= :reportdate4) group by DATE_FORMAT(CreateDate,"%M%Y")) x Group by DATE_FORMAT(x.CreateDate,"%M%Y") order by MONTH(CreateDate) ';
$query_params = array(
':reportdate1' => $_SESSION['ReportDate1'],
':reportdate2' => $_SESSION['ReportDate2'],
':reportdate3' => $_SESSION['ReportDate3'],
':reportdate4' => $_SESSION['ReportDate4']
);
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
$data = $stmt->fetchAll();
$rows = array();
$table = array();
$table['cols'] = array(
array('label' => 'MonthYear', 'type' => 'string'),
array('label' => 'Dried In', 'type' => 'number'),
array('label' => 'Slab', 'type' => 'number'),
array('label' => 'Total', 'type' => 'number')
);
foreach($data as $row) {
$temp = array();
$temp[] = array('v' => (string) $row['MonthYear']);
$temp[] = array('v' => (int) $row['DriedIn']);
$temp[] = array('v' => (int) $row['Slab']);
$temp[] = array('v' => (int) $row['Total']);
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;
?>
Column names are case-sensitive in MySQL...Check the name again and give it a try
ASKER
CREATE TABLE `myDatabase`.`FieldSuperDa taEntry` ( `DataEntryID` int( 11 ) NOT NULL auto_increment ,
`BuilderCommunityID` int( 11 ) NOT NULL ,
`Lot` varchar( 255 ) default NULL ,
`Block` varchar( 255 ) default NULL ,
`Type` int( 11 ) NOT NULL ,
`Latitude` float default NULL ,
`Longitude` float default NULL ,
`Notes` varchar( 500 ) default NULL ,
`UserID` int( 11 ) NOT NULL ,
`CreateDate` datetime NOT NULL ,
PRIMARY KEY ( `DataEntryID` ) ) ENGINE = MyISAM DEFAULT CHARSET = utf8;
`BuilderCommunityID` int( 11 ) NOT NULL ,
`Lot` varchar( 255 ) default NULL ,
`Block` varchar( 255 ) default NULL ,
`Type` int( 11 ) NOT NULL ,
`Latitude` float default NULL ,
`Longitude` float default NULL ,
`Notes` varchar( 500 ) default NULL ,
`UserID` int( 11 ) NOT NULL ,
`CreateDate` datetime NOT NULL ,
PRIMARY KEY ( `DataEntryID` ) ) ENGINE = MyISAM DEFAULT CHARSET = utf8;
Try separating out the subqueries and testing them separately. Too much stuff going on in that one long line to read the query and understand it!
ASKER
Ok I separated and added single quotes to the CreateDate:
This is the output:
{"cols":[{"label":"MonthYe ar","type" :"string"} ,{"label": "Dried In","type":"number"},{"lab el":"Slab" ,"type":"n umber"},{" label":"To tal","type ":"number" }],"rows": [{"c":[{"v ":""},{"v" :0},{"v":3 36},{"v":3 36}]}]}
It is not showing anything for the MonthYear, am I using Date_Format incorrectly? I just want to see the Month and Year (November 2013, December 2013 etc.) and want it group by that.
$query ="SELECT DATE_FORMAT('FieldSuperDataEntry.CreateDate','%M-%Y') As MonthYear, sum(x.Slab) as Slab, SUM(x.DriedIn) as DriedIn, Sum(x.Slab) + Sum(x.DriedIn) as Total from (SELECT DATE_FORMAT(CreateDate,'%M%Y') As MonthYear, count(1) as Slab, 0 as DriedIn FROM FieldSuperDataEntry Where Type = 1 AND (Date(CreateDate) >= :reportdate1 AND Date(CreateDate) <= :reportdate2) group by DATE_FORMAT(`CreateDate`,'%M%Y') ) x Group by DATE_FORMAT('x.CreateDate','%M%Y') order by MONTH('CreateDate') ";
$query_params = array(
':reportdate1' => $_SESSION['ReportDate1'],
':reportdate2' => $_SESSION['ReportDate2']
);
$stmt = $db->prepare($query);
$result = $stmt->execute($query_params);
$data = $stmt->fetchAll();
$rows = array();
$table = array();
$table['cols'] = array(
array('label' => 'MonthYear', 'type' => 'string'),
array('label' => 'Dried In', 'type' => 'number'),
array('label' => 'Slab', 'type' => 'number'),
array('label' => 'Total', 'type' => 'number')
);
foreach($data as $row) {
$temp = array();
$temp[] = array('v' => (string) $row['MonthYear']);
$temp[] = array('v' => (int) $row['DriedIn']);
$temp[] = array('v' => (int) $row['Slab']);
$temp[] = array('v' => (int) $row['Total']);
$rows[] = array('c' => $temp);
}
$table['rows'] = $rows;
$jsonTable = json_encode($table);
echo $jsonTable;
This is the output:
{"cols":[{"label":"MonthYe
It is not showing anything for the MonthYear, am I using Date_Format incorrectly? I just want to see the Month and Year (November 2013, December 2013 etc.) and want it group by that.
Try adding var_dump($data) at line 11. There are still too many moving parts to really understand what is happening.
You might try this query all by itself. I think it's correct, but it would be wise to test the parts separately.
SELECT DATE_FORMAT('FieldSuperDat aEntry.Cre ateDate',' %M-%Y') AS MonthYear
SELECT DATE_FORMAT('FieldSuperDat
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I solved it myself.
Column names are case-sensitive. It might make sense to test the subqueries separately. There is a lot of complicated stuff going on in that query, and I would be suspicious of the subqueries if the column name is correct.