Passing multiple parameters to prepared statement

Question

This is my server side code:

<?php
    include('DBconnection.php');

    $q = "";
    $q = $_REQUEST["q"];

    function getAlSubjects($searchtext){
        $connection = db_connect();
        $statement = $connection->prepare('select * from olsubjectmaster where (ifnull(?,"")="" or SubjectID like ? or SubjectID like ? ) ORDER BY SubjectID');

        $statement->bind_param(1,$searchtext,PDO::PARAM_STR, 200);
        $statement->bind_param(2,$searchtext.'%',PDO::PARAM_STR, 200);
        $statement->bind_param(3,'%'.$searchtext.'%',PDO::PARAM_STR, 200);        

        $result=$statement.execute();
        $connection.close();
        $statement.close();
        return $result;
    }

    $value='';

    while($row = getAlSubjects($q)->fetch_assoc()) {
        echo $row["SubjectID"];
    }
?>

When I execute this, it shows the following error:

Fatal error: Cannot pass parameter 2 by reference in D:\xampp\htdocs\GetSubject.php on line 15

How can I fix this? This is my DBconnection.php file code

<?php
 function db_connect() {

// Define connection as a static variable, to avoid connecting more than once 
static $connection;

// Try and connect to the database, if a connection has not been established yet
if(!isset($connection)) {
     // Load configuration as an array. Use the actual location of your configuration file
    $config = parse_ini_file('config.ini'); 
    $connection = mysqli_connect('localhost',$config['username'],$config['password'],$config['dbname']);
}

// If connection was not successful, handle the error
if($connection === false) {
    // Handle error - notify administrator, log to a file, show an error screen, etc.
    return mysqli_connect_error(); 
}
return $connection;
 }
 ?>

In which line? should i remove this function and write directly? — Preshan Pradeepa, Dec 13 '16 at 12:15
you're mixing mysqli_ with PDO; you can't do that. You need to use the same one from connection to querying. — Funk Forty Niner, Dec 13 '16 at 12:19
`bind_param(x,$xxx,PDO::PARAM_STR` - PDO does not fit in with mysqli_. You need to read the manual on mysqli_ prepared statements. You can't just drop in any code from different libraries; this isn't vegetable soup you know ;-) — Funk Forty Niner, Dec 13 '16 at 12:21
RTM http://php.net/manual/en/mysqli.quickstart.prepared-statements.php and don't pop in PDO syntax and stick to the manual from beginning to end. I closed the question respectively. also http://php.net/manual/en/book.mysqli.php — Funk Forty Niner, Dec 13 '16 at 12:24
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/130517/discussion-between-preshan-pradeepa-and-fred-ii). — Preshan Pradeepa, Dec 13 '16 at 13:53
`bind_param("qqq"` that isn't proper syntax. Those are either `i,d,s,b` as per the manual http://php.net/manual/en/mysqli-stmt.bind-param.php — Funk Forty Niner, Dec 13 '16 at 13:54
sorry but I don't have time to chat. You'll have to see if `e4c5` has time. — Funk Forty Niner, Dec 13 '16 at 13:54
@PreshanPradeepa Please don't edit posts completely to change their meaning and invalidate the previous revisions. Ask a new questions with the new details. — Bhargav Rao, Dec 13 '16 at 14:16

e4c5 · Answer 1 · 2016-12-13T12:20:28.767

3

Yes this is not allowed,

$statement->bind_param(2,$searchtext.'%',PDO::PARAM_STR, 200);
$statement->bind_param(3,'%'.$searchtext.'%',PDO::PARAM_STR, 200);

These operations result in new string literals being created. String literals cannot be bound. You need to

$param2 = $searchtext.'%';
$param3 = '%'.$searchtext.'%';
$statement->bind_param(2,$param2,PDO::PARAM_STR, 200);
$statement->bind_param(3,$param3,PDO::PARAM_STR, 200);

As a side note, since you are comparing for %searchtext%, there isn't a need to look for searchtext%

update: As Fred pointed out, you appear to be using PDO but calling bind_param, which is a part of the mysqli api rather than PDO. The correct all in PDO is bindParam

edited Dec 13 '16 at 12:20

answered Dec 13 '16 at 11:49

e4c5

52,766
11
101
134

I fixed it as you said,But its still not working, Its says "Fatal error: Cannot pass parameter 3 by reference in D:\xampp\htdocs\GetSubject.php on line 15" again – Preshan Pradeepa Dec 13 '16 at 11:54
Is there any syntax error in this segment? "while($row = getAlSubjects($q)->fetch_assoc())" – Preshan Pradeepa Dec 13 '16 at 11:56
I change all 3 places, $param1 = $searchtext; $param2 = $searchtext.'%'; $param3 = '%'.$searchtext.'%'; $statement->bind_param(1,$param1,PDO::PARAM_STR, 200); $statement->bind_param(2,$param2,PDO::PARAM_STR, 200); $statement->bind_param(3,$param3,PDO::PARAM_STR, 200); – Preshan Pradeepa Dec 13 '16 at 11:59
Fatal error: Cannot pass parameter 3 by reference in D:\xampp\htdocs\GetSubject.php on line 15 – Preshan Pradeepa Dec 13 '16 at 12:03
This search text is passing from a text box and I'm calling this using jquery ajax call, initially its value is null("") – Preshan Pradeepa Dec 13 '16 at 12:05
2

quitet sure? if param2 worked obviously param3 should also work because it's the same pattern – e4c5 Dec 13 '16 at 12:05
2

you did the same mistake the OP did; still mixing mysql apis. `bind_param()` is not part of the PDO library functions. So strange how this got so many upvotes though. – Funk Forty Niner Dec 13 '16 at 12:14
there's also `fetch_assoc()` which is mysqli_ and we don't know which api they're using to connect with. That question is way too unclear and OP did not respond to my comment about the connection. – Funk Forty Niner Dec 13 '16 at 12:18
@Fred-ii- that red herring was probably what set me off the wrong track, my mind had subconsciously ignored the other factors. Why not post an answer? you will get an upvote from me – e4c5 Dec 13 '16 at 12:20
@e4c5 your edit is still wrong `bind_param(1,$searchtext,PDO::PARAM_STR` etc. is still PDO. I can't submit an answer because I fear I may not see the end of it. – Funk Forty Niner Dec 13 '16 at 12:22

Passing multiple parameters to prepared statement

1 Answers1