The SQL Class - Page 5

Tanax · 01-30-2009, 10:10 PM

A little update about the functions. They look like this right now:

php Code:

public function getInsert($table, $args) 
        {
            
            $field_names = array_keys($args);
            $field_names = array_map(create_function('$var', 'return "`" . $var . "`";'), $field_names);
            
            $sql_string_names = '(';
            $sql_string_names .= implode(',', $field_names);
            $sql_string_names .= ')';
            
            $exceptions = array('now()', 'null');
            $field_values = array();
            
            foreach($args as $field_value)
            {
                
                $field_value = trim($field_value);
                $field_value = strtolower($field_value);
                
                if(!in_array($field_value, $exceptions))
                {
                    
                    if($field_value == '"now()"')
                    {
                        
                        $field_value = "'".str_replace('"', '', $field_value)."'";
                        
                    }
                    elseif($field_value == '"null"')
                    {
                        
                        $field_value = "'".str_replace('"', '', $field_value)."'";
                        
                    }
                    else
                    {
                        
                        $field_value = "'".$field_value."'";
                        
                    }
                    
                }
            
                $field_values[] = $this->secure($field_value);
                
            }
            
            $sql_string_values = '(';
            $sql_string_values .= implode(',', $field_values);
            $sql_string_values .= ')';
            
            return 'INSERT INTO `' . $table . '` ' . $sql_string_names . ' VALUES ' . $sql_string_values . ';';
            
        }
        
        public function insert($table, $args)
        {
            
            $sql = $this->getInsert($table, $args);
            
            if($this->exeQuery($sql))
            {
                
                return true;
                
            }
            
            return false;
            
        }

Usage:

PHP Code:


			
$sql_data = array('comment_post_ID'      => $comment_post_ID,
                  'comment_author'       => $comment_author,
                  'comment_author_email' => $comment_author_email, // some comments here
                  'comment_author_url'   => $comment_author_url,
                  'comment_author_IP'    => $comment_author_IP,   // IP of the author
                  'comment_date'         => $comment_date,    // Comment date
                  'comment_date_gmt'     => $comment_date_gmt,
                  'comment_content'      => $comment_content,
                  'comment_approved'     => $comment_approved,
                  'comment_agent'        => $comment_agent,
                  'comment_type'         => $comment_type,
                  'comment_parent'       => $comment_parent,
                  'user_id'              => $user_id);

$db->insert('wp_comments', $sql_data);

or..

PHP Code:


			
$sql_data = array('comment_post_ID'      => $comment_post_ID,
                  'comment_author'       => $comment_author,
                  'comment_author_email' => $comment_author_email, // some comments here
                  'comment_author_url'   => $comment_author_url,
                  'comment_author_IP'    => $comment_author_IP,   // IP of the author
                  'comment_date'         => $comment_date,    // Comment date
                  'comment_date_gmt'     => $comment_date_gmt,
                  'comment_content'      => $comment_content,
                  'comment_approved'     => $comment_approved,
                  'comment_agent'        => $comment_agent,
                  'comment_type'         => $comment_type,
                  'comment_parent'       => $comment_parent,
                  'user_id'              => $user_id);

$sql = $db->getInsert('wp_comments', $sql_data);

//and execute the sql at later moment whenever you wish:
$db->exeQuery($sql);

Haven't tested function though. Anyone might want to take a glance at it to see possible errors.

And if you then want to, I can implement it.

Salathe · 01-30-2009, 11:24 PM

You're going to have a whole heap of trouble (perhaps only a small heap) with that function as-is. Just from looking at the code, without testing it live, there are numerous points of either, "hey, that won't work" or "why on earth is this here?"

What's going on with the "now()" and "null" (with and without double quotes)? Exceptions to what? You strtolower all values? You feed a single-quote-wrapped value through the DBmysql::secure method, is the value secured then?

As for implementing this sort of function, keep it out of the current class at least. There's no harm in creating another class for this sort of query building job.

Tanax · 01-30-2009, 11:55 PM

As said, this was from that site.
I looked at it at a glance. The "now()" and "null" seemed logical, since you want those to be the sql value of now()..

That's true.. ahwell, it was just an idea