PDO run LIKE query for multiple table columns for a single string

Question

I want to use like on two columns in my table now.

tablename = roomnames

table columns = roomID, roomName, roomNo

i have an input field which sends a string value on which the query should execute.

Here below is the PHP PDO code

<?php
/**
 * Created by PhpStorm.
 * User: HaiderHassan
 * Date: 9/3/14
 * Time: 9:52 PM
 */
header('Access-Control-Allow-Origin: *');
try {
    $conn = new PDO('mysql:host=localhost;dbname=houserentsystem;charset=utf8', 'root', 'admin');
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch(PDOException $e) {
    echo 'ERROR: ' . $e->getMessage();
}
if($_POST['searchFilter']){
    $searchFilter = $_POST['searchFilter'];
    $stmt = $conn->prepare("SELECT roomName, roomNo FROM roomnames WHERE roomName LIKE ? OR roomNo LIKE ?");
    $stmt->execute(array('%'.$searchFilter.'%'));
    $results = $stmt->fetchAll(PDO::FETCH_ASSOC);
    $stmt->closeCursor();
    print_r(json_encode($results));
}

the query i tried is wrong and not working here is what i tried now.

$stmt = $conn->prepare("SELECT roomName, roomNo FROM roomnames WHERE roomName LIKE ? OR roomNo LIKE ?");
$stmt->execute(array('%'.$searchFilter.'%'));

i want if i search room No or roomName, it will show me result if that room number or room name is present in the table of database.

Sorry for my bad English. I hope i have explained. i want to search in multiple columns for a table against a input string, Input string can be a no or name. Number is for roomNo and name for room name.

VMai · Accepted Answer · 2014-09-06 15:13:27Z

6

You've got to have a value for every parameter, so double your searchfilter in the array of the values for the parameters:

$stmt = $conn->prepare("SELECT roomName, roomNo FROM roomnames WHERE roomName LIKE ? OR roomNo LIKE ?");
$stmt->execute(array('%'.$searchFilter.'%','%'.$searchFilter.'%' ));

answered Sep 6, 2014 at 15:13

VMai

10.4k9 gold badges28 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Daniel Gasser · Accepted Answer · 2014-09-06 15:47:09Z

1

You could use the MATCH struct from MySQL:

SELECT roomName, roomNo FROM roomnames WHERE MATCH (roomName, roomNo IN BOOLEAN MODE) AGAINST(?)

The params needed inside AGAINST(?) Would then go as an array inside $stmt->execute(...)

For further information, see here.

Restrictions:

Full-text indexes can be used only with MyISAM tables. (In MySQL 5.6 and up, they can also be used with InnoDB tables.) Full-text indexes can be created only for CHAR, VARCHAR, or TEXT columns.

From the docs:

The IN BOOLEAN MODE prevents from searching only by words, but by string.

edited Sep 6, 2014 at 15:47

answered Sep 6, 2014 at 15:15

Daniel Gasser

5,1738 gold badges48 silver badges64 bronze badges

6 Comments

Sizzling Code Over a year ago

problem solve with @VMai query, But i also tried your query and its not working.

Sizzling Code Over a year ago

$stmt = $conn->prepare("SELECT roomName, roomNo FROM roomnames WHERE MATCH (?, ?) AGAINST ('houserentsystem')");     $stmt->execute(array('%'.$searchFilter.'%','%'.$searchFilter.'%' ));

not working.

Daniel Gasser Over a year ago

@SizzlingCode Sry to hear that, I edited my answer. It's all about the query, not the PDO, Glad to hear that an answer helped you. And your title says "multiple tables", that's why I put the MATCH thing...

VMai Over a year ago

It should be $stmt = $conn->prepare("SELECT roomName, roomNo FROM roomnames WHERE MATCH (roomName, roomNo) AGAINST (?)");, I think. But the problem with fulltext search is that it searches words by default.

VMai Over a year ago

Please have a look at my suggestion: the columns to be searched go into the MATCH clause, the search term into AGAINST. We know the two columns to be sarched: roomName and roomNo.

|

Collectives™ on Stack Overflow

PDO run LIKE query for multiple table columns for a single string

2 Answers 2

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related