Hello,

The SQL Insert Syntax allows us to insert single or multiple records in the database. This is a sample command:

INSERT INTO users (`name`, `email`, `phone`) VALUES ('john', 'demo@domain.com', '1234');

This is a quite simple syntax and can be integrated in a PHP script like this:

$sql_insert = mysql_query("INSERT INTO `users` (`name`, `email`, `phone`)
VALUES ('john', 'demo@domain.com', '1234');") or die(mysql_error());

Can a query be constructed easily?

The following function parses the data from an array (key = field names, value = field values) and constructs the SQL INSERT Query. It’s recommended especially for large SQL inserts. You can have the ‘name’ & ‘value’ on the same line and also add and remove easily any values from the array. It’s a better alternative to the classical SQL insert string.

<?php
function sql_insert_compile($table, $array)
{
/* -------- SQL INSERT FIELD NAMES -------- */

$sql_string_names = '(';
$field_names = array_keys($array);
$field_names = array_map(create_function('$var', 'return "`".$var."`";'), $field_names);
$sql_string_names .= implode(',', $field_names);
$sql_string_names .= ')';

/* -------- SQL INSERT FIELD VALUES -------- */

$field_values = array();

// values that won't be enquoted
$exceptions = array('now()', 'null');

// Loop through the field values and make the necessary changes
foreach($array as $field_value)
{
$field_value = trim($field_value);

$to_check = strtolower($field_value);

if(!in_array($to_check, $exceptions))
{
	if($to_check == '"now()"')
	{
		$field_value = "'".str_replace('"', '', $field_value)."'";
	}
	elseif($to_check == '"null"')
	{
	    $field_value = "'".str_replace('"', '', $field_value)."'";
	}
	else
	{
		$field_value = "'".$field_value."'";
	}
}

if(get_magic_quotes_gpc()) // Enabled? Apply stripslashes() to the data
{
$field_value = stripslashes($field_value);
}

$field_values[] = mysql_real_escape_string($field_value);
}

$sql_string_values = '(';
$sql_string_values .= implode(',', $field_values);
$sql_string_values .= ')';

return 'INSERT INTO `'.$table.'` '.$sql_string_names.' VALUES '.$sql_string_values.';';
}
?>

This is a basic WordPress SQL insert to fill a comment into the database:

$sql = "INSERT INTO wp_comments (comment_post_ID, comment_author, comment_author_email,
comment_author_url, comment_author_IP, comment_date, comment_date_gmt,
comment_content, comment_approved, comment_agent, comment_type, comment_parent, user_id)
VALUES
('$comment_post_ID', '$comment_author', '$comment_author_email', '$comment_author_url',
 '$comment_author_IP', '$comment_date', '$comment_date_gmt', '$comment_content',
'$comment_approved', '$comment_agent', '$comment_type', '$comment_parent', '$user_id')";

Here’s how we can build it in a nicer way (having the name and value on the same line):

$sql_data = array('comment_post_ID'      => $comment_post_ID,
                  'comment_author'       => $comment_author,
                  'comment_author_email' => $comment_author_email, // some comments here
                  'comment_author_url'   => $comment_author_url,
                  'comment_author_IP'    => $comment_author_IP,   // IP of the author
                  'comment_date'         => $comment_date,    // Comment date
                  'comment_date_gmt'     => $comment_date_gmt,
                  'comment_content'      => $comment_content,
                  'comment_approved'     => $comment_approved,
                  'comment_agent'        => $comment_agent,
                  'comment_type'         => $comment_type,
                  'comment_parent'       => $comment_parent,
                  'user_id'              => $user_id);

$sql = sql_insert_compile('wp_comments', $sql_data);

A special value is now() which is used to return the current date and time. In an INSERT case it is added without any quotes. This way it is interpreted as a function. Here’s an example:

INSERT INTO `activity` (`user_id`, `type`, `date`) VALUES ('3', 'premium', now())

The `date` field could have one of the following types: DATETIME (’0000-00-00 00:00:00′), DATE (’0000-00-00′), TIMESTAMP ( ’0000-00-00 00:00:00′), TIME ( ’00:00:00′), YEAR (0000).

If, for any reason, you do not want to use now() as a function and you just need to insert the text ‘now()’ in the DB, consider double quoting the value like in this example:

$sql_data = array('field_one'      => 'value here',
                  'field_two'       => '"now()"');

$sql = sql_insert_compile('some_table', $sql_data);

The function will return the following SQL Insert Query:

INSERT INTO `some_table` (`field_one`,`field_two`) VALUES ('value here','now()');

UPDATE: The NULL value is included in the $exceptions list. Just like now() it will be unquoted, unless you double quote it.

If you have any questions regarding this function, do not hesitate to comment on this post ;-)

Happy coding!

Do you wish to receive the latest updates as soon as they are posted? Get our RSS Feed or Subscribe to the Newsletter!