0

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

Blue
  • 22,608
  • 7
  • 62
  • 92
MaxF01234
  • 15
  • 4
  • What about using "WHERE date = '$current'" –  Nov 02 '16 at 20:25
  • It looks like you don't have PHP errors on, because this code cannot execute, put this on top of your code: error_reporting(E_ERROR); ini_set('display_errors','On'); – TravisO Nov 02 '16 at 20:25
  • @TravisO Hi travis, I DO have errors on but nothing comes up – MaxF01234 Nov 02 '16 at 20:27
  • @Hopper Hi thanks! I tried this and it still didn't work.... $data = $con->query('SELECT id, date, location, description, time from calendar WHERE date = '$current''); – MaxF01234 Nov 02 '16 at 20:28
  • @MaxF01234 so add the code I pasted to see the errors, they'll help you learn – TravisO Nov 02 '16 at 20:38
  • What type of column is `date`? The DATE column type uses 'YYYY-MM-DD' format $current = date("Y-m-d"); The DATETIME format is 'YYYY-MM-DD HH:MM:SS' $current = date("Y-m-d H:i:s"); – Duane Lortie Nov 02 '16 at 20:49

4 Answers4

2

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.

Community
  • 1
  • 1
Blue
  • 22,608
  • 7
  • 62
  • 92
  • Ok.... But how do I ouput that now? I tried echo $data; just to see what came up and only a "1" was printed... – MaxF01234 Nov 02 '16 at 20:37
  • Ok... So i just realised I may have MASSIVELY f*cked up....I'm pretty sure the dates in my table are just text.... They aren't a specific date data type... Is that bad? – MaxF01234 Nov 02 '16 at 20:39
  • @MaxF01234 I updated my answer. You want to use [`fetch()`](http://php.net/manual/en/pdostatement.fetch.php) to grab the actual rows that match your output. `execute()` just returns a boolean if it ran correctly. – Blue Nov 02 '16 at 20:40
  • Hey! So first off, thank you! I made it semi-work! Just wondering... I printed the array and for some reason it only printed the first row... Even though there are about 5 rows in my table which have the date... – MaxF01234 Nov 02 '16 at 21:09
  • `while ( $row = $q->fetch(PDO::FETCH_ASSOC) ) { //do whatever with row }` – Blue Nov 02 '16 at 21:33
  • OR @MaxF01234 use `$data = $q->fetchAll(PDO::FETCH_ASSOC)` – Blue Nov 02 '16 at 21:36
  • Awesome thanks you have been a HUGE help! Final thing: when I output it it comes out in really strange formatting, like this: "2 Nov WednesdayArray ( [0] => Array ( [date] => 2 Nov Wednesday [location] => Hall [description] => Talk [time] => 9:00 am )" and so on for all the 5 or so rows... Any idea how I can format it? – MaxF01234 Nov 02 '16 at 22:00
  • See mysql's DATE_FORMAT – Blue Nov 02 '16 at 22:01
1

This example fetches data based on a key value supplied by a form. The user input is automatically quoted, so there is no risk of a SQL injection attack.

<?php
$stmt = $dbh->prepare("SELECT * FROM REGISTRY where name = ?");
if ($stmt->execute(array($_GET['name']))) {
  while ($row = $stmt->fetch()) {
    print_r($row);
  }
}
?>

Source: http://php.net/manual/en/pdo.prepared-statements.php

Dan Wilson
  • 3,937
  • 2
  • 17
  • 27
1

It depends on the type of your date column, if it is DATE or DATETIME, you should know how your database stores DATE or DATETIME. MySQL, for example, stores DATE as '2016-11-02'. So, you need to pass in your $current variable an date that respects this format.

Also, you dont need to use LIKE, just an = is sufficient.

leoap
  • 1,684
  • 3
  • 24
  • 32
1

I suppose it's just a problem of date format. In phpMyAdmin and MySQL (if you use them), it's a user-friendly format, but in the database, it's not the same. You have to use the date_format() MySQL function. http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_date-format

fragadass
  • 65
  • 5