Skip to content
PHP Logo

Where is the best place to set database queries, such as in a login script, and is it problematic to include them directly in the script?

It is best practice to separate database queries from the rest of your code to improve maintainability and security. One common approach is to create a separate file specifically for database queries, such as a "database.php" file, and include it in your scripts when needed. This way, you can easily manage and update your queries in one central location.

php 
// database.php
<?php

function loginUser($username, $password) {
    // Connect to database
    $conn = new mysqli("localhost", "username", "password", "database");

    // Check connection
    if ($conn->connect_error) {
        die("Connection failed: " . $conn->connect_error);
    }

    // Prepare and execute query
    $stmt = $conn->prepare("SELECT * FROM users WHERE username = ? AND password = ?");
    $stmt->bind_param("ss", $username, $password);
    $stmt->execute();
    
    // Process results
    $result = $stmt->get_result();
    if ($result->num_rows == 1) {
        // User authenticated
        return true;
    } else {
        // User not found or invalid credentials
        return false;
    }

    // Close connection
    $stmt->close();
    $conn->close();
}
?>
```

In your login script, you can include the "database.php" file and call the `loginUser` function to handle the database query for logging in a user:

```php
// login.php
<?php

include "database.php";

$username = $_POST['username'];
$password = $_POST['password'];

if (loginUser($username, $password)) {
    // User authenticated
    echo "Login successful!";
} else {
    // Invalid credentials
    echo "Invalid username or password.";
}

?>

Related questions