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 & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect:

Subscribe to our YouTube Channel here

FIELD : Search for String position

With WHERE condition

PHP Script

Subhendu Mohapatra