William Jiang

JavaScript,PHP,Node,Perl,LAMP Web Developer – http://williamjxj.com; https://github.com/williamjxj?tab=repositories

PHP: mysqli's bind_param

I use a lot of binding in my codes, no matter Oracle or MySQL, pure SQL or stored-procedures/functions in Perl or PHP scripts. they are more secure. There are a lot of articles quoting binding paramters or Database operation.
The following are syntax and 3 examples:

bool mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )
// 1.
$stmt = $db->prepare(SELECT A FROM tbl WHERE id IN(?, ?, ?, ?));
$stmt->bind_param("iii", $int_one, $int_two, $int_three);
// 2.
$stmt = mysqli_prepare($link, "INSERT INTO T VALUES (?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'ssd', $code, $name, $percent);
// 3.
$sql = "INSERT INTO user SET name=?,email=?";
$res = $db->prepare($sql);
$arr = array("name"=>$name,"email"=>$email);
$res->bind_param("ss",$arr['name'],$arr['email']); 

The first parameter of bind_param is the TYPE: data type. A string that contains one or more characters which specify the types for the corresponding bind variables. The following is the summary:

Type specification chars
Character Description
i corresponding variable has type integer
d corresponding variable has type double
s corresponding variable has type string
b corresponding variable is a blob and will be sent in packets

For the above example, if you specify type “i” (integer), the maximum value it allows you to have is 2^32-1 or 2147483647. The relationship between DB Table and PHP’s bind_param() are:

  • (UN)SIGNED TINYINT: I
  • (UN)SIGNED SMALLINT: I
  • (UN)SIGNED MEDIUMINT: I
  • SIGNED INT: I
  • UNSIGNED INT: S
  • (UN)SIGNED BIGINT: S
  • (VAR)CHAR, (TINY/SMALL/MEDIUM/BIG)TEXT/BLOB should all have S.
  • FLOAT/REAL/DOUBLE (PRECISION) should all be D.
Advertisements

One response to “PHP: mysqli's bind_param

  1. Code Geass Cosplay wigs 08/05/2011 at 6:03 am

    I thought it was g to be saoslink to this page on. I am sure my visitors will find that very useful.I think you have done an excellent job with your blog. I will return in the near future.I had bookmark it 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: