MaxF01234 MaxF01234 - 1 month ago 8
PHP Question

How can I specifically target certain rows of an SQL table?

What is the syntax required for me to specifically select rows of an SQL table which contain a certain value?

I have a database which has date, time, location and description columns. I need to display ONLY the rows which have today's date in them.

I have tried this:

I have a

$date
value which contains today's date in the same format as my database and I would like to be able to select and display ONLY the rows in the table which contain the same date as today's date.

So I tried this:

$current = date("j M l");
$data = $con->query('SELECT id, date, location, description, time from calendar WHERE date LIKE '$current'');


but it didn't work...

Answer

MySQL date format is Y-m-d H:i:s. Depending on the column type (DATE vs DATETIME), you would need to pass one of two strings (see below). To get the current time or a specific time in a specific format, use PHP's date() function.

Also, get used to binding parameters/variables to the query using bindParam to avoid SQL Injection. While the date is most likely not going to contain sql injection, it's still good practice to sanitize your variables to the database.

If your MySQL is stored as a DATETIME:

$startDate = date("Y-m-d") . ' 00:00:00';
$endDate = date("Y-m-d") . ' 23:59:59';
$q = $con->prepare('SELECT id, date, location, description, time FROM calendar WHERE date BETWEEN :startDate AND :endDate');
$q->bindParam(':startDate', $currentDate, PDO::PARAM_STR);
$q->bindParam(':endDate', $endDate, PDO::PARAM_STR);
$data = $q->execute();
$data = $q->fetch(PDO::FETCH_ASSOC);

If your MySQL is stored as a DATE, it's much simpler:

$currentDate = date("Y-m-d");
$q = $con->prepare('SELECT id, date, location, description, time FROM calendar WHERE date = :currentDate');
$q->bindParam(':currentDate', $currentDate, PDO::PARAM_STR);
$q->execute();
$data = $q->fetch(PDO::FETCH_ASSOC);

For more information, see this post.

Comments