Making MySQL Update and Insert Easier

This is a continuation to the MySQL Queries Made Easy With PHP Functions Library post. These functions belong with the class (but not in the class) we created in the functions-db.php file from that post.

I don’t always write the most organized code. I will admit that. But one thing I hate to see when I open up a script is something like this:

PHP

<?php
    $result1 = new Query("INSERT INTO seminars (id,conference,date,time1,time2,topic,company,text,speaker,handout,modified,modifiedby,created,createdby) VALUES ('','$in_conference','$in_date','$in_time1','$in_time2','$in_topic','$in_company','$in_text','$in_speaker','$in_handout',NOW(),'$in_modifiedby',NOW(),'$in_createdby')",$sql); 
?>

Even worse: I hate trying to update that, cause I know you’ve all seen queries 2x as long and 5x more complicated.

So I sat and thought about a more efficient way to update something like that. To me an array was the best option.

Making MySQL Inserts Easier to Maintain

I knew I could either do an associative array or two arrays, for inserting data I thought it would be easier to have the fields and values in separate arrays so I can easily implode them as comma separated values which is perfect for an INSERT query.

Creating my Insert Arrays

First I’ll construct my array to send to the function

PHP

<?php
    $fields[] = 'id';
        $values[] = 'NULL';
    $fields[] = 'conference';
        $values[] = $in_conference;
    $fields[] = 'date';
        $values[] = $in_date;
    $fields[] = 'time1';
        $values[] = $in_time1;
    $fields[] = 'time2';
        $values[] = $in_time2;
    $fields[] = 'topic';
        $values[] = $in_topic;
    $fields[] = 'company';
        $values[] = $in_company;
    $fields[] = 'text';
        $values[] = $in_text;
    $fields[] = 'speaker';
        $values[] = $in_speaker;
    $fields[] = 'handout';
        $values[] = $in_handout;
    $fields[] = 'modified';
        $values[] = 'NOW()';
    $fields[] = 'modifiedby';
        $values[] = $in_modifiedby;
    $fields[] = 'created';
        $values[] = 'NOW()';
    $fields[] = 'createdby';
        $values[] = $in_createdby;

    $query = constructInsert('seminars', $fields, $values);
?>

MySQL Insert Constructor Function

I find that to be refreshingly organized. Now we create the function that works with that array constructInsert()

PHP

<?php
    function constructInsert ($table, $fields, $values){

        if(!is_array($fields) || !is_array($values))
            return 'Error - Fields and values must be sent as an array';

        $field_ct  = count($fields);
        $value_ct = count($values);

        if($field_ct != $value_ct)
            return 'Error - Field count and value count do not match.';

        $query = "INSERT INTO `$table` (`";
        $query .= implode('`, `', $fields) . "`) VALUES ('";
        $query .= implode("', '", $values) . "');";

        $query = str_replace("'NOW()'", "NOW()", $query);
        $query = str_replace("'NULL'", "NULL", $query);

        return $query;
    }
?>

Just like in the example listing the arrays you just pass the function the table name, then the fields and values arrays.

I’ve specifically made sure the function makes NOW() and NULL work correctly by removing the single quotes from around their values and the function even adds the back ticks ` which aren’t required but when you use a MySQL keyword to name a field you’ll learn your lesson real quick, use your back ticks!

Continue on to page 2: making an easy to maintain MySQL Update Query

Pages: 1 2

Tagged as: database, functions, insert, mysql, PHP, update