BillyNair BillyNair - 1 year ago 46
SQL Question

How to efficiently create an array with a define() list?

I have a list of items that I need to check against in SQL queries and defined a constant to be used in the queries:

define(ORG_CHART_VARS, "'first_name', 'last_name', 'nickname', 'title', 'oc_work_status', 'oc_branch', 'oc_dept_id', 'oc_dept_card', 'oc_dept_card_group', 'oc_dept_card_sub', 'oc_emp_rank', 'about_page_URL', 'description'");

I want to create an array with the list and tried
$allMetaKeys = array(str_replace("'", "", explode("', '", ORG_CHART_VARS)));
which worked for all but the first and last items.

I know I can use regex to parse it out, but I am guessing there is an entirely more efficient way of doing this that I am not seeing.

Both the string and the array need to contain the same items, and they might change in the future (which is why I want a single list rather than 2) but I don't care if the array comes first or the string, they just need to match (and work in loops and SQL queries)

Answer Source

First off, I'd question the decision to store a string list of values that like in a constant. It would be ideal to work with a data structure, like an array.

Having said that, your current code does appear to work for me, and includes the first and last items. However it does have an extra array level in there:

Assuming you really need to use a constant, you have a couple options:

PHP 5.6+

If you are using PHP 5.6, you can actually store an array in a constant!

define('ORG_CHART_VARS', ['first_name', 'last_name', 'nickname', 'title', 'oc_work_status', 'oc_branch', 'oc_dept_id', 'oc_dept_card', 'oc_dept_card_group', 'oc_dept_card_sub', 'oc_emp_rank', 'about_page_URL', 'description']);

Working example:

It would be quite easy to use this array in your SQL queries with little modification. Just use implode when building your query.

$vars = "'" . implode("', '", ORG_CHART_VARS) . "'";
$query = "SELECT foo FROM table WHERE bar IN ($vars)";


PHP <5.6

Otherwise, if you stick with your existing constant, you can improve your parsing code slightly.

If you flip your str_replace and explode you get a single-dimension array, with all your items:

$allMetaKeys = explode(", ", str_replace("'","", ORG_CHART_VARS));

Working example: