English
English
In PHP, connecting to a MySQL database is a common task when developing web applications. There are several ways to establish a connection, and using the mysql_connect() function to create a persistent connection is a classic approach. This article provides a detailed explanation of how to use mysql_connect() for persistent connections, its working principle, and real-world applications.
A persistent connection means that once the connection is established, it will not close after the script finishes execution. Instead, it stays active and can be reused by the next script request. This reduces the overhead of repeatedly creating and closing connections, making it particularly useful for applications with frequent requests. In contrast, a regular connection is opened each time a script runs and then closed once execution completes.
In PHP, the mysql_connect() function can be used to establish a persistent connection. The basic syntax is as follows:
<span><span><span class="hljs-title function_ invoke__">mysql_connect</span></span><span>(</span><span><span class="hljs-string">'hostname'</span></span><span>, </span><span><span class="hljs-string">'username'</span></span><span>, </span><span><span class="hljs-string">'password'</span></span><span>, </span><span><span class="hljs-literal">true</span></span><span>);
</span></span>'hostname': The database server host address, usually localhost or an IP address.
'username': The username used to connect to the database.
'password': The password corresponding to the username.
The fourth parameter true indicates a persistent connection.
Connection Closing Mechanism:
Regular connection: Closed after each script finishes execution.
Persistent connection: Remains active even after the script ends, ready for reuse by subsequent requests.
Performance Differences:
Persistent connections reduce the overhead of creating and destroying connections. They perform better in long-running or high-concurrency web applications.
Regular connections require creating a new connection for every request, which may cause bottlenecks under heavy load.
Main Benefits of Persistent Connections:
Efficiency: Reduces repetitive connection setup.
Resource saving: Connections are reused within the pool for a certain period of time.
First, confirm that MySQL configuration allows persistent connections. In the MySQL config file (my.cnf or my.ini), check the following settings:
<span><span><span class="hljs-section">[mysqld]</span></span><span>
skip-name-resolve
</span><span><span class="hljs-attr">wait_timeout</span></span><span> = </span><span><span class="hljs-number">28800</span></span><span>
</span><span><span class="hljs-attr">interactive_timeout</span></span><span> = </span><span><span class="hljs-number">28800</span></span><span>
</span></span>skip-name-resolve: Disables DNS reverse lookups to avoid DNS queries for each connection.
wait_timeout and interactive_timeout: Define connection timeout values to prevent early disconnections.
<span><span><span class="hljs-meta"><?php</span></span><span>
</span><span><span class="hljs-comment">// Establish a persistent connection using mysql_connect</span></span><span>
</span><span><span class="hljs-variable">$link</span></span><span> = </span><span><span class="hljs-title function_ invoke__">mysql_connect</span></span><span>(</span><span><span class="hljs-string">'localhost'</span></span><span>, </span><span><span class="hljs-string">'root'</span></span><span>, </span><span><span class="hljs-string">'password'</span></span><span>, </span><span><span class="hljs-literal">true</span></span><span>);
<p></span>if (!$link) {<br>
</span>die(</span>'Could not connect: ' . mysql_error());<br>
}</p>
<p>echo 'Connected successfully!';<br>
// Other database operations...<br>
?><br>
</span>Here, the fourth parameter of mysql_connect() is set to true, which requests a persistent connection. Once connected successfully, database operations can proceed.
Once the persistent connection is established, you can perform standard database operations, such as selecting a database and running queries:
<span><span><span class="hljs-title function_ invoke__">mysql_select_db</span></span><span>(</span><span><span class="hljs-string">'my_database'</span></span><span>, </span><span><span class="hljs-variable">$link</span></span><span>);
<p></span>// Execute SQL query<br>
$result = mysql_query('SELECT * FROM users', $link);</p>
<p>while (</span>$row = </span>mysql_fetch_assoc(</span>$result)) {<br>
</span>echo $row[</span>'username'] . '<br>';<br>
}<br>
</span>For persistent connections, manual closing is not usually necessary because the connection remains active for reuse. However, if needed, you can explicitly close it with mysql_close():
<span><span><span class="hljs-title function_ invoke__">mysql_close</span></span><span>(</span><span><span class="hljs-variable">$link</span></span><span>);
</span></span>Connection Pool Limits:
Persistent connections may be limited by the maximum number of connections in MySQL. Excessive connections could exhaust the pool.
Session State Issues:
Persistent connections preserve session state, such as previously set variables. If multiple scripts rely on different states, conflicts may occur.
Resource Consumption:
While persistent connections improve performance, overuse without proper management can drain MySQL server resources.
Security Concerns:
Since persistent connections last longer, ensure credentials (username, password) are secure to prevent misuse.
Using the mysql_connect() function for persistent connections helps reduce the overhead of frequent connection setup and teardown. This approach is especially useful for high-concurrency, database-intensive applications. However, careful resource management is essential to avoid session conflicts or overloading the connection pool. When configured and used properly, persistent connections can significantly enhance PHP database performance.
