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!

This is a tutorial that will give you an idea of how you can select/unselect all checkboxes from a form using JavaScript and also change the class style for the selected row from a table.

Read more from this entry…

Do you want to render data-driven and animated-charts for your web-applications and presentations using the power of Flash? Checkout the following FREE Flash Charting components. A lot of companies are using them to deliver eye-catching and snazzy graphics out of the boring (classical) data tables.

FusionCharts Free v2.1 is a cross-browser and cross-platform solution that can be used with PHP, ASP, ASP.NET, JSP, ColdFusion, Ruby on Rails, simple HTML pages and even Power Point presentations to deliver interactive and powerful flash charts. No Flash programming knowledge is required to use FusionCharts. You only need to know the language you’re programming in.

Open Flash Chart is an open source project which creates charts, pies, line bars etc. using the power of Flash.

Open Flash Chart is a compiled flash object which communicates with the world by downloading a data file. This is ‘at arms length’ and means you can use it in a commercial product, so long as you inform your users that Open Flash Chart is GPL and provide access (e.g. a link) to the source code.

amCharts is a free software which generates (animated) flash charts and requires flash player 8 or higher. The only limitation of the free version is that a small link to the http://www.amcharts.com/ address will be displayed in the top left corner of the charts.

Tabular data in table form is very common in web sites and web applications, a common requirement being the manipulation of table data on the client side. It is a better alternative than going back to the server, to sort, filter etc.

Here are some key features of the library that I found on JavaScript Toolbox.

- Fast Sorting due to a number of optimizations
- Rowspan and Colspan in headers that do not confuse the code
- Alternate row colors, handled by re-shading after sorting or filtering
- Tbody contents are handled separately, so there can be separate sortable sections of a table
- Easy CSS classes are applied to cells after filtering, sorting, etc which you can hook into from your CSS rules to show the sorted column or the cells which are filtered.

Client-Side Table Sorting Basic Example

Client-Side Table Filtering with alternate rows containing names starting with “Ma”

Client-Side Table Paging

Click here to view live examples!

JCrop is a JQuery image cropping plugin that can be implemented easily and quickly into a web application. Its advanced cropping functionality can be integrated in any web-based application without sacrificing power and flexibility. This plugin also features clean and well-organized code that works in most modern browsers.

Read more from this entry…

JQZoom is a JavaScript image magnifier built using the popular library JQuery. It works on all modern browsers: IE 6+, Mozilla Firefox 2+, Google Chrome 1.0, Safari 2+, Opera 9+.

Features

• Standard zoom
• Reverse zoom
• Zoom without lens and title
• Custom positioning the jqzoom window
• Fade out effect while hiding
• Fixed IE6- select box bug

[Go to Project Page | View Demos]

MooTools 1.2 image zoom is a script that displays a thumbnail with a selected region in it which is magnified and showed next to the thumbnail. Besides the small image, the original image is needed. Both have to be proportional in size. For example, if the thumbnail has a size of 100 x 300, the big image has to have a size of 400 x 1200 or similar (the ratio should be kept). The JS file is structured as a class and all CSS styles are mainly external.

[Go to Homepage | View Demo]

This is a tutorial useful to learn how to Ajaxify a basic login form. To do that we’ll use the powerful framework MooTools:

MooTools is a compact, modular, Object-Oriented JavaScript framework designed for the intermediate to advanced JavaScript developer. It allows you to write powerful, flexible, and cross-browser code with its elegant, well documented, and coherent API.

First, let’s start creating the login page. I will explain you the code in details.

login.php

<?php
require_once 'config.php';

// Is the user already logged in? Redirect him/her to the private page

if(isSet($_SESSION['username']))
{
header("Location: private.php");
exit;
}
?>

We start with the inclusion of the config.php file. Beside the configuration variables, in this file the session data is initialized. This is neccesary in order to check if the ‘username’ session is already registered when login.php is accessed. If it is, the user will be redirect to the members area.

Now it’s time to add the HTML code in the file:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
 <HEAD>
  <TITLE>AJAX Login Form</TITLE>

  <META name="Author" Content="Bit Repository">

  <META name="Keywords" Content="ajax, login, form, mootools">
  <META name="Description" Content="A nice & simple AJAX Login Form">

  <script type="text/javascript" src="js/mootools-1.2.1-core-yc.js"></script>
  <script type="text/javascript" src="js/process.js"></script>

  <link rel="stylesheet" type="text/css" href="style.css" />
</HEAD>

As you can notice, the ‘js’ folder contains: mootools-1.2.1-core-yc.js (the compact JavaScript framework) & process.js. The latter is the file that ajaxifies the login form.

Read the rest of this entry…

The aim of this tutorial is to help you create a web fetching script that can extract content from a password protected area using the necessary login credentials. I will use the well know cURL command line tool to connect to the protected web area. PHP supports libcurl which is required in order to use cURL functions in PHP.

The following script signs in to YouTube and fetches the latest favorite videos.

First, let’s create the file functions.php which should contain a practical cURL function & another function that extracts content between 2 delimiters (click here to view details about it):

<?php
function LoadCURLPage($url, $agent='', $cookie='', $referer='', $post_fields='', $ssl='')
{
$ch = curl_init(); 

curl_setopt($ch, CURLOPT_URL, $url);

if($ssl) curl_setopt($ch, CURLOPT_SSL_VERIFYHOST,  2);

curl_setopt ($ch, CURLOPT_HEADER, 0);

if($agent) curl_setopt($ch, CURLOPT_USERAGENT, $agent);

if($post_fields)
{
curl_setopt($ch, CURLOPT_POST, 1);
curl_setopt($ch, CURLOPT_POSTFIELDS, $post_fields);
}

curl_setopt($ch, CURLOPT_RETURNTRANSFER, 1);
curl_setopt($ch, CURLOPT_FOLLOWLOCATION, 1);

if($referer) curl_setopt($ch, CURLOPT_REFERER, $referer);

if($cookie)
{
curl_setopt($ch, CURLOPT_COOKIEFILE, $cookie);
curl_setopt($ch, CURLOPT_COOKIEJAR, $cookie);
}

$result = curl_exec ($ch);

curl_close ($ch);

return $result;
}

function extract_unit($string, $start, $end)
{
$pos = stripos($string, $start);  

$str = substr($string, $pos);  

$str_two = substr($str, strlen($start));  

$second_pos = stripos($str_two, $end);  

$str_three = substr($str_two, 0, $second_pos);  

$unit = trim($str_three); // remove whitespaces  

return $unit;
} 

?>

Read more from this entry…

PHP Simple HTML DOM Parser, written in PHP5+, allows you to manipulate HTML in a very easy way. Supporting invalid HTML, this parser is better then other PHP scripts that use complicated regexes to extract information from web pages.

Before getting the necessary info, a DOM should be created from either URL or file. The following script extracts links & images from a website:

// Create DOM from URL or file
$html = file_get_html('http://www.microsoft.com/');

// Extract links
foreach($html->find('a') as $element)
       echo $element->href . '<br>'; 

// Extract images
foreach($html->find('img') as $element)
       echo $element->src . '<br>';

The parser can also be used to modify HTML elements:

// Create DOM from string
$html = str_get_html('<div id="simple">Simple</div><div id="parser">Parser</div>');

$html->find('div', 1)->class = 'bar';

$html->find('div[id=simple]', 0)->innertext = 'Foo';

// Output: <div id="simple">Foo</div><div id="parser" class="bar">Parser</div>
echo $html;

Read more from this entry…