SELECT FIELD('str','str1','str2','str3','str4','str');
Output is 5.
The first string is the searched string.
Returns 0 if searched string is not found.
Using our student table.
SELECT FIELD('john',f_name,l_name) as output from student_name
With WHERE condition
Get all records where any column matches search string.
SELECT * FROM `student_name` WHERE FIELD('john',f_name,l_name) >0
f_name | l_name | class |
John | Deo | Four |
We get one record only from our table.
WHERE condition Query
PHP Script
MySQLi database connection string
require "config.php";// Database connection
$query="SELECT * FROM `student_name` WHERE FIELD('john',f_name,l_name) >0";
if ($result_set = $connection->query($query)) {
while($row = $result_set->fetch_array(MYSQLI_ASSOC)){
echo $row['f_name'],$row['l_name'],$row['class'].."<br>";
}
$result_set->close();
}
Here is the sql dump of student_name table for your use.
CREATE TABLE IF NOT EXISTS `student_name` (
`f_name` varchar(20) DEFAULT NULL,
`l_name` varchar(20) DEFAULT NULL,
`class` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `student_name`
--
INSERT INTO `student_name` (`f_name`, `l_name`, `class`) VALUES
('John', 'Deo', 'Four'),
('Larry', NULL, 'Four'),
('Ronald', NULL, 'Five'),
('Garry', 'Miller', 'Five'),
(NULL, NULL, 'Five'),
(NULL, 'Ruller', NULL);
← SQL String References
elt(): String from the postion→
substring_index to get part of string using delimiter→
FIND_IN_SET →
← Subscribe to our YouTube Channel here