Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Unknown column in MySQL query in php file

Posted on 2013-11-12
9
Medium Priority
?
837 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 111

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 111

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 111

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 111

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

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

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

Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
It’s a season to be thankful, and we’re thankful for users like you who engage on site, solve technology problems, and network with others in the industry. What tech are we most thankful for? Keep reading.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

581 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