How to display all MySQL results with one query?

[flynismo]

Hey guys,

here is the table structure:

Table name: globalsettings
column names: setting, value, description

An example query could be this:

$query = "SELECT * FROM globalsettings WHERE setting = 'name'";

Okay, so here is what I am trying to do (I am using PDO for DB activity, but just starting to learn PDO).

I have a class that will set sitewide global settings. What I am trying to do is set each propery in the class to the corresponding DB value. For example:

$config = new globalSettings;
echo $config->sitename;

When I echo $config->sitename, it should print out the website name that is stored in the DB.

Now, I do know how to do this, but the problem I have is that I only know how to do this by adding WHERE clause to the query -- "SELECT * FROM globalsettings WHERE setting = 'setting'";

which I want to avoid, because I have about 80 rows right now, and I dont want to make 80 different queries just to set each property.

My current code (stored in the class) looks like this:

`$sql = "SELECT * FROM globalsettings";
        $q = $db->prepare($sql);
        $q->execute();

        foreach($q as $row)
        {
            $this->baseurl = $row['BASEURL'];
            $this->subdir = $row['SUB_DIR'];
            $this->cur_ver = $row['value'];
        }`

but this doesn't look right.

How would I build a query so that it fetches all of the rows and data, and set the properties for me, all in one query?

As always, thank you so much!

[Atli]

In the query you issue there, you will get a "setting" and "value" pair for each row. So what you should be doing, to set the object attribute with the setting name to the value, would be this:

foreach ($q as $row) {
    $this->{$row["setting"]} = $row["value"];
}

It may be a wiser choice to use the magic __get method instead, and use a attribute array internally.

$db->execute()
$this->attributes = $db->fetchAll(PDO::FETCH_ASSOC);

And then

public function __get($name) {
    foreach ($this->attributes as $row) {
        if ($row["setting"] == $name) {
            return $row["value"];
        }
    }
    throw new InvalidArgumentException("Attribute {$name} doesn't exist.");
}

[pritaeas]

Basically, you can store all the rows in a single array variable and use the magic method __get to get the individual values. Have a look here.

[flynismo]

Those are both great suggestions.

Now, the only thing is, and hopefully I articulate myself well, what I need to be able to do is maintain the ability to use each property as needed, rather than get all the properties and list their values all at once.

-- Edit --

Nevermind, I reread your code, atli, and I see what you did there. That's pretty ingenious. Thanks to both you guys, I am going to play with both ideas and see which one fits into the site better. As usual, you guys rock!

[pritaeas]

Thanks to both you guys, I am going to play with both ideas

While I was typing the idea, Atli already gave you the code for that (second snippet). The ideas are identical ;)