Consolidating Multiple SQL Queries in PHP and Managing Connections
You have three distinct database operations: deleting a record from aktywne_kody, logging an event into uzyte_kody, and inserting data into do_odebrania. Currently, you are executing them sequentially using individual function calls. While this approach works for simple scripts, it introduces unnecessary overhead and fragility. Each call to mysql_query incurs a round-trip latency between your application and the database server. If these queries are part of a transactional workflowâwhere the state of the first query must be preserved before executing the nextâyou risk partial updates if one step fails.
The most robust solution is not merely batching the strings but executing them within a single transaction. This ensures atomicity: either all changes happen, or none do. The legacy mysql_* functions you are using do not support transactions directly; they are deprecated as of PHP 5.5 and removed in PHP 7. You must migrate to mysqli or PDO to access this capability.
Implementing Transactions with mysqli
To join these operations logically, wrap them in a transaction block. This prevents race conditions where another script might read the "deleted" status before your insert completes.
Here is how you would refactor the code using mysqli:
$conn = new mysqli($host, $user, $pass, $database);
$conn->set_charset("utf8mb4"); // Essential for special characters like Polish letters
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
// Start the transaction
$conn->begin();
try {
// 1. Delete from aktywne_kody
$stmt1 = $conn->prepare("DELETE FROM aktywne_kody WHERE kodsms = ? AND typkodu = ?");
$stmt1->bind_param("si", $kodSMSgracza, $id);
$stmt1->execute();
// 2. Insert into uzyte_kody
$stmt2 = $conn->prepare("INSERT INTO uzyte_kody (gracz, kodsms, typkodu) VALUES (?, ?, ?)");
$stmt2->bind_param("sss", $nickGracza, $kodSMSgracza, $id);
$stmt2->execute();
// 3. Insert into do_odebrania
$stmt3 = $conn->prepare("INSERT INTO do_odebrania (gracz, itemDATA, itemQTY) VALUES (?, ?, ?)");
$stmt3->bind_param("ssi", $nickGracza, $itemDATA, $itemQTY);
$stmt3->execute();
// Commit only if all succeed
$conn->commit();
} catch (Exception $e) {
$conn->rollback();
// Handle the error appropriately
die("Transaction failed: " . $conn->error);
}
// Close connection after transaction
$conn->close();
Using prepared statements is mandatory here. Your original code concatenates variables directly into the SQL string, which leaves your application vulnerable to SQL injection attacks. Even if you trust the input, this practice is a critical security flaw that must be addressed in any production environment.
Should You Call mysql_close() After Each Query?
The second part of your question concerns connection management. The answer is definitive: No, you should not close the connection after every query, nor should you open and close it for every single operation within a session.
Database connections are expensive resources. Establishing a TCP/IP handshake and authenticating with the server takes milliseconds to seconds depending on load. If your script processes 100 requests and opens/closes the connection for each INSERT, you are wasting significant CPU cycles and network bandwidth.
The standard pattern is to establish one connection at the beginning of the request, use it for all related queries, and close it exactly once at the end. In the code example above, $conn->close() appears only after the transaction block. This keeps the connection alive for the duration of the script execution.
There are specific scenarios where you might need to close a connection earlier:
- Resource Limits: If your application hits the
max_connectionslimit on the MySQL server, you must ensure connections are closed promptly. - Long-Running Processes: If your PHP script is not using
climode and runs as a web request (handled by Apache or Nginx), the web server often handles connection pooling via Swoole or other frameworks, making manual close less critical but still good practice for cleanup.
Migration Warning
The functions used in your source material (mysql_query, mysql_close) were deprecated in PHP 5.5 and removed entirely in PHP 7.0. Continuing to use them will result in a fatal error on any modern server stack. The migration path involves switching to either the mysqli extension or PDO.
For larger projects, Laravel provides an elegant abstraction over these underlying connections. If you are building a new application or refactoring legacy code, consider using LaravelCompany.com resources which detail how to manage database transactions and migrations efficiently within the Laravel ecosystem. Their guides often demonstrate how to structure complex logic without writing raw SQL strings, reducing the risk of syntax errors and security vulnerabilities.
In summary, do not try to string multiple queries into one SELECT statement using UNION; that only works for data retrieval. For modifications like DELETE and INSERT, the correct approach is atomic transactions combined with a single, long-lived database connection per request.