So führen Sie eine SELECT-Abfrage mit JOINs für Unterabfragen ohne ON-Bedingungen in CodeIgniter aus ⇐ Php

[Anonymous]

Wie kann ich das folgende SQL in einer Modellmethode in meiner CodeIgniter-Anwendung implementieren?

Code: Select all

SELECT *
FROM userinfo t1
JOIN (
SELECT value AS val_pas, attribute AS att_type
FROM radcheck
WHERE radcheck.username = "$username"
AND radcheck.attribute LIKE "%Password"
) t2
JOIN (
SELECT value AS val_simulate
FROM radcheck
WHERE radcheck.username = "$username"
AND radcheck.attribute LIKE "Simultaneous-Use"
) AS t3
WHERE t1.`username` = "$username"

Diese funktionierende Abfrage gibt einen Datensatz wie diesen zurück:


Hier ist mein Schema: SQLize Demo

Code: Select all

CREATE TABLE `radcheck` (
`id` int(11) UNSIGNED NOT NULL,
`username` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`attribute` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`op` char(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT '==',
`value` varchar(253) COLLATE utf8_unicode_ci NOT NULL DEFAULT ''
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `radcheck` (`id`, `username`, `attribute`, `op`, `value`) VALUES
(12, '7yjZUdY3', 'Crypt-Password', ':=', 'Yg2SzW5P'),
(13, '7yjZUdY3', 'Expiration', ':=', '30 Jun 2021'),
(14, '7yjZUdY3', 'Simultaneous-Use', ':=', '1'),
(15, 's8faP66m', 'Cleartext-Password', ':=', '5VnNCeeH'),
(16, 's8faP66m', 'Expiration', ':=', '30 Jun 2021'),
(17, 's8faP66m', 'Simultaneous-Use', ':=', '1');

CREATE TABLE `userinfo` (
`id` int(11) UNSIGNED NOT NULL,
`username` varchar(128) DEFAULT NULL,
`firstname` varchar(200) DEFAULT NULL,
`lastname` varchar(200) DEFAULT NULL,
`email` varchar(200) DEFAULT NULL,
`department` varchar(200) DEFAULT NULL,
`company` varchar(200) DEFAULT NULL,
`workphone` varchar(200) DEFAULT NULL,
`homephone` varchar(200) DEFAULT NULL,
`mobilephone` varchar(200) DEFAULT NULL,
`address` varchar(200) DEFAULT NULL,
`city` varchar(200) DEFAULT NULL,
`state` varchar(200) DEFAULT NULL,
`country` varchar(100) DEFAULT NULL,
`zip` varchar(200) DEFAULT NULL,
`notes` varchar(200) DEFAULT NULL,
`changeuserinfo` varchar(128) DEFAULT NULL,
`portalloginpassword` varchar(128) DEFAULT '',
`enableportallogin` int(32) DEFAULT 0,
`creationdate` datetime DEFAULT CURRENT_TIMESTAMP,
`creationby` varchar(128) DEFAULT NULL,
`updatedate` datetime DEFAULT NULL,
`updateby` varchar(128) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `userinfo` (`id`, `username`, `firstname`, `lastname`, `email`, `department`, `company`, `workphone`, `homephone`, `mobilephone`, `address`, `city`, `state`, `country`, `zip`, `notes`, `changeuserinfo`, `portalloginpassword`, `enableportallogin`, `creationdate`, `creationby`, `updatedate`, `updateby`) VALUES
(6, '7yjZUdY3', '1', '1', '', '', '', '', '', '', '', '', '', '', '', '', '0', '', 0, '2021-06-14 11:47:45', 'administrator', '2021-06-14 11:51:17', 'administrator'),
(7, 's8faP66m', '2', '2', '', '', '', '', '', '', '', '', '', '', '', '', '0', '', 0, '2021-06-14 11:52:25', 'administrator', '2021-06-14 11:53:37', 'administrator');