Optimize code for sending collected data to a database

Question

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);

• Your Common Sense: It is unclear what do you mean "to send a bunch of stuff all at once". if it's on this single query, then it is not a bunch, it's just a query. Just keep it as is. You can redesign your database though, as fields like usage_xx always smell of a bad design
• Dan: Are you intentionally using mysqli ?

Answer 1

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.

• tisaconundrum: This works perfectly! Thank you. I'm curious to know where I can read up more about these functions and more.