PHP / mysqli：使用mysqli_stmt_bind_param（）插入IP地址 [英] PHP/mysqli: Inserting IP address with mysqli_stmt_bind_param()

问题描述

我有一个数据库表，其中包含一个无符号整数字段来存储访问者的IP地址：

I have a database table which contains an unsigned integer field to store the visitor's IP address:

`user_ip` INT(10) UNSIGNED DEFAULT NULL,

以下是试图存储IP地址的PHP代码片段：

Here's the snippet of PHP code which tries to store the IP address:

$ipaddr = $_SERVER['REMOTE_ADDR'];
if ($stmt = mysqli_prepare($dbconn, 'INSERT INTO visitors(user_email, user_ip) VALUES (?,?)'))
{
    $remote_ip = "INET_ATON('$ipaddr')";
    mysqli_stmt_bind_param($stmt, 'ss', $email, $remote_ip);
    if (mysqli_stmt_execute($stmt) === FALSE) return FALSE;
    $rows_affected = mysqli_stmt_affected_rows($stmt);
    mysqli_stmt_close($stmt);
}

INSERT操作成功，但user_ip字段包含空值。
我也尝试将mysqli_stmt_bind_param（）中的参数类型（在上例中设置为字符串）更改为整数，即mysqli_bind_param（...'si'，... ） - 但无济于事。

The INSERT operation succeeds, however the user_ip field contains a null value. I have also tried changing the parameter type in mysqli_stmt_bind_param() (which was set to string in the above example) to integer, i.e. mysqli_bind_param(... 'si',...) - but to no avail.

我也尝试使用以下代码而不是mysql的INET_ATON（）SQL函数：

I've also tried using the following bit of code instead of mysql's INET_ATON() SQL function:

function IP_ATON($ipaddr)
{
    $trio = intval(substr($ipaddr,0,3));
    return ($trio>127) ? ((ip2long($ipaddr) & 0x7FFFFFFF) + 0x80000000) : ip2long($ipaddr);
}

它仍然不起作用 - 'user_ip'字段仍然设置为空值。我已经尝试将$ ip_addr变量作为整数和& mysqli_bind_param（）中的字符串 - 无济于事。

It still doesn't work - the 'user_ip' field is still set to null. I've tried passing the $ip_addr variable as both integer & string in mysqli_bind_param() - to no avail.

似乎问题在于参数化插入。

It seems the problem lies with the parameterized insert.

以下旧式代码没有任何问题：

The following "old-style" code works without any problem:

mysqli_query(..., "INSERT INTO visitors(user_email, user_ip) VALUES ('$email',INET_ATON('$ipaddr'))");

我在这里做错了什么？

提前致谢！

推荐答案

我认为它应该是这样的：

I think it should look like this:

$ipaddr = $_SERVER['REMOTE_ADDR'];
if ($stmt = mysqli_prepare($dbconn, 'INSERT INTO visitors(user_email, user_ip) VALUES (?, INET_ATON(?))'))
{
    mysqli_stmt_bind_param($stmt, 'ss', $email, $ipaddr);
    if (mysqli_stmt_execute($stmt) === FALSE) return FALSE;
    $rows_affected = mysqli_stmt_affected_rows($stmt);
    mysqli_stmt_close($stmt);
}

请注意第二次更改？以及将哪些参数发送到mysqli_stmt_bind_param。

Note the change by the second ? and what parameters are sent into mysqli_stmt_bind_param.

因为传递给mysqli_stmt_bind_param的参数应该是要插入的实际值，而不是任何函数。

Since the parameters you pass into mysqli_stmt_bind_param should be the actual values to be inserted, and not any functions.

这篇关于PHP / mysqli：使用mysqli_stmt_bind_param（）插入IP地址的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！