How to solve this MYSQL Query in php page?-CodePudding

I am new on MYSQL. I have a data in MYSQL database like this (example):-

S.no	Book Name	Date of Publication	Publication	ctags
1	ABC	2021	Oxford	Free,Video,Audio,Licensed
2	DEF	2020	Cambridge	Free,Video,Licensed,
3	GHI	2019	Bloomberry	Free,Audio,Licensed
4	JKL	2018	Penguin	Free,
5	MNO	2017	Jaya Prakash	Licensed
6	PQR	2016	Null	Free,Video,Audio

There are multiple tag in the ctags column that are separated with comma. I shared above table only for simple example. In my MYSQL Database there is a column named as ctags and here I am sharing the original mysql querry on php page.

The below code show all the information (s.no 1 to 6) in the page but I want to show those resource that are not ctags with licensed (s.no 4 and 6)

$qnew = "SELECT title, 
                location, 
                access_restrictions 
         FROM title t, location_title lt, location l 
         WHERE t.title_id = lt.title_id 
         AND l.location_id = lt.location_id 
         AND eres_display = 'Y' 
         order by t.title_id DESC limit 0,5";

The below code show nothing but I want to show those resource that are ctags with licensed (s.no 1, 2, 3 and 6).

$qnew = "SELECT title, 
                location, 
                access_restrictions 
         FROM title t, location_title lt, location l 
         WHERE t.title_id = lt.title_id AND l.location_id = lt.location_id 
         AND eres_display = 'Y' 
         AND ctags = 'Licensed' 
         order by t.title_id DESC limit 0,5";

What should I use to get the information from the MYSQL database. Kindly help me to get this.

CodePudding user response：

Another way of doing is using FIND_IN_SET , but keep in mind for larger tables it is a performance killer, you should normalize your data

CREATE TABLE t1  (
  S_no int,
  Book_Name varchar(5),
  Date_of_Publication int,
  Publication varchar(15),
  ctags varchar(255));

INSERT INTO t1 VALUES
(1,'ABC',2021,'Oxford','Free,Video,Audio,Licensed'),
(2,'DEF',2020,'Cambridge','Free,Video,Licensed'),
(3,'GHI',2019,'Bloomberry','Free,Audio,Licensed'),
(4,'JKL',2018,'Penguin','Free'),
(5,'MNO',2017,'Jaya Prakash','Licensed'),
(6,'PQR',2016,'Null','Free,Video,Audio');

For Licensed only :

SELECT * 
FROM t1 
WHERE find_in_set('Licensed',ctags) >0;

Demo

For non Licensed only :

SELECT * 
FROM t1 
WHERE find_in_set('Licensed',ctags) <=0;

Demo

CodePudding user response：

Use Below Query

$qnew = "SELECT title, location, access_restrictions FROM title t, location_title lt, location l WHERE t.title_id = lt.title_id AND l.location_id =lt.location_id  AND eres_display = 'Y' AND FIND_IN_SET('Licensed', ctags)  order by t.title_id DESC limit 0,5";

CodePudding user response：

You can use like operator to match Licensed in ctags col.

     $qnew = "SELECT title, 
            location, 
            access_restrictions 
     FROM title t, location_title lt, location l 
     WHERE t.title_id = lt.title_id 
     AND l.location_id = lt.location_id 
     AND eres_display = 'Y' 
     ctags NOT like  '%Licensed%'
     order by t.title_id DESC limit 0,5";

For licensed you can use something like this.

   $qnew = "SELECT title, 
            location, 
            access_restrictions 
     FROM title t, location_title lt, location l 
     WHERE t.title_id = lt.title_id AND l.location_id = lt.location_id 
     AND eres_display = 'Y' 
     AND ctags like '%Licensed%' 
     order by t.title_id DESC limit 0,5";