Lateral Code

A Web Development Blog Focused on Code and Technology

Streamlining MySQL Insert Queries

By Karthik Viswanathan

PHP and MySQL have often been known as two peas in a pod. Unfortunately, when you use PHP to insert information into a MySQL database, you often have to write large queries that take up much of your time. Not only that, but you also have to clean user input. This quick tip aims to streamline your efficiency by writing a simple function to do this job for you.

Write helper functions

Before we start working on the main program, we first need to create two helper functions which will be used later on:

function query( $mysql ) {
	$result = mysql_query( $mysql ) or die( mysql_error() );
	return $result;
}

This first function runs a MySQL query and returns the result. It will also exit if the query fails to execute.

function clean( $input ) {
	if( get_magic_quotes_gpc() )
		$input = stripslashes( $input );
	return mysql_real_escape_string( $input );
}

User input is never guaranteed to be safe. This function cleans input and prevents malicious attacks.

Now we’re ready to start the main program.

Create the function prototype

To begin, we will create a function that requires two parameters: a table name and an associative array (map) of MySQL field names and their respective values.

function insertInto( $table, $info )

After the function finishes executing, a new row of the database will be created with values corresponding to those in $info.

Find the columns in the MySQL table

In order to run an insert query, we must know what columns are in the given MySQL table. This can be done using the following query:

$cols = query( "SHOW COLUMNS FROM $table" );

Loop through the columns and store the necessary information

For each column that is found, we need to store the value in the $info map that corresponds to the its name:

$fields = array();
while( ( $row = mysql_fetch_assoc( $cols ) ) !== false ) {
	// ignore primary keys, which are often times auto-incremented ids
	if( $row[ 'Key' ] === 'PRI' )
		continue;

	$name = $row[ 'Field' ];
	$fields[ $name ] = clean( $info[ $name ] );
	if( strpos(  $row[ 'Type' ], 'int' ) === false )
		$fields[ $name ] = "'" . $fields[ $name ] . "'";
}

Notice how the value is cleaned before being stored inside the $fields array. In addition, if the type of the MySQL field is some sort of string, the value must be enclosed in single quotes for the query to work properly.

Construct the query and run it

Now that we have all the information, we just need to construct a query based off of the keys (which are MySQL field names) and values (the corresponding information stored in each field) of the $fields array:

$keys = array_keys( $fields );
$values = array_values( $fields );

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

PHP’s implode function is perfect in this situation, as it places all array elements into a string separated by a given delimiter. By using a comma and space as the delimiter, the MySQL query is easily formed.

Conclusion

The function is now complete. It can be used in a form handler by simply passing in the $_POST or $_GET array as a parameter:

// post data
insertInto( 'tableName', $_POST );

// get data
insertInto( 'tableName', $_GET );

And that’s really all there is to it. The full function along with the helpers can be found below:

function insertInto( $table, $info ) {
	$cols = query( "SHOW COLUMNS FROM $table" );

	$fields = array();
	while( ( $row = mysql_fetch_assoc( $cols ) ) !== false ) {
		if( $row[ 'Key' ] === 'PRI' )
			continue;

		$name = $row[ 'Field' ];
		$fields[ $name ] = clean( $info[ $name ] );
		if( strpos(  $row[ 'Type' ], 'int' ) === false )
			$fields[ $name ] = "'" . $fields[ $name ] . "'";
	}

	$keys = array_keys( $fields );
	$values = array_values( $fields );

	$str = "INSERT INTO $table(" . implode( $keys, ', ' )  . " ) VALUES( " . implode( $values, ', ' ) . " )";
	query( $str );
}

function query( $mysql ) {
	$result = mysql_query( $mysql ) or die( mysql_error() );
	return $result;
}

function clean( $input ) {
	if( get_magic_quotes_gpc() )
		$input = stripslashes( $input );
	return mysql_real_escape_string( $input );
}

For those of you who prefer something more MySQLi-related (MySQL Improved), take a look at my active records database class on GitHub, which can streamline your select, insert, update, and delete queries. It supports where clauses, joins, ordering, limits, and more.

Thanks for reading!

Tags: php

This entry was posted on Friday, December 31st, 2010 at 23:21:24. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

• RSS
• Tweet this!
• Delicious
• Facebook
• DFloat
• DMoo
• E-mail

Leave a Reply

Click here to cancel reply.

* Name

* E-mail (not published)

Website

Want to be notified when someone replies? Subscribe to this post's comment RSS feed.
Any field marked with a * is required.

Copyright © 2015 Lateral Code. Proudly powered by WordPress.