MySQL Error: Unknown column in 'field list' error

Question

Hey guys I have a problem with MySQL, i'm trying to insert data in a table but it's returning this error message

Fatal error: SQL: SELECT e.idtarea AS 'idTarea', e.detalle AS 'detalle', e.precio AS 'precio', e.idOrdenReparacion AS 'idOrdenReparacion', e.fecha AS 'fecha', concat( 'Editar ', 'Eliminar' ) AS Opciones FROM Tarea e WHERE e.idtarea like '%%' ORDER BY e.idtarea;, Error: Unknown column 'e.fecha' in 'field list' in /opt/lampp/htdocs/scep/tareas.php on line 75

Here is my code:

    $SQL="

      SELECT 
        e.idtarea AS 'idTarea', 
        e.detalle AS 'detalle', 
        e.precio AS 'precio', 
        e.idOrdenReparacion AS 'idOrdenReparacion', 
        e.fecha AS 'fecha', 
        concat('<a href=\'editarTarea.php?id=',e.idtarea,'\'>Editar</a>&nbsp;','<a href=\'eliminarTarea.php?id=',e.idtarea,'\' onclick=javascript:confirm(\'Eliminar?>\')>Eliminar </a>') AS Opciones 
      FROM Tarea e ".$FILTRAR_POR." 
      ORDER BY e.idtarea;";

      $RESULT = mysql_query($SQL) or trigger_error("SQL: $SQL, Error: " . mysql_error(), E_USER_ERROR);

Table Tarea from database:

CREATE TABLE IF NOT EXISTS `Tarea` (
   `idTarea` int(11) NOT NULL AUTO_INCREMENT,
   `detalle` varchar(45) COLLATE latin1_danish_ci DEFAULT NULL,
   `precio` varchar(45) COLLATE latin1_danish_ci DEFAULT NULL,
   `idOrdenReparacion` int(11) NOT NULL DEFAULT '0',
   `fecha` date DEFAULT NULL,
   PRIMARY KEY (`idTarea`,`idOrdenReparacion`),
   KEY `fk_Tarea_OrdenReparacion1_idx` (`idOrdenReparacion`)
  ) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_danish_ci AUTO_INCREMENT=2 ;

.....

  ALTER TABLE `Tarea`
  ADD CONSTRAINT `fk_Tarea_OrdenReparacion1` FOREIGN KEY (`idOrdenReparacion`) 
  REFERENCES `OrdenReparacion` (`idOrdenReparacion`) ON DELETE NO ACTION ON UPDATE NO ACTION;

yes fecha is Data type, i want to use e.fecha to display it in a table later... I use e.idtarea to edit the table by editarTarea.php — Gonzalo Baldassarri
– Gonzalo Baldassarri, Commented Nov 6, 2012 at 16:48

AJ Henderson · Accepted Answer · 2012-11-06 16:46:11Z

1

Have you verified that a previous version of the table didn't exist where the column didn't exist? If there was already a version of the table, then your SQL will not made changes to the columns in the table.

answered Nov 6, 2012 at 16:46

AJ Henderson

1,1901 gold badge12 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Gonzalo Baldassarri Over a year ago

Hmmm I add fecha column by phpMyAdmin then I make a dump

AJ Henderson Over a year ago

Another thing to try is see if the command runs when you remove that from the select list. Sometimes I've seen another error cause weird issues to appear that look valid. If you still get an error, but it simply changes when you remove that field from the selection list, then something else may be up.

Gonzalo Baldassarri Over a year ago

I did that, this fix the error, but it does not display any of the other values in the table. btw I need to show the fecha column. i'm really confused

AJ Henderson Over a year ago

Are you sure you are connecting to the same DB that you connected to in PHPMyAdmin? If you are expecting results to be returned when the query runs and you are not getting any results, then it sounds like maybe you might be running against an outdated version of your DB. Did you possibly create a second database with a different name at some point, but similar table structure?

Gonzalo Baldassarri Over a year ago

Yes I edited the table by mysql console and it worked, the db was te same (that's weird), maybe something wrong went on phpMyAdmin so for the later modifications i will use the console directly. Thanks a lot.

|

Jocelyn · Accepted Answer · 2012-11-06 16:54:04Z

0

Fieldnames and aliases must be enclosed in backticks, not in single quotes as you did. Updated query:

$SQL="SELECT 
    e.idtarea AS `idTarea`, 
    e.detalle AS `detalle`, 
    e.precio AS `precio`, 
    e.idor AS `idOrdenReparacion`, 
    e.fecha AS `fecha`, 
    concat('<a href=\'editarTarea.php?id=',e.idtarea,'\'>Editar</a>&nbsp;','<a href=\'eliminarTarea.php?id=',e.idtarea,'\' onclick=javascript:confirm(\'Eliminar?>\')>Eliminar </a>') AS Opciones 
  FROM Tarea e ".$FILTRAR_POR." 
  ORDER BY e.idtarea;";

answered Nov 6, 2012 at 16:54

Jocelyn

11.4k11 gold badges47 silver badges63 bronze badges

1 Comment

Jocelyn Over a year ago

What does echo $SQL; display?

Collectives™ on Stack Overflow

MySQL Error: Unknown column in 'field list' error

2 Answers 2

6 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related