php - 如何使用正则表达式在 sql 中搜索包含 # 标签的记录,但忽略 #FFFFFF 或 html 标签内的任何其他颜色
问题描述
我使用 codeigniter 作为框架。我正在设置一个过滤选项来显示 SQL 数据库中包含哈希标签的项目,它工作得很好,但是它也返回了包含 html 标签的记录,例如
style="color:#f00; color:#ff0000; ETC
里面只有#个标签
我做了一些研究并且对正则表达式的了解有限(这对我来说就像一门外语!)。
这工作正常。
$hashtag = "#" . $hashtag;
$this->db->like("feed_item.content", $hashtag);
我想我需要这样的东西(苦苦挣扎)。
$sq = "/^(?![\"\'].*#.*[\"\']).*$/m";
$this->db->where('LOWER(otherColumn) REGEXP', $sq);
我复制了上面的代码,并尝试修改它,但什么也没做。
我没有收到任何错误,但这是 PHP 的典型情况 :( 当我这样做时它没有返回任何输出:
public function get_hashtag_feed($hashtag, $userid, $page)
{
$hashtag = "#" . $hashtag;
$sq = $re = '/^(?!["\'].*#.*["\']).*$/m/g';
//$this->db->like("feed_item.content", $hashtag);
return $this->db
->select("feed_item.ID, feed_item.content, feed_item.post_as,
feed_item.timestamp, feed_item.userid, feed_item.likes,
feed_item_polls.question as poll_question, feed_item_polls.type as poll_type,
feed_item_polls.votes as poll_votes,
user_blog_posts.Id as blog_postid, user_blog_posts.title as blog_post_title,
user_blog_posts.image as blog_post_image")
->join("users", "users.ID = feed_item.userid")
->join("user_images", "user_images.ID = feed_item.imageid", "left outer")
->join("user_albums", "user_albums.ID = user_images.albumid", "left outer")
->join("user_videos", "user_videos.ID = feed_item.videoid", "left outer")
->join("users as profile", "profile.ID = feed_item.profile_userid", "left outer")
->join("feed_item_polls", "feed_item_polls.ID = feed_item.pollid", "left outer")
->join("user_blog_posts", "user_blog_posts.ID = feed_item.blog_postid", "left outer")
->join("pages", "pages.ID = feed_item.pageid", "left outer")
->join("page_users", "page_users.pageid = feed_item.pageid AND page_users.userid = " . $userid, "LEFT OUTER")
->join("calendar_events", "calendar_events.ID = feed_item.eventid", "left outer")
->join("feed_likes", "feed_likes.postid = feed_item.ID AND feed_likes.userid = " . $userid, "LEFT OUTER")
->join("user_saved_posts", "user_saved_posts.postid = feed_item.ID AND user_saved_posts.userid = " . $userid, "left outer")
->join("feed_item_subscribers", "feed_item_subscribers.postid = feed_item.ID and feed_item_subscribers.userid = " . $userid, "LEFT OUTER")
->where("LOWER(otherColumn) REGEXP", $sq)
->order_by("feed_item.ID", "DESC")
->limit(10,$page)
->get("feed_item");
解决方案
我刚刚解决了,当你有答案时非常简单。
这是我正在使用的代码,它需要一点点改进,但使用 REGEXP 可以完美运行。
$this->db->where("!(feed_item.content REGEXP '#(([a-fA-F0-9]){3})') AND feed_item.content like '%#%'");
推荐阅读
- amazon-web-services - Docker 在 AWS Elastic Beanstalk 中失败
- vue.js - 如何在设置值时更改 Vue 中的输入值并在它是数组对象时更新它?
- ruby-on-rails - video_tag 未在 Safari 上显示 - RoR
- javascript - 将坐标 gmap 转换为路径 SVG
- excel - 复制到剪贴板 Powershell 故障
- python - 使用集群时如何忽略颜色或 alpha
- android - Flutter IOS 构建失败并出现以下错误
- laravel - Chartisan/Laravel - >“调用未定义的方法”错误
- python-3.x - 将 RGB 图像转换为 Oklab 颜色空间
- regex - 获取包含名称的句子的正则表达式