Hi,

I have the following db table:

CREATE TABLE foo (
    id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
    this VARCHAR(100) NOT NULL,
    that VARCHAR(100) NOT NULL,
    other VARCHAR(100) NOT NULL,
    PRIMARY KEY(id),
    UNIQUE INDEX(this, that, other)
);
[download]

I want to make searching it as easy as possible and at the moment am using:

sub find_entries
{

    my $sql = "SELECT * " .
              "FROM foo " . 
              "WHERE this " .
              "LIKE '%" . $this . "%' " .
              "AND that " .
              "LIKE '%" . $that . "%' " .
              "AND other " .
              "LIKE '%" . $other . "%'";

    my $sth = $dbh->prepare($sql);
    $sth->execute;
    ...
    
    return $entries;

}
[download]

This works great at the moment but dies horribly if I enter the name Mike O'Brien for one of the fields.

The ' in the name is causing problems.

I am using placeholders everywhere else in my program but can't work out how to do it with this kind of query. The combination of ' and " and % has really got me confused. However, I really need to work it out because of the above problem and the ability of bind_param to overcome this.

Would someone be able to tell me how to write the above query using place holders?

I have tried:

sub find_entries
{

    my ($this, $that, $other) = @_;
    
    my $sth = $dbh->prepare("SELECT * " .
                            "FROM foo " . 
                            "WHERE this " .
                            "LIKE '%" . "?" . "%' " .
                            "AND that " .
                            "LIKE '%" . "?" . "%' " .
                            "AND other " .
                            "LIKE '%" . "?" . "%'");

    $sth->bind_param(1, $this);
    $sth->bind_param(2, $that);
    $sth->bind_param(3, $other);
    
    $sth->execute();
    
    ...
    
    return $entries;
}
[download]

But this gives me output of:

*** unhandled exception in callback:
***   DBD::mysql::st bind_param failed: Illegal parameter number
[download]

I have tried a number of other combinations but cannot work out how to get the placeholders recognised and then be able to bind parameters to them.

Any help would be greatly appreciated.