Creating an SQL Query based on data from an Array

Question

I'm trying to find a way to simplify an existing function which communicated with our database. The function currently has several parameters (upwards of 15), and everytime a record is added or updated, all the parameters are required.

I have the following PHP Function (simplified):

function addSomethingToDB($var1, $var2, $var3, $var4...) {

    # Do SQL Injection checks
    ...

    $query = 'INSERT INTO `table` (`var1`,`var2`,`var3`,`var4`) VALUES ($var1, $var2, $var3, $var4)';

    # OR

    $stmt = $db->prepare('INSERT INTO `table` (`var1`,`var2`,`var3`,`var4`) VALUES (?,?,?,?)');
    $stmt->bind_param('ssss', $var1, $var2, $var3, $var4);

}

The above code obviously gets pretty messy if you have more than a few variables. And it's difficult to work with if not all variables are required. Because of this I attempted a second scenario where I either had one main/required parameter followed by an array or I just had an array as the parameter.

function addSomethingToDB($var1, array $attributes) {}

The goal here was to allow the array to have a more flexible approach in case the SQL query either needs to be extended in the future, or to build the SQL query based on optional values.

For example:

If Var2 and Var4 are not provided, the array would look like:

{
    'var1': 'Var1_Value',
    'var3': 'Var3_Value'
}

and the SQL would be:

$query = 'INSERT INTO `table` (`var1`,`var3`) VALUES ($var1, $var3);

As you can see, in the above scenario, the query was adapted for only the necessary values.

What I want to try and achieve is to build the SQL query based on the values provided. The first was I assume would be to have an IF ELSE statement or a SWITCH. Which gives me something weird like the following:

function getlogs($type, $id='') {

    $types = array('client_log', 'system_log', 'user_log', 'api_log', 'project_log', 'lead_log');

    if (in_array($type, $types)) {

        if ('client_log' == $type) {
            if (!empty($id)) {
                $query = 'SELECT * FROM `logs` WHERE `client_id` = ' . $id . ' AND `type` = "client_log"';
            } else {
                $query = 'SELECT * FROM `logs` WHERE `type` = "client_log"';
            }
        } elseif ('project_log' == $type) {
            if (!empty($id)) {
                $query = 'SELECT * FROM `logs` WHERE `project_id` = ' . $id . ' AND `type` = "project_log"';
            } else {
                $query = 'SELECT * FROM `logs` WHERE `type` = "project_log"';
            }
        } elseif ('user_log' == $type) {
            if (!empty($id)) {
                $query = 'SELECT * FROM `logs` WHERE `staff_id` = ' . $id . ' AND `type` = "staff_log"';
            } else {
                $query = 'SELECT * FROM `logs` WHERE `type` = "staff_log"';
            }
        } elseif ('lead_log' == $type) {
            if (!empty($id)) {
                $query = 'SELECT * FROM `logs` WHERE `client_id` = ' . $id . ' AND `type` = "lead_log"';
            } else {
                $query = 'SELECT * FROM `logs` WHERE `type` = "lead_log"';
            }
        } else {
            $query = 'SELECT * FROM `logs` WHERE `type` = ' . $type;
        }

        $logs = Config::$db->query($query);
        return $logs->fetch_all(MYSQLI_ASSOC);


    } else {
        return 'invalid log type';
    }

    $stmt->close();

}

The above is not quite the code I want to be writing, it's a similar example where the query related to the Log Type is being called. But that is a lot of mess that is not pleasing to look at. Also, the above code does not use Arrays which is what I hope to be using.

Lastly, the code I am hoping to write is mostly related to Updating existing records. So, say we have a User. The user has an Email and a Password and Address. According to the above code (first one) we will be updating the Email, Password, and Address every time the user updates any one of his field. I would like to avoid that.

My assumption is that I'd have to do something like so:

# 1. Loop Array using maybe For or Foreach
# 2. Have a whitelisted array of allowed values.
# 3. Append to query if an Array value exists.
# 4. Run query.

I fear my problem is at Point 3. I can't seem to figure out how to build the query without going through a lot of messy IF ELSE statements.

Now, by this point, I have certainly searched around SO to find a similar question, however, searches related to SQL and Arrays are almost entirely related to adding "multiple rows in a single SQL query" or something similar.

Rakesh Jakhar · Accepted Answer · 2019-06-24 06:03:36Z

3

You can approach this by using arrays, in which keys are column name and containing the values

$columns = [
      'field1' => 'value1', 
      'field2' => 'value2', 
      'field3' => 'value3', 
      'field4' => 'value4'
];
addSomethingToDB($columns);
function addSomethingToDB($cols){

    # Do SQL Injection checks

    $query = "INSER INTO `tablename` ( ".implode(",",array_keys($cols))." )  VALUES ( '".implode("','",array_values($cols))."' )";
}

answered Jun 24, 2019 at 6:03

Rakesh Jakhar

6,4082 gold badges13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating an SQL Query based on data from an Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related