CODEWITHSUNDEEP
php
Niklas Granander
Niklas Granander

Reputation: 25

Full-Text search with PHP and PDO not returning any result

I've searched a lot on this matter but I can't find out what's wrong with my setup.

I'm trying to do a full-text search using PDO and PHP, but I don't get any results or error messages at all.

My table contains customer details:

id          int(11)          AUTO_INCREMENT      
name        varchar(150)         
lastname    varchar(150)         
company     varchar(250)         
adress      varchar(150)         
postcode    int(5)       
city        varchar(150)         
email       varchar(250)         
phone       varchar(20)      
orgnr       varchar(15)      
timestamp   timestamp        CURRENT_TIMESTAMP

I did run the SQL-query:

ALTER TABLE system_customer ADD FULLTEXT(name, lastname, ... ...)

except for the columns 'id', 'postcode' and 'timestamp'. No signs of any trouble so far.

I have no idea if the problem lies in my DB configuration or my PHP code so here goes the PHP:

$STH =  $DBH->prepare("SELECT name,lastname,company,adress,city,phone,email,orgnr FROM '.$db_pre.'customer WHERE MATCH(name,lastname,company,adress,city,phone,email,orgnr) AGAINST(:search IN BOOLEAN MODE)");
//Bind placeholders
$STH->bindParam(':search', $data);
$STH->execute();
$rows = $STH->fetchAll();
//Just for testing
print_r($DBH->errorInfo());

if(empty($rows)) {
    echo '[.....]';
} else {
echo '[....]';
    foreach ($rows as $row) {
        echo '<tr data-href="new_order.php?cid='.$row['id'].'">';
        echo '<td>'.$row['name'].'</td>';
        echo '<td>'.$row['lastname'].'</td>';
        echo '<td>'.$row['company'].'</td>';
        echo '<td>'.$row['phone'].'</td>';
        echo '<td>'.$row['email'].'</td>';
        echo '<td>'.date("Y-m-d", strtotime($row['timestamp'])).'</td>';
        echo '</tr>';
    }
echo '[....]';
}

I tried to change the parameter in the searchquery to a string like 

AGAINST('testcompany somename' IN BOOLEAN MODE)

I also read about that if a word is found in 50% or more of the rows it counts as a common word. I'm pretty sure that's not the case here (uses very specific words)

My table uses MyISAM engine

I don't get any results or any error messages.

Please help my point out what's wrong

Thank you

Upvotes: 2

Views: 80

Answers (1)

jeroen
jeroen

Reputation: 91792

Your first line is wrong:

$STH =  $DBH->prepare("SELECT name,lastname,company,adress,city,phone,email,orgnr
                       FROM '.$db_pre.'customer WHERE MATCH(name,lastname,company,adress,city,phone,email,orgnr) AGAINST(:search IN BOOLEAN MODE)");

To concatenate like you do, you need to close the string with double quotes or you put the variable directly in the query:

$STH =  $DBH->prepare("SELECT name,lastname,company,adress,city,phone,email,orgnr
                       FROM ".$db_pre."customer WHERE MATCH(name,lastname,company,adress,city,phone,email,orgnr) AGAINST(:search IN BOOLEAN MODE)");

or:

$STH =  $DBH->prepare("SELECT name,lastname,company,adress,city,phone,email,orgnr
                       FROM {$db_pre}customer WHERE MATCH(name,lastname,company,adress,city,phone,email,orgnr) AGAINST(:search IN BOOLEAN MODE)");

Upvotes: 2

Related Questions