MySQL WHERE based on PHP variable

Question

I have a form where I collect a bunch of basic information (name, email, etc), I then post that to a database. On that form, I use the php date function to insert a "day", "month", and "year" database column so it will show me what date the form data was submitted.

I want to output this to a table (so I can check it without logging into the db), and I want that table to default to the current date. I thought something like this would work, but it doesn't:

$query="SELECT * FROM form_data WHERE day='echo date("d");' AND month='echo date("m");' AND year='echo date("Y");'";

Ultimately, I want to have some date select boxes where the table is displayed, so I can select any date I want, submit, and return the relevant data to the table. But the first step is just getting the table to default to display today's current data.

Much appreciated if anybody can help.

i would suggest to use prepared statements, and PDO.

code-jaff
– code-jaff

2012-08-20 03:11:35 +00:00
Commented Aug 20, 2012 at 3:11 — code-jaff
– code-jaff, Commented Aug 20, 2012 at 3:11
Would probably be better to use a mysql date field too.

Cameron Martin
– Cameron Martin

2012-08-20 03:18:16 +00:00
Commented Aug 20, 2012 at 3:18 — Cameron Martin
– Cameron Martin, Commented Aug 20, 2012 at 3:18

Jermin Bazazian · Accepted Answer · 2012-08-20 04:03:13Z

2

To make the thread complete I am gonna go ahead and suggest mysqli::prepare. As it is stated here it prepares the query and returns a handle to it.
It is to prevent SQL injection, lower the query parsing overhead, and improve code readability. All and all it is a better practice to use prepare.
And this is how you do it:

$database = new mysqli('localhost', 'username', 'password', 'database');
if ($stmt = $database->prepare("SELECT * FROM form_data WHERE day=? AND month=? AND year=?")) {
  $stmt->bind_param('sss', date("d"), date("m"), date("Y"));
  $stmt->execute();
  $result = $stmt->get_result();
  $stmt->close();
}
$database->close();

In the code above $result contains the rows read from database.

Here is the PDO version:

$stmt = $database->prepare("SELECT * FROM form_data WHERE day=? AND month=? AND year=?")
$stmt->execute(array("".date("d"), "".date("m"), "".date("Y")));

edited Aug 20, 2012 at 4:03

answered Aug 20, 2012 at 3:32

Jermin Bazazian

1,9702 gold badges18 silver badges20 bronze badges

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

1 Comment

Cameron Martin Over a year ago

Or use PDO, with named parameters.

Jared Drake · Accepted Answer · 2012-08-20 03:09:30Z

1

You can't do echo date("d"); in an sql statement or you would be effectively putting a statement inside a statement.

You must save your echo date("d"); into a variable and then do something like

$myDay = date("d");
$myMonth = date("m");
$myYear = date("Y");
$query="SELECT * FROM form_data 
        WHERE day='$myDay' 
        AND month='$myMonth' 
        AND year='$myYear'";

or use separating . to shoot them in like

 $query="SELECT * FROM form_data 
         WHERE day='".date("d")."' 
         AND month='".date("m")."' 
         AND year='".date("Y")."'";

edited Aug 20, 2012 at 3:09

answered Aug 20, 2012 at 3:03

Jared Drake

9645 silver badges12 bronze badges

3 Comments

Paul Over a year ago

Yep, this works. Thank you! It shouldn't be too hard now to create select inputs on the table page that modifies the date variables that are set.

Jared Drake Over a year ago

Just don't forget to select the answer @Paul I live and breathe on points :)

Paul Over a year ago

I wouldn't ever forget! Makes me wait 5min ;). Thanks again, I actually tried what you suggested earlier but my syntax must have been off.

mash · Accepted Answer · 2012-08-20 03:04:35Z

0

Something like

$query="SELECT * FROM form_data WHERE day='" . date("d") . "' AND month='" . date("m") . "' AND year='" . date("Y") . "'";

should work.

Good way to debug your queries is to output them as a string to see how PHP interpreted it.

echo "SELECT * FROM form_data WHERE day='" . date("d") . "' AND month='" . date("m") . "' AND year='" . date("Y") . "'"

Also note that this method is subject to SQL Injection.

answered Aug 20, 2012 at 3:04

mash

15.3k3 gold badges32 silver badges36 bronze badges

3 Comments

Paul Over a year ago

That returns a blank table (there is data for today).

mash Over a year ago

@Paul This is no different than Jared's answer.

Paul Over a year ago

Perhaps I didn't enter it properly, or it didn't fully save in the ftp. I'd still give this rep if I had the ability to. Thanks for the answer!

Maisarah · Accepted Answer · 2018-07-18 07:55:31Z

0

I use this and it works for me select * from record where date1=CURDATE() I hope this is what you want to do

answered Jul 18, 2018 at 7:55

Maisarah

133 bronze badges

Collectives™ on Stack Overflow

MySQL WHERE based on PHP variable

4 Answers 4

1 Comment

3 Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related