Efficient way to get data from mysql in a php foreach loop

Question

I have some options stored in mysql. The table contains 4 columns that look like this:

option_id | option_name | option_default | option_user_value
------------------------------------------------------------
1         | timezone    | Europe/Athens  |
2         | mode        | 1              |
3         | email       | [email protected] |

So far I get that info from the db using something like the following php code:

$table  = 'options';
$values = '
    option_name,
    option_default,
    option_user_value
';
$where = null;
$options = get_db_entries($table, $values, $where);

if ($options)
{
    foreach ($options as $db_entry)
    {
        if ($db_entry['option_name'] == 'timezone')
        { $_timezone = $db_entry['option_default']; }

        if ($db_entry['option_name'] == 'mode')
        { $_mode = $db_entry['option_default']; }

        if ($db_entry['option_name'] == 'email')
        { $_email = $db_entry['option_default']; }
    }
}

Is there a more efficient way in order to avoid those 'if' statements there in the loop ? I mean is there a way to directly insert that info from those columns to each variable without IFs ?

Just out of curiosity, please check my get_db_entries() function too if you want. I am still learning (not familiar with OO yet) and thanks in advance. Have a nice day.

function get_db_entries($table, $values, $where)
{
    $db_host = '........';
    $db_name = '........';
    $db_user = '........';
    $db_pass = '........';
    //  -----------------------------------------------------
    $dbh = new PDO(
        'mysql:host='.$db_host.'; dbname='.$db_name,
        $db_user,
        $db_pass,
        array(PDO::MYSQL_ATTR_INIT_COMMAND => "SET NAMES utf8")
    );
    //  -----------------------------------------------------
    if ($where) { $where = ' WHERE '.$where; }

    $sql = 'SELECT '.$values.' FROM '.$table.$where;
    $results = $dbh->query($sql);
    //  -----------------------------------------------------
    $results_array = array();
    foreach ($results as $db_entry)
    { array_push($results_array, $db_entry); }

    return $results_array;
}

Don't recreate your database handle each time you make a database query. And instead of if - thening your loop to death, why not just throw the results of the table into an array? — WWW
– WWW, Commented Mar 21, 2013 at 16:35
what do you mean by "recreate your database handle" ? how do I do this mistake if you could explain please ? Also, if you check the function, it is already returning an array (check near the end). — durduvakis
– durduvakis, Commented Mar 21, 2013 at 16:40

AbsoluteƵERØ · Accepted Answer · 2013-03-21 17:10:23Z

1

Every time you call that function with the DB connection you're opening, querying and closing the connection to the database... this causes a lot of overhead on the SQL server and the PHP/MySQL connector. You can run the server out of resources if there are a lot of queries. Pull it into an array so you only use one DB connection and one query like this:

<?php
$sql = "select * from database.table;";
$result = mysql_query($sql);
while ($db_entry = mysql_fetch_array($result)){

    if ($db_entry['option_name'] == 'timezone')
    { $_timezone = $db_entry['option_default']; }

    if ($db_entry['option_name'] == 'mode')
    { $_mode = $db_entry['option_default']; }

    if ($db_entry['option_name'] == 'email')
    { $_email = $db_entry['option_default']; }

}
?>

Example with PDO (Not tested):

<?php
try {
$DBH = new PDO("mssql:host=$host;dbname=$dbname, $user, $pass");    
}
catch(PDOException $e) {  
   echo $e->getMessage();  
}  

$STH = $DBH->query('select * from database.table');

$STH->setFetchMode(PDO::FETCH_OBJ); 

while($row = $STH->fetch()) {

    switch($row->option_name){
    case 'mode':
    $_mode = $row->option_default;
    break;
    case 'email':
    $_email = $row->option_default;
    break;
    case 'timezone':
    //timezone
    $_timezone = $row->option_default;
    default:
    //do something else
    }

}

?>

edited Mar 21, 2013 at 17:10

answered Mar 21, 2013 at 16:40

AbsoluteƵERØ

7,8792 gold badges27 silver badges35 bronze badges

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

1 Comment

durduvakis Over a year ago

Thanks I understand, but can I do this using PDO ? I made a function for it so that I use less code each time etc.

Mike Higginbottom · Accepted Answer · 2013-03-21 16:48:33Z

0

Use an array, called $option_defaults for example, instead of your individual variables ($_timezone, $_mode, $_email etc.) and add entries to it using $db_entry['option_name'] as a key with $db_entry['option_default'] as the associated value.

answered Mar 21, 2013 at 16:48

Mike Higginbottom

5432 gold badges5 silver badges8 bronze badges

1 Comment

durduvakis Over a year ago

Good idea. Although $options is already an array and I thought there is a better way to get the data directly from that array than mine above. Right now I am trying your solution.

Collectives™ on Stack Overflow

Efficient way to get data from mysql in a php foreach loop

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related