Mysql results as PHP named variables

Question

I have a table in mysql called site_settings that looks like this

I am trying to store all of my website settings in mysql and want to pull them into PHP as variables.

I want to pull all values from the variable_name column as the $variable names in PHP and have their values set to whats in the variable_type column.

$site_name = Vexed

$registration_enabled = False

here is my code:

$sql = connect($database_address, $database_username, $database_password, $database);

$query = "SELECT * FROM site_settings";
$result = $sql->query($query);
//$row = $result->fetch_all(MYSQLI_ASSOC);

while($row = $result->fetch_assoc())
{
  $$row['variable_name'] = $row["variable_type"];
}

$arr = get_defined_vars();
print_r($arr);

the last two lines i am using to see if the variable have been created but i cant get it to work. the best result i have got so far is

    [Array] => Array
        (
            [variable_name] => Vexed
        )

Can anyone tell me where i am going wrong?

Thanks in advance to anyone who can help.

I'd recommend against this, as you can clobber existing variables pretty easily. You'll have to make sure you never create a setting that's named the same as any in-use variable. You're better off reading them into a `$config` array or an instance of a `stdClass` or similar. — Alex Howansky, Mar 04 '21 at 22:30

score 1 · Answer 1 · answered Mar 04 '21 at 22:37

What you're trying to duplicate is PHP's extract() builtin function.

It's generally considered a bad practice, because it will make your code harder for readers to understand or debug.

score 0 · Accepted Answer · answered Mar 04 '21 at 22:46

0

What I think is happening is that when you call $$arr['variable_name'] it's actually doing $$arr first (which evaluates to $Array after the string conversion), and then trying to assign into assign the ['variable_name'] key into $Array.

I would expect this minor modification to work:

$sql = connect($database_address, $database_username, $database_password, $database);

$query = "SELECT * FROM site_settings";
$result = $sql->query($query);
//$row = $result->fetch_all(MYSQLI_ASSOC);

while($row = $result->fetch_assoc())
{
  $name = $row['variable_name'];
  $$name = $row["variable_type"];
}

$arr = get_defined_vars();
print_r($arr);

Edit: I'll also echo, that it's a little bit weird to dynamically create variables in this way and it will make your code hard to follow.

answered Mar 04 '21 at 22:46

Jeffrey Van Alstine

51
5

Really appreciate your help, agreed it a little weird, i am creating a site where i can check and monitor different web apps, to monitor everything in one site i need to store a lot of values, my Mysql skills are much sharper than my php, hence going the route i have, is there a better way to store the values? i'm trying to create the site as open as possible so that i can share with others, or use the base of the site on different projects. – Sutherland Mar 04 '21 at 22:58
I'd probably shove everything into a look up dictionary/array like this: $settings = array(); while($row = $result->fetch_assoc()) { $settings[$row['variable_name']] = $row['variable_value']; } Then later on in the code you can access everything through $variables array. e.g. $settings['site_name'] = Vexed, $settings['registration_enabled'] = true, etc. – Jeffrey Van Alstine Mar 04 '21 at 23:06
where would you write this data though? – Sutherland Mar 04 '21 at 23:10
I'm not sure what you mean by "write" the data? Long-term persistent storage would still be in the database and all the settings in short-term memory would be accessed through the $settings dictionary. – Jeffrey Van Alstine Mar 05 '21 at 20:01
Sorry, yeah i meant long term persistent storage, so storing in a database is fine but calling them into php the way i do is the concern, is there somewhere i could find some examples on the recommended way to store site values? – Sutherland Mar 05 '21 at 22:01
What you're doing for persistent storage is pretty standard. We do that on a few projects. If you're curious, I'd recommend looking up how Wordpress and Drupal configure their databases. – Jeffrey Van Alstine Mar 07 '21 at 02:15
Ok, thats again for your help and advice. – Sutherland Mar 07 '21 at 20:11

Mysql results as PHP named variables

2 Answers2