Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Neat MySQL query function.

    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.
    Last edited by marek_mar; 12-18-2005 at 07:37 PM.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #2
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    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()); 
    ?>

  • #3
    Regular Coder
    Join Date
    Jun 2004
    Posts
    565
    Thanks
    0
    Thanked 18 Times in 18 Posts
    From what I've seen in your code, the function doesn't support queries like "UPDATE `table` SET `column`= `column` + 1", does it?

    dumpfi

  • #4
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote 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.
    Quote 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());
    ?>
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #5
    Senior Coder
    Join Date
    Sep 2005
    Posts
    1,791
    Thanks
    5
    Thanked 36 Times in 35 Posts
    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

  • #6
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    This is not a DBAL. Ok I'll add it becouse it really is the most common thing.
    I updated my first post.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #7
    Regular Coder Element's Avatar
    Join Date
    Jul 2004
    Location
    Lynnwood, Washington, US
    Posts
    855
    Thanks
    2
    Thanked 2 Times in 2 Posts
    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?

  • #8
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    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.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #9
    Regular Coder Element's Avatar
    Join Date
    Jul 2004
    Location
    Lynnwood, Washington, US
    Posts
    855
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Quote 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.

  • #10
    Senior Coder
    Join Date
    Apr 2005
    Location
    Colorado, United States
    Posts
    1,208
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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.
    "$question = ( to() ) ? be() : ~be();"

  • #11
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Ok I didn't really understand what you've written. What is the part with connecting about. I never mentioned connecting or disconnecting.
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #12
    Regular Coder Element's Avatar
    Join Date
    Jul 2004
    Location
    Lynnwood, Washington, US
    Posts
    855
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Quote 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.

  • #13
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    How about a forum or CMS?
    I've seen pages do more that 100 queries (and still load quite fast).
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.

  • #14
    Regular Coder Element's Avatar
    Join Date
    Jul 2004
    Location
    Lynnwood, Washington, US
    Posts
    855
    Thanks
    2
    Thanked 2 Times in 2 Posts
    Quote 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.

  • #15
    Senior Coder
    Join Date
    Aug 2003
    Location
    One step ahead of you.
    Posts
    2,815
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by Element
    different differences.
    You sure they're different?
    I'm not sure if this was any help, but I hope it didn't make you stupider.

    Experience is something you get just after you really need it.
    PHP Installation Guide Feedback welcome.


  •  
    Page 1 of 2 12 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •