CODEWITHSUNDEEP
phpmysqlsqldynamic
Paul
Paul

Reputation: 201

MySQL WHERE based on PHP variable

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.

Upvotes: 0

Views: 112

Answers (4)

Maisarah
Maisarah

Reputation: 13

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

Upvotes: 0

Jermin Bazazian
Jermin Bazazian

Reputation: 1970

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")));

Upvotes: 2

Jared Drake
Jared Drake

Reputation: 1002

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")."'";

Upvotes: 1

mash
mash

Reputation: 15229

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.

Upvotes: 0

Related Questions