fourteen

connecting php to mysql/mariadb

there are many ways to connect to a mysql/mariadb database from php but, for most uses, the best is mysqli (mysql improved), a built-in connection built into php.

before we talk about the actual connection, though, we should talk about security. somewhere in this process, you're going to have to tell the web server to connect to the database server and authenticate. when you're working inside your program, that's not something you have to consider. it's unavoidable, though, with the database connection, even if they're on the same physical machine. there are several ways to do this but the usual way is with a username and password. which means your php program needs to know the username and password to access the database server. where you store that is up to you but it's important to remember that, if it's hardcoded into your code, anyone who can view your code can see that password -- including anyone who might be looking at it on an open-source coding platform like github. one option is to store this information in a text file and read it before connecting to the database. it's up to you how you secure your database connection and its access codes and there's no one right answer. but it's something you need to think about if you're connecting to a database and now's the best time to be aware of it before it becomes an issue later.

the simplest connection is one that doesn't need to return any results. that applies to insert, update and delete.

$dbInfo = ["server" => "localhost", "database" => "myDb", "username" => "mySite", "password" => "myPassword"];

function db($sql)
{
    global $dbInfo;
    if (!($db = mysqli_connect($dbInfo["server"], $dbInfo["username"], $dbInfo["password"], $dbInfo["database"]))) {
        return false;
    }
    if (!mysqli_query($db, $sql)) {
        return false;
    }
    return true;
}

in this case, the information required to connect to the database is stored in an array but, in practice, how you get that information into php is unrelated to the connection.

mysqli_connect creates a connection to the database. the four parameters are exactly as you see them here -- server, username, password, database, in that order. if the database connection is unsuccessful, it is false so we can check for that and exit the function, returning false so whatever called it knows there was an error.

mysqli_query uses the connection we just opened and executes the sql command. this can be any sql the user is permitted to execute on that database. if there's an error, it is false so, again, we can check for that and return false when we exit the function. otherwise, the command executed and we can return true. it's important to note that this means successful execution, not that whatever we wanted it to do was successful. if the sql was delete user where userId=10 and there was no user matching that id code, the command would be successful but no user would be deleted.

as you might have noticed, we are checking for two false conditions in a row before continuing. we can simplify that into a single check for either being false.

function db($sql)
{
    global $dbInfo;
    if (!($db = mysqli_connect($dbInfo["server"], $dbInfo["username"], $dbInfo["password"], $dbInfo["database"])) || !mysqli_query($db, $sql)) {
        return false;
    }
    return true;
}

this function works for any sql that doesn't need to return records. it will certainly execute select but, despite there possibly being something returned, we can't access it because there's nothing to point to to get that information yet.

thankfully, mysqli_query contains those results and we can access them with mysqli_fetch_all.

function db($sql)
{
    global $dbInfo;
    if (!($db = mysqli_connect($dbInfo["server"], $dbInfo["username"], $dbInfo["password"], $dbInfo["database"])) || !($r = mysqli_query($db, $sql))) {
        return false;
    }
    return $r === true ? true : mysqli_fetch_all($r, MYSQLI_ASSOC);
}

now, instead of just executing the query, we set a new variable $r to be the possible results of that query. $r is a typical shorthand for "records" or "recordset". you'll also frequently see $rs.

we already know that result is not false because we're checking for that. so, in our return statement, we check to see if the result is identical to true, in other words, did it just execute a command that doesn't return results? if that's the case, we can just return true and know it was successfully run on the database. if it's not true, though, that means it is a result that we can pass back to whatever called the db function. MYSQLI_ASSOC is a built-in constant telling mysqli_fetch_all to return the results as an associative (named) array.

there are ways to loop through the results one at a time but it is less efficient and more cumbersome to code. the only reason that is ever an issue is when the results are too large to store in available memory. in that case, though, you should have requested smaller results long before you have to think about maximum memory usage. only ever grab the minimum amount of data you need from the database. if you're getting thousands or millions of results for a single execution of a php program -- which generates only a single page or query result for the user -- you're doing something wrong.

mysql query security

sql databases will execute whatever code you send, even if it includes dangerous commands. that means that, if you're sending anything coming from outside your program, you have to be careful to parse it for anything that might cause a problem before you allow it into your sql query.

for example, if you're passing a string to the database, a user could include " to end the string and allow them to just write more sql code after that point. you can make strings safe as variables manually, using regular expressions, for example. but the simplest way is to use mysqli_real_escape_string.

$query = "select userId from email where emailAddress=\"" . mysqli_real_escape_string($db, $email) . "\"";

now, regardless of what the user has entered as their email, it will only be used to match the string in the database. in practice, it is wise to use mysqli_real_escape_string on any user-provided variables before building your sql queries.

closing connections

when you're finished with a database connection, it is a good idea to close it if you're going to be doing a lot more processing in php to save server resources.

mysqli_close($db);

in practice, this is rarely relevant unless the program has to run for a long time after dealing with the database connection. mysqli automatically terminates connections once the program finishes executing. it's a good practice, though, to include this in your sql connection function, for efficiency. remember, only execute mysqli_close once you're finished with all your sql commands or you'll have to manually reconnect.

connection security

database servers are not meant to be accessed by users so there's no reason for them to be accessible by anything other than the web servers they're used by. yes, securing your databases with usernames and passwords is a great first step. but it is wise to restrict connections by ip. if you're using a database server on the same machine as your web server, you can restrict it to only local connections. this way, even if someone does manage to learn your username and password, it's unhelpful unless they're connecting directly from your web server.

sending email

dealing with databases in php almost always comes along with emails and accounts. thankfully, php has built-in functions to send mail. for production systems, it is recommended to use an external mail provider and any provider will have a specific way to connect. but, to use the web server's built-in email system, you can use the mail function within php itself.

$emailToName = "Isla White";
$emailToAddress = "[email protected]";
$emailSubject = "the subject";
$emailContent = "hello";
$emailFromName = "PHP Server";
$emailFromAddress = "[email protected]";

mail($emailToName . " <" . $emailToAddress . ">", $emailSubject, $emailContent, "From: " . $emailFromName . " <" . $emailFromAddress . ">");

this is the basic format for a text email. to perform the same task with an html message...

$emailToName = "Isla White";
$emailToAddress = "[email protected]";
$emailSubject = "the subject";
$emailContent = "hello";
$emailFromName = "PHP Server";
$emailFromAddress = "[email protected]";
$emailHeader = "MIME-Version: 1.0\nContent-type:text/html;charset=UTF-8\n";

mail($emailToName . " <" . $emailToAddress . ">", $emailSubject, $emailContent, $emailHeader . "From: " . $emailFromName . " <" . $emailFromAddress . ">");

the only difference is that it requires some additional header information. you can add that in the function as text or, as here, set it as a variable to be passed in -- $emailHeader.

the problem with this is that it doesn't support secure email delivery like what's required for gmail and most other commercial providers. the solution to do one of two things -- automatically redirect all email to a commercial relay service that authenticates based on your server's ip address or use a php extension that allows secure email authentication. thankfully, msmtp allows automatic forwarding and authentication from any unix-based server and mail just sends directly to it as if it's the built-in mail system. there are certainly other options but msmtp is the industry-standard option.

php extensions that allow authentication include the commonly-used phpmailer and pear mail, though both are unfortunately object-oriented and somewhat cumbersome compared to the mail function.