Link to home
Start Free TrialLog in
Avatar of Sim1980
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!

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

Avatar of Ray Paseur
Ray Paseur
Flag of United States of America image

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.
Avatar of dannygonzalez09
dannygonzalez09

Column names are case-sensitive in MySQL...Check the name again and give it a try
Avatar of Sim1980

ASKER

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;
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!
Avatar of Sim1980

ASKER

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.
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('FieldSuperDataEntry.CreateDate','%M-%Y') AS MonthYear
ASKER CERTIFIED SOLUTION
Avatar of Sim1980
Sim1980

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Sim1980

ASKER

I solved it myself.