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!

<?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;
?>

Open in new window

Sim1980Asked:
Who is Participating?
 
Sim1980Connect With a Mentor Author Commented:
I figured it out. I forgot to add the x to the very first Date_Format(CreateDate, '%M %Y') in the SELECT statement. It works now.

$query ="SELECT DATE_FORMAT(x.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 CreateDate, count(1) as Slab, 0 as DriedIn FROM FieldSuperDataEntry  Where Type = 1 AND (Date(CreateDate) >= :reportdate1 AND Date(CreateDate) <= :reportdate2)  group by CreateDate union all SELECT CreateDate, 0 as Slab, count(1) as DriedIn FROM FieldSuperDataEntry  Where Type = 2 AND (Date(CreateDate) >= :reportdate3 AND Date(CreateDate) <= :reportdate4)  group by CreateDate ) x Group by DATE_FORMAT(x.CreateDate,'%M %Y') ORDER BY CreateDate ";

Open in new window

0
 
Ray PaseurCommented:
Please post the CREATE TABLE statement for FieldSuperDataEntry.

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.
0
 
dannygonzalez09Commented:
Column names are case-sensitive in MySQL...Check the name again and give it a try
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Sim1980Author Commented:
CREATE  TABLE  `myDatabase`.`FieldSuperDataEntry` (  `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;
0
 
Ray PaseurCommented:
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!
0
 
Sim1980Author Commented:
Ok I separated and added single quotes to the CreateDate:

$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;

Open in new window


This is the output:
{"cols":[{"label":"MonthYear","type":"string"},{"label":"Dried In","type":"number"},{"label":"Slab","type":"number"},{"label":"Total","type":"number"}],"rows":[{"c":[{"v":""},{"v":0},{"v":336},{"v":336}]}]}

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.
0
 
Ray PaseurCommented:
Try adding var_dump($data) at line 11.  There are still too many moving parts to really understand what is happening.
0
 
Ray PaseurCommented:
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('FieldSuperDataEntry.CreateDate','%M-%Y') AS MonthYear
0
 
Sim1980Author Commented:
I solved it myself.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.