English
English
In PHP's MySQLi extension, mysqli_stmt::send_long_data is a function used to send large data (such as long text or large binary data) to a MySQL database. When handling large-scale data insertion, the standard bind_param() method may be limited by data size. In such cases, send_long_data() becomes essential. This article introduces the basic usage of mysqli_stmt::send_long_data and key considerations for its use.
<span><span>mysqli_stmt::</span><span><span class="hljs-title function_ invoke__">send_long_data</span></span><span>(</span><span><span class="hljs-keyword">int</span></span> </span><span><span class="hljs-variable">$param_nr</span></span>, </span><span><span class="hljs-keyword">string</span></span> </span><span><span class="hljs-variable">$data</span></span>): </span><span><span class="hljs-keyword">bool</span></span></span>$param_nr: The parameter number corresponding to the data to be sent. It matches the position of the ? placeholder in the SQL statement, starting from 0.
$data: The large data to be sent, usually a large string or binary data.
Return value: Returns true on success, or false on failure.
send_long_data is mainly used to transmit large amounts of data to a MySQL database. Normally, mysqli_stmt::bind_param() is used to bind parameters. However, when data is large (e.g., contents of a large file or large text fields), it may be limited by the max_allowed_packet setting in PHP configuration. To bypass this limitation, send_long_data allows data to be sent in chunks to the MySQL database.
Assume we have a table structured as follows:
<span><span><span class="hljs-keyword">CREATE</span></span><span> </span><span><span class="hljs-keyword">TABLE</span></span><span> files (
id </span><span><span class="hljs-type">INT</span></span><span> AUTO_INCREMENT </span><span><span class="hljs-keyword">PRIMARY</span></span><span> KEY,
name </span><span><span class="hljs-type">VARCHAR</span></span>(<span>255</span>),
file_data LONGBLOB
);
</span></span>This table stores file names and their corresponding binary data. We can insert large files using the following steps.
<span><span><span class="hljs-meta"><?php</span></span>
</span><span><span class="hljs-comment">// Create a database connection</span></span>
</span><span><span class="hljs-variable">$mysqli</span> = </span><span><span class="hljs-keyword">new</span> </span><span><span class="hljs-title function_ invoke__">mysqli</span>("localhost", "username", "password", "database");
</span><span><span class="hljs-comment">// Check connection</span></span>
</span><span>if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
</span><span><span class="hljs-comment">// Prepare SQL statement</span></span>
</span><span>$stmt = $mysqli->prepare("INSERT INTO files (name, file_data) VALUES (?, ?)");
</span><span><span class="hljs-comment">// Bind the first parameter (file name)</span></span>
</span><span>$stmt->bind_param("s", $name);
</span><span><span class="hljs-comment">// Read file data</span></span>
</span><span>$filePath = 'large_file.txt';
$fileData = file_get_contents($filePath);
</span><span><span class="hljs-comment">// Send large data</span></span>
</span><span>$stmt->send_long_data(1, $fileData);
</span><span><span class="hljs-comment">// Set file name</span></span>
</span><span>$name = basename($filePath);
</span><span><span class="hljs-comment">// Execute SQL statement</span></span>
</span><span>$stmt->execute();
</span><span><span class="hljs-comment">// Close connection</span></span>
</span><span>$stmt->close();
$mysqli->close();
</span><span><span class="hljs-meta">?></span></span>
</span>In the above code, the file name is bound using bind_param() (with s indicating string type), while the file data is sent using send_long_data(), allowing large file transfer.
Chunked Data Transfer: send_long_data sends data in chunks to MySQL rather than binding it all at once like bind_param(). This helps prevent high memory usage and PHP execution time limits when handling large data.
Supports Large Data Types: send_long_data is mainly used for LONGBLOB and LONGTEXT data, especially when uploading or inserting large files.
Parameter Number: send_long_data uses a parameter number to identify the data chunk being sent. Numbering starts from 0, so ensure the correct parameter number when calling it multiple times.
MySQL Configuration Limits: When using send_long_data, pay attention to the max_allowed_packet setting in MySQL. If the data exceeds this limit, insertion will fail. Check the current setting with:
SHOW VARIABLES LIKE 'max_allowed_packet';
To change this value, modify the MySQL configuration file:
max_allowed_packet=64M
PHP Configuration Limits: Additionally, PHP settings like upload_max_filesize and post_max_size may restrict upload size. Ensure these values are sufficient when using send_long_data.
Database Connection Timeout: Since send_long_data transfers large data in chunks, long operations may cause connection timeouts. Adjust MySQL or PHP timeout settings to avoid this.
Data Transmission Order: The order of send_long_data calls should match the order of bound parameters. Ensure each data chunk corresponds to the correct parameter number.
mysqli_stmt::send_long_data is a very practical function, especially when inserting large files or data. It effectively overcomes the limitations of traditional parameter binding. By using it properly, large data can be inserted smoothly while avoiding memory shortages or transfer timeouts. However, it is important to pay attention to MySQL and PHP configuration settings to ensure unrestricted data transmission.
