Query using like to trieve field value with additional parameters

[jkkenzie]

I have two tables, mod_music_category has a field id which autoincrements and the other table, mod_music_entries has category_id field whose value is mod_music_category.id but enclosed with "b" and "e"  .e.g."b mod_music_category.id e" no spaces like b1e.

 

I am trying to select all records from mod_music_entries whose category_id is LIKE or EQUAL to mod_music_category id field

as follows:

SELECT `c`.id, `c`.category, `e`.`category_id`, `c`.teaser, `c`.style FROM `".DB_EXTENTION."mod_music_category` `c` LEFT JOIN `".DB_EXTENTION."mod_music_entries` `e` ON( `e`.`category_id` = '%b`c`.`id`e%') ORDER BY c.`priority` ;

Its returning all values even if there is no join.

 

Is ther a better way. Thanks

[mac_gyver]

sorry to be blunt, but you would absolutely never store an id that defines the relationship between tables like this. you would use the actual value. if you have a need to display the id with the b and e around it, you would add those characters at the point where you are producing the output to display.

 

the reason your query is returning everything as a match is it's probably doing a type conversion between strings/numbers and coming up with a true value that matches everything.

[Barand]

You are using LEFT JOIN so that will return all rows from mod_music_category, matched or not. Where there is a match you will get the data from the matched mod_music_entries records otherwise NULL in the fields from that table.

 

If you only want matched category records returned, use INNER JOIN.

[jkkenzie]

I have the followimng tables:

CREATE TABLE IF NOT EXISTS `mod_music_periods` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `the_in` varchar(4) NOT NULL,
  `period` varchar(4) NOT NULL,
  `priority` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  UNIQUE KEY `period` (`period`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=19 ;

CREATE TABLE IF NOT EXISTS `mod_music_category` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `category` varchar(250) NOT NULL,
  `teaser` text,
  `priority` int(11) NOT NULL DEFAULT '0',
  `style` varchar(25) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;

CREATE TABLE IF NOT EXISTS `mod_music_entries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` text COLLATE latin1_general_ci,
  `teaser` text COLLATE latin1_general_ci,
  `description` text COLLATE latin1_general_ci,
  `song_yr` varchar(4) COLLATE latin1_general_ci DEFAULT NULL,
  `song_name` varchar(250) COLLATE latin1_general_ci DEFAULT NULL,
  `photo` text COLLATE latin1_general_ci,
  `active` enum('1','0') COLLATE latin1_general_ci NOT NULL DEFAULT '0',
  `priority` int(5) DEFAULT NULL,
  `period_id` char(150) COLLATE latin1_general_ci DEFAULT NULL,
  `category_id` char(150) COLLATE latin1_general_ci NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=21 ;

1. When saving to mod_music_entries we allow an entry/record to have more than one "period_id" and "category_id".

2. A "mod_music_entries.period_id" field uses the mod_music_periods.id field enclosed in "b" and "e" characters plus a "/n". e.g. b1e /n b2e/n b3e/n   .. e.t.c

3. A "mod_music_entries.category_id" field uses the mod_music_category.id field enclosed in "b" and "e" characters plus a "/n". e.g. b1e /n b2e/n b3e/n   .. e.t.c

 

I need to select  mod_music_entries records but group first in mod_music_category.category then for each group of the later as a subgroup i need to group in mod_music_periods.period.

 

somthing like:

 

//Write Category one only if it has something in mod_music_entries.category_id

 1. mod_music_category.category

 

     //Write this period only if there is something in mod_music_entries.period_id

          a) mod_music_periods.period

                  i) mod_music_entries.name

                  ii) mod_music_entries.name

           b)mod_music_periods.period

                  i) mod_music_entries.name

                  ii) mod_music_entries.name

 

//Write Category two only if it has something in mod_music_entries.category_id

2. mod_music_category.category

 

    //Write this period only if there is something in mod_music_entries.period_id

            a) mod_music_periods.period

                  i) mod_music_entries.name

                  ii) mod_music_entries.name

            b)mod_music_periods.period

                    i) mod_music_entries.name

                    ii) mod_music_entries.name

Edited November 24, 2013 by jkkenzie

[Barand]

You need to normalize the period and category fields in the entries table storing each id (without the b,e and \n) in separate rows in separate tables.

CREATE TABLE IF NOT EXISTS `mod_music_entry_periods` (
 `id` int(1) NOT NULL AUTO_INCREMENT,
 `entryid` int(11) NOT NULL DEFAULT '0',
 `periodid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1  ;

CREATE TABLE IF NOT EXISTS `mod_music_entry_categories` (
 `id` int(1) NOT NULL AUTO_INCREMENT,
 `entryid` int(11) NOT NULL DEFAULT '0',
 `categoryid` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ;

So your relationship model would look like the attached

[jkkenzie]

Ok. thanks. That means when entries are entered in mod_music_entries, i include script to add to the extra tables you have suggested. Well, its a long shot , check this out 

http://sqlfiddle.com/#!2/f1361/1/0

Edited November 25, 2013 by jkkenzie

[jkkenzie]

Still that example doesn't work when category_id and period_id are different values for one entry. I think i should focus normalizing the tables.