FIELD to get MySQL Query

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

output
1
0
0
0
0
0

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

Subhendu Mohapatra

Author

🎥 Join me live on YouTube

Passionate about coding and teaching, I publish practical tutorials on PHP, Python, JavaScript, SQL, and web development. My goal is to make learning simple, engaging, and project‑oriented with real examples and source code.

Subscribe to our YouTube Channel here

FIELD : Search for String position

With WHERE condition

PHP Script

Subhendu Mohapatra