Geek Answers Handbook

LIKE, but with an int representing the string on which to be checked

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

These string values ​​are stored serialized in another table ( tableB) as follows:

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 the PHP array as follows:

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

The thing is, I want to filter on Afgeschreven. Therefore, I insert this into the request part LIKE, but this does not work, because instead of stringin tableAthere is int.

How can I get around this problem? Can I change the temporary value of a column with a string value for a filter?

Edit Here is the structure 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);

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";}');

Waarde

+4
3

ints kwaliteit, , / MySQL.

, , Kwaliteit , . Kist ON Kist.kwaliteit = Kwaliteit.id where WHERE label = 'Afgeschreven'

Instellingen: Waarde 2, id . 4 kwaliteit :

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


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

, , PHP. , , , ID, , , . , , , , SELECT.

+1

, , :

select * from tablea where kwaliteit = 'Afgeschreven';

, kwaliteit - , . , :

select * from tablea where kwaliteit =
 (select kwaliteit from tableb where text = 'Afgeschreven');
+2

You can not. You must search by array key (aka 3 or 2), this will also speed up the query.

For an updated structure, you can try:

select * from Instellingen where Waarde like "%Afgeschreven%";

-1
source

More articles:


All Articles