11 Replies - 7739 Views - Last Post: 05 August 2011 - 02:06 AM Rate Topic: -----

#1 zauii  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-January 11

Mysqli, bind param with %like% ?

Posted 04 August 2011 - 11:33 PM

Is it possible to use mysqli's bind_param for this?

Essentially what im doing is a search function, so the "model" will receive a searchstring and then look in certain areas of the database for a result using the LIKE mysql however..

I seem to just get an invalid query if i attempt something like %?% in the query so instead i've to write the variable and skip the bind_param part in something like this: '%$string%' but this would render the whole purpose of bind param / mysqli useless since sql injection would be possible right?

Just wondering if anyone have any idea?

Is This A Good Question/Topic? 0
  • +

Replies To: Mysqli, bind param with %like% ?

#2 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3520
  • View blog
  • Posts: 10,159
  • Joined: 08-June 10

Re: Mysqli, bind param with %like% ?

Posted 04 August 2011 - 11:40 PM

I know from PDO that you use
$ps = $pdo->prepare("SELECT text FROM mytable WHERE text LIKE ?");
$ps->bindValue(1, "%$searchTerm%", PDO::PARAM_STR);

Was This Post Helpful? 3
  • +
  • -

#3 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1003
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Mysqli, bind param with %like% ?

Posted 04 August 2011 - 11:50 PM

Exactly as Dormilich said, you put the wildcards into the bind itself, as such (with MySQLi - I'm not ripping your answer Dormi):

$likeString = '%' . $string . '%';
$query = $conn->prepare("SELECT * FROM tableName WHERE columnName LIKE ?");
$query->bind_param('s', $likeString);
$query->execute();

This post has been edited by RudiVisser: 05 August 2011 - 12:43 AM

Was This Post Helpful? 1
  • +
  • -

#4 zauii  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-January 11

Re: Mysqli, bind param with %like% ?

Posted 04 August 2011 - 11:58 PM

Thanks guys.
Was This Post Helpful? 0
  • +
  • -

#5 zauii  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-January 11

Re: Mysqli, bind param with %like% ?

Posted 05 August 2011 - 12:23 AM

Gives the following error tho:
"Cannot pass parameter 2 by reference"

SELECT news.newsid, news.headline FROM news INNER JOIN users ON news.authorid = users.pk WHERE news.headline LIKE ? OR news.message LIKE ? OR users.username LIKE ?

$stmt->bind_param('sss', '%'.$string.'%', '%'.$string.'%', '%'.$string.'%');

Was This Post Helpful? 0
  • +
  • -

#6 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1003
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Mysqli, bind param with %like% ?

Posted 05 August 2011 - 12:43 AM

Sorry, I've updated my post. You can't pass it in by reference because the static strings there are values (basically you can only bind variables, not constants).

Just create that string earlier into a variable and utilise that in the bind_param method.

This post has been edited by RudiVisser: 05 August 2011 - 12:44 AM

Was This Post Helpful? 1
  • +
  • -

#7 zauii  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 35
  • Joined: 02-January 11

Re: Mysqli, bind param with %like% ?

Posted 05 August 2011 - 12:51 AM

View PostRudiVisser, on 05 August 2011 - 12:43 AM, said:

Sorry, I've updated my post. You can't pass it in by reference because the static strings there are values (basically you can only bind variables, not constants).

Just create that string earlier into a variable and utilise that in the bind_param method.


Yep, that solved it.
Thanks again.
Was This Post Helpful? 0
  • +
  • -

#8 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3520
  • View blog
  • Posts: 10,159
  • Joined: 08-June 10

Re: Mysqli, bind param with %like% ?

Posted 05 August 2011 - 12:59 AM

View PostRudiVisser, on 05 August 2011 - 08:50 AM, said:

(with MySQLi - I'm not ripping your answer Dormi)

I find Prepared Statements with MySQLi too uncomfortable to use them, so I can only reason from what I know in PDO.
Was This Post Helpful? 0
  • +
  • -

#9 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1003
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Mysqli, bind param with %like% ?

Posted 05 August 2011 - 01:19 AM

View PostDormilich, on 05 August 2011 - 08:59 AM, said:

I find Prepared Statements with MySQLi too uncomfortable to use them, so I can only reason from what I know in PDO.

I dunno, find them both to be about the same to be honest.

However I do like being able to bind multiple parameters in one call.
Was This Post Helpful? 0
  • +
  • -

#10 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3520
  • View blog
  • Posts: 10,159
  • Joined: 08-June 10

Re: Mysqli, bind param with %like% ?

Posted 05 August 2011 - 01:38 AM

then try that in MySQLi:
$ps = $pdo->prepare("SELECT firstname, lastname, phone, address, bday FROM organiser WHERE state = ?;");
$ps->bindValue(1, $_GET['state'], PDO::PARAM_STR);
$ps->setFetchMode(PDO::FETCH_CLASS, "AddressTable");
$ps->execute();

echo PHP_EOL . "<table>";
foreach ($ps as $entry)
{
    echo $entry;
}
echo PHP_EOL . "</table>";

class AddressTable
{
    private $firstname, $lastname, $phone, $address, $bday;

    const TABLE_ROW = <<<ROW
    <tr>
        <td>%s</td>
        <td>%s</td>
        <td>%d</td>
        <td>%s</td>
    </tr>
ROW;

    public function __construct()
    {
        $this->bday = date("d/m/Y", $this->bday);
    }

    public function __toString()
    {
        return sprintf(PHP_EOL . self::TABLE_ROW, $this->lastname . ", " . $this->firstname, $this->bday, $this->phone, $this->address);
    }
}

This post has been edited by Dormilich: 05 August 2011 - 01:40 AM

Was This Post Helpful? 1
  • +
  • -

#11 RudiVisser  Icon User is offline

  • .. does not guess solutions
  • member icon

Reputation: 1003
  • View blog
  • Posts: 3,562
  • Joined: 05-June 09

Re: Mysqli, bind param with %like% ?

Posted 05 August 2011 - 01:58 AM

Am I missing the point?

$stmt = $conn->prepare('SELECT firstname,lastname,phone,address,bday FROM organiser WHERE state = ?');
$stmt->bind_param('s', $_GET['state']);
$stmt->execute();
$results = $stmt->get_result();

echo PHP_EOL . "<table>";
while ($entry = $results->fetch_object('AddressTable')) {
    echo $entry;
}
echo PHP_EOL . "</table>";


Of course, untested, but seems perfectly fine.
Was This Post Helpful? 2
  • +
  • -

#12 Dormilich  Icon User is offline

  • 痛覚残留
  • member icon

Reputation: 3520
  • View blog
  • Posts: 10,159
  • Joined: 08-June 10

Re: Mysqli, bind param with %like% ?

Posted 05 August 2011 - 02:06 AM

interesting to know. I have always looked for a way to convert MySQLi_STMT into MySQLi_Result but to no avail.

still, Iíll stay with PDO due to the fact that itís not limited to MySQL and that it can throw Exceptions in the case of an error.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1