Solved

Unknown column in MySQL query in php file

Posted on 2013-11-12
9
807 Views
Last Modified: 2013-11-17
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

0
Comment
Question by:Sim1980
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39642058
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
 
LVL 5

Expert Comment

by:dannygonzalez09
ID: 39642068
Column names are case-sensitive in MySQL...Check the name again and give it a try
0
 

Author Comment

by:Sim1980
ID: 39642077
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39642100
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
 

Author Comment

by:Sim1980
ID: 39642140
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
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39642229
Try adding var_dump($data) at line 11.  There are still too many moving parts to really understand what is happening.
0
 
LVL 110

Expert Comment

by:Ray Paseur
ID: 39642240
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
 

Accepted Solution

by:
Sim1980 earned 0 total points
ID: 39642241
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
 

Author Closing Comment

by:Sim1980
ID: 39654322
I solved it myself.
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article discusses how to create an extensible mechanism for linked drop downs.
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question