Neat MySQL query function.

[marek_mar]

This is a little function I use to build INSERT and UPDATE queries. It makes it a lot simpler to do these queries and it escapes non numerical data too.

PHP Code:

<?php
function build_query($type, $table, $array, $where = false, $value = false)
{
    $type = strtoupper($type);
    switch($type)
    {
        case 'UPDATE':
            $ret = array();
            foreach($array as $k => $v)
            {
                if(is_array($v))
                {
                    $ret[] = '`' . $k . '` = `' . $k . '`' . $v[0];
                }
                else
                {
                    $ret[] = '`' . $k . '` = ' . ((is_numeric($v)) ? $v : '\'' . mysql_real_escape_string($v) . '\'');
                }
            }
            $ret = 'SET ' . implode(', ', $ret);
            if($where && $value)
            {
                $ret .= ' WHERE `' . $where . '` = ' . ((is_numeric($value)) ? $value : '`' . $value . '`'); 
            }
        break;
        case 'INSERT':
            $type = 'INSERT INTO';
            foreach($array as $k => $v)
            {
                $array[$k] = ((is_numeric($v)) ? $v : '\'' . mysql_real_escape_string($v) . '\'');
            }
            $ret = '(`' . implode('`, `', array_keys($array)) . '`) VALUES (' . implode(', ', $array) . ')';
        break;
    }
    return $type . ' `' . $table . '` ' . $ret;
}
?>

It accepts 3 arguments
- The query type (INSERT or UPDATE).
- The tabel name.
- An array contining the data.
Heres an example on how to use this function.

PHP Code:

<?php
define('TABLE_USERS', 'users');
$sql_data = array(
    'user_name' => 'Marek',
    'user_id' => 0,
    'user_level' => 5,
    'comment' => 'It really works!',
    'counter' => array('+1')
);
$sql = build_query('UPDATE', TABLE_USERS, $sql_data, 'user_id', 4);
mysql_query($sql) or die($sql . "<br />\n" . mysql_error());?>

The array can not contian values of the following types: array, object, resource.

[GJay]

Why not have the name(s) of any id's as another argument to the function, so you wouldn't have to worry about appendingthe 'WHERE...' to the update?
So in your example you'd have

PHP Code:

<?php 
define('TABLE_USERS', 'users'); 
$sql_data = array( 
    'user_name' => 'Marek', 
    'user_id' => 0, 
    'user_level' => 5, 
    'comment' => 'It really works!' 
); 
$sql = build_query('UPDATE', TABLE_USERS, $sql_data,'user_id'); 
mysql_query($sql) or die($sql . "<br />\n" . mysql_error()); 
?>

[dumpfi]

From what I've seen in your code, the function doesn't support queries like "UPDATE `table` SET `column`= `column` + 1", does it?

dumpfi

[marek_mar]

Originally Posted by GJay

Why not have the name(s) of any id's as another argument to the function, so you wouldn't have to worry about appendingthe 'WHERE...' to the update?

the WHERE part can be pretty complicated (WHERE `field` <= $number AND `otherfield` LIKE 'whatever'). It was just an example. It's would be easier to write that part yourself than tell a function to do it.... unless the whole WHERE statement would be the fourth argument.

Originally Posted by dumpfi

From what I've seen in your code, the function doesn't support queries like "UPDATE `table` SET `column`= `column` + 1", does it?

Apart from adding that manually I don't think it would be that simple to identify if it's coded to increment or if it's user input. That's why I went with changing the type to array which has only one element.

PHP Code:

<?php
function build_query($type, $table, $array)
{
    $type = strtoupper($type);
    switch($type)
    {
        case 'UPDATE':
            $ret = array();
            foreach($array as $k => $v)
            {
                if(is_array($v))
                {
                    $ret[] = '`' . $k . '` = `' . $k . '`' . $v[0];
                }
                else
                {
                    $ret[] = '`' . $k . '` = ' . ((is_numeric($v)) ? $v : '\'' . mysql_real_escape_string($v) . '\'');
                }
            }
            $ret = 'SET ' . implode(', ', $ret);
        break;
        case 'INSERT':
            $type = 'INSERT INTO';
            foreach($array as $k => $v)
            {
                $array[$k] = ((is_numeric($v)) ? $v : '\'' . mysql_real_escape_string($v) . '\'');
            }
            $ret = '(`' . implode('`, `', array_keys($array)) . '`) VALUES (' . implode(', ', $array) . ')';
        break;
    }
    return $type . ' `' . $table . '` ' . $ret;
}

define('TABLE_USERS', 'users');
$sql_data = array(
    'user_name' => 'Marek',
    'user_id' => 0,
    'user_level' => 5,
    'comment' => 'It really works!',
    'counter' => array('+1')
);
$sql = build_query('UPDATE', TABLE_USERS, $sql_data) . ' WHERE `user_id` = ' . $sql_data['user_id'];
mysql_query($sql) or die($sql . "<br />\n" . mysql_error());
?>

[GJay]

I was thinking only for instances where you'd be updating one record based on a unique ID, and it's how 'Replace' works in ADODB

[marek_mar]

This is not a DBAL. Ok I'll add it becouse it really is the most common thing.
I updated my first post.

[Element]

That seems a bit to complicated, really. I mean, why do that when you can make a function to connect and disconnect within the function, submit a query and then return the result. Maybe something like:

PHP Code:

define('TABLE_USERS');
$result = query(TABLE_USERS, "INSERT * INTO users VAUES('', '".$username."', '".$userid."', '".$userlevel."', '".$comment."', '".$counter."')");
if ($row = @mysql_fetch_rows($result)) { 


Doesn't it just... make sense? Maybe I'm not just seeing the uniqueness of your function, if so, I'm sorry. Can you explain the bonuses?

[marek_mar]

Yes.
It works very well with my form building class. Kind of odd as that function is older then the class.
Ok jokes aside. The function makes it simpler to build those queries.
BTW I don't see why I would want to connect and diconnect, yet alone get the result from an INSERT or UPDATE query.

[Element]

Originally Posted by marek_mar

Yes.
It works very well with my form building class. Kind of odd as that function is older then the class.
Ok jokes aside. The function makes it simpler to build those queries.
BTW I don't see why I would want to connect and diconnect, yet alone get the result from an INSERT or UPDATE query.

Err the last part was just from my function, I just edited the query. And why wouldn't you? It is in the top list of MySQL security, to connect and disconnect only when you need it, which was one of the points of original custom MySQL functions, so you didn't need to connect, you simply ran the query function.

[Velox Letum]

Its actually less secure to connect and disconnect...more round trips for the data to be taken during transit. Also it would increase the connection overhead astronomically by connecting/disconnecting for every query. Just think, if you had 30 queries, that would mean connecting and disconnecting 30 times. 30 times for someone to intercept the connection info, 30 times the connection overhead.

[marek_mar]

Ok I didn't really understand what you've written. What is the part with connecting about. I never mentioned connecting or disconnecting.

[Element]

Originally Posted by Velox Letum

Its actually less secure to connect and disconnect...more round trips for the data to be taken during transit. Also it would increase the connection overhead astronomically by connecting/disconnecting for every query. Just think, if you had 30 queries, that would mean connecting and disconnecting 30 times. 30 times for someone to intercept the connection info, 30 times the connection overhead.

What script runs 30 queries in one page? Hell, thats more then any script needs to do.

[marek_mar]

How about a forum or CMS?
I've seen pages do more that 100 queries (and still load quite fast).

[Element]

Originally Posted by marek_mar

How about a forum or CMS?
I've seen pages do more that 100 queries (and still load quite fast).

Well... they must be horrible forums and CMS's because here on Invision (1.3) it has less then 20, and on Droopal, one of the best CMS it has less then 10. At leat on the pages I'm looking at, I'll keep going through them. Anyway, been talking to Velox and understanding some different differences.

[marek_mar]

Originally Posted by Element

different differences.

You sure they're different?