Search code examples
phpsqlfiltermariadbsql-like

is LIKE, but with int representing an string on which should be checked

Let's say I have a table (tableA) with a column Kwaliteit, which will hold an int value (0, 1, 2, 3) that will represent some string values.

These string values are stored serialized in an another table (tableB) like this:

a:4:{i:0;s:4:"Goed";i:1;s:5:"Matig";i:2;s:6:"Slecht";i:3;s:12:"Afgeschreven";}

Which will give a PHP array like this:

Array
(
    [0] => Goed
    [1] => Matig
    [2] => Slecht
    [3] => Afgeschreven
)

The thing is, I want to filter on Afgeschreven. So I insert that in the LIKE part of the query, but that isn't working because there's an int instead of the string in tableA.

How can I bypass this problem? Can I like alter the column value temporarily with the string value to do the filter?

Edit Here's the structure of tableA (Kist)

CREATE TABLE IF NOT EXISTS `Kist` (
  `idKist` int(11) NOT NULL AUTO_INCREMENT,
  `idKistType` int(11) NOT NULL,
  `Tag1` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `Tag2` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `VisueelNr` varchar(45) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Bouwjaar` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `kwaliteit` tinyint(1) NOT NULL,
  `Actief` tinyint(1) NOT NULL,
  PRIMARY KEY (`idKist`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=982 ;

INSERT INTO `Kist` (`idKist`, `idKistType`, `Tag1`, `Tag2`, `VisueelNr`, `Bouwjaar`, `kwaliteit`, `Actief`) VALUES
(1, 1, '0086-1700-0000-0000-0000-371E', '0086-1700-0000-0000-0000-3868', '0', '', 3, 0),
(2, 1, '0086-1700-0000-0000-0000-413F', '0086-1700-0000-0000-0000-409A', '0', '', 0, 1);

And tableB (Instellingen)

CREATE TABLE IF NOT EXISTS `Instellingen` (
  `idInstellingen` int(11) NOT NULL AUTO_INCREMENT,
  `Instelling` varchar(45) COLLATE utf8_unicode_ci NOT NULL,
  `Waarde` longtext COLLATE utf8_unicode_ci,
  PRIMARY KEY (`idInstellingen`),
  UNIQUE KEY `Instelling_UNIQUE` (`Instelling`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=220 ;

INSERT INTO `Instellingen` (`idInstellingen`, `Instelling`, `Waarde`) VALUES
(200, 'kistKwaliteit', 'a:4:{i:0;s:4:"Goed";i:1;s:5:"Matig";i:2;s:6:"Slecht";i:3;s:12:"Afgeschreven";}');

The serialized value is stored in column Waarde

Solution

  • Since you saved the mapping of ints to strings for the kwaliteit values serialised as text, it's not possible to select based on the text/label in MySQL.

    What you could do, for example, is create a new table Kwaliteit, with 2 columns, id and label. Then you could join that new table with the Kist table ON Kist.kwaliteit = Kwaliteit.id The where condition would be with those column names WHERE label = 'Afgeschreven'

    Another possibility would be changing the Instellingen table: Split the Waarde column into 2, id and value. That would mean 4 rows in the table for the kwaliteit in that table:

    (200, 'kistKwaliteit', '0', 'Goed'), 
(200, 'kistKwaliteit', '1', 'Matig'), 
(200, 'kistKwaliteit', '2', 'Slecht'), 
(200, 'kistKwaliteit', '3', 'Afgeschreven')

    The request could be 

    SELECT * FROM Kist WHERE kwaliteit = 
(SELECT id FROM Instellingen WHERE Instelling = 'kistKwaliteit' AND value = 'Afgeschreven')

    If you want to keep the database structure the same, you have to handle getting the ID that matches the quality you want on the PHP side. For example if it's something a user can chose via dropdown, make the value the ID as saved in the serialised array, and use the labels just as labels. Or when getting passed the string for the quality, first get the serialized array from the DB to find the ID, then use that ID in the SELECT.