how to bind multiple parameters to MySQLi query

Question

I have a mysql query, but I can't bind param for it

SELECT users.email,users.handle,userprofile.mobile FROM users,userprofile WHERE users.email =? OR users.handle =? OR userprofile.mobile=?

I've tried below line

$query = "SELECT users.email,users.handle,userprofile.mobile FROM users,userprofile WHERE users.email =? OR users.handle =? OR userprofile.mobile=?";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("sss",$email,$username,$mobile);
if ($stmt->execute()) {
if($stmt->num_rows){
   echo '......';
    }
}

but I've received and error :

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

Can you post your entire query code including the full prepare not just the sql statement — Dave, May 17 '13 at 15:06
Actually just relased your using bindParam its actually $stmt->bind_param() when you're passing multiple parameters in like your example — Dave, May 17 '13 at 15:08
"sss" means that the 3 variables he wants to bind are all strings and he's passing all 3 variables in at once so he needs to use bind_param not bindparam. He should only be using it without the _ if he's going to bind each input individually as in the example below. — Dave, May 17 '13 at 15:11
You are actually opening mysqli in an OO method aren't you ie: `$mysqli = new mysqli(); ` not doing it in a procedural style are you in which case you'd have to pass in the link id too — Dave, May 17 '13 at 15:14
@JasonMcCreary thought it would be but also thought it was worth checking since I can't see anything else wrong with it unless its a case of can't see the wood for all the trees. — Dave, May 17 '13 at 15:16
I'm wondering if you need a space before the `?`... Can't find the requirement in the docs, but try it. — Jason McCreary, May 17 '13 at 15:16

score 40 · Accepted Answer · edited Mar 02 '21 at 17:06

40

This is the correct syntax for binding params in mysqli

$SQL = "SELECT 
              users.email,
              users.handle,
              userprofile.mobile 
        FROM users,userprofile      
        WHERE users.email =? OR users.handle =? OR userprofile.mobile=?";

$stmt = $mysqli->prepare($SQL);
   
$stmt->bind_param("sss", $one,$two,$three);
$stmt->execute();

//do stuff

edited Mar 02 '21 at 17:06

Dharman

30,962
25
85
135

answered May 17 '13 at 15:12

Fabio

23,183
12
55
64

2

can I call bind_params 3 times for binding different argument if I received them as an array? – Minhaz Sep 10 '14 at 07:05
5

Just to remind: The argument may be one of four types: i - integer d - double s - string b - BLOB – Zafer Feb 15 '15 at 11:33
1

What is diffrent between this solution and the code provided ? – Koen Demonie May 05 '15 at 16:16
2

@KoenDemonie Actually there is no difference but if you look at question history by clicking on "edited May 17 '13 at 15:11" you will spot the difference. Op was using bindParam instead of bind_param – Fabio May 06 '15 at 06:06

score 5 · Answer 2 · answered May 17 '13 at 15:12

5

Try this...

$stmt = $dbConn->prepare("SELECT users.email,users.handle,userprofile.mobile FROM users,userprofile WHERE users.email = ? OR users.handle = ? OR userprofile.mobile= ?");
$stmt->bind_param("sss", $email, $handle, $mobile);
$stmt->execute();

answered May 17 '13 at 15:12

Josh Balcitis

490
6
19

how to bind multiple parameters to MySQLi query

2 Answers2

Linked