Optimize code for sending collected data to a database I'm sending some data over to a database. There is a particular part that requires me to send a bunch of stuff all at once. It seems a little silly to me to write it this way. I feel that I could do this with an array and for loop, but I'm not sure about how that would look. I tried finding implementations elsewhere but I'm not even sure how to phrase what it is I'm looking for.

$sql = "
        INSERT INTO `computers` (`ComputerName`, `usage_0`, `usage_10`, `usage_20`, `usage_30`, `usage_40`, `usage_50`, `usage_60`, `usage_70`, `usage_80`, `usage_90`, `users`, `OrderDate`)
        VALUES (
        '" . mysqli_real_escape_string($cxn, $_POST['ComputerName']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['usage_0']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['usage_10']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['usage_20']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['usage_30']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['usage_40']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['usage_50']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['usage_60']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['usage_70']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['usage_80']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['usage_90']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['users']) . "',
        '" . mysqli_real_escape_string($cxn, $_POST['OrderDate']) . "');
";

mysqli_query($cxn, $sql);
Functional approach

For simplicity you can create few helper functions to get rid of code duplication:


getValuesFromPost(array $list): gets list of parameters names and
returns according POST values.
composeFieldListSql(array $list): gets list of fields and returns SQL expression of fields list like 'ComputerName', 'usage_0', 'usage_10'
composeValuesListSql(array $list): gets list of values and returns SQL expression of escaped values list like 'PC', 'value1', 'value2'
composeInsertSql(array $fields, array $values): composes full insert SQL expression from fields and values lists.


Functions composeFieldListSql() and composeValuesListSql() could be declared inside function composeInsertSql() since you don't need to use them elsewhere.

Your code will look like this:

$fields = ['ComputerName', 'usage_0', 'usage_10', 'usage_20', 'usage_30', 'usage_40', 'usage_50', 'usage_60', 'usage_70', 'usage_80', 'usage_90', 'users', 'OrderDate'];
$sql = composeInsertSql($fields, getValuesFromPost($fields));
mysqli_query($cxn, $sql);


Object oriented approach

Basically you could write wrapper class for mysqli extension functions to encapsulate low level interactions with extension and to make interaction with database more abstract. Also, it will be easy to change inner working of wrapper, if for example you will decide to change database type.

Or you can use one of many existent wrappers, which are already covered by tests and have rich functionality.