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.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.