FIELD : Search for String position

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_namel_nameclass
JohnDeoFour
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


Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com

    Post your comments , suggestion , error , requirements etc here





    SQL Video Tutorials










    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer