get a date from a mysql query in php

Question

I have a mysql database. I have a php script, that connects to my database. My database consists of 3 tables, error, date, email address, as per the screenshot

Database structure

my php query is as follows:

<?php

mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("pmta_reporting") or die(mysql_error());

$result = mysql_query("SELECT * FROM microcompany WHERE date='2013-01-28' AND code='2.0.0'") or die(mysql_error());
$count= mysql_num_rows($result);
echo $count;

mysqli_close($con);
?>

I would like to replace '2013-01-28' with yesterdays date and in that format Y-m-d. How can I do this?

Any feedback would be appreciated

Depending on your script you must choose either to use PHP generated date or MYSQL generated.. If you insert date generated from your PHP then generate date string in PHP and use it else you should use mysql date_sub function to get the correct date :) — Svetoslav, Apr 26 '13 at 13:06

score 4 · Accepted Answer · answered Apr 26 '13 at 12:59

4

$yesterday = date("Y-m-d", strtotime('-1 day'));
$result = mysql_query("SELECT * FROM microcompany WHERE date='$yesterday' AND code='2.0.0'") or die(mysql_error());

answered Apr 26 '13 at 12:59

Dom

7,135
1
11
13

No probs, happy to help, if your happy with it please make sure to mark it as the answer :) – Dom Apr 26 '13 at 13:10

Shomz · Answer 2 · 2013-04-26T13:14:49.727

3

Why not use the MySQL function? They are meant to be used in MySQL.

SELECT * FROM microcompany WHERE date=DATE(DATE_SUB(NOW(), INTERVAL 1 DAY)) AND code='2.0.0'

btw. mysql-* functions are deprecated: http://www.php.net/manual/en/function.mysql-query.php

edited Apr 26 '13 at 13:14

answered Apr 26 '13 at 13:02

Shomz

37,421
4
57
85

Thank you for the feed back, I tried that but for some reason does not work on my server. – rezizter Apr 26 '13 at 13:09
Pardon me, I forgot to extract only the date from the function, see my updated answer. – Shomz Apr 26 '13 at 13:15
No problem, glad you fixed it! :) – Shomz Apr 26 '13 at 16:48

score 1 · Answer 3 · edited May 25 '19 at 14:43

$date = new DateTime('Yesterday');
echo $date->format('Y-m-d');

Or

$date = new DateTime('2013-01-28');
$date->modify('-1 day');
echo $date->format('Y-m-d');

FYI, you shouldn't use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO, or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

Ali Mohammadi · Answer 4 · 2013-04-27T16:05:28.457

0

$date = new DateTime('Yesterday');
$fdate = $date->format('Y-m-d');
echo $fdate;

edited Apr 27 '13 at 16:05

answered Apr 26 '13 at 13:01

Ali Mohammadi

1,306
1
14
28

score 0 · Answer 5 · answered Apr 26 '13 at 13:01

0

use native mysql functions for that purpose.

$result = mysql_query("SELECT * FROM microcompany WHERE
date=date_sub(date(now()), interval 1 day) AND code='2.0.0'")
or die(mysql_error());

answered Apr 26 '13 at 13:01

jancha

4,916
1
24
39

score -1 · Answer 6 · answered Apr 26 '13 at 13:06

<?php

$yesterday = date('Y-m-d', strtotime('-1 day',strtotime(date("y-m-d")) ));

$query = "SELECT * FROM microcompany WHERE date='".$yesterday."' AND code='2.0.0'";

$newQuery = 'update microcompany set date = "'.$yesterday.' where date = "'.$oldDate.'" and code ="2.0.0"';
?>

get a date from a mysql query in php

6 Answers6