Solved

Unknown column in MySQL query in php file

Posted on 2013-11-12
9
783 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
  • 4
  • 4
9 Comments
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
Column names are case-sensitive in MySQL...Check the name again and give it a try
0
 

Author Comment

by:Sim1980
Comment Utility
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
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:Sim1980
Comment Utility
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 108

Expert Comment

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

Expert Comment

by:Ray Paseur
Comment Utility
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
Comment Utility
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
Comment Utility
I solved it myself.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Creating and Managing Databases with phpMyAdmin in cPanel.
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now