Using a GET variable in a MySQL/PHP query

Question

I'm working with a mysql date column called 'class_of' that has dates ranging from 2014-08-01 to 2019-08-01, all formatted as a date. These dates coincide to what year an article was written.

I have set up my php execution page to grab the year through a url action. for example - www.mywebsite.com/mypage.php?action=2016

$classOf = ($_GET["action"]);

I now need to somehow use said variable within a mysql query so my php while loop will only echo dates that have for example 2016 within the date.

This is what I have tried

This works, but I need the year to be a variable

$query = 'SELECT * FROM news_content 
          WHERE 
            hot = "false" 
            AND trash="false" 
            AND class_of = DATE("2020-08-01") 
          ORDER BY article_id DESC';

I have tried the below, but with no success

$query = 'SELECT * FROM news_content 
          WHERE 
            hot = "false" 
            AND trash="false" 
            AND class_of = DATE("<?php echo $classOf ?>-08-01") 
          ORDER BY article_id DESC';

And

 $query = 'SELECT * FROM news_content 
           WHERE 
             hot = "false" 
             AND trash="false" 
             AND class_of = "<?php echo $classOf ?>" ORDER BY article_id DESC';

And

$query = 'SELECT * FROM news_content 
          WHERE 
            hot = "false" AND trash="false" 
            AND class_of 
             LIKE "%<?php echo $classOf ?>%" ORDER BY article_id DESC

And

$query = 'SELECT * 
          FROM news_content 
          WHERE 
            hot = "false" AND trash="false" 
            AND class_of = 
            "<?php date("echo $classOf-08-01")?>" ORDER BY article_id DESC';

All of the above no success.

It looks like you are trying to embed PHP code inside of another PHP code. This is not going to work. — Dharman, Sep 15 '19 at 16:23
You need to look into [prepared statements](https://www.w3schools.com/php/php_mysql_prepared_statements.asp) for passing a variable into an SQL statement in a safe way. Putting a submitted string directly into the SQL string is very unsafe. You can then use sql date_format to look for values on a given year `DATE_FORMAT( your_date_column, "%Y") = ?`. — Gavin, Sep 15 '19 at 16:24
@Gavin the site you linked to is notoriously known for the bad quality. For example, on this page there is no example for SELECT queries which is important due to rather tedious nature of mysqli prepared statements. — Your Common Sense, Sep 15 '19 at 16:38
That's a shame I remember it being decent years ago. Was just the first beginner-friendly google result I saw. — Gavin, Sep 15 '19 at 16:40
Why are you using DATE as data type to store years? Use INT, or YEAR instead. — Paul Spiegel, Sep 15 '19 at 17:27

Dharman · Answer 1 · 2019-09-15T16:36:27.283

Use PHP DateTime class to get the full date and then prepared statements to pass the value to SQL.

$date = (new DateTime ($_GET['action'] . '08-01'))->format('Y-m-d');
$stmt = $mysqli->prepare ('SELECT * FROM news_content WHERE hot = "false" AND trash="false" AND class_of=? ORDER BY article_id DESC');
$stmt->bind_param('s', $date);
$stmt->execute();
$result = $stmt->get_result();

If you want to get all records from a matching year then create 2 PHP dates, dateFrom and dateTo, and then use BETWEEN in MySQL query

$dateFrom = (new DateTime ($_GET['action'] . '01-01'))->format('Y-m-d');
$dateTo = (new DateTime ($_GET['action'] . '12-31'))->format('Y-m-d');

$stmt = $mysqli->prepare ('SELECT * FROM news_content WHERE hot = "false" AND trash="false" AND class_of BETWEEN ? AND ? ORDER BY article_id DESC');
$stmt->bind_param('ss', $dateFrom, $dateTo);
$stmt->execute();
$result = $stmt->get_result();

I wrote it on mobile and haven't tested it. Let me know if I made a mistake. — Dharman, Sep 15 '19 at 16:33
I am not sure they always have '08-01' as the rest of the date. Probably year(class_of) in the query would be a better match, though less optimal — Your Common Sense, Sep 15 '19 at 16:34

Using a GET variable in a MySQL/PHP query

1 Answers1