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
– Svetoslav, Commented Apr 26, 2013 at 13:06

Dom · Accepted Answer · 2013-04-26 12:59:45Z

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, 2013 at 12:59

Dom

7,3351 gold badge13 silver badges13 bronze badges

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

1 Comment

Dom Over a year ago

No probs, happy to help, if your happy with it please make sure to mark it as the answer :)

Shomz · Accepted Answer · 2013-04-26 13:14:49Z

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, 2013 at 13:14

answered Apr 26, 2013 at 13:02

Shomz

37.7k4 gold badges61 silver badges87 bronze badges

3 Comments

rezizter Over a year ago

Thank you for the feed back, I tried that but for some reason does not work on my server.

Shomz Over a year ago

Pardon me, I forgot to extract only the date from the function, see my updated answer.

Shomz Over a year ago

No problem, glad you fixed it! :)

Zoe - Save the data dump · Accepted Answer · 2019-05-25 14:43:38Z

1

$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.

edited May 25, 2019 at 14:43

Zoe - Save the data dump

28.4k22 gold badges130 silver badges163 bronze badges

answered Apr 26, 2013 at 12:59

John Conde

220k100 gold badges464 silver badges504 bronze badges

Comments

jancha · Accepted Answer · 2013-04-26 13:01:36Z

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, 2013 at 13:01

jancha

4,9851 gold badge27 silver badges40 bronze badges

Comments

Ali Mohammadi · Accepted Answer · 2013-04-27 16:05:28Z

0

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

edited Apr 27, 2013 at 16:05

answered Apr 26, 2013 at 13:01

Ali Mohammadi

1,3332 gold badges15 silver badges29 bronze badges

Comments

ismail atkurt · Accepted Answer · 2013-04-26 13:06:48Z

-1

<?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"';
?>

answered Apr 26, 2013 at 13:06

ismail atkurt

1391 silver badge5 bronze badges

Collectives™ on Stack Overflow

get a date from a mysql query in php

6 Answers 6

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

3 Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related