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.phpfile 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
This site runs on the Thesis WordPress Theme
If you're someone who doesn't understand a lot of PHP, HTML, or CSS, Thesis will give you a ton of functionality without having to alter any code. For the advanced, Thesis has incredible customization possibilities via extensive hooks and filters. And with so many design options, you can use the template over and over and never have it look like the same site.
If you're more familiar with how websites work, you can use the fantastic Thesis User's Guide and world-class support forums to make more professional customizations than you ever thought possible. The theme is not only highly customizable, but it allows me to build sites with a much more targeted focus on monetization than ever before. You can find out more about Thesis below:
