Dynamic Database function for getting and setting data

Question

I have created a class to handle all database operations for my application. So far I have gathered and displayed the data in foreach statements within my view. What I wish to be able to do is get and set individual elements from a function from my database class.

Here is my database function for displaying data within a foreach:

   public function test($sql, $type, $param)
   {
       $variables = array();
       $results = array();

       // Mysqli
       if($stmt = $this->AC->Mysqli->prepare($sql))
       {
        $params = array_merge($type, $param);
           call_user_func_array(array($stmt, 'bind_param'), $this->make_values_referenced($params));
        $stmt->execute();  
        $stmt->store_result();          
        // Existance
        if($stmt->num_rows > 0)
        {

        $meta = $stmt->result_metadata();

        while($field = $meta->fetch_field()) 
        {  
            $parameters[] = &$row[$field->name];  
        }  

        call_user_func_array(array($stmt, 'bind_result'), $parameters);                     


        while($stmt->fetch()) 
        {  

            $elemet = array();  

            foreach($row as $key => $val) 
            {  
                $element[$key] = $val;  
            }  

            $results[] = $element;  

        } 

        return $results;            

        }
        else
        {
            $results = FALSE;
            return $results;
        }            
        }
        else
        {
        die("ERROR: We could not connect.");
        }
    }

The function below is called from my model:

    public function profile_information($account_name)
    {
    // Mysqli Variables
    $sql = "SELECT $this->account_details.bio 
            FROM account_details 
            WHERE $this->account_details.account_name = ? LIMIT 10";    

            $type = array("s");
            $param = array($account_name);

            $results = $this->AC->Database->prepared_select_loop($sql, $type, $param);

            $this->AC->Template->set_data('profile_information', $results, FALSE);
    }

Once set within my model, I call the function within my controller and access it within my view with a foreach for displaying data:

    $profile_information = $this->get_data('profile_information');
    foreach ($profile_information as $row) : 
        //Displaying data here
    endforeach;

The above works fine for displaying a large amount of data, but what I'm wanting to do is call the a database function that will allow me set individual data elements. Therefore not having to use a foreach if im only getting a limited amount of data (i.e. one row of Name, age, address)

A non dynamic way I have tackled this problem is to write the database for every function that only desires one row from the database:

    function name($variable)
    {
            $sql = 'statement here';
            $stmt = $this->AC->Mysqli->prepare($sql);
                $stmt->bind_param("i", $id);
                $stmt->execute();
                $stmt->store_result();
                $stmt->bind_result(bind results);
                $stmt->fetch();

            $this->AC->Template->set_data('id', $id);               
            $this->AC->Template->set_data('account_name', $account_name);
    }

So in basically I want to make the above statement refactored into my database class and thus making it more dynamic.

I dont know how I would be able to tackle this problem, I don't want to use PDO, as I wish to find a solution within Mysqli. Any help would be appreciated.

your idea of combining database api with template calls in one function looks err... unusual to me. However, I wish you luck. Mysqli especially evil with dynamic prepared statements. If you don't like PDO one-liners, several screens of mysqli code is the best choice. — Your Common Sense
– Your Common Sense, Commented Mar 11, 2013 at 14:51

Your Common Sense · Accepted Answer · 2013-03-11 19:39:42Z

1

Would you recommend just switching over to PDO?

Definitely.

Whole your function test() could be rewritten into three lines (assuming $param contains an array with parameters for the prepared statement):

public function test($sql, $param)
{
    $stmt = $this->AC->pdo->prepare($sql);
    $stmt->execute($param);  
    return $stmt->fetchAll();
}

answered Mar 11, 2013 at 19:39

Your Common Sense

158k42 gold badges226 silver badges374 bronze badges

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

Collectives™ on Stack Overflow

Dynamic Database function for getting and setting data

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