mysql syntax highlighter by jquery

Written by @kerixa 23 February 2014

For the websites that usually contain programming codes, such as those that sell free codes or solve programming problems, It is essential to use a syntax highlighter. Without a syntax analyzer, the programmer's eyes get tired and can not read the code perfectly. The following code is a sample syntax highlighter and with linearization it has, the lines are not mixed together and the audience's attention is drawn back again.

Code Snippet:

                                                <!-- this script is provided by coded by: Kerixa Inc. -->
<style type="text/css">
* {
    margin: 0;
    padding: 0;

body {
    padding: 30px;

#highlight {
    display: none;

/*styles for the pre tag*/
#layer {
    font-family: monospace;
    font-size: 12px;
    line-height: 20px;
    border: 1px solid #ccc;
    background: linear-gradient(white, white 19px, #ddd 19px, #ddd 20px);
    background-size: 20px 20px;
    padding: 20px 30px;
    white-space: pre-wrap; /*to wrap words*/
    word-wrap: break-word; /*IE 5.5+*/

.sc {
    color: brown;

.keyword {
    color: purple;
    font-weight: bold;

.string {
    color: darkgreen;

.comment {
    color: green;
    background: lightyellow;

.function {
    color: red;

<pre id="layer"></pre>

    <!-- textarea to contain the raw sql -->
<textarea id="highlight" name="highlight">
#This is a mysql syntax highlighter
-- This is a comment
And this is a multi line comment
SELECT things FROM table;

SELECT AS agent, ``, d.first_payment_date, d.account_number, as disbursement_id, d.amount as loan_amount, d.late_fee, as bid, as borrower, as payment_frequency, as application_id, 

(SELECT COUNT(id) FROM payment_schedule WHERE disbursement_id = AND skip = '1') as skip_count, 

IFNULL(SUM(r.principal), 0) as paid_principal, 

IF(d.interest_calculation_method_id = 2, d.amount, IFNULL((SELECT SUM(principal) FROM payment_schedule WHERE disbursement_id = AND skip = '0' AND date <= CURDATE()), 0)) as psprincipal, 

IF(d.interest_calculation_method_id IN(2, 3), d.amount - (SELECT IFNULL(SUM(principal), 0) FROM receipts WHERE disbursement_id =, IFNULL((SELECT SUM(principal+interest) FROM payment_schedule WHERE disbursement_id = AND skip = '0' AND date <= CURDATE()), 0)) as psamount, 

FROM disbursements d 
LEFT JOIN receipts r ON 
LEFT JOIN applications ap ON d.application_id =  
LEFT JOIN agents ag ON ap.vt_user_id = ag.vt_user_id 
LEFT JOIN borrowers b ON ap.borrower_id = 
LEFT JOIN time_frequencies tf ON d.payment_frequency_id = 

WHERE d.status = 'active'
ORDER BY due_date

    <!-- jquery -->
<script src="" type="text/javascript"></script>
<script type="text/javascript">
	//full list of reserved words:
	//adding lowercase keyword support
	var len = k.length;
	for(var i = 0; i < len; i++)

	var re;
	var c = $("#highlight").val(); //raw code

	//regex time
	//highlighting special characters. /, *, + are escaped using a backslash
	//'g' = global modifier = to replace all occurances of the match
	//$1 = backreference to the part of the match inside the brackets (....)
	c = c.replace(/(=|%|\/|\*|-|,|;|\+|<|>)/g, "<span class=\"sc\">$1</span>");

	//strings - text inside single quotes and backticks
	c = c.replace(/(['`].*?['`])/g, "<span class=\"string\">$1</span>");

	//numbers - same color as strings
	c = c.replace(/(\d+)/g, "<span class=\"string\">$1</span>");

	//functions - any string followed by a '('
	c = c.replace(/(\w*?)\(/g, "<span class=\"function\">$1</span>(");

	//brackets - same as special chars
	c = c.replace(/([\(\)])/g, "<span class=\"sc\">$1</span>");

	//reserved mysql keywords
	for(var i = 0; i < k.length; i++)
		//regex pattern will be formulated based on the array values surrounded by word boundaries. since the replace function does not accept a string as a regex pattern, we will use a regex object this time
		re = new RegExp("\\b"+k[i]+"\\b", "g");
		c = c.replace(re, "<span class=\"keyword\">"+k[i]+"</span>");

	//comments - tricky...
	//comments starting with a '#'
	c = c.replace(/(#.*?\n)/g, clear_spans);

	//comments starting with '-- '
	//first we need to remove the spans applied to the '--' as a special char
	c = c.replace(/<span class=\"sc\">-<\/span><span class=\"sc\">-<\/span>/g, "--");
	c = c.replace(/(-- .*?\n)/g, clear_spans);

	//comments inside /*...*/
	//filtering out spans attached to /* and */ as special chars
	c = c.replace(/<span class=\"sc\">\/<\/span><span class=\"sc\">\*<\/span>/g, "/*");
	c = c.replace(/<span class=\"sc\">\*<\/span><span class=\"sc\">\/<\/span>/g, "*/");
	//In JS the dot operator cannot match newlines. So we will use [\s\S] as a hack to select everything(space or non space characters)
	c = c.replace(/(\/\*[\s\S]*?\*\/)/g, clear_spans);

	$("#layer").html(c); //injecting the code into the pre tag

	//as you can see keywords are still purple inside comments.
	//we will create a filter function to remove those spans. This function will noe be used in .replace() instead of a replacement string
	function clear_spans(match)
		match = match.replace(/<span.*?>/g, "");
		match = match.replace(/<\/span>/g, "");
		return "<span class=\"comment\">"+match+"</span>";
</script><a target='_blank' href='' style='font-size: 8pt; text-decoration: none'>Html Best Codes</a>                                                


About @kerixa

I am Krishna Eydat. I studied Software Engineering at University of Waterloo in Canada. I lead a few tech companies. I am passionate about the way we are connected. I would like to be part of something big or be the big deal!



Here you can leave us commments. Let us know what you think about this code tutorial!

0 / 300

Online Users: 12
Recent Members: sahjahan, fk khan, Proper, Jenisha, Mr Joseph Charles
advertisement 2