0

It's my first post on StackOverflow. I have a small problem with sql query. I need search by year in topic title (phpbb3).

Now that is my code (result by year - ex. 2019):

// $search_by_year = 2019;
$sql_array = array(
    'SELECT'    => 'COUNT(t.topic_id) AS num_topics',
    'FROM'      => array(
        TOPICS_TABLE    => 't',
    ),
    'WHERE'     => '(t.topic_title LIKE "%'. $db->sql_escape($search_by_year). '%") AND t.forum_id = ' . $forum_id . ' AND ' . $phpbb_content_visibility->get_visibility_sql('topic', $forum_id, 't.'),
);

I want to create second query to get topic less than X year. It is possible?

Something like this

(t.topic_title LIKE "%'. $db->sql_escape($search_by_year). '%") AND t.topic_title <= '2019'

But topic_title is Varchar (example - Best Computers for 2019) - not INT. Anyone can help?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
Crazzel
  • 1
  • 1
  • Possible duplicate of [Cast from VARCHAR to INT - MySQL](https://stackoverflow.com/questions/12126991/cast-from-varchar-to-int-mysql) – Script47 Sep 23 '19 at 15:55
  • 1
    Are the `topic_title` values _only_ a year or are they some characters plus a year such as "Best Computers For 2019"? – Patrick Q Sep 23 '19 at 15:57
  • Yes, topic_title are with some characters and year (some like your example - Best Computers For 2019) – Crazzel Sep 23 '19 at 16:00
  • That's probably going to get pretty messy. Would it be feasible to use `topic_time` or `topic_last_post_time` instead? – Patrick Q Sep 23 '19 at 16:04
  • You're right, but problem is when topic_title look like this: "Best Movies from 1999", "Titanic 1997", "Avatar 2010" etc. I want to select ex. movies from 1999 and less (I think easiest way is create another column with year)... – Crazzel Sep 23 '19 at 16:12

0 Answers0