0

I'm using this query in phpmyadmin and work perfect

UPDATE TABLE SET DETALLE = 'PENDING FOR TODAY' where DATE(FECHA) = DATE(NOW());

but if I do the same from PHP 

$sql= "UPDATE TABLE SET DETALLE = 'PENDING FOR TODAY' 
where DATE(FECHA) = DATE(NOW())
$resultado = mysql_query($sql) or die ("error");

Is not doing anything, if any problem with DATE() from php?

Nir Alfasi
  • 53,191
  • 11
  • 86
  • 129
Natalia
  • 1

1 Answers1

5

You missed the "; at the end of your code line.
Besides you can use curdate() instead of date(now()):

$sql = "UPDATE TABLE 
        SET DETALLE = 'PENDING FOR TODAY' 
        WHERE DATE(FECHA) = CURDATE()";

And if you have performance problems you can change it to 

$sql = "UPDATE TABLE 
        SET DETALLE = 'PENDING FOR TODAY' 
        WHERE FECHA >= CURDATE() AND FECHA < CURDATE() + interval 1 day";

which uses the index if you have one on FECHA.

And please don't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. Learn about Prepared Statements instead, and use PDO or MySQLi. See this article for a quick overview how to do it and why it is so important.

Community
  • 1
  • 1
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • Could be the right answer. just a quick addiction. if you plan on using your SQL in MSSQL, change DATE(NOW()) to just GETDATE(). – Mic1780 Jul 18 '13 at 17:35
  • Thanks for your replies, I tryied all the alternatives but still not working $sql = "UPDATE TABLE SET DETALLE = 'PENDING FOR TODAY' WHERE FECHA>= GETDATE() AND FECHA< GETDATE() + interval 1 day"; $resultado = mysql_query($sql) or die ("ERROR"); Return allways error, is any other way to debug this? Extrange in MySQL is working perfect and from php is not working – Natalia Jul 18 '13 at 17:52
  • juergen: I want to see the error too! apache is not shooting any error log message. I'm using Apache 2.4.2 with php 5.4.3 – Natalia Jul 18 '13 at 18:10
  • replace `mysql_query($sql) or die ("error");` with `mysql_query($sql) or die (mysql_error());` to see the error. – juergen d Jul 18 '13 at 18:14
  • Thanks Juergen, I see the error is 'No database selected'... I have this in the beginning $con=mysqli_connect(DB_HOSTNAME,DB_USERNAME,DB_PASSWORD,DB_NAME); where echo DB_NAME; return BASE that is the correct database name – Natalia Jul 19 '13 at 01:30
  • You have to use that connection when you run the query. Change `mysql_query($sql)` to `mysql_query($sql, $con)`. I am no PHP expert but you seem to mix mysql and mysqli functions. – juergen d Jul 19 '13 at 01:49
  • You are completely right mysqli_query($con,$sql) work perfect! I didn't knew that different Thanks a lot! – Natalia Jul 19 '13 at 14:33